Scenario : Working with Array Filters, JSON and Variables in Loop using Dataverse in Power Automate

[Music] hi welcome to another video in this video we'll discuss an important use case in power automate where we have a data source here our data source is datae then our data verse uh we have a table whose name is Dr in information Dr daily information here we are storing doctor names and in next column we are storing patient name and in third column we are storing patient count okay so the uh information is like we can define a new doctor name here or an existing doctor name here with same name if uh the doctor uh let's say Sant Kumar is the doctor and this doctor treated two patients means I have two records here with same doctor name but different patient name and each line the patient count is one okay so here we can have duplicate uh doctor names but patient name be patient name may be different and patient count is always one okay the point here is if your data is stored in such a way then how we can uh read all this data and send a consolid table uh to an administrator or to someone with properly formatted email for example if it is showing two records for a doctor that means it should display in the table one column srikant Kumar and count is two we can ignore patient name right now so the requirement is uh the email should contain only the doctor and how many patients uh he has treated for that s same day okay so this is how you can achieve using array filters and array manipulation uh and we will use gestion we will use fil um ion ship and we'll use Loops in power automate okay so let us uh understand how the uh Power automate is done I have created a recurring workflow scheduling uh scheduled power automate to run every uh 7 p.m. everyday in time and the first step is when the power automat will run it will get all the doctor information daily information records from this table right so this table name is Dr daily info so here I am using a uh list row action using data Force connector and give him the table name and use a p XML to read all those dror daily information records created today okay so it will only consider the records which are created today whenever the power automat will run if other day records are there then it will ignore once it is retrieved so it will retrieve all these information SP doctor name and patient name and patient count next we have taken a array variable initialized as blank array with uh blank Square bracket means uh it is an empty array okay and I have selected I have given taken a string variable whose name is Doctor name we'll discuss how these variables will be used later on next we have taken another variable whose type is object I want to create an array variable and each item of the array should contain a g item G object that's why I have initialized a g object variable with this structure craes start and Coles end First Property doctor name initialized as blank second property patient count initialized as zero so this is the initialization of the G object next we want to do a apply to each Loop or Loop this Loop will help you to go through all the records available in this list row and process what it will process it will keep adding the items to array the array is declared here already right so you can add this apply to each Loop and inside that select the list of items value object which will which the loop will go through then here the condition is if doctor name does not contain the record doctor name that means when the first iteration will come the doctor name variable which is the string variable is blank so first for when the power automat will run first time it will check if the variable value is blank or not if it is blank and it not equal to the first records name field value then it will create new record or it will add the item into the array right so this branch is for new doctor record and this branch is for existing doctor record okay because here in data first time when it will process for this record it is a new record but when it will process second record no check for second one for this record when it is entering it is first time it is new new record but when it will process the second record it is the same doctor so it will go to the no Branch okay and process uh manipulate the array so if this is a new doctor record what it will do we are assigning the doctor name variable with the record doctor name current records doctor name because we need to assign the variable value so that in next iteration we can check here right otherwise if you do not assign here doctor name variable value remains blank always so for new doctor check the condition and as because first time doctor name is blank it will come to the uh yes condition because it is not matching name contains some value and do doctor name does not conal value so this condition is true so here the first step we are assigning the name value to the doctor name variable next we are setting the value of the gestion object you know that we have defined here gestion object and initialize it as Doctor name blank and pent count zero and this time we are setting the value of this variable as Doctor name the variable value and patient count one because this is one patient record one do patient record that is treated by the doctor that's why it is patient count one once the value is dynamically generated Z value you can use append to array variable step and select the array and choose the previous object the previous object is G doctor patient count this is the variable that you need to append so this way if you have unique doctors here without duplicate doctors every time it will go to the this branch and keep appending the item into the array object or array variable but if there are multiple doctors records are there then it will go to this Branch so next iteration it will come here and it will check if the doctor name is equal to is does not equal to current records doctor name or not now let us understand first it will it will come to this record for processing okay now this record for this record it is a new record so it will create the new record and happen to the array next it will come to the second record second record is a different doctor so again it will it is true it is not equal to the so previous doctor is uh shanj and this doctor is shant so does not me so it is again a unique record so it will create but when the third iteration will come the third iteration name and the second iteration name is same so as because it is same it will come to this branch and in this Branch what we are doing we are doing array manipulation what is the array manipulation we are filtering the array first step we are doing is setting the doctor name variable with the current records doctor name right this value we are setting to the variable first then we are filtering the array using filter array data operation action to filter the array to get only the items where doctor name is equal to variable name because already the main array contains all the items which are already created already doctor records which are already created now I just want to extract the or filter the array where the doctor is matching doctor so that this filter will create another filter which will contain only the items of matching doctor name like that I will create another filter array action step which will extract another array with a filter doctor name does not equal to doctor name okay this is the property and this is is the variable name So currently I have three array one is the main array which is Doctor patient uh which is the doctor patient count array which the main array this is another array which only contain the matching doctor name and information and this is third array which contains non-matching doctors list or count it's because we have now three array Our intention is to set this no matching doctor counts or doctor count records that means the array now the actual array contains these three records main array contains these three records now I am extracting two filter array one filter to contain uh to contain matching records matching record means only this record okay where it is matching where processing third record but there is a matching record this so one filter array contains This Record only and another uh array which contain this two records this one record okay so this is non-matching record and this is matching record and the main main array contains this two record only now we are processing this record right so what we will do once we have three array using filter array third step we are doing updating the non- matching records or removing the matching records from original array so what we'll do so this is done through set variable if you replace if you want to replace main array with a an item of the filter array then you can use this set variable array with not matching record so choose the main array and use the value from Dynamic content which is from the body output of not matching doctor's filter ARR this body is assigned here now at this moment what it will do the main array which was previously containing these two records now will contain only this record because we have overridden the actual original a array with no matching filter array why we are doing this because we have to update this filter array item and once it is updated with new patient count we will append this updated value with updated paent count to the main array so go here and next step is to update or to Define an item with updated patient count so as because the doctor is already there record is already there it is existing we have to increment the patient count so to increment patient count I have taken a compos action step and here I have written this expression this expression is using set property set property expression can be used to update an items property it is it can be a gestion also so our gestion is this one variable Gest doctor patient count this is a variable so I am updating this variables property which property name is patient count let me copy this and explain you here okay now we are set setting the property of G variable and update in the property patient count with this value this value this expression adds the existing patient count of the existing doctor which there is a match and adds one to the existing paent count right so we are converting the we are getting the first item from from the filter array with matching doctor and converting the uh patient count attribute or patient count property to integer then once it will get the item we are adding one so this will update the patient count with incremented value by one with the existing patient count for that doctor now this is an item that can be appended to the original array because the original array is now this one so we have to append this record with updated patient count right because we want to roll up all the patient counts for a specific doctor now it should add now the new item will contain srikant Kumar but patient count is two because previously one now it is one so it will combine and create one more item in the array and append the new item output of of the compos to the original array so that finally the final array will contain Sanjay one count and Sant two count because the third third item is the duplicate of the same doctor existing doctor just increment the count okay and third is a unique one which has no matching record so it will create so there only one right and finally we create a HTML table from the uh we have used create HTML action step from data operation and used the pr as the array dror paent count array columns to generate automatically and we are doing sending email and Define the output of the create HTML step right so this is how you can filter array and uh update array and update properties using set property right so let us run this and test this I have this much record so I'm expecting in the email it should display shanj 1 srikant 2 sudhir 1 right patient count so let me test this now it will it is running it is extracting the doctor info entity and it will send an email so let me open the email let me go to that mailbox where I have mentioned the uh inbox now here if you click on the email the first email you will see the information Shanay doctor name patient count one s doctor name patient count two sudhir doctor name patient count one you see guys now it is rolling up the values a single doctor treated how many patients okay so this is how you can uh manipulate array for this type of data source if it is stored somewhere in Excel also whatever data source whether it is Excel or SharePoint or data verse you can manage using this logic I hope you understand how it works thank you everyone see you next time [Music] bye

Channel: Softchief Learn

Views: 1,011

Keywords: Dynamics, CRM, Dynamics 365, Power Apps, d365, power platform, dynamics 365 tutorials, dynamics crm training, Scenario : Working with Array Filters, JSON and Variables in Loop using Dataverse in Power Automate

Id: 0kHVVoPknEg

Length: 18min 47sec (1127 seconds)

Published: Tue Oct 17 2023