Hello , Need help on 500k row limit in Power Apps

(0) Share

Report

Posted on by bsoleti

Hello,

I have a powerapp , using a Collection at AppStart to collect records from my Sharepoint library.

The Sharepoint library currently has 560 rows.

Then i have a text field to input which is a Application ID , I key in the Application ID and hit a search button , which will retreive rest of the columns from sharepoint lib - (Customer (choice field) , name (text) , place Itext), source (choice))

Also i added a column ID from sharepoint View , when i search for Application ID which has assigned ID in sharepoint over 500 (520) and hit search button i dont get any results , when i use an Application ID which has ID in sharepoint say 400 i will get the results.

I Know if can change the setting in powerapps to 2000 , but my data set will grow beyond 2000 in next few days .

Need help to solve this issue urgently.

On Start APP CODE IS

Concurrent(
ClearCollect(VarCollection1, Filter('Wheeler Docs', ID >= 1 && ID <= 500)),
ClearCollect(VarCollection2, Filter('Wheeler Docs', ID >= 501 && ID <= 600)));ClearCollect(CollectK, VarCollection1, VarCollection2)

Search Button Code is

UpdateContext({CustomerNameVar:First(Filter(CollectK,ApplicationNumber=TextInput1_AppID_2.Text)).Customer});
UpdateContext({NameVar:First(Filter(CollectK,ApplicationNumber=TextInput1_AppID_2.Text)).Name});UpdateContext({DocSourceVar1:First(Filter(CollectK,ApplicationNumber=TextInput1_AppID_2.Text)).SourceSystem});UpdateContext({PlaceVar:First(Filter(CollectK,ApplicationNumber=TextInput1_AppID_2.Text)).Place})

Thanks & Regards

Categories:

Building Power Apps

All responses (8)

Answers (2)

Sort by

WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @bsoleti ,
I posted a solution to this recently.
Happy to help further with any clarification.

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

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @bsoleti ,

SharePoint only supports the equal ('=') operation for delegation on an ID field, so your filter functions are not delegable.

The workaround is to create a new Number column and copy the ID field via MS flow, and then Filter the sharepoint based on that delegable number column so that your scenario would work.

1. Create a new Automated flow from blank triggered when a file is created (properties only)(since you mentioned Sharepoint library)

2. Add a Update file properties action, set Id column and Number column to ID from the output of trigger

This flow will automatically synchronize Id value to Number column for these new created items.

For these existing items, you can synchronize them by PowerApps UpdateIf function.

UpdateIf('SP Library', true, {NumberColumn: ID})

Note: the UpdateIf function is not delegable, you need to increase the threshold to 2000, since you said that 'The Sharepoint library currently has 560 rows.'

When all items ID values have been synchronized the NumberColumn, modify the App.OnStart property as follows:

Concurrent( ClearCollect(VarCollection1, Filter('Wheeler Docs', NumberColumn >= 1 && NumberColumn <= 500)), ClearCollect(VarCollection2, Filter('Wheeler Docs', NumberColumn >= 501 && NumberColumn <= 600))); ClearCollect(CollectK, VarCollection1, VarCollection2)

Hope this helps.

Sik
WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@v-siky-msft ,
Not sure why you repeated the solution link I posted by another method, but you are correct, that is the process. It can also be done very simply buy a SharePoint Designer workflow (yes, we still use those).
@bsoleti , happy to elaborate further if required.

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

bsoleti 70 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello Sik,

Thanks for your feedback . As i am new to Power Apps i have some questions if you don't mind.

1) Should i create a new column in my existing sharepoint Library as below ?

2) i started creating a new flow as below , but how do i an item to assign the ID to the NumColumn like you have shown

3) After the flow is created and saved , how is it triggered ? as its automatic meaning i wont be calling from my app correct ?

4) Where do i call this Update function from ?

For these existing items, you can synchronize them by PowerApps UpdateIf function.
UpdateIf('SP Library', true, {NumberColumn: ID})
Note: the UpdateIf function is not delegable, you need to increase the threshold to 2000, since you said that 'The Sharepoint library currently has 560 rows.'

When all items ID values have been synchronized the NumberColumn, modify the App.OnStart property as follows:

Thanks & Regards
bsoleti 70 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry with Attathment now

Thanks

Doc3.docx
v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @bsoleti ,

1) Should i create a new column in my existing sharepoint Library as below ?

Yes, that's right. you need to create a new Number type column.

2) i started creating a new flow as below , but how do i an item to assign the ID to the NumColumn like you have shown

If the NumList column has been created in Wheeler Docs library, the NumList field should should appear in the Update file properties. Then go to NumList field, select add Dynamics content, select ID from the output of trigger 'When a file is created (properties only)'.

3) After the flow is created and saved , how is it triggered ? as its automatic meaning i wont be calling from my app correct ?

Yes, it is automatically triggered. When a new item or files created in library, the NumList column will be updated with the ID value.

4) Where do i call this Update function from ?

The UpdateIf formula should be called in PowerApps canvas app, its intention is to update NumList column of all existing items with its corresponding ID value.

In canvas app, you need to add the sharepoint connection to data source, add a button, and then set the UpdateIf formulas to the OnSelect property of button. When this is all done, preview your app, click the button, and the update will start.

Hope this helps.
Sik
bsoleti 70 on at

Like
a
(0)

Report
Copy link

Link copied!

Mr.Sik & Warren,

Thanks very much for your help. I was able to resolve my issue with your advice and guidance.

Regards
bsoleti 70 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Sik,

The solution you have provided seems to work. I am facing an issue with the Documents uploaded to the sharepoint.

The issue is that in sharepoint the Modified By column is displaying always my name , instead of the user's name who actually uploaded the document. I want the correct user name in the modified by column who actually uplaoded the doc via the power apps screen and flow.

The problem is the flow i created to generate the Unique Id for the document is mapped to the NumList , which is a Number column in sharepoint . The flow uploads the document with metadata into the sharepoint and the next version creates the NumList ID from file update properties on the flow and updates the Modified By column with my ID . I dont want this. How can i resolve this ?

Any advice to resolve this appreciated

Thanks