Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
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 | Talent TechTalks | Upcoming TechTalks
Microsoft Flow and a flat file (CSV) of data can provide a cost effective and quick way to integrate with Microsoft Dynamics 365. On the face of it, it seems simple. Chuck a CSV file at Flow and get it to add data into Dynamics 365, right?
It is not quite that simple, until you know what you are doing. This is because, like all other applications, Microsoft Flow and Dynamics 365 have their quicks. Continuing on on from a previous post Dynamics 365 Integration with Microsoft Flow, this article provides a step by step guide on how to build a Microsoft Flow which integrates data from a CSV file into Dynamics 365.
The article covers the following:
For this scenario, a source system needs to provide billing data to Account records in Dynamics 365. This enables the sales reps to have a more complete view of the customer record and empowers them to discuss credit limit or payment terms while visiting the customer.
The integration process is as follows.
The screenshot below shows the Billing section on the Account form and the fields that will be updated from the CSV file, via Flow.
The data has been deliberately chosen for this example as it shows five different Dynamics 365 data types. Each one needs to be handled differently by Flow.
A document location was set up in SharePoint to hold the CSV file. This is the location that the Flow trigger monitors.
The Flow starts with SharePoint’s When a file is created in a folder trigger. This means that when a new file is created in a specific folder the Flow will start runnin.
However, other triggers for the SharePoint connector could be used, such as When a file is created or modified in a folder. The trigger you choose depends on how the source system delivers the CSV file.
After the trigger, the data is extracted from the CSV file into a format that can be processed easily by Flow. This is done with two Compose actions. The first one extracts the text from the CSV file and the second puts it into an array of rows using the split function.
There is an issue with Flow when working with the split expression using the new line character (‘\r\n’). For some reason you cannot use the Expression builder but must type the expression by hand into the Input box. See the example screenshot for the Array of Rows compose action below. It must include the double quotes at the start and end. Thanks to Serge Luca for this great post which points out the solution to this issue.
The output from the Array of Rows looks like the following.
It would be nice to be able to parse a CSV file more easily. There is a Flow Ideas post here . Vote if you want this feature added to Microsoft Flow.
Variables are initialised at this stage. This is because the string data types in the CSV do not match the data types of the fields in Dynamics 365.
A. An Apply to each action is used to loop through the rows of the CSV data. The input to the Apply to each action uses the skip function to skip over the first row of CSV data, which is the header. The expression used is…
B. Flow uses a Condition action to check that the CSV data row contains data. I found that Flow seems to think there is an empty row at the end of the CSV data. This causes the Flow to fail.
If the Condition action results in Yes then Current Item will contain a value that is one row of the CSV file. It will look like…
C. I like to use Scope actions to group Flow actions. They are not mandatory but they are good at keeping the Flow clean and add more commentary about what the Flow is doing.
As discussed in my previous post, Dynamics 365 Integration with Microsoft Flow, the different Dynamics 365 field types need to be handled correctly. This means that the data must be transformed from the CSV string values into data types that match Dynamics 365.
The Payment Terms option set, Credit Hold two option set and Currency lookup all have to be transformed from their string value.
The Payment Terms column in the CSV data is a string value. The Payment Terms attribute in Dynamics 365 requires an integer value. The following table defines the mappings.
A Switch control is used to implement this transformation in Flow, with a case statement for each of the possible Payment Term values. The input to the Switch control is an expression with includes a split function to get the Payment Terms text from the CSV data.
Basically, the split function splits the text by “,” to produce an array of strings, and then returns the 4th string in the array. Note that the first item in the array is 0, hence the value of 3 being used for the array item.
For example, Net 45 is the value that the split function above would return from the CSV row below.
The Credit Hold column in the CSV data is a string value which is either “Yes” or “No”. The Credit Hold attribute in Dynamics 365 requires a Boolean value (true or false).
Again, a Switch control is used to implement this transformation in Flow, with a case statement for each of the possible Credit Hold values. The input to the Switch control is also an expression with includes a split function to get the Credit Hold text from the CSV data.
It is important to handle the Default case of the Switch action. This occurs when the input value is neither “Yes”, or “No”, in this instance. This means that the CSV data is not what is expected and an error should be logged and a notification sent to an administrator to resolve.
The Currency column in the CSV data is a string value which represents the currency code. The Currency attribute in Dynamics 365 is a lookup field to a Currency record. Flow must look up the Currency record ID (GUID) from Dynamics 365 in order to set the Currency look up field on the Account entity.
I found the post Alternative approach to check for a single Dynamics record in Flow helpful in getting a single record from Dynamics 365 without knowing the Dynamics 365 GUID. However, the method below is slightly different.
A. Flow first uses the List Records action from the Common Data Service connector. Note that the Dynamics 365 connector is deprecated and the Common Data Service connector should be used to connect Flow to Dynamics 365.
The currency code is stored in the ISO Currency Code field on the Currency entity in Dynamics 365. Therefore the Filter Query used by Flow to look up the Currency record via the Currency Code is as follows.
isocurrencycode eq 'split(items('Apply_to_each_CSV_row'),',')'
isocurrencycode eq 's
The output of the List Currency records step is a list of Dynamics 365 Currency records. However, the list should only ever contain one record.
B. Flow then determines whether a Currency record is found using an expression that checks whether the output list is empty or not.
C. If a Currency record was found in Dynamics 365 then Flow uses the Apply to each action to loop through the returned Currency records. Of course, there should only be one Currency record returned.
The data input from the CSV file is now transformed into a format which Dynamics 365 understands. Following this, the Account records in Dynamics 365 can be updated with the Billing data.
Flow first looks up the Account record in Dynamics 365 based on the Account Number in the CSV data. Similar to the Currency record look up, using the List records action for the Common Data Service connector. The Filter Query used is…
accountnumber eq 'split(items('Apply_to_each_CSV_row'),',') '
accountnumber eq '
Again, similarly to the Currency lookup, Flow checks that the Account record is found. The expression used is…
If a matching Account record is found in Dynamics 365 then Flow uses the Apply to each action to loop through the returned Account records. Of course, there should only be one Account record returned.
The variables that were initiated and populated earlier in the Flow are used to update the Account. Except for the Credit Limit. The Credit Limit is already in a format that Dynamics 365 can understand (a number) so it does not need to be transformed. Therefore, the Credit Limit is set straight from the CSV data using the following expression.
And we are done!
In the end this article became quite lengthy to explain what should be a very simple integration. The integration is not perfect and integration specialists\perfectionists will see holes all through it. However, I do believe the Flow is a great integration tool for Dynamics 365 for certain scenarios where low cost and ease of build and maintenance are must have requirements. For instance, prototyping or non-business critical integrations etc.
In addition, check out a previous post Dynamics 365 Integration with Microsoft Flow for my thoughts on when Flow is an appropriate tool for integrations with Dynamics 365.
The post Microsoft Flow: CSV Integration with Dynamics 365 (Example) appeared first on Dynamics Citizen Developer.
Business Applications communities