Personalized Community is here!
Quickly customize your community to find the content you seek.
‘Better Together’ Integration forum available
We're launching a how-to forum where you can learn and engage about how Dynamics 365 integrates with other Power Platform products.
Read about Better Together forum
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
Early this year a wrote an article on how to send an email with an attached excel file containing all leads created during the week, kind of like a summary report of all new leads.
This time I wanted to go the other way around, and actually create CRM Opportunities from an excel file.
Let's say that we have an excel file containing potential opportunities. This file might have been sent from partners all over the world as they don't have access to our CRM system.
But the file contains 200 rows of records, so we have to either created them one by one, or take the information we need, create our own template, and use it for bulk import in CRM.
But what happens if for some of the records we don't have an Account or Contact in CRM? The import will fail for these records and we have to create the Accounts/Contacts first before we can try to import them again.
I came with the idea to create a process using Power Automate which actually goes through an excel file, and checks if for the given email address (this is what I'm using as a unique identifier for Accounts and Contacts) there is an Account and Contact, if yes creates a new opportunity and add them there. If there isn't an existing Account and Contact, then it creates one and then proceeds and creates an opportunity and adds the newly created Account and Contact.
First, let's start with a trigger point. In my case, the flow will be trigger via a button as whenever our salespeople want to upload the opportunities from the file provided by our partners, all they need to do is start it using the Power Automate Mobile App
Then we need to Get rows, basically, go to each row from the excel file.
These are the rows. You can add as many as you need, but for this article, I kept everything simple and added only the required fields for Account, Contact, and Opportunity entity in our CRM.
The next stage of the process is to check in CRM for existing Account and Contact in CRM using the email address from the excel file.
But we also need to make sure that the flow confirms if they exist or not, so we will add an extra step to get this confirmation.
So we add a step List records, and right after that Compose step.
For the List record step, we select the Environment, Entity Name (the one we will be searching for), and the filter criteria. For us, this is the email address from the excel file, and we want to search for it in the emailaddress1 filed in CRM.
For the compose step we need to insert the following formula:
if(greater(length(body('Get_Account')?['value']),1), true, false)
So basically we are asking the flow to return true of Account exist, and false if it doesn't.
I'm going the same but this time for the Contact entity as I also wanna check for existing Contact.
If you remember my advice, you should stop here and test the flow, before going deeper into the logic. So let's test it and see the Compose step Output results.
As we can see for the first row the flow returns details about the account found in CRM. And the Compose step Output returns a true value.
Now for the non-existing one, as we can see there is not Output details, meaning that the COmpose will return a false value.
If you are referring to a lookup field in your excel file, such as Account, Contact. Currency, etc. you need to have a get step for each of the lookup entities, as we did for the Account.
Here you can see that I have quite a lot of lookups to check using the filter query and the value from the excel file.
So far so good. It's time to tell the process now what to do with the true and with the false values.
This is easy, we are using a Condition step with two filters (as I'm checking for existing account and contact)
Condition one is the Output for Compose for Account to be true or for condition two the Output for Compose of Account to be also true.
You can group them together and say that both have to be true, but I'm using OR in this scenario.
Now if either one of the above conditions is true, I want to create a new Opportunity.
We select the environment and entity name first and then start adding the rest of the required information.
The string values we can get directly from the excel file as they are not referring to another entity. That values are Estimated Close Date, Estimated Revenue, Topic, etc.
However, for the lookup one, we've already Get them using the List records step, and filter them from what we have in the excel file.
So the only thing left here is to add them to the opportunity.
When using Lookup values make sure AWAYS you are using the unique identifier of the record in your system or (GUID).
I know most people will rush to put there the Name or email address, but trust me this is not a unique identifier for the system, it is the GUID.
So for Account, I put Account filed from the Get Account step, for Contact, Contact option from Get Contact step, and ...... you got the idea.
One more important thing to REMEMBER, in case you are adding a specific owner, apart of the fact that you need to have a List Records step to Users entity, when adding the output to the Opportunity, you need to select an owner type, as the owner can be either user or Team. If you have a team the List record will be to the team, for User will be Users entity.
Now for the false values from our Condition step, we need to first create Account and Contact, and then Create a new Opportunity. Everything here is the same as what we did for the Opportunity, but we need to create an Account and Contact first.
First the Account.
Then the Contact.
And last but not least, the Opportunity.
That's pretty much everything. If we test it we can see the following:
For the one that row where the Account and Contact already exist in CRM, the process just moved forward and created a new Opportunity.
For the second row, where neither the Account nor the Contact exists, the process creates a new Account and Contact and then add them to a new Opportunity.
Feel free to contact me if you have any ideas on how we can improve this flow or suggestions for other automatization I can write about.
Business Applications communities