Using Filter Array, Loops, Variables and SetProperty Scenario in Power Automate

In this Post, you will see how you can use Array manipulation using Array Filters, Loops, Variables and SetProperty Scenario in Power Automate.

Scenario:

We have the below records in the table. There may be multiple records for a single doctor with patient count 1 for every row.

The requirement is to send a consolidated table email containing unique doctors and total patient count. Like below.

Solution :

Follow below steps

Step 1 : Define Trigger

Here Scheduled Flow taken. You can take any trigger.

STEP 2 : Get All Records From Dataverse Target Table

Use List Row Action to read all records from target table using FetchXML. You can use Model Driven App Advance Find option to download FetchXML.

STEP 3 : Define Variables to manage string and Array

Define a String variable to store Doctor Name of iterated record in loop so that we can match if the doctor record is new entry or duplicate record.

Take another Array to store items of a JSON object which stores both Doctor name and patient count.

STEP 4 : Define Variables to initialize JSON variable

below json to be used.

{
  "Doctor Name": "",
  "Patient Count": 0
}

STEP 5 – Use Loop

This Loop will run and iterate through all records present in the list retrieved in Step 2. First add a condition step to check if the Doctor name variable is not contain records Doctor name.

If New Doctor record go to YES branch else Existing Doctor go NO branch.

In Yes Branch add below steps.

1. First Set the Doctor name variable with the current records
2. Set JSON object variable with Dynamic Value {“Doctor Name”: @{variables(‘Doctor Name’)},”Patient Count”: 1}
3. Append the JSON object to Main Array using Append to array variable

In NO branch use below steps.

1. Set The Doctor name variable as Current Record doctor name
2. Add a Filter Array Data Operation and in the From property select the main array variable and in next line add Doctor Name property using this expression item()?[‘Doctor Name’] and in operator choose is equal to and value to select is doctor name variable or in advance mode you can edit and add this expression @not(equals(item()?[‘Doctor Name’], variables(‘Doctor Name’))) . Rename the step as Filter Array for matching doctor.
3. Repeat the previous step but use operator not equal to, so that we can get two new array one contains not matching doctors and other matching doctors items and also we have a main array previously which contains all items. Rename this step as “Filter array with not matching doctor”
4. Now Update the Main Array with the array from this array “Filter array with not matching doctor”
5. Add a Compose Step to update the Patient Count by adding old count value by 1 using this expression. we will use here SetProperty expression so that we can modify Patient Count property in the JSON. code given below.
6. Next Append the compose output to main Arrary.

The below is Set Property Screenshot. use this code.

setProperty(variables(‘JSON Doctor Patient Count’),’Patient Count’,add(int(first(body(‘Filter_the_Array_with_Matching_Doctor_name’))[‘Patient Count’]),1))

STEP 6 : Create HTML Table

Watch the full video Here.