cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sheikx800
Frequent Visitor

Lookup Filter Query not working on Activity Table

Okay here is the breakdown:

- I have a flow which is aggregating data from 4 different tables using a 'List Rows' step.

- In each of the tables in my CDS, there is an identical custom lookup field for Account.

- Each of these 'List Rows' steps in my Flow has the same 'Filter Rows' ODATA query in it which reads as: <siteid>_Account/name eq 'Account Name' and createdon le <dateObject1> and createdon ge <dateObject2> 

 

In the case of 2 of the List Rows steps, they work just as expected with no errors. In the case of the other 2, it errors with "Could not find a property" error. 

 

I troubleshooted by removing the account part of the filter query to test the proceeding steps. The query works fine with just the date filtering elements regardless of which table is being queried. 

 

With my limited knowledge, the only difference structurally between the steps that work and the ones that dont is that the steps that work are querying tables I set up as "Standard Table", whereas the ones that dont work are querying tables I set up as an "Activity Table".

 

Is there any reason why that '<siteid>_Account\name' query would be causing issue with the activity table? Or is the Table type a coincidence? In which case, can anyone suggest any troubleshooting tips for this one? I have checked case and such. 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sheikx800
Frequent Visitor

I have found a workaround suggested here for a different problem by Jcook: https://powerusers.microsoft.com/t5/General-Power-Automate/Filter-Query-in-List-Records-not-working-...

 

I got the Account GUID with this method but I was unable to reference the account NAME (which, in hindsight, was a better solution anyway). The flow did not like me using the full reference to it of "_<sitecode>_Account_value@OData.Community.Display.V1.FormattedValue"

but it was happy with me using "_<sitecode>_Account_value" 

 

During my travels I read somewhere that the extra "_" in front of the lookup fields relates to a different kind of lookup. With no research or investigation, I am guessing that Activity tables configure lookups within their records as N:N relationships to work whatever other magic they have going on in the background. Only theory I can come up with of why the filter query only had issues on the Activity tables and not the Standard ones (given both were custom). Its a theory at least. 

 

I am glad that for my use case I didn't have to go down the path of using a Filter Array action.

 

I will leave this here for a while to see if anyone knows any other tricks with the formatting to make the filter query work correctly by formatting it differently. Otherwise I will mark this as the solution. 

View solution in original post

2 REPLIES 2
Sheikx800
Frequent Visitor

Very quiet... Am I asking the wrong question here/not explaining it well enough/or found a bug? Lol

Sheikx800
Frequent Visitor

I have found a workaround suggested here for a different problem by Jcook: https://powerusers.microsoft.com/t5/General-Power-Automate/Filter-Query-in-List-Records-not-working-...

 

I got the Account GUID with this method but I was unable to reference the account NAME (which, in hindsight, was a better solution anyway). The flow did not like me using the full reference to it of "_<sitecode>_Account_value@OData.Community.Display.V1.FormattedValue"

but it was happy with me using "_<sitecode>_Account_value" 

 

During my travels I read somewhere that the extra "_" in front of the lookup fields relates to a different kind of lookup. With no research or investigation, I am guessing that Activity tables configure lookups within their records as N:N relationships to work whatever other magic they have going on in the background. Only theory I can come up with of why the filter query only had issues on the Activity tables and not the Standard ones (given both were custom). Its a theory at least. 

 

I am glad that for my use case I didn't have to go down the path of using a Filter Array action.

 

I will leave this here for a while to see if anyone knows any other tricks with the formatting to make the filter query work correctly by formatting it differently. Otherwise I will mark this as the solution. 

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (2,356)