Answered

Big Data SharePoint

(0) Share

Report

Posted on by CarlosN

Good Morning,
I have an application that uses a sharepoint list as a database, since the database list passed 2000 records and I can't see the records from 2001 onwards, is it possible to see these records in a gallery? In the code I have a search to search in a text box, also a search by drop-down list and buttons to filter when selecting, this database was created in a month of work. This is the code I am using. I am attaching a capture. Your help please.

SortByColumns (Filter (AddColumns ('Solicitudes Expres'; "Buscar"; Cliente & Referencia & Producto & UsuarioEst & UsuarioVentas & ID & Observacion);
 StartsWith (Estatus; FilterIng) && TextSearchBox1.Text in Buscar && DdownPIExpresEstilo.SelectedText.TipoEstilo in Estilo); "CARNÉ DE IDENTIDAD"; Si (SortDescendingPI; Descendente; Ascendente))

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (2)

Pstork1 69,042 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You can definitely use a SharePoint data source greater than 2,000 items as long as you pay attention to delegation. Several of the functions you are using are non-delegable and cut off the records retrieved at 2,000. Neither AddColumns or 'in' are delegable in SharePoint. The key to working with large data sources is to use inner delegable functions to get the data set down below 2,000 records before applying non-delegable functions like AddColumns.

Was this reply helpful? Yes No
CarlosN on at

Like (0)

Report
Copy link

Link copied!

Thanks for the observation. How can I modify my code? to continue maintaining the functions of the application.

Was this reply helpful? Yes No
Pstork1 69,042 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
You need to move the AddColumns and use of 'in' to an outer function and pre-filter your data source using delegable queries. something like this:

SortByColumns ( AddColumns ( Filter( Filter('Solicitudes Expres';StartsWith (Estatus; FilterIng)); TextSearchBox1.Text in Buscar && DdownPIExpresEstilo.SelectedText.TipoEstilo in Estilo); "Buscar"; Cliente & Referencia & Producto & UsuarioEst & UsuarioVentas & ID & Observacion);"CARNÉ DE IDENTIDAD"; Si (SortDescendingPI; Descendente; Ascendente))

If the StartsWith filter can get the record count below 2,000 then it won't matter that the outer filter uses in and the AddColumns is non-delegable.

Was this reply helpful? Yes No
Verified answer

CarlosN on at

Like (0)

Report
Copy link

Link copied!
If I want the search in text box to be also delegable. how would the code look?
I am looking for the last records in the text box (TextSearchBox1:Text) and it does not show them.
SortByColumns( AddColumns(Filter(Filter('Solicitudes Expres';StartsWith(Estatus;FilterIng)); TextSearchBox1.Text in "Buscar" && DdownPIExpresEstilo.SelectedText.Value in Estilo); "Buscar";Cliente & Referencia & Producto & UsuarioEst & UsuarioVentas & ID & Observacion);"ID"; If(SortDescendingPI;Descending;Ascending))

Was this reply helpful? Yes No
Pstork1 69,042 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

'in' isn't a delegable operator for SharePoint. So the only way to use it is to pre-filter the records to below 2,000. Your original filter used all "ands". So the filter on StartsWith should contain the record you need to search for. If it doesn't then either the use of "And" wouldn't have worked in the original filter or the StartsWith filter doesn't get you below the 2,000 record limit.

I suggest starting with the simplest delegable filter and then add layers to the outside after you have a delegable filter that reliably gets you below the threshold.

I should also point out that the Gallery will only display the first 100 records returned until you try to scroll to record 101. Then it will add the second hundred, and so on. But that's just what is displayed in the gallery, not what is available in the data source.

Was this reply helpful? Yes No
CarlosN on at

Like (0)

Report
Copy link

Link copied!

I am sorry, but i don´t understand

Was this reply helpful? Yes No
CarlosN on at

Like (0)

Report
Copy link

Link copied!

I don't know if I'm doing something wrong, but some of the code doesn't work for me. I attached the capture

Was this reply helpful? Yes No
CarlosN on at

Like (0)

Report
Copy link

Link copied!
Is it possible to use this code ?, I think it is working for me.
SortByColumns( Filter( 'Solicitudes Expres';StartsWith(Estatus;FilterIng) && StartsWith(Cliente;TextSearchBox1.Text) || StartsWith(Referencia;TextSearchBox1.Text) ||StartsWith(UsuarioEst;TextSearchBox1.Text)); "ID"; If(SortDescendingPI;Descending;Ascending))

Was this reply helpful? Yes No
CarlosN on at

Like (0)

Report
Copy link

Link copied!
I used this code, but I can't get it to filter or search by ID from the text box.
SortByColumns( Filter(Filter( 'Solicitudes Expres'; StartsWith(Estatus;FilterIng)); StartsWith(Cliente;TextSearchBox1.Text); StartsWith(Producto;TextSearchBox1.Text); StartsWith(UsuarioEst;TextSearchBox1.Text); StartsWith(UsuarioVentas;TextSearchBox1.Text); StartsWith(Estilo;DdownPIExpresEstilo.SelectedText.Value)); "ID"; If(SortDescendingPI;Descending;Ascending))

Was this reply helpful? Yes No
Verified answer

Pstork1 69,042 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
None of the fields in your Filter statement are the ID field. So why would you expect it to Filter by ID?

If you do add a search for ID to the Filter make sure you handle it as an Integer. It needs to be something like this

Filter(Filter(Test_New,StartsWith(Title,TextInput2.Text)),ID = Value(TextInput1.Text))

Was this reply helpful? Yes No