Filtering Dynamics 365 CE Fields in Flow

Overview:

Microsoft Flow adds a lot of new functionality for Dynamics 365 CE beyond the capacity of workflows. One exciting new capability is the ability to use a Parent record to access all Child records in a 1:N relationship. For example, a Flow can be triggered when an Account is modified to update all Contacts who work for that Account. Utilizing this functionality requires using the List Records Step to return multiple records at once. To make sure the List Records step only returns relevant records requires filtering the Dynamics 365 records based on their field values using OData syntax.

For general information on the OData syntax used in this step, refer to this excellent blog post on the basics: https://flow.microsoft.com/fr-fr/blog/advanced-flow-of-the-week-filtering-with-odata/

This post will review the syntax required to reference Dynamics 365 CE fields; especially Option Sets and Lookups. First some things to keep in mind:

• When hard coding values, numbers do not require single quotes around them, but all other fields do – including Primary Keys.
• Even though the Flow steps and Dynamic values use the “Display Name” for fields, OData queries use the “Name” listed in the solution view. Only lookups require more than the “Name”.
• All characters in OData field names are lower case, but field values can be upper or lower case.
• The basic strategy for getting child records is to use a List Records step with a filter on the value of the lookup field to the parent record to only include child records with the right parent.
• The Flow options for Order By, Top Count and Expand Query use the same syntax as the Filter Query.
• Looking in your organization’s “Default Solution” is a good place to get field names. You can also look in Flow itself in the “OUTPUT” section of a Get Record step in the execution history for a Flow.

Option Sets:

Option Set fields will return the value of the option set instead of its label. For example, the default Account field “accountcategorycode” (Display Name – Category) returns 1 for “Preferred Customer” and 2 for “Standard”. So when filtering based on an option set, be sure to reference the value, which is again visible in the Default Solution. Although the Option Set label can be returned by Flows, you can’t use it to filter. Example:

Lookups:

Lookups are referenced in a unique way. Instead of just the field name from the Default Solution, they require starting with “_” and ending with “_value”. For example, the Contact entity field “parentcustomerid” (Display Name – Company Name) is referenced as “_parentcustomerid_value”.  The leading underscore will be in front of a prefix, if there is one.

Another thing to remember is that the system does not store the name of the record in the lookup value, it stores the Primary Key. So instead of querying “_parentcustomerid_value eq ‘A. Datum’ ” you would query “_parentcustomerid_value eq ‘ad6b3f4b-1be7-e611-8101-e0071b6af231’ ” as in the example below. If you want a hard-coded value to lookup, you can find it by doing a List Records step filtered by another field and checking the value from the Output. You can also use the Level Up for Dynamics CRM/D365 add-in “All Fields” option among other ways.

However, the more common approach would be using a Dynamic Value provided through earlier steps. To do that, select the field and scroll down for the Primary Key field of an earlier step. Note they are organized by the step that the Dynamic field was generated in, so try to name your steps logically.

Using OData filter queries on Dynamics 365 CE fields enables exciting new functionality without requiring code. These basics can help you get started writing Flows of your own to do things like updating child entities, emailing all resources assigned to a Project Activity and much more.

Sam Stern

Sam is a Senior Consultant in RSM's Dynamics practice. He has experience working with many aspects of Dynamics CE as well as integration technologies like Scribe and SmartConnect.