cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jronash
Impactful Individual
Impactful Individual

OData filter on Relationship property

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Impactful Individual
Impactful Individual

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.

View solution in original post

5 REPLIES 5
rampprakash
Super User
Super User

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
Impactful Individual
Impactful Individual

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. 

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (5,276)