Working with more than 2000 records

(0) Share

Report

Posted on by AppsBe-Med

Is there any method to work with more than 2000 records in PowerApps?

When I open my form which has some Dropdowns and one Gallery I only get one single record instead of 33.

This is my code:

Form: OnVisible: ClearCollect(colAll;Quellendaten)

Gallery: Items: SortByColumns(Filter(colAll;If(IsBlank(DropdownAbteilung.SelectedText.Value); true ;Title=DropdownAbteilung.SelectedText.Value)&&If(IsBlank(DropdownKlasse.SelectedText.Value); true ;Klasse=DropdownKlasse.SelectedText.Value)&&If(IsBlank(DatePickerAU.SelectedDate); true ;Datum=DatePickerAU.SelectedDate));"Klasse";If(SortDescending1;Descending;Ascending))

Or is there any other way how I can filter records from my SharePoint list?

All responses (16)

Answers (2)

hpc 36 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
the filter works correct, I get all the items.
Thank you for your patient and extensive help. It is really much appreciated!
Best regards,
Hans Peter
v-siky-msft on at

Like (0)

Report

Re: Working with more than 2000 records

Hi @AppsBe-Med ,

it looks good now, but do you still think the gallery doesn't show all match item?
Is there any delegation warning there? How many items it should be?
BR
Sik
hpc 36 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
it works (All.png, All.png)!
How about the date transfer into numbers? This also doesn't work for big datasets.
Best regards,
Hans Peter

OnlyDA.png

All.png
AppsBe-Med 22 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
so far it works (if.png).
Best regards,
Hans Peter

If.jpg
Verified answer

v-siky-msft on at

Like (0)

Report

Re: Working with more than 2000 records

Hi @AppsBe-Med ,

Thanks for you debug, that reminds me a thread I ever answered: Hitting Non-Delegable query limit when I think I'm using delegable functions .
It seems the IF function in the SortByColumns/Filter function will cause the non-delegation error. And the workaround is to move If part function to the beginning of the formula, first to check IF condition. Although you have three if condition, the code could be much complex, I think it deserves a try.
Best regards,
Sik
AppsBe-Med 22 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
selection by "Klasse" works as well.
If the first two dropdowns ("Abteilung" and "Klasse") are blank and the DatePicker shows the date of today then all the records of today should be shown. How can I handle this as I may not use If()?
Best regards,
Hans Peter

Dropdowns.jpg
hpc 36 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
here is what I got: "Sort" and "Filter" works (SortFilter.png), but "If" doesn't work (If.png).
Is there any other way to select for "Abteilung" and "Klasse" or empty?
Best regards,
Hans Peter

SortFilter.png

If.png

If.png
v-siky-msft on at

Like (0)

Report
Re: Working with more than 2000 records

Hi @AppsBe-Med ,

Could you check if the Date2Num column has been all converted into number successfully?
What's the type of Title and Klasse column? Can you remove the sortbycolumns function and the first two condition to check if what causes the non-delegation?
I have test on my side: the Num is number column converted from date by ForAll function
Then I set the limitation for non-delegable requires to 1, set the gallery Items as below. Finally it still displays all items matched
Filter(test1,Num = Value(Text(DatePicker1.SelectedDate,"[$-en]ddmmyyy")))

Could you test, as I did, adding more function one by one to debug which function would cause the non-delegable error?
Best regards,
Sik
AppsBe-Med 22 on at

Like (0)

Report

Re: Working with more than 2000 records

Hi Sik,
thanks for your instructions.
Unfortunately I still get the same result. All data shown by limitation of non-delegable queries to 2000, only one old record by limitation of non-delegable queries to 500 records.
Best regards,
Hans Peter

Items.jpg

OnStart.jpg
Verified answer

v-siky-msft on at

Like (0)

Report
Re: Working with more than 2000 records

Hi @AppsBe-Med ,

Oh, Sorry for that the Calculated Column isn't delegable in PowerApps, it should be the real Text/Value column which is delegable.
so we should first to convert the date column to Number column, and then add Number column to Patch expression to keep the column up to date when creating the new items later.
To transfer the Date column to real Number column, I provide a workaround: Create a Number column is SP, use ForAll function to convert date to Number column in PowerApps.
ClearCollect(temcol;'SP list');;ForAll(temcol;Patch('SP list';LookUp('SP list'; ID=temcol[@ID]);{'Date2Num':Value(Text(temcol[@Datum];"ddmmyyy"))})) /* temcol[@ID] isn't delegable, set the limit to 2000 */
Then apply Date2Text column into the code.
SortByColumns(Filter(colAll;If(IsBlank(DropdownAbteilung.SelectedText.Value); true ;Title=DropdownAbteilung.SelectedText.Value)&&If(IsBlank(DropdownKlasse.SelectedText.Value); true ;Klasse=DropdownKlasse.SelectedText.Value)&&If(IsBlank(DatePickerAU.SelectedDate); true ;Date2Num=Value(Text(DatePickerAU.SelectedDate,"ddmmyyy"))));"Klasse";If(SortDescending1;Descending;Ascending))
Hope this can help.
Best regards,
Sik