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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,445)