Unanswered

Collection of Sharepoint List data holding customers > 2000 rows

(1) Share

Report

Posted on by MarcB1966

In power app, I have a screen which collects sharepoint data (Customers with an ID starting at 1 al the way up to 3600 today and counting), which is partially working.

But, it only collects the first 2000 records, despite the formula collecting the data in parts (see below).

I'm trying to figure out why the result is not picking Customers from 2000 up to the current 3600

The formula is embedded in the "onVisible" property :

//show loading overlay
Set(varLoading,true);
//data refresh Customer_Prod List
Concurrent(
ClearCollect(colCustomerPart1,Filter(Customer_Prod, ID > 1 && ID<= 1000)),
ClearCollect(colCustomerPart2,Filter(Customer_Prod, ID > 1000 && ID<= 2000)),
ClearCollect(colCustomerPart3,Filter(Customer_Prod, ID > 2000 && ID<= 3000)),
ClearCollect(colCustomerPart4,Filter(Customer_Prod, ID > 3000 && ID<= 4000))
);

ClearCollect(colCustomerALL, colCustomerPart1 , colCustomerPart2, colCustomerPart3, colCustomerPart4);

Clear(colCustomerPart1);
Clear(colCustomerPart2);
Clear(colCustomerPart3);
Clear(colCustomerPart4);

//hide Loading overlay
Set(varLoading,false)

Much obliged for finding a way to sort this out 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
It's because despite delegation, even when your function calls are delegable no function can escape the data row limit. I see you raised the data row limit to the max possible, 2000. However any individual function call is subject to that limit.

Your single call to ClearCollect is itself subject to the 2000 data row limit, specifically this one:

ClearCollect(colCustomerALL, colCustomerPart1 , colCustomerPart2, colCustomerPart3, colCustomerPart4); //Your single function call to ClearCollect is itself subject to the data row limit here.

So, try this instead:

// Initialize the master collection Clear(colCustomerALL); // Generate sequence of numbers, each representing a range of IDs. ForAll( Sequence(4), // This will generate [1,2,3,4] With( { wValue: Value }, ClearCollect( colTempCustomer, Filter( Customer_Prod, ID >= ((wValue - 1) * 1000 + 1) && ID <= wValue * 1000 ) ) ); Collect(colCustomerALL, colTempCustomer) ); // Clear temporary collection Clear(colTempCustomer);

I strongly do not recommend using ForAll in this way like the above, without using it's output Table, and I also don't usually recommend having more than 2000 records in memory, but if you really have to for some special use cases, you may try like the above.

See if it helps @MarcB1966

Was this reply helpful? Yes No
MarcB1966 73 on at

Like
a
(1)

Report
Copy link

Link copied!

Thx for the quick reply 🙂
I've meanwhile "replaced" the previous with your suggested solution. Unfortunately, this results in a dropdown (previously holding all first 2000 customers) which is now empty.
The reason why I need to 'collect' all customers, is while technicians need to select based on a dropdown box with search functionality the customer they are visiting.
Will further look into it why the customer database is no longer showing 😞

Was this reply helpful? Yes No
MarcB1966 73 on at

Like
a
(1)

Report
Copy link

Link copied!

Not sure if this helps, but this is the error message I get ...

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@MarcB1966

Try to debug like this, by testing with buttons first, then seeing if we can identify why the original formula didn't work:

A piecemeal or "Load More" approach can be implemented to fetch data incrementally, providing both a performance benefit and easier debugging.

Here's how you can try it:

Reset Button:

Place a button for resetting the collection. Let's call it ButtonReset.

For ButtonReset, the OnSelect property would have:

ClearCollect(colCustomerInitial, Filter(Customer_Prod, ID > 0 && ID <= 1000)); Set(gloLastLoadedID, 1000); // Reset the last loaded ID

OnVisible Property:

Your screen's OnVisible property could then simply use the Select function to trigger the reset button.

Select(ButtonReset);

Load More Button:

Place a "Load More" button below your dropdown.

On its OnSelect, you would load the next set of data and append it to your existing collection.

// Load More Button's OnSelect ClearCollect(colCustomerNext, Filter(Customer_Prod, ID > gloLastLoadedID && ID <= gloLastLoadedID + 1000)); Collect(colCustomerInitial, colCustomerNext); Set(gloLastLoadedID, gloLastLoadedID + 1000);

Dropdown Items:

For this test, comment out what you have now in the Items property of the dropdown
Then, for now it should be set to:

colCustomerInitial

See if the above works as a test @MarcB1966 , then we might check what was wrong with the formula I gave initially.

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@MarcB1966 Hmm that might help, which function is the one it says which cannot be invoked within ForAll, can you take a screenshot of the formula bar?

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@MarcB1966 Oh yeah I think Clear and ClearCollect can't be used inside ForAll.

Try this below instead:

// Initialize the master collection Clear(colCustomerALL); // Generate sequence of numbers, each representing a range of IDs. ForAll( Sequence(4), // This will generate [1,2,3,4] With( { wValue: Value }, // Clear the temporary collection by removing all records RemoveIf(colTempCustomer, true); // Populate the temporary collection Collect( colTempCustomer, Filter( Customer_Prod, ID >= ((wValue - 1) * 1000 + 1) && ID <= wValue * 1000 ) ) ); // Add the temporary collection to the master collection Collect(colCustomerALL, colTempCustomer) ); // Clear temporary collection for the next round RemoveIf(colTempCustomer, true);

Check if above works better @MarcB1966

Was this reply helpful? Yes No
MarcB1966 73 on at

Like
a
(0)

Report
Copy link

Link copied!

@poweractivate ... thx for your continued help on the subject 🙂
I first tried the button approach in your previous message, but that didn't work as expected. Wasn't sure if I had to keep the initial code or not. Tried without, and that didn't work (blanc dropdown box).
Then I embedded (after removing all the previous) your updated 'For All' (without the Clear and ClearCollect bits), and that works for the first 2000 records (first 2000 customers available in the dropdown box).
However, the remaining 1600 (from 2001 up to 3600) are still missing 🤔
I keep having the "Delegation warning" on the OnVisible property of that screen holding below code (copy paste of yours).

Note : not aware of how you create those separate boxes with code

Was this reply helpful? Yes No