cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Big Data SharePoint

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))

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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))

 

View solution in original post

Highlighted
Dual Super User III
Dual Super User III

Re: Big Data SharePoint

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))


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
Highlighted
Dual Super User III
Dual Super User III

Re: Big Data SharePoint

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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

Highlighted
Dual Super User III
Dual Super User III

Re: Big Data SharePoint

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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))

 

View solution in original post

Highlighted
Dual Super User III
Dual Super User III

Re: Big Data SharePoint

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



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

I am sorry, but i don´t understand

Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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

Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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))

 

Highlighted
Helper IV
Helper IV

Re: Big Data SharePoint

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))

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,759)