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

Delegation Warning

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Delegation Warning

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.

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Delegation Warning

Hi @Akishore,

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.

Microsoft Employee
@8bitclassroom
Highlighted
Frequent Visitor

Re: Delegation Warning

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 

Highlighted
Super User
Super User

Re: Delegation Warning

Hi @Akishore,

 

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

 

 

Highlighted
Super User
Super User

Re: Delegation Warning

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.

View solution in original post

Highlighted
Power Apps
Power Apps

Re: Delegation Warning

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)

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,700)