Trying to collect 4000 items, getting error on Sort

(0) Share

Report

Posted on by wonka1234

920 Super User 2024 Season 1

Hi all,

Trying to collect around 4000 items. Getting error Sort has some invalid arguments. Name isnt Valid. Descending isnt recorgnized.

getting error on line - Collect(MyTable2, FirstN(Sort(Distinct([@'Test List'],'Control Title'),Descending),2000));

Here is my code -

Collect(MyTable1, Distinct([@'Test List'],'Control Title'));
Collect(MyTable2, FirstN(Sort(Distinct([@'Test List'],'Control Title'),Descending),2000));
Collect(MyTable, MyTable1, MyTable2)

anyone know where Im going wrong?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

LowCodeJim 306 Moderator on at

Like (1)

Report
Hi @wonka1234 ,

Can you confirm where you have "Descending" this is actually from the Enum and evaluates out to "SortOrder.Descending". So your code should actually be:
Collect(MyTable2, FirstN(Sort(Distinct([@'Test List'],'Control Title'),SortOrder.Descending),2000));

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.

Was this reply helpful? Yes No
wonka1234 920 Super User 2024 Season 1 on at

Like (0)

Report

You are correct! thanks! but whats weird is that is it collecting the same table twice, i want to collec the first 2000 and then the second 2000, do you know where im going wrong?

Was this reply helpful? Yes No
LowCodeJim 306 Moderator on at

Like (0)

Report

Hi @wonka1234 ,

Your two statements are doing the same thing, the first gets the first 2000 items (limited by the app settings) then the second is again asking for the FirstN items with N being 2000. Getting large numbers of items from SharePoint can be challenging and if possible I would say you want to design your app so your are querying SharePoint for the items you need. If you do need them all at once though @mdevaney has a post about collecting large collections here that may help: https://www.matthewdevaney.com/create-power-apps-collections-over-2000-rows-with-these-4-tricks/#Collect-Data-In-Chunks-With-For-All-Loops

Was this reply helpful? Yes No
wonka1234 920 Super User 2024 Season 1 on at

Like (0)

Report

@SharePointJB thanks. Any idea on where im going wrong here? Trying @mdevaney solution but getting an error.

getting an error - Filter(MyTable2, Not(ID in MyTable1.ID))) . 'ID' isnt recognized.

full code -

Concurrent(
ClearCollect(
MyTable1,
Sort(Distinct([@'Test List'],'Control Title'),SortOrder.Ascending)
),
ClearCollect(
MyTable2,
Sort(Distinct([@'Test List'],'Control Title'),SortOrder.Descending)
)
);

ClearCollect(
MyTable,
MyTable1,
Filter(MyTable2, Not(ID in MyTable1.ID)))

Was this reply helpful? Yes No
LowCodeJim 306 Moderator on at

Like (0)

Report
Hi @wonka1234 ,

I think it is the distinct function that is causing the issue. This will just be returning a collection with a single column "Value" that contains a distinct list of "Control Titles". I think you would want something like this to get the first and last 500-2000 (depending on app settings) list items:
Concurrent( ClearCollect( MyTable1, Sort( 'Another Test List', ID, SortOrder.Ascending ) ), ClearCollect( MyTable2, Sort( 'Another Test List', ID, SortOrder.Descending ) ) ); ClearCollect( MyTable, MyTable1, Filter( MyTable2, Not(ID in MyTable1.ID) ) )

Was this reply helpful? Yes No
wonka1234 920 Super User 2024 Season 1 on at

Like (1)

Report
@SharePointJB

I should mention that I need only Control Titles values as I am makin g a collection for that one column only. It will serve as a dropdown values a user selects.

So I got this now -

Concurrent( ClearCollect( MyTable1, Sort([@'Test List'],'Control Title',SortOrder.Ascending) ), ClearCollect( MyTable2, Sort([@'Test List'],'Control Title',SortOrder.Descending) ) ); ClearCollect( MyTable, MyTable1, Filter(MyTable2, Not('Control Title' in MyTable1.'Control Title')));

Was this reply helpful? Yes No