Avoid Unnecessary Loops in Power Automate by converting Arrays for FetchXML in Dataverse

List Rows in Dataverse is a great action. It supports multiple ways to query rows in the database but I tend to always use FetchXML as I am used to this method. We use List Rows as we don’t know the guids of the records we want, so we search for all records that match the query. FetchXML has an operator called “IN” that allows you to pass multiple values and find all records with those values. But what if you don’t know what those values are or what if they need to be dynamic?

If you pass and array to the List Rows action it will create a loop, as it will loop through each one of the values in the array. But if you want to create rows based on the returned rows, you will have loops within loops, which is not best practice or good for performance. But with a few easy steps you can format an array and avoid unnecessary loops.

First, you need to check if the number in the array are 1 or more, because you will use different actions based on if there is a single value in the array or multiple. You can use the length() function and a condition to achieve this.

length(outputs('Find_Company_By_Number')?['body/sic_codes'])

In my example, I’m using my Connector for Companies house and retrieving a list of SIC Codes that I will use to update an Account record with.

If the SIC Code array contains more than 1 value, I use a Join Action and pass it the array and join it with </value>.

Then the next step, I use the replace function to add most of the tags we need.

replace(body('Join'),'</value>','</value><value>')

On the negative/no path, you want to use the First() function and pull out the value in the array that contains only a single value.

first(outputs('Find_Company_By_Number')?['body/sic_codes'])

Then we can use the List Rows action and insert the output of either of the yes/no paths into the FetchXML.

<fetch>
  <entity name="mcj_siccode">
    <filter>
      <condition attribute="mcj_code" operator="in" value="">
<value>OUTPUTS HERE< value>
     </condition>
    </filter>
  </entity>
</fetch>

As I’m not using a variable and using a compose steps instead, I have an if() statement that controls what goes in the value tags.

if(empty(outputs('MultiSIC')), outputs('SingleSIC'),outputs('MultiSIC'))

There you go, this will avoid any unnecessary loops and you can format the array in a way that can be added to FetchXML easily.

Ciao for now.