cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Dual Super User III
Dual Super User III

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
Highlighted
Resolver I
Resolver I

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

Highlighted
Dual Super User III
Dual Super User III

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

Highlighted
Frequent Visitor

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?

Highlighted
Frequent Visitor

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.

Highlighted
Dual Super User III
Dual Super User III

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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