Check if records exist or don’t exist with Power Automate
One of the most common scenarios for businesses is to check if a record exists and if it exists do something, if not, do something else. You could check if the record exists and if it does, update it, if it doesn’t create it. I say it’s one of the most common scenarios because, not only do I use this all the time, but it’s actually the first Flow I created for a production environment. A friend asked me about this recently so I thought it was a good idea to document and share this method. The example below is for CDS/D365 but this method can be applied to other structured data systems.
Scenario I have an Opportunity where I can specify a Contact and Account. I know both will be in the system but maybe my data integrity is not that great and my Contact might not be associated with my Account. If I put them both on an opportunity, I want to make sure they are connected/related. But I don’t want to update it if they are already connected, because that is an additional change to my database/API call. We can use Power Automate to do this.
Start by creating a new Flow in a solution as we will be using the Common Data Service (Current Environment) triggers and actions.
We’ll create the CDS trigger “When a record is created, updated or deleted” and we’ll set this to run on create of an Opportunity and update or either Account or Contact fields.
Next we use a List Records action running on Contacts and we’re going to look for contacts where the Contact matches the contact on the Opportunity and where they Parent Account is null.
This will bring back all records where this matches. In this example it will either bring back a single record or no records. This example can be expanded, the important part is checking how many records come back.
Next we can create a condition control and we can create a true/false path. In the left hand side we can put a function of length() and this function counts what you put into it and returns a number. In our example we will put length(List Records) and this will return the number of records which are returned in the List Records action. The exact function is: length(outputs('List_records')?['body/value'])
Then in the condition we can configure how we want this to work. In my example below, I chose to configure this where if we do return a record, we will go and update it, if we don’t return a record, we will not do anything. So we say length(List Records) is greater or equal to 1, go down the yes/true path, if 0, go down the no path.
Going down the Yes/True path we will go and update the contact record, associating the Contact to an Account.
You can use this tip of finding the number of records returned, to list and create connections between records if they don’t exist, you can check if appointments exist in outlook and D365 and remove/warn people of double bookings. This has a lot of uses, so I thought it was important to document this.
Ciao for now