When there is a requirement to match the rows from two arrays using a
particular property value, you can use the Filter Array action to
get the matching object from the array. In the scenario below, you will
see how it can be used to get the matching row from the List Records action
output based on the value of the current row.
Scenario
To demonstrate the capability of the Filter Array, I will use the Opportunity, Opportunity Line, Quote, Quote Line and Product tables within Dynamics 365 Sales.
When creating an Opportunity Line for an Opportunity, Users can manually enter the Manual Discount for each Opportunity Line. A Quote can also be created from an Opportunity which copies the value of the Manual Discount from the respective Opportunity Lines when creating the related Quotes Line.
The requirement for our scenario today is "When the Manual Discount in an Opportunity Line is updated and the user checks the flag to refresh the data, the system must update the respective Quote Lines with the new value of Manual Discount from Opportunity Lines. Now that is going to be a challenge as there is no direct connection between the Opportunity Line and the Quote Line as you can see in the Entity Relationship Diagram below.
To
get the
Manual Discount value from the
Opportunity Line row, the
Existing Product can be used the match and get the corresponding
Quote Line. The assumption for this scenario is that there will not be more than one
Opportunity Line for each
Product. If there can be multiple
Opportunity Lines with the same
Product, the values of multiple columns need to be matched to get the rows. But for the sake of simplicity in this scenario, let's assume that all Opportunity Line rows have a unique product. Let's see
how you can easily get the corresponding
Opportunity Line row using
another
List Records action.
Nested List Records to Retrieve Matching Row
In this flow, the matching
Opportunity Line is retrieved for
each
Quote Line row by using another
List Records step
with
Opportunity and
Existing Product as a filter
query. I then use the first() row of the output to get the
Manual Discount value and update the
Quote Line. This solution fulfils the
requirement but it will take time to query the data with
List Opportunity Lines steps for each
Quote Line especially if there are dozens of
Quote Line rows in one particular
Quote. This solution can be optimised further by using the
Filter Array action instead of querying data in the loop.
Filter Array to Retrieve Matching Row
In this flow, all the
Opportunity Lines of the
related
Opportunity are retrieved before the
Apply to each loop. For each
Quote Line, get the
Manual Discount value from the matching
Opportunity Line by filtering
with
Existing Product using
Filter array action.
Populate the
From property with the
['body/value'] of the
List Opportunity Lines step. Then, choose one side of
Where property with the
Existing Product value
item()?['_productid_value'] from the
List Opportunity Lines step and another
with the
Existing Product value
items('Apply_to_each_Quote_Line')?['_productid_value'] from
List Quote Lines step.
The output of the Filter array step is also an array which is the subset of the main array with values matching the criteria. In this scenario, there is only one Opportunity Line that matches with the Existing Product value, so let's use first() to get the value without another Apply to each loop.
For some reason, the output of
Filter array step does not show up in the
Dynamic Value when the expression is being added for
Manual Discount column. (maybe because it is the currency column which accepts decimal value).
The output of
Filter array step is nowhere to be found when the
Dynamic Value is added in the text column either.
If you are not sure how to write the expression of the body output of the
Filter array step, just create one
Compose step to build your expression. One disadvantage of using filter array on output of the
List records action is that you need to write the expression for getting the property. That is because properties of the Filter array is not available in the
Dynamic Value list if we filter the output of the
List records step. To overcome this problem, you may use
Parse JSON action to make the properties selectable from the
Dynamic Value list. But if you are comfortable with the expression, you can add the
Body of the
Filter array step in
first() and then, append with the property (column) name of the array object that we need. In this case, it would be
?['manualdiscountamount'].
Finally, copy the expression from the
Compose step above to the
Update Quote Line step.
Summary
By using the Filter Array action we can the output data from the List Records action to get specific rows and by doing so, we can query all data at once and filter to reduce the number of server-side calls. Even though it can improve the performance of the flow, the flow author will require a little bit of knowledge of writing an expression to use the Filter Array action.
Comments
Post a Comment