OData Filter For Multi Relationship Levels For Dataverse Data:
- When you want to get the records for related records that has multi-stage relationships, we
cannot directly use filter query for logical names – we have a specific method in Power Automate to Achieve this.
High level resolution steps
- Get to know the schema name, logical names of columns and how the records are interlinked via relationships
- Filter Query to be used in Power Automate while listing the rows.
Detailed resolution steps
Step 1: Trigger a flow using any method to get to know the unique row id.
Refer to my other blog to get to know on how to call a power automate flow on click of a button in Model driven app.
OneDrive (sharepoint.com)
Step 2: Currently I have some dummy data stored in Dataverse, here is the explanation of the data:
I have a table called “Cust table” and have customer data:
Next, I have another relationship that relates Order Master and Order Table as shown below:
So, here it is how it goes:
Cust number 2211 has order ID SHIP9322 and this SHIP9322 is again linked to Order Master Called
Apparel. This is the base setup I have taken.
Step 3: Navigate it to make.powerautomate.com and pick the flow that has a trigger from Model Driven App Button.
Step 4: Add a trigger to the flow and get a row by ID. Which ever row/rows you have selected will be picked up into the flow.
Step 5: List the rows of Order table – since you want to get to know the related records to order table.
Here in Filter Rows – patch it in such a way that:
“Schema name of cust table”/”logical name of Cust ID field” eq ‘”Dynamic content to get Cust ID from previous action”’
Step 6: Next, you want to know the related records between the order table and Order Master table.
So for that we will list the Order Master Table and filter the rows again:
Here, all you have to do in filter rows is:
“Logical name of Primary column from Order master” eq ‘”Column ”’
Output:
First, we got the particular cust ID that is coming from the trigger from Model Driven App:
Next, we got the related orders for that particular customer:
And then we got the order master for the related order that the customer was having:
