Unanswered

Creating a Set in OnChange with multiple Data Sources

(0) Share

Report

Posted on by MPayne-94

I am currently building a PowerApp Form that is utilizing 10 different SharePoint Lists as the Delegation limit is 2000 rows.

I am trying to create a Set that will then change other information in the form based on the value entered.

What I have as of right now is below but doesnt seem to be working:

Set(CustomerRows,If(Left(AccountNo.Text,2) in "11011121314",Filter('SGWS KY Accounts 1-14',AccountNo.Text=Text('Customer No')),
If(Left((AccountNo.Text,2) in "1516171819",Filter('SGWS KY Acounts 15-19',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 2,Filter('SGWS KY Accounts 2',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 3,Filter('SGWS KY Accounts 3',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 4,Filter('SGWS KY Accounts 4',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 5,Filter('SGWS KY Accounts 5',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 6,Filter('SGWS KY Accounts 6',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 7,Filter('SGWS KY Accounts 7',AccountNo.Text=Text('Customer No')),
If(Left(AccountNo.Text,1) = 8,Filter('SGWS KY Accounts 8',AccountNo.Text=Text('Customer No')),
Filter('SGWS KY Accounts 9',AccountNo.Text=Text('Customer No')))))))))))),'Customer No')

AccountNo is the test field where the information will be entered.

SGWS KY Accounts 1-14 and SGWS KY Acounts 15-19 are SharePoint lists where Account numbers start with one and I split them to be below the 2000 row limit for Filter. Becasue of this the Left has to be the first 2 numbers, all other are first number.

The Formula I am using to pull related information based on Set also below

Text(First(CustomerRows).'Customer Name')

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

RezaDorrani 12,143 on at

Like (0)

Report

Hi @MPayne-94

If you build delegable queries then you will not be bound to the 2000 item limit
As your data keeps growing continously adding lists will not be a maintenable solution

I did a blog series on PowerApps delegation with respect to SharePoint as a data source and has all tips/tricks on how to filter/query data with any column type and avoiding the delegation limits

http://rezadorrani.com/index.php/2019/09/18/powerapps-delegation-with-sharepoint-as-a-data-source-with-respect-to-all-column-types/

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

Was this reply helpful? Yes No
MPayne-94 41 on at

Like (0)

Report
@RezaDorrani

I followed your video and retooled my formula to below:
Set(CustomerRows,SortByColumns(Filter('SGWS KY Accounts',AccountNo.Text=Text('Customer No')),"Customer_x0020_No",Ascending))
I have turned to the Data Source 'SGWS KY Accounts' that currently has 7800 items but that shouldnt be an issue becasue I am using a Delegable query yet the 2000 row limit is still affecting my app.

Do you have any further insights on how to fix?

AccountNo is the text field in the app that a user will be inputting a Customer Number that should match a value in 'SGWS KY Accounts' column Customer No.

Was this reply helpful? Yes No