How to use filter array data operation

2020-09-02

The Common Data Service connectors provides two actions to retrieve data from other entities: Get a record, and List records. The Get a record action is straightforward: provide the entity name and the GUID of the record you want to retrieve. This action always results in 1 record.

To retrieve a list of records, the List records can be utilised: provide the entity name, a select query, and a filter query. This action can result in multiple records. Then, an Apply to each control loops through each record in the list, and steps can be executed for each record.

However, what happens if we need to retrieve 1 specific record for which we do not have the GUID? We can still use the List records action with a specific filter query and a top count of 1. But, the Apply to each action makes no sense. This is especially true when multiple List records actions are present in the Flow which results in nested Apply to each actions, all in order to get multiple single records - nasty!

A not so elegant solution with multiple Apply to each actions A not so elegant solution with multiple Apply to each actions

Meet the data operation Filter array, which I touched upon in another post. This data operation allows to filter the result of a List records action. The output of the data operation can be directly used in subsequent steps, without needing an Apply to each action. Let’s redesign the previous example.

A better structure with filter arrays A better structure with filter arrays

The use of the Filter array data operation allows for a more elegant design of the Flow without nested Apply to each operations. The data operation is able to filter the array output that is created by the List records action. Now, it is possible to use conditions in the Filter array data operation, but that is only necessary if the output array has more than 1 object. In this example, we only need 1 specific record and our List records action has a top count of 1, so no condition is needed in the Filter array data operation.

Detail of filter array data operation Detail of filter array data operation

In order to use its output we can use the following formula:

body('Name_of_your_filter_array')[0]?['yourfield']

The example below maps the ‘name’ field of the filter array element, but all fields of the retrieved entity can be used.

Using the output of a filter array_ Using the output of a filter array

Bonus tip: if you want to map an identifier, body('Name_of_your_filter_array')[0]?['entityid'] will not work - but body('Name_of_your_filter_array')[0]?['_entityid_value'] will!

Common Data ServiceFlowData Operation

Sending Emails with Flow