cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashaikh
Level: Powered On

Lookup Delegation Warning

Hello All,

 

I have an app created which does search on a table with 2000 + records.

Gallery Bar Search For this, I have written a search function to search for text written in the search box.  This code works perfectly with delegation.

The code for that is below -

SortByColumns(
    Search(
        Filter(
            '[dbo].[note_attachment_time_entry]',
            task_id <> -1 || row_content_type <> "TIME_ENTRY"
        ),
        TextSearchBox3.Text,
        "description"
    ),
    "unique_id",
    If(
        SortDescending3,
        Descending,
        Ascending
    )
)

This app page is based on searching task_id in the note_attachment_time_entry table, which has 2000+ recordsimage.pngApp Page

Now what I am doing is, after the search the result I get, I click on the Right Arrow and it goes to next page. On the next page, I am looking to display task_number which comes from another table wh_task; wh_task also has more than 2000 records. This is how that page looks like -image.pngLookup Page

Here, I tried to write a simple lookup function which looks up task_id that I received from the previous page and task_id from the wh_task table and the result displays task_number. Here is that code - 

LookUp('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = BrowseGallery3.Selected.task_id, task_number)

If I do so, I get a delegation warning for wh_task.task_id having more than 2000 records, here is that warning below-

image.pngDelegation Warning

 

Understanding the issue, I tried to do the search in task_id to return a single row, the search function I wrote was similar to what I wrote in the previous step. But that is still causing delegation issue. Please see that code below

 

LookUp('[dbo].[wh_task]', 
Filter('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = BrowseGallery3.Selected.task_id) = '[dbo].[note_attachment_time_entry]'[@task_id],
task_number)

Please help me out as to how can I fix it. I have tried quite a lot of things but not able to get an accurate result.

 

I would also appreciate if there is a better way I can do this, other than what I am trying

 

Thanks In Advance

 

Hoping for some good ideas/solution.

3 REPLIES 3
Dual Super User
Dual Super User

Re: Lookup Delegation Warning

Hi @ashaikh 

 

Can you try out below expression:
LookUp('[dbo].[wh_task]', task_id= BrowseGallery3.Selected.task_id, task_number)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

ashaikh
Level: Powered On

Re: Lookup Delegation Warning

@yashag2255 if you see the question I already tried this and get an delegation warning

Community Support Team
Community Support Team

Re: Lookup Delegation Warning

Hi @ashaikh ,

Do you want to get the corresponding task name from your  wh_task table based on the task_id that I received from the Gallery?

Could you please share a bit more about the 'task_id' column in your two SQL Tables? Are they both Number type column or Text type column?

 

Based on the LookUp formula you provided, I think there may have something issue with it. On your side, please consider modify your formula as below:

LookUp('[dbo].[wh_task]', Value(task_id) = Value(BrowseGallery3.Selected.task_id), task_number)

Or

LookUp('[dbo].[wh_task]', Text(task_id) = Text(BrowseGallery3.Selected.task_id), task_number)

 

Please consider take a try with above solution, then check if the issue is solved on your side.

Note: The Number column or Text column is delegable within SQL Server data source with "=" operator, please check the following article:

https://docs.microsoft.com/en-us/connectors/sql/#powerapps-delegable-functions-and-operations-for-sq...

 

In addition, if the amount of your your second SQL Table records is not more than 2000, you could ignore this Delegation warning issue. You could consider change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then you could process 2000 records locally at most, try your LookUp formula again, check if the issue is solved.

More details about the Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

If the amount of your your second SQL Table records is more than 2000, please check and see if the alternative soluion within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Set the OnStart property of the App control to following (I assume that there are 10000 records in your '[dbo].[wh_task]' table):

 

Concurrent(
    ClearCollect(col1, Filter('[dbo].[wh_task]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[wh_task]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[wh_task]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[wh_task]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[wh_task]', recordID >= 8001 && recordID <= 10000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

Note: I assume that the recordID represents the Primary Identity(1,1) type column in your '[dbo].[wh_task]' table.

Then save and reload your app (fire the OnStart property of the App control). Then go to your Ticket Detail screen, modify your LookUp formula as below:

LookUp(MergedCollection, Value(task_id) = Value(BrowseGallery3.Selected.task_id), task_number)

Or

LookUp(MergedCollection, Text(task_id) = Text(BrowseGallery3.Selected.task_id), task_number)


Please take a try with above solution, then check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,702)