OData filter on Relationship property

[Jronash]

I'm in Power Automate, and trying to use the Dataverse List Rows action to return a list to me based on a column in a separate table.

For example, let's say I have a Schools Table and a Students table.  There are many schools, and each School can have multiple Students.

Now let's say that I want to see a list of all Students who go to school in New York City.  How would I construct the OData filter to show me all students where the School's City column value is equal to "New York"?

This seems pretty easy to do in Power Apps, but I haven't figured out how to do it in Power Automate yet.

Thanks for the help.

[rampprakash]

Hi @Jronash 

You can use Advance find and download the fetchxml and use the same in Power Automate

Navigate to make.powerapps.com

Click Gear icon at the top

Then select Advance settings

Click Filter Symbol at the top then

Select Look for as School

Then Select rows Nd use filter condition as City equals Newyork 

Once done click Download fetchxml then open it in NotePad copy and paste it in List Rows in Power Automate

That's it ☺️

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA

Blog : https://microsoftcrmtechie.blogspot.com

[Jronash]

Hi @rampprakash,

Your method gives me a list of Schools in New York City, but what I'm looking for is a list of Students with relationships to Schools in New York City. 

[rampprakash]

Cool @Jronash 

Then you can rewrite the Advance find like this

1. In Advance Find Look For Students

2. the in the Select Column Search for Related Entities and Select School

3. Then Inside Related Entities Select City Equals New York

[Jronash]

This is helpful, @rampprakash - I haven't solved my problem yet, but I think I may have an idea for why it's causing me trouble.

In my scenario, the "School" relationship is not required for a "Student".  So there are some Students in the Student table who do not have a relationship with any school.  I think for this reason, the School entity does not show up on the list of Related Entities in Advanced Find.  I can see other related entities there (the ones that are required), but no School entity.

Is there a way of using Advanced Find on a non-required related entity?

[Jronash · Accepted solution]

After more research, I decided to try a different approach and created a calculated column in the Students table which refers to the City column in the Schools table.  So I can filter on that now.

I'd still be interested in knowing if there's a way to do this without a calculated column, but this work-around is good enough for now.