Collection of Sharepoint List data holding customers > 2000 rows

[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 🙂

[poweractivate]

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 

[MarcB1966]

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 😞

[MarcB1966]

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

[poweractivate]

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

[poweractivate]

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

[poweractivate]

@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 

[MarcB1966]

@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