cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jacktransform
Helper III
Helper III

Using a Double Lookup to Dataverse (CDS) in Filter Query

Hi All!

 

I have a flow where I want to filter a table in the Microsoft Dataverse (using CDS List Records) by multiple levels of LookUps. I'll explain what I mean.

 

I have 4 tables in the Dataverse; Table 1, Table 2, Table 3, and Table 4. Table 1 is the parent of Table 2, Table 2 is the parent of Table 3, and Table 3 is the parent of Table 4. I want to filter Table 4 by the record it is associated with in Table 1. Essentially, I want to filter Table 4 by the record in Table 1 that is its parent's parent's parent (great grandparent?)


Previously, I was using List Records for Table 4 and only filtering by a record in it's parent table Table 3. I did that by making 'Filter Query' in the CDS List Records for the entity Table 4 have the following:

 

_cp999_lookuptable3_value eq 'Table 3 GUID'

 

What I'd like to do is something like the following, but obviously my code is wrong:

 

_cp999_lookuptable3_lookuptable2_lookuptable1_value eq 'Table 1 GUID'

 

I hope that makes sense, please let me know if I need to further clarify. I'm not well versed in Power Automate, so please let me know if this is something that is even possible. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Gopala_Krishna
Super User
Super User

@jacktransform 

 

One idea I can think is to have multiple filter actions and use one GUID in another.

 

Let's say that I have the List Records action on table 4 and you have the filter query for that enlisted as table4_guid eq guid_from_table3.

 

Once you have the result for above mentioned expression then you can use result of that to format the next say I want to filter table3 I will put the filter query as table3_guid eq guid_from_above_list_records

 

Like wise we can have list records till table1 and you have the exact result now as you followed the back tracking process where you filtered each entity based on the records you had and the guid that came from the parent.

 

If the information shared helps you, please consider giving a thumbs up 👍 and mark solution as resolved.

View solution in original post

2 REPLIES 2
Gopala_Krishna
Super User
Super User

@jacktransform 

 

One idea I can think is to have multiple filter actions and use one GUID in another.

 

Let's say that I have the List Records action on table 4 and you have the filter query for that enlisted as table4_guid eq guid_from_table3.

 

Once you have the result for above mentioned expression then you can use result of that to format the next say I want to filter table3 I will put the filter query as table3_guid eq guid_from_above_list_records

 

Like wise we can have list records till table1 and you have the exact result now as you followed the back tracking process where you filtered each entity based on the records you had and the guid that came from the parent.

 

If the information shared helps you, please consider giving a thumbs up 👍 and mark solution as resolved.

View solution in original post

@Gopala_Krishna 

 

That seemed to work, I just have a lot of nested "List records" in "Apply to each" loops. Will update if I have any problems going forward

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (64,854)