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.

@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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,734)