Gallery to Search by multiple fields and Sort by Latest 500 Entries. (Formula Help)

[Questionasking]

Hi,

I wish to use a searchbox to search a gallery by two fields 'DisplayName' and 'Assett. + Only Display the latest 500 entries, however i can only get errors could someone please enlighten me to why my code is wrong and perhaps show myself the correct code?

Thankyou, here is my incorrect code

FirstN(
       Sort(
             Filter([@Prestarts],TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
              ),[@ID], If(SortDescending1, Ascending, Descending
        ),500
)

Thankyou

[v-bofeng-msft]

Hi @Questionasking :

Please try this formula:

FirstN(
    Sort(
         Filter(Prestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value),

         ID,

         If(SortDescending1, Ascending, Descending)

     ),

     500
)

Best Regards,

Bof

[Questionasking]

I should say i know how to get it working using a collection

Collect(colPrestarts, FirstN(Sort(Prestarts,ID,Descending),500))

and 

FirstN(
       Sort(
             Filter(colPrestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
              ,ID, If(SortDescending1, Ascending, Descending
        )),1000
)

However its not what i want to achieve for another reason..

[v-bofeng-msft]

Hi @Questionasking :

Do you want to replace colPrestarts with items in the gallery?

If so,the formula should be:

FirstN(
Sort(
Filter(GalleryName.AllItems,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
,ID, If(SortDescending1, Ascending, Descending
)),1000
)

Best Regards,

Bof

[Questionasking]

---

@v-bofeng-msft wrote:

Hi @Questionasking :

Please try this formula:

FirstN(
    Sort(
         Filter(Prestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value),

         ID,

         If(SortDescending1, Ascending, Descending)

     ),

     500
)

Best Regards,

Bof

---

Sorry that only displays the first 500 entries, check my other reply. im working with a list of over 5000 entries. (it cannot be made smaller..

[Questionasking]

---

@v-bofeng-msft wrote:

Hi @Questionasking :

Do you want to replace colPrestarts with items in the gallery?

If so,the formula should be:

FirstN(
Sort(
Filter(GalleryName.AllItems,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
,ID, If(SortDescending1, Ascending, Descending
)),1000
)

Best Regards,

Bof

---

No Sorry, i was just saying that using a collection to download the latest 500 entries i can get the gallery to display the items i want, however when i use the same code with the sharepoint online list i get only the first 500 items ID in a sharepoint list

[v-bofeng-msft]

Hi @Questionasking :

Do you need your Gallery to display all 5000 items in the data source? (Or make your formula can handle 5000 items in the data source)

First of all,Because you used "FirstN(XX,500)", your formula can only return 500 results at most.

Secondly,could tell me what colPrestarts is ?

I assume colPrestarts  is a SharePoint list 

The Point is that your formula cannot be delegated.So your formula can only process up to the first 2000 records in the data source

• "FirstN" and "in" are functions that cannot be delegated
• Lookup and choices column cannot be delegated

I think this link will help you a lot:
Power Apps delegable functions and operations for SharePoint 

Understand delegation in a canvas app 

At present, the only way is to save the items in colPrestarts in batches in a collection. Then filter this collection.

For example:

Collect(Prestarts,Filter(colPrestarts,Type="Type1"));/*I assume there is a single line of text column named Type*/
Collect(Prestarts,Filter(colPrestarts,Type="Type2"));
Collect(Prestarts,Filter(colPrestarts,Type="Type3"));
Collect(Prestarts,Filter(colPrestarts,Type="Type4"));
……

Best Regard,

Bof