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

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
Dual Super User
Dual Super User

Re: How can I make these lookups delegable?

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

Re: How can I make these lookups delegable?

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

Dual Super User
Dual Super User

Re: How can I make these lookups delegable?

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

ApocryphalApe
Level: Powered On

Re: How can I make these lookups delegable?

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?

ApocryphalApe
Level: Powered On

Re: How can I make these lookups delegable?

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

Dual Super User
Dual Super User

Re: How can I make these lookups delegable?

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,028)