Delegation Warning

[Anonymous]

I have a delegation warning and  not sure how this needs to be fixed. My app works fine except for this part. Any thoughts on this will be higly appreciated.  

On the App setting I increased the limit from 500 to 2000, But that doesn't solve my problem since I have more than 2000 records.

This is what it looks like:

Filter('[dbo].[VendorExpenses]',TextInput3.Text in Month,TextInput4.Text in Year,TextInput1.Text in VendorKey,TextInput2.Text in VendorPaymentKey) 

The error appears to be with the usage of "in". 

Error - Delegation warning 

Thanks in advance"

[mr-dang]

Hi @Anonymous,

It looks like you're using SQL with PowerApps and the in operator with multiple columns. I'm assuming you're using the text input boxes as a kind of search.

Filter('[dbo].[VendorExpenses]',
    TextInput3.Text in Month,
    TextInput4.Text in Year,
    TextInput1.Text in VendorKey,
    TextInput2.Text in VendorPaymentKey
) 

What I would suggest is using one input box and searching against all those columns using Search(). The Search function lets you search any text columns against any string in the app (hard coded string, label, text input, etc.) and it can do so against multiple columns. Here's an example:

Search('[dbo].[VendorExpenses]',TextInput1.Text,"Month","Year","VendorKey","VendorPaymentKey")

This means, "Search your table to return results where whatever is typed into the text input field matches either the month, the year, the vendor key, or the vendor payment key."

Reducing the text input boxes on the screen will simplify the user experience and give you space back to do other things too.

[Anonymous]

Hello Dang,

Appreciate you response and your observations are spot on.

My bad that I didn't tell you that "Month","Year","VendorKey","VendorKey"and"VendorPaymentKey" all are numeric Inputs. As far as I know Search wont work with numeric fields. I tried 

Search('[dbo].[VendorExpenses]',Value(TextInput1.Text),"Month","Year","VendorKey","VendorPaymentKey")

Search('[dbo].[VendorExpenses]',Number(TextInput1.Text),"Month","Year","VendorKey","VendorPaymentKey")

the following and that doesn't seem to work. I am not sure if I am doing something wrong here, any suggestions will be highly appreciated.

Thanks ,

Anil 

[tchin-nin]

Hi @Anonymous,

What I first see is a type missconfiguration : you are filtering by comparing Text and Numbers. Moreover, according to the documentation, the "in" operator can only be used to compare strings. 

Also, the issue your facing has already been reported several times (Here and in the Git available on the documentation linked above). It appears that the in operator is not delegable when the DataSource comes from SQL ONPREMISE. Is that your case ?

Maybe @mr-dang can request some infos about this issue to the PA team ?

Théo

[timl · Accepted solution]

Hi Anil,
To overcome your problem, I would create a SQL Server view that converts your numeric fields to text. It would look something like this.

CREATE VIEW [dbo].[vuVendorExpenses]
AS
SELECT [YourIdColumn],
       CAST(Month AS NVARCHAR(max)) AS [Month],
       CAST(Year AS NVARCHAR(max)) AS [Year],
       CAST(VendorKey AS NVARCHAR(max)) AS [VendorKey],
       CAST(VendorPaymentkey AS NVARCHAR(max)) AS [VendorPaymentKey]
FROM   dbo.VendorExpenses

Once you do this, you can search against this view using @mr-dangs formula.

Search('[dbo].[vuVendorExpenses]',TextInput1.Text,"Month","Year","VendorKey","VendorPaymentKey")

If you prefer to keep separate textboxes, you can nest the calls to the Search function like so...

Search(Search(
              Search('[dbo].[vuVendorExpenses]',TextInput1.Text, "VendorKey"), 
       TextInput3.Text, "Month"), 
TextInput4.Text, "Year")

Hope that helps.

[Mr-Dang-MSFT]

It's complicated.

"In" is delegable with SQL when you use it to compare strings.

Ex:

Filter(dbo.table,"string" in Column)

It is not delegable if you try to compare membership.

Filter(dbo.table,Column in collection.Column)