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+ records
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 -
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-
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
Filter('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = BrowseGallery3.Selected.task_id) = '[dbo].[note_attachment_time_entry]'[@task_id],
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.
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!
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)
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:
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:
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:
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)
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.
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications