Handling more that 2000 items in powerapps

[vish3027]

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.

[vish3027]

@RandyHayes , Yes its list for test purpose i tried in excel as well,

I am using collection as i have multiple sharePoint list in actual scenario(15) and based on company name i am calling lookup on that list and taking that entry collection and this will be used to assign values to multiple fields.

If(company = A, ClearCollect(Empdata,Lookup on list A,IF(company =B,ClearCollect(Empdata,Lookup on list N... and so on.

Also I have tried your formula it gives me results but my question is, what is the difference between below 2 formulas:

ClearCollect(EmpData,LookUp('Test 2000 rows',Text(EmployeeID)=TextInput1.Text)) : Old formula

ClearCollect(EmpData,LookUp('Test 2000 rows',EmployeeID=Value(TextInput1.Text))); : Your suggested formula

None of them gives delegation warning.

Also if you can suggest some documentation on this will be helpful.

Thanks for your help.

[RandyHayes]

@vish3027 

The difference between the two formulas is subtle - in the first one, you are asking PowerApps to convert the column value of EmployeeID to text.  This means that PowerApps would need to pull down every record in order to convert it to text.  This will only happen for the number of records in your record limit.

So the session manager sees the criteria as:

   Go through each record in the source and convert the EmployeeID to Text and see if it is equal to some other text.

That little Convert part is the problem.

The second formula converts the Text that is IN your app to a Value.  So, when the criteria is sent to the session manager to evaluate, which is more like:

   Go through each record in the source and return the records where the EmployeeID number is equal to some number.

No conversion is needed at that level.

As for your formula for the collection - I would strongly advise against using different datasources for your formula.  PowerApps is highly schema based and when you mix schemas, it can cause unpredictable results.

And, there is really no need for a collection!  You are using a collection with one record in it.  A collection is an in-memory database that should only really be used when you need such functionality.  If you don't need to add/remove/update records in a table (which, since you are assigning only one record...is a one record table), then I would skip them and use your lookup directly where you need it.  There is no advantage to a collection in your scenario and it just adds overhead. 

[vish3027]

@RandyHayes Thanks for detailed info and this is really helpful.

Regarding my scenario, I have 5-6 textboxes which will be depend on lookup result.

I need to lookup with 15 SharePoint lists based on Company Dropdown, 

So if i go with assigning direct lookup value to each field and i need to write that condition check at every textbox level.

If

Comapny A , Lookup on list A

CompanyB, Lookup on list B

....

...

Company 15, Lookup on list 15

As my employees are present in different list based on their company.

To avoid 15 condition check at each level, I am creating collection.

Is there any other way to handle it effectively.

One way is variable creation but i am not sure how effective that will be.

Thanks

[RandyHayes]

@vish3027 

Yes, you can incorporate that all in the dropdown.  No variables or collections needed.

What is your items property for the dropdown and what are some of your lookup formulas?

[vish3027]

@RandyHayes 

I have dropdown where user select company name

It is choice field from sharepoint with 14 values.....  This is one user input

Each company has separate emp list

I have textbox where user enters EmployeeID... This is another user input

Based on above 2 values I pull data from SharePoint list(one SP list per company) and that will be assigned to 6 textboxes like empname,phone,email,role etc.

and on change of emp id i am creating collections with below formula.... 

If(
varCompanyName = "Company name1",
ClearCollect(
EmpData,
LookUp('Company name1',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name2",
ClearCollect(
EmpData,
LookUp('Company name2',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name3",
ClearCollect(
EmpData,
LookUp('Company name3',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name4",
ClearCollect(
EmpData,
LookUp('Company name4',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name5",
ClearCollect(
EmpData,
LookUp('Company name5',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name6",
ClearCollect(
EmpData,
LookUp('Company name6',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name7",
ClearCollect(
EmpData,
LookUp('Company name7',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name8",
ClearCollect(
EmpData,
LookUp('Company name8',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name9",
ClearCollect(
EmpData,
LookUp('Company name9',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name10"
ClearCollect(
EmpData,
LookUp('Company name10',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name11",
ClearCollect(
EmpData,
LookUp('Company name11',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name12",
ClearCollect(
EmpData,
LookUp('Company name12',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name13",
ClearCollect(
EmpData,
LookUp('Company name13',Text(EmployeeID) = DataCardValue31.Text
)
),
If(
varCompanyName = "Company name14",
ClearCollect(
EmpData,
LookUp('Company name14',Text(EmployeeID) = DataCardValue31.Text
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

As data is coming from multiple list i need to make call which sp list to select based company name selected in dropdown.

Thanks

[RandyHayes]

@vish3027 

Couple of questions:

1) What is the Items property formula for the dropdown?

2) Do all companyname lists have the same columns - empname,phone,email,role etc. And, what are the exact names of all of them?

3) How are you setting varCompanyName?

[vish3027]

@RandyHayes 

1. Its choice field from SharePoint, All the choices added at SharePoint level. No changes in Items property in app:

Choices([@NewDischargeRequest].CompanyName) - Exact same name is used for other 14 lists for employee master

2. Yes, Same column

CompanyID

CompanyName

CompanyID  - CompanyName

EmployeeID

LastName

FirstName

EmployeeName

EmployeePhone

EmployeeEmail

Position/Job Title

Location

3.On change of dropdown:

Set(varCompanyName=ddlcompanyname.selected.value ..... Creating variable so i can use it at many places

Thanks

[RandyHayes · Accepted solution]

@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...

1. Add a new scrollable screen to your app.
2. In the new screen there will be a Canvas control in it.  
3. Click on the Canvas control in the left control tree.  This will select the canvas control in the screen.
4. 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.
5. 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.
6. In your Canvas control, you will have a DataCard control (I'll call is DataCard1 for this sample).  Select the datacard control in it.
7. 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. 

[vish3027]

@RandyHayes , Thank You so much for everything.

I learnt new concepts as well new control from PowerApps. Thank You so much for Sharing such valuable info. This is definitely helpful in many future projects to me.

Please do share if you such info, I will be happy to learn.

Please let me know if we have any training/documentation for this and similar new controls in powerapps.

Thank You🙂👍.

[RandyHayes]

@vish3027 

Happy to share!  Personally I find the dynamic variables, as I call them, are one of the more powerful abilities in PowerApps.  I will tell you that you'll find nothing about this documented anywhere.  In fact, there is no reference to anything about the Canvas and its contained DataCard.  All references in the docs are just about datacards in forms.  I looked at that Update property on the canvas datacard for a year and scratched my head...then one day I saw a purpose for it.

At some point when my workload gets a little lighter, I will be putting out my video on these type of things.