Filter on a column added by an $expand query

(0) Share

Report

Posted on by ghdunn

409

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!

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Verified answer

Expiscornovus 33,857 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ChrisPiasecki 6,424 Most Valuable Professional on at

Like (4)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ghdunn 409 on at

Like (1)

Report
Copy link

Link copied!

thanks! will test over the weekend.

Was this reply helpful? Yes No
ghdunn 409 on at

Like (0)

Report
Copy link

Link copied!

Thanks @ChrisPiasecki - will test over the weekend.

Was this reply helpful? Yes No
ghdunn 409 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
SwJ23 85 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
KatieL1 26 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No