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!
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? 🙂
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 🙂
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.
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%'...
Does it make more sense now?
Thanks & bye!
Check out new user group experience and if you are a leader please create your group
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Don't miss the call this month on June 16th - 8a PDT