Today we are going to upload data from an excel sheet to a table “Training Attendee” in Dataverse for rows whose Member ID (in Contact table) matches with the table “Contacts” in D365.We are considering the following types of data types to be uploaded:
1. Single Line of Text
2. Whole Number
4. Option Set
5. Date & Time
Firstly, my excel sheet looks like this:
It shows the Members who have done the Certifications for the given 3 courses and its associated details like Certification Number, Expiry Date & Grade. The Member ID is a unique identifier for identifying the contact.
& a Snapshot of the flow I created is as follow:
Now let me make you go through it step by step.
Create a flow and type initialize in the search bar and select “Initialize Variable” under “Action”
First, we initialize 3 array variables for storing the data of the 3 courses separately as follows:
Then we list the rows present in the excel sheet (make sure here that u have formatted your excel sheet as table otherwise in the Table section you won’t get an option to select). Choose your source location of excel and the required path.
Now for each row of the excel, let’s apply the condition:
Here we are checking if the Certificate Number1 & Member ID are not blank, and if it’s not blank, then we are adding the Member ID, Certificate Number1, Expiry Date1 & Grade1 from the excel into Course 1 variable array. Similarly, we will do for Course 2 & 3. You can select these dynamically from the drop down and append to array in the JSON format as shown in the image.
Now compose to save the array and select the variable to be composed. The details of the Members who have done Course 1 are listed now.
Apply to each row of this output and match it with the Member ID present in contact database by filtering rows by:
column_schema_name eq ‘items(Apply_to_each_2’)?[‘Member ID’]’ as shown below:
You will only get one row but the output can be more than 1 – hence it comes as Apply to each. Once the matching Member ID is found, it adds a new row to the table “Training Attendee” and add the required columns.
1. Single Line of Text [Certificate Number] –> (‘Apply_to_each_2’)?[‘Certificate Number]
2. Whole Number [Rank] –> int(items(‘Apply_to_each_2’)?[‘Grade’])
3. Lookup (Attendee-Contacts) –> contacts(items(‘Apply_to_each_3’)?[‘contactid’]
For Lookups , it works like “plural_name_ of_that_table(guid)”
PS: You may select these items from the Dynamic Content. Be careful from which step are you choosing the result value of. In the above example, for 1 & 2, we are taking the result from Step ‘Apply_to_each_2’ but for 3, we are choosing the result from step ‘Apply_to_each_3’.
4. Option Set/ Two Options –> Simply select from the dropdown list available.
5. Date & Time –> The approach is a bit different for this datatype- will be explained in the upcoming blog.
& the Power Quote of the day is:
“You’re braver than you believe, and stronger than you seem, and smarter than you think.”