Filter on a column added by an $expand query

[ghdunn]

I am querying Dynamics 365 (Project Service Automation PSA) data using the Dataverse List Rows action.

I want to return the rows for a particular user but I want to add an additonal clause to bring back only those rows where the projectTask = "Vacation". Unfortunately,  projectTask is not on that table, so I have to expand the Project Task table.  However, I can't work out a way of then filtering on that new field.  My thoughts:

1. Do I try to add it to the filter, referencing the newly added column?  Seems to fail.

2. Do I try to add the filter to the expand clause?  I couldn't work out the syntax.

3. Do I just add the column in this particular step and then try to filter the entire table in a subsequent step?  If so, can anyone advise what would that Action step I could use?

Hoping someone can help!

[Expiscornovus · Accepted solution]

Hi @ghdunn,

Maybe you can use the fetch xml query instead?

In that field you should also be able to add filters for linked entities:

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-construct-query

[ChrisPiasecki]

Hi @ghdunn,

You can use the Filter query to filter by a lookup's properties.

For example, if I want to get all Accounts where the Primary contact's Gender is Male, my filter query would look like the following:

(primarycontactid/gendercode eq 1)

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[ghdunn]

thanks!  will test over the weekend.

[ghdunn]

Thanks @ChrisPiasecki  -  will test over the weekend.

[ghdunn]

Thanks @Expiscornovus Sorry for delay but on your recommendation have spent some time digging into FetchXML and you are right...it seems a good way to do this...especially since you can design the FetchXML query in Dynamics Advanced Find and cut and paste the FetchXML.  However, one more question if I may.  Do you know if I can use a combination of the two? If I bring a column back in Fetch XML, can I then refer to it in the Filter field?  The reason I ask is that adding a dynamic content element to FetchXML field is quite fiddly.  The example below fails with the error:  "Could not find a property named 'businesstypecode' on type 'Microsoft.Dynamics.CRM.contact'." This makes some sense but I wonder if there is a way around it?

 

[SwJ23]

I realize this is quite an old post, but I think Chris' answer more directly solves this issue. FetchXML is certainly an option, but the '/' syntax for nested odata filtering is slick.

[KatieL1]

Hi @ChrisPiasecki  I recognize this is old, but this is useful! In Dataverse Query, I brought in my wanted Child column using Expanded Query:

outputfromlookupcolumnNavigation($select = modifiedon). This works, but how do I filter on the child modifiedon?

I placed this in the Filter rows with no luck: outputfromlookupcolumnNavigation/modifiedon

Any thoughts or suggestions?