cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

500 Row Limit for Lookup function?

I was under the impression the lookup function was delegable.  I have a formula that populates the item field of a displayform.  However, it only works for the first 500 rows.  

 

If(Len(tSS_2.Text)=9,Set(itemRecord,LookUp(dataSourceRead,UNIQUE_ID=tSS_2.Text)),
Set(itemRecord, LookUp(dataSourceRead,BADGE=Value(tSS_2.Text))))

Thoughts?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: 500 Row Limit for Lookup function?

I found my solution.  In the event you're struggling the following worked for me:

 

Button Press

 

Set(dataSource,Filter(dataSourceRead,unique_id=id))

Form Item property:

 

first(datasource)

Form Datasource

 

datasource

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: 500 Row Limit for Lookup function?

The lookup function is delegable but the Value() is not. You should see a blue squiggly line by that the Value() function.



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Highlighted
Community Support
Community Support

Re: 500 Row Limit for Lookup function?

Hi @KUNGFUPANDA559 ,

What is your data source? Can you share the delegation warning details?

How many rows does data source have?

 

I have made a test (based on Sharepoint, more than 500 items), and don’t have the delegation issue you mentioned.

As the image shown, the 512th record can be looked up.

Annotation 2019-08-22 131214.png

By the way, the lookup function , the operator ‘=’, and the data source are all the factors that can’t be delegated.

So please post your data source and delegation warning details to make an accurate judgment.

 

If you want to overcome the 500 items limitation, you can refer to following:

  1. Increase the total limit items from 500 to 2000(Max) if the amount of record is under 2000.

Click file (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

  1. If the amount of record is more than 2000, create collection to store the data, and use collection in your formula instead of the current data source.
  • Set the OnStart property of the App control as below:

Note: assume the amount of datasource is 6000, and have the primary identify type column “ID”.

Concurrent(
    ClearCollect(VarCollection1, Filter(dataSourceRead, ID >= 1 && ID <= 2000)),
    ClearCollect(VarCollection2, Filter(dataSourceRead, ID >= 2001 && ID <= 4000)),
    ClearCollect(VarCollection 3, Filter(dataSourceRead , ID >= 4001 && ID <= 6000)),
    );
ClearCollect( VarCollection, VarCollection 1, VarCollection 2, VarCollection 3)

Tips: If you don’t know the amount of datasource, you can check this:

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource, ID, Descending))});
Amount= lastrecord.ID - firstrecord.ID
  • Modify your formula as below:
If(Len(tSS_2.Text)=9,Set(itemRecord,LookUp(VarCollection,UNIQUE_ID=tSS_2.Text)), Set(itemRecord, LookUp(VarCollection ,BADGE=Value(tSS_2.Text))))

Please have a try with above workaround, and check if the issue is solved.

 

Best regards,

Sik

Highlighted
Helper II
Helper II

Re: 500 Row Limit for Lookup function?

@v-siky-msft I'm using Azure SQL.   Shouldn't i be able to filter the datasource using the conditions in the if then statement?

 

e.g.

 

Filter(VarCollection,tss_2.text=unique_id)
Highlighted
Helper II
Helper II

Re: 500 Row Limit for Lookup function?

@v-siky-msft 

 

The data source is dynamic and each could be as many as 200k records.  Surely there has to be an alternative to hardcoding logic for each 2k rows.

Highlighted
Helper II
Helper II

Re: 500 Row Limit for Lookup function?

I found my solution.  In the event you're struggling the following worked for me:

 

Button Press

 

Set(dataSource,Filter(dataSourceRead,unique_id=id))

Form Item property:

 

first(datasource)

Form Datasource

 

datasource

View solution in original post

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 (3,421)