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

Delegation warning in Filter function using IN operator: how to avoid it?

Hi all!
My app is reading data from a MS SQL Database.

 

I must filter a table selecting only the rows with a value included in a SUBGROUP of values taken from another table.

In other words: I want to take only rows of TABLE1 where TABLE1.VALUE1 is one of the values TABLE2.VALUE2 taken from TABLE2 where TABLE2.ATTRIBUTE2 = TRUE.

As a "target" for the "in" operator, I filter TABEL2 where ATTRIBUTE2 = True, and then I use only the column "Value2" with ShowColumns.


This is my function:

Filter(TABLE1, Value1 in ShowColumns(Filter(TABLE2,Attibute2 = TRUE),"Value2"))

 

Well, I get a delegation warning: "the right side of the "in" operator should be a column name from the correct data source.


Then, I thought about doing like this: 

 

Filter(TABLE1, RowCounts(TABLE2, Attribute2 = TRUE and Value2 = value1)>0)

But also RowCounts is not delegable! 

What else can I try to filter this table without getting the delegation warning?

Thanks in advance!
Bye,

A.-

 

 

6 REPLIES 6
LanceDelano
Power Apps
Power Apps

Can you denormalize the table and bring the values into the primary table?  Or create a view that does so?

Yes, I know that creating a view would be a solution, but I'd need to heavily modify my whole application... I will use this solution only in case there are NO alternatives. My gosh, I am afraid this is the case, isnt' it? 🙂

Thanks @LanceDelano!

 

Hi @MisterMagoo,

 

One way to denormalize a table is to use the Groupby() and Ungroup() functions.  You can check out how to use them at https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby . The examples at this page might show you a way to work with your tables.   It may not be the solution but it might help get you started on the right track.  Hope this helps.  

Hi @Drrickryp, thanks for your suggestion. I am not sure I understood how either the group() or the ungroup() could help me.  If I am not wrong, you're suggesting to denormalize my 2 tables to one, and then re-normalize the "denormalized" table by using the "group()" function, is it correct? 

That would still require me to heavily change my whole application... which I am afraid is the most probable way out, at the moment 🙂

 

@MisterMagoo

Don't give up yet.  I am still trying to understand your database and its relationships.  You may not need the "In" operator and we might be able to get around the delegation issues.  Does your primary Table include the foreign key from second table that you need to lookup values from?  In other words, is the ID column of your second table like the Customers ID and is included in a column in your first table like the Orders table in this picture?  Please let me know if this is the case.relationship.PNG

 

 

Hi @Drrickryp. Thanks for your additional effort on this 🙂
Yes, it is exactly like this: the link to the second table is the primary key of the first table. 

Very quickly: the main table ("Applications") is a list of different applications used in my Company, which primary key is the "ApplicationName".
In the second table ("AppDetails"), since the same Application can be installed in several countries (and in each Country the Application has different attributes), the key is "ApplicationName" + "Country".

Well, I need to list ONLY records, from the main table, for applications that are used, say, in USA. 
This is why I am doing that Filter. The filter is quite complex, though, because based on user's preferences, I may be filtering only USA application & only applications that are CLOUD & only applications where ApplicationName like 'JDE%'...
2018-10-01_0928.png
Does it make more sense now?
Thanks & bye!
A.-



Helpful resources

Announcements
PA 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

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (25,285)