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

How can I make these lookups delegable?

This is an Azure SQL server data source. I'm using this formula in a form to display data from another table

 

SelectedJobKey is the primary key for the record as selected from a previous page. I've also done it directly and the result is the same problem with delegation. The selected record is being grabbed from a SQL view, so I have to use the lookup to select the right record in another table. This was assigned with 

 

Set(SelectedJobKey, JobViewGallery_1.Selected.JobKey);

I'm using the below formula to display data

 

LookUp('[dbo].[EvaluateJobs]', SelectedJobKey = '[dbo].[EvaluateJobs]'[@JobKey], ApplicationPriority)

I also have a gallery displaying the changes to the record as recorded in another table, using

 

LookUp('[dbo].[EvaluateJobsActions]', JobKey=SelectedJobKey)

 

These all have delegation issues. How can I change my approach or rewrite these so that I can delegate the query? I'm already past the 500 record limit for non-delegable queries and it's already affecting many records.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ApocryphalApe 

 

Can you modify the below setting if not already:

App Settings -> Advanced Settings -> Data Row Count for Non-Delegable Queries -> 2000

Also, can you try to update the expression LookUp('[dbo].[EvaluateJobs]', SelectedJobKey = '[dbo].[EvaluateJobs]'[@JobKey], ApplicationPriority) as:

LookUp('[dbo].[EvaluateJobs]', JobKey = SelectedJobKey, ApplicationPriority)

 

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!

View solution in original post

5 REPLIES 5
Resolver I
Resolver I

Hi,

 

at first you could try to save your data to a collection.

 

You could do something like this:

 

Collect(colYourCollection,FirstN(yourDataSource,500))

 

Then you could LookUp or Filter your collection.

 

https://docs.microsoft.com/de-de/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

Best regards,

 

Stef

Hi @ApocryphalApe 

 

Can you modify the below setting if not already:

App Settings -> Advanced Settings -> Data Row Count for Non-Delegable Queries -> 2000

Also, can you try to update the expression LookUp('[dbo].[EvaluateJobs]', SelectedJobKey = '[dbo].[EvaluateJobs]'[@JobKey], ApplicationPriority) as:

LookUp('[dbo].[EvaluateJobs]', JobKey = SelectedJobKey, ApplicationPriority)

 

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!

View solution in original post

Hello,

 

Thank you very much for your help. This appears to have worked at first glance as it doesn't give a delegation warning, and it appears to be pulling all records in the data fields. I'll have to fully test to be sure. However, there still seems to be a delegation issue with the gallery that shows update actions. I've modified the gallery with the modified lookup, but it's only showing the first item and not subsequent matches.

 

Here's the records it should show

recordsexample.png

 

 

 

 

 

 

 

Here's what it does show

powerapps_screen2_20190823.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The JobKey is a foreign key in this table. It should work though. It worked in a previous iteration. The only reason I can think it doesn't is that it still has a delegation issue. Any ideas?

Solved the issue with returning only 1 record. I changed the lookup to a filter instead with the same arguments.

Hey @ApocryphalApe 

 

Yes, that is correct way to return number of records, Lookup returns the first record satisfying the criteria while Filter returns all the records satisfying the provided criteria. 
 
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!

Helpful resources

Announcements
News & Announcements

Community Blog

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

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (4,337)