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

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

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

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

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

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (12,900)