Solved: Handling more that 2000 items in powerapps

vish3027 · ‎08-12-2021

Hi All,

This is needed on an urgent basis.

I have SharePoint list customization done with the help of PowerApps.

I have a separate list of employee details.

In my form I am creating a collection like the below:

ClearCollect(empdata, LookUp(empdetails,EmpID=EmployeedID))and then using thsese collction to populate employee details in form.

My issue is that I have more than 2000 records in empdetails list and lookup search only for the first 2000 items.

I want to search in all records present in sp list. I am no tsure total employee in list it can be 2000,2200,4000,5000 anything.

Also employee id wont be in sequence as employee leaves his id will be empty and that row is removed only present employee are available in list.

Please help how to handle this. URGENT

@RandyHayes @WarrenBelz @RezaDorrani Please suggest.

RandyHayes · ‎08-13-2021

@vish3027

Okay...let's skip the variable that is not needed, your dropdown already has the value in it, no need to be redundant.

What you really need is a dynamic variable. You might not be aware of this capability in PowerApps and it takes a couple of steps but works wonderfully.

Here is what to do...

Add a new scrollable screen to your app.
In the new screen there will be a Canvas control in it.
Click on the Canvas control in the left control tree. This will select the canvas control in the screen.
Resize the canvas control. There are two reasons for this. First is that resizing breaks its connection to the other screen controls in that screen, and two is that you really don't need the canvas to be large...just very small - you'll eventually make it not visible, but small is good.
Copy the canvas control and then go back to your original screen and paste it there. You can delete the scrollable screen that you just added now, we just needed the Canvas control and PowerApps does not provide that in the Insert menu.
In your Canvas control, you will have a DataCard control (I'll call is DataCard1 for this sample). Select the datacard control in it.
Find the Update property of the DataCard control and set the formula in it to the following:

{
   EmpData:
       With({_empID: Value(DataCardValue31.Text)},
            With({_data: 
                Switch(yourCompanyNameDropdown.Seleceted.Value, 
                   "Company name1", Filter('Company name1', EmployeeID = _empID),
                   "Company name2", Filter('Company name2', EmployeeID = _empID),
                   "Company name3", Filter('Company name3', EmployeeID = _empID),
                   "Company name4", Filter('Company name4', EmployeeID = _empID),
                   "Company name5", Filter('Company name5', EmployeeID = _empID),
                   "Company name6", Filter('Company name6', EmployeeID = _empID),
                   "Company name7", Filter('Company name7', EmployeeID = _empID),
                   "Company name8", Filter('Company name8', EmployeeID = _empID),
                   "Company name9", Filter('Company name9', EmployeeID = _empID),
                   "Company name10", Filter('Company name10', EmployeeID = _empID),
                   "Company name11", Filter('Company name11', EmployeeID = _empID),
                   "Company name12", Filter('Company name12', EmployeeID = _empID),
                   "Company name13", Filter('Company name13', EmployeeID = _empID),
                   "Company name14", Filter('Company name14', EmployeeID = _empID)
                )
               },
               First(
                   ShowColumns(_data, "CompanyID", "CompanyName", "CompanyID - CompanyName", "EmployeeID", "LastName", "FirstName", "EmployeeName", "EmployeePhone", "EmployeeEmail", "Position/Job Title", "Location)
               )
            )
        )
    
}

If your lists are all identical the above should not have any schema collisions.

Now all you need to do is reference the DataCard1.Update.EmpData property for your data.

So for example, the TextInput control for EmployeeName, the Default property would be the following:

DataCard1.Update.EmpData.EmployeeName

If the Company dropdown is changed or the employee ID entered in the textinput is changed, then the EmpData record will change automatically. There is no need to use any "On" actions to have it changed.

This is what I call a dynamic variable as it will act like a variable, but it is dynamic based on the changes you make.

Try it out and let me know if you run into any obstacles.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes · ‎08-12-2021

@vish3027

I am not seeing where record limit is an issue in what you are doing. You are only doing a LookUp - that is 1 record.

If EmpID is a numeric or a text column in your list, then there is no delegation issue.

So, what problem are you running into?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

vish3027 · ‎08-12-2021

@RandyHayes , Yes there should not be any issue.

But when i search 2001th row employee id, it says data not found

It only searches the empid in first 2000 rows and gives result.

I tried with excel file as well same issue. it doesn't consider 2001th row for lookup

RandyHayes · ‎08-12-2021

@vish3027

What is the column type of EmpID? Are you seeing any delegation warning in your formula?

Stay away from Excel for this - nothing is delegable there.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

happyume · ‎08-12-2021

2000 limit applies when you have delegation warning.

What is the column type of EmpID and have you tried by using static value of EmployeedID to be 2001st row in your SharePoint List / Dataverse? (I am assuming you are NOT using Excel as your data-source).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

RandyHayes · ‎08-12-2021

@happyume

Record limit and delegation are two different things. If you have a non-delegable formula, then PowerApps will need to pull all records and perform your criteria in-app...this is where record limit comes into play. However, you are *always* restrained by the record limit - delegable or not.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

Irfan · ‎08-12-2021

Hi Vish,

Please go through the below. I have been playing with 80K records in one of my app.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Preloading-more-than-2000-items-from-a-Share...

Thanks.

-Irfan

vish3027 · ‎08-13-2021

@RandyHayes , I don't see any warning.

Initially PowerApps setting is row limit 500 at that time it working upto 500 rows and failing for 501st item.

Now i have changed that to 2000 and it works upto 2000 and fails for 2001st item. No error and no warning thats suprising.

Could you please give it a try, If you want i can share the excel file with 2025 items and even the test app created by me.

TextInput1 is ID for filter

on button : ClearCollect(EmpData,LookUp(Table1,Text(EmployeeID)=TextInput1.Text))

Other textinputboxes :

First(EmpData).EmployeeName

First(EmpData).EmployeeEmail

First(EmpData).EmployeePhone

This is test app created by for test and it has same issue. It pulls data for 2000 rows items only.

vish3027 · ‎08-13-2021

@Irfan , Here in your scenario you are assuming no of employees to be fixed. like 2000,8000 or 80K and you are creating those many collection in advance and then creating single collection.

In my scenario, Currently my list has 2025 items so i can create 2 collection but in future no of items will increase.

I am not sure how many items will be there at any particular time like today 2025, after a month it may be 3000 then 4500 or more than that. So i am not sure how many collections will be there and how to create collection on the go and then create single collection.

In rest api, we check data.d.next != null but here how we can check.

If you say rowID/2000 will give no of collections but how to create then dynamically we are not sure what will be that value and how to select top 2000 then next 2000 and then....

creating collections dynamically is the concern.

Please help

RandyHayes · ‎08-13-2021

@vish3027

Hold the phone!!! You initially said you were working in SharePoint. Now you are mentioning Excel.

You cannot delegate to Excel!!

Your formula (although there is no need for a ClearCollect) will work fine in SharePoint - except for the Text part of your formula. If you introduce functions into your LookUp or Filter criteria, it will not be delegable.

Your formula should be:

LookUp(yourSharePointList, EmployeeID = Value(TextInput1.Text))

This formula works perfectly on large lists.

@Irfan in general, bringing in that many records to your app is a huge overhead that you usually don't need. Consider using a prefilter on your datasources and then narrowing filters to provide the information you want. Your app will crash less and be much more performant.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!