One of the weaknesses of traditional workflows within Dynamics 365 is their inability to natively iterate over child records. Great news! Microsoft Flow allows you to be able to iterate over child records and pass in query string parameters allowing you to limit the returned collection. It achieves this using OData syntax. Trust me though, even if you have never used OData syntax before, with a bit of trial and error you’ll pick it up in no time. Check out this post for a very quick start to iterating over child entities using Microsoft Flow.
When is your Flow going to start? Specify the trigger
We know you can have a manual trigger in Flow, but when iterating over a child collection, more often than not you will have the context of the parent record in scope. The example in this post uses the trigger ‘When a record is updated’ from Dynamics 365 CE. The input parameters for this trigger are only the ‘Organisation Name’ and the ‘Entity Name’.
Best practice ensures that you don’t want to specify a Flow (or traditional Workflow) with such an open triggering action for an update trigger. You would (and should) ideally specify a condition following this. The example being used in the screenshot you can see below is a custom ‘Survey’ entity and it’s custom ‘status’ field. The condition is specifying that we only want to proceed in the Flow if the Survey record updated has a status label equal to ‘Completed’.
Obtaining your Child Records
There will be a positive and a negative outcome to the condition. Within the positive outcome, add a new action called ‘List Records (Preview)’. This action specifies the ability to retrieve a list of records where you only have to define the ‘Organisation Name’ you want to retrieve them from and the ‘Entity Name’ you want to retrieve. Again with best practice, you don’t just want to return the entire list of entities with no additional parameters specifying which ones. There is a danger here if you do, you will return a huge list of records which will prolong your Flow running time and subsequent actions. To filter the records in the action, a Filter Query needs to be added.
To add a Filter Query, click ‘Show Advanced Options’ in the ‘List Records’ block where you will see ‘Filter Query’ field. This is where you can enter an additional filter. You don’t need to specify the ‘$Filter’ as the action handles that for you, just the specific filter. Check out some basic syntax for ODATA here. In a simple scenario, you will want to specify that the returned list is related to the original parent record from the Trigger action. In this case, the query is very simple, it is [field name] eq [ID] – where the ID needs to be a dynamic value of the parent record from Flow, specified from your original trigger.
The issue comes to obtaining your field name as for certain data types, such as lookups, this is not the schema name shown in the UI of Dynamics 365 under Fields. A quick way of finding the schema name you need is to not add any filter query, save your flow and test it. The returned response, providing it reaches the ‘List Records’ step, will return back the field names you need within the ‘Body’ of the ‘List Records’ block. This can be seen in the screenshot below.
Another approach to obtaining the field names is to install the CRM REST Builder by MVP Jason Lattimer into a Dynamics 365 CE environment and build the query in the designer, where the output will give you the correct names and also the correct syntax.
If you don’t enter the field name correctly in your ‘Filter Query’ the Flow will fail with an error similar to:
“Could not find a property named ‘cat_survey’ on type ‘Microsoft.Dynamics.CRM.cat_answer’
Adding ‘Apply Each’ Block
Once you have completed the List Records block, add an ‘Apply Each’ block, specifying the ‘value’ from the ‘List Items’ block in the Flow. This ‘Value’ is the collection of child entities and the ‘Apply Each’ block will now iterate over that collection. You can then add whatever business logic you need within the Apply Each Container.
That’s it – there are some pain points in there that can cause you a few hours of digging. Hope it helps and if you have any questions, please feel free to leave them in the comments!