cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sheikx800
Helper II
Helper II

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
Helper II
Helper II

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
Helper II
Helper II

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

Sheikx800
Helper II
Helper II

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. 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,760)