When you start to use Microsoft Flow To automate tasks and improve the productivity of your environment, one of the most common actions is data searches supported by filters. Whether you are focused on going through Dynamics records, listas de Sharepoint, tables in a SQL database, etc., one of the disadvantages of Flow connectors is that, as a rule, they do not offer the opportunity to search for a single element filtered by a field other than its identification field (Id).
When we need to filter an item, but we don't have this identifier, we have to resort to getting all the rows and applying a filter to it as follows:
Example of getting data based on a filter in Microsoft Flow
In this example we can see that using the action Get rows from the section SQL We can set not only the list of all the rows of a table, for this case TProvincias, but we can filter these query results. For this case, the filter is configured to return a record that matches an Id previously obtained in the flow that is not the primary Id in this table. At the same time, as we are clear that this query will only return one record, we limit the result to this register by assigning the first position to 1.
Here comes the problem, if we try, as an example, insert this province that we have obtained within a Dynamics record with the action Create a new record the following will happen:
The moment we assign one of the values retrieved from the previous step to one of the fields of the record creation, a repeating condition will be automatically generated between all returned values.
This happens because being a retrieval of rows, the output of the Get Province step will always be a result matrix.
This places an additional burden on the procedure that you understand that you will have to navigate between multiple results and also an additional complication when it comes to understanding the procedure that the Flow will perform..
To avoid it, whenever we are obliged to use an action with this type of output, we can resort to data operation To compose or in spanish to write.
This action will allow us to keep those items from the previous output that interest us, for this case we can filter that only the first (and only) element of
returned array.
For this we will have to use the expression first in the advanced expression editor indicating where we get the data from and what specific value we are looking for, for this case it will be the description field of the output value of the Get_Province action.
first (body (‘Get_Province’)?)?
Now we can check how, when using the output of this action in our Dynamics record creation, the loop will not show, leaving a cleaner structure and improving processing time.
As we can see, we don't just save this loop, but also the nested loops in case we need to go through more than one table in search of these records. Now we can run the procedure and check the result:
Achievement of provinces
Running the Compose action
This useful action of To compose, not only will it allow us to maintain a better structure in our Flow, It will also allow us to model the outputs of all kinds of actions to obtain those results that we need, ignoring those we don't, in this way it becomes a magnificent solution for multiple situations. throughout our journey through the world of automation with Microsoft Flow.
