Power Automate Checks Before Creating a New Child Record in Dataverse

Automation, we all think about it. Especially in this unprecedented era of Generative AI advances. When it comes to creating elegant user experiences with Dynamics 365, it is important we identify what part of the business processes and data input can be automated.

It takes a village to create Minimum Lovable Products out of complex CRM processes. One classic example is the complicated parent-child relationships we create in our data model and table forms.

A use case that I recently looked into included the creation of a subgrid line item (child record) when certain conditions are met e.g., a business process flow stage is met, columns are populated with a specific value etc. You may also remember the automation checklist solution I had put together previously to digitize processes which cannot only be implemented by business process flows.

Wait a minute, you are telling me something else is needed?

Well, in this particular use case we had a Dynamics 365 Sales instance/ Model-Driven App where we were triggering the creation of multiple rows through a toggle.

But what happens if users go back and forth with this toggle? Whether it is by mistake or sheer confusion, it is a great enhancement to add a check as to whether there are any lines already in the subgrid.

How does this extend in other use cases?

It is common to automate the creation of child records partly or completely. Whilst it speeds things up, we must consider what happens if a child record exists already. Do we want to keep creating new records or stop?

So how do we check a subgrid does not have any line items already?

Or, in other words, how do we check the parent record does not have any children when it comes to specific tables within out Dynamics 365 app?

We can use a 2-step approach in Power Automate to enhance our existing flow. In this case, the existing checklist automation flow is described here.

Step 1:

We will first add a List Rows action after our initial trigger. We'll need to filter the Battle Checklist records we are retrieving to include only those related to the Battle table. We will do this with FetchXML into the Fetch Xml query section.

The key condition we need in this query is to say that we need to match the foreign key for Battle look ups (ap_battle) from Battle Checklists (child table) to the primary key of the record from Battles (ap_battleid) (parent table).

Depending on your familiarity with FetchXML, you can either write it yourself, use XrmToolBox tools or through Advanced Find and the old or new UI once you set up the right filters.

Word to the wise: As great as these tools are, we should always leverage our programming best practices to optimize our flow run. This is particularly important when our database is much bigger and retrieving child records could significantly impact your system performance AND business operations.

A non-exhaustive list of questions and tips to get your started with a big bang:

• Reduce the use of distinct="false": This is the default setting anyway and can usually be ignored unless we specifically need to ensure there are no duplicate records.

• Minimize attribute retrieval: By default if we download that Fetch XML query, we will notice quite a few attributes being retrieved. We should investigate whether we truly need them for our flow. Or maybe they can be removed and make it faster?

• Limit the number of records returned: If we only need the top few records, we can consider using the top attribute in the fetch node to limit the number of records returned. For example, if we just wanted at least one row return to establish there is a parent-child relationship, we will add at the end of the fetch node top="1". In this case we have a small database with a handful of rows so it will not make a massive difference. However, in enterprise databases this becomes a scale or fail matter.

  
  
  
  

A big shoutout to Yannick Reekmans for giving me a healthy challenge to optimize this flow to the max!

So to summarize, in this step, we select the child record table in Table name which is Battle Checklists. We will also add our query as you can see in the screenshot below.

Data hygiene should always apply when it comes to naming your actions’ titles. However, please bear in mind that whatever title you choose will impact the expression of step 2.

Step 2:

We will now introduce a condition to say that if the results of this List rows action do not return anything (i.e. that specific record which triggered the flow does not have any associated subgrid lines) then do create a new line. Otherwise, stop there.

The condition will be the expression empty(outputs('List_rows')?['body/value']).

Let's break down these theoretical components:

• outputs('List_rows'): This part refers to the output of our step 2 action named List rows in our flow.

• ?['body/value']: This is retrieving the value property from the body of the output of the List rows action.

• empty(...): This function checks if the retrieved value is empty. Thankfully, if that body does not exist, it does not throw an error; just comes back as null and thus the empty (…) expression will come back as true. Hence the condition!

As always, we rename our condition to explain what this step is doing. Love your fusion team, fellow makers and admins!

In our Yes branch, we will move our pre-existing part of the flow (existing List rows and Apply to each actions to create the new Battle Checklist subgrid line into the Battle record – all previously explained here). Remember, we can drag and drop!

In our No branch, we will use a Terminate action with a Status of Cancelled. This will mean that if our flow does return values from step 2, then the step 3 condition will not be empty and thus a new subgrid line will not be created.

And this is it my friends, may the power of the platform live long and your automations prosper!