Answered

OData filter on Relationship property

(0) Share

Report

Posted on by Jronash

827

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Jronash 827 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Jronash 827 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

Jronash 827 on at

Like (0)

Report

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.

Was this reply helpful? Yes No