Power Automate: How to filter Dataverse List Rows Action for a case-sensitive field 

I had the task to process an Excel sheet of accounts containing external IDs with a Power Automate Flow. The account records with the IDs from the Excel sheet can be found in a Dataverse table (account table). 

Both the external IDs in the Excel sheet and in the Dataverse table are case-sensitive. 

The Power Automate Flow processes all rows of the Excel sheet. For each row, there is a Dataverse query which should find the matching record in the D365 account table. 

I used the Dataverse connector “List Rows” action to retrieve a list of the matching rows in Dynamics 365, based on the field “msdyn_externalaccountid” as filter criterium. When configuring the filter expression to match the external ID from the Excel sheet with the current account’s external ID, it turned out that unfortunately, by default, the OData filter of the List Rows action is case-insensitive, so it was impossible to find the correct account. The List rows result always contained more than one account, which had an external id with the same character sequence, but with different usage of upper- and lower-case characters. 

Documentation also confirms that all filters using string values are case insensitive. 

I tried to replace the OData style Expand Query filter by a FetchXML query as shown in the screenshot. The result of the List Rows action was still not case-sensitive when filtering by FetchXML instead of Expand Query. 

To get to the expected result – only one account record with the exact same external account id as given with the current excel row should be returned – I needed to implement a workaround. 

First step: Doing a case-insensitive comparison which returns more than one account record from Dataverse: 

This query still returned a list of account records with the similar external account ids, with equal characters but differences in upper and lowercase characters, but we will proceed this list in the next step. 

Second step: Filter the result of the first step with a “Filter Array” action. This action presents the possibility to add a case-sensitive filter of the list of account records which was returned from the “List rows” action. 

Explanation:

• Value is the returned list of the List Rows action.
• External ID is part of the returned list and is the field to be compared with the value of the Excel sheet. 
• Account ID is the value from the Excel sheet. 

The comparison now takes place between the fields msdyn_externalaccountid (which is shown by its display name External Id in the screenshot) from the account table and a field from the Excel sheet which is named “Account ID”.

The value from the List Rows output can be accessed by use of this formula:

item()?[‘msdyn_externalaccountid’] 

The output of the filter array action is now an array which contains the rows with the matching condition only. 

Luckily, this comparison is case-sensitive and with this little workaround, I was able to find the exact match record in the D365 account table, with consideration of case-sensitivity of the field external account id. 

For the further processing of the Flow, the output of the Filter array action can be used, for example the Guid of the filtered account row which is in field “accountid”. 

As the returned result is an array, I use the “first” function to retrieve the first (and probably only) element of the array: 

first(body('Filter_array_for_case_sensitive_external_account_id'))?['accountid'] 

With this ID, the correct account record in Dataverse can be updated in a later step. 

Conclusion 

As a conclusion, we have to take the result from List Rows, which may contain more than the excepted result rows, and then as a second step take the functionality of the Filter Array action which is able to filter case-sensitively. With this workaround the correct account can be found in Dataverse and we do further actions with it.