Let your Operations Flow – Part 6
There is still more to explain about Microsoft Flow and the Dynamics 365 for Finance and Operations connector. This time I will inform you about the action Lists items present in table. You can use it to loop contents of an entity or select a single record without getting an error if there is no record found.
Lists items present in table Action
When you want to use the action List items present in table, you can start very simple by only providing the Instance and Entity name variables. In that scenario, it will loop all records from the selected entity. Be aware that large number of records would take some time to process.
For this scenario, I prepared an Excel template with one table and 5 columns. When you run the Flow, it will fill the Excel file with all customer records.
Advanced options
With the advanced option on the List items present in table action, you can filter records, define a sorting, limit the number of records, skip fields, reduce the number of columns to be retrieved and specify if records from all companies needs to be retrieved.
In the Filter Query field you can use Odata statements to filter records. Probably, you are not familiar with the statements or options. In that case, you can find information on the internet. One page with help I used is OData expression syntax for filters and order-by clauses in Azure Search. The query expression can both contain constant text and variables. The example above shows how to filter the customers based on an input of the currency code by the user who starts the flow.
The Order By option needs one or more field names. The names should match the Entity field names. You can separate more fields with a comma. The count fields can be controlled by specifying a number.
The Select Query option is to limit the number of columns which will be retrieved. This could be beneficial for the performance if you need only one or a few columns and the entity is having a large number of fields.
If you need records from all companies, you can set the value in the Field Cross Company to Yes.
Select a single record
As mentioned in part 5, I will also explain how to retrieve a single record. You can achieve this by setting the Top Count field to 1. Also, you need to specify a Filter Query with the exact statement to retrieve the correct record. You can create a filter based on the primary key fields, but you can also use other fields to get a certain record. For example, the bar codes do have the Item number as primary key, but you can actually search in the field Bar code to find the related product.
In the example above, I’m trying to find the last purchase order line based on a descending order of the LineNumber field for a certain purchase order in a legal entity. Additional tip: In the after this action, you can evaluate if a record was returned or not. The Empty variable is defined at the beginning of the flow diagram as array without a value.
As you might notice, the latest example is taken from a Flow called ‘Purchase orders’. This is also a demo I used on the Summit EMEA in a presentation. I will share this example with full explanation soon. So for sure, there is more to write around Microsoft Flow.
I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.
That’s all for now. Till next time!
These posts about Flow and Fin Ops have been a great help!
I have been trying to find a good way to do left joins with related data in the List items present in table:
Is there a way to use the feature $expand /expand query in this function similar to what is possible in the CDS current environment connector? The field seem to be missing in the Fin Ops connector?
Hi Jan,
The feature $expand is not supported. Usually, I do add multiple entities separately as work around. The FinOps data entities are designed in a different way compared to CDS entities.
That is a shame – I had hoped the join could be done server side rather than the slow “apply to each”