Power apps delegation and 2000 item limit using excel as datasource

[1210]

I have read that the "Starts With" Function can be used to assist with the limit of 2000 records in power apps.  I am using Excel as my datasource.  I have a long "Search" function for my gallery (filtering by only 2022 data) and I am looking for assistance to see if I can add the "Starts With" function to helps with 2000 record limitation.  Here is my search formula for the gallery:

Filter(
Search(If(EquipmentDropDown.Selected.Result="All" And ContractDropDown.Selected.Result="All",SortByColumns([@Transfers],"Date",Descending,"Ticket_x0020__x0023_",Descending),
EquipmentDropDown.Selected.Result="All" And ContractDropDown.Selected.Result<>"All",SortByColumns(Filter([@Transfers],Contract=ContractDropDown.Selected.Result),"Date",Descending,"Ticket_x0020__x0023_",Descending),
EquipmentDropDown.Selected.Result<>"All" And ContractDropDown.Selected.Result="All",SortByColumns(Filter([@Transfers],Equipment=EquipmentDropDown.Selected.Result),"Date",Descending,"Ticket_x0020__x0023_",Descending),EquipmentDropDown.Selected.Result<>"All" And ContractDropDown.Selected.Result<>"All",SortByColumns(Filter([@Transfers],Equipment=EquipmentDropDown.Selected.Result And Contract=ContractDropDown.Selected.Result),"Date",Descending,"Ticket_x0020__x0023_",Descending)),TextSearchTicket.Text,"Ticket_x0020__x0023_"),
CropYear="22")

Thanks,

[WarrenBelz]

Hi @1210 ,

Firstly, Excel is not the most forgiving data source to complex formulas (SharePoint is many levels ahead), however what are you using Search on ? The below should work - except I am wondering whether your multiple SortByColumns are also cauusing issues.

SortByColumns(
   Filter(
      [@Transfers],
      (
         EquipmentDropDown.Selected.Result="All" ||
         Equipment=EquipmentDropDown.Selected.Result
      ) &&
      (
          ContractDropDown.Selected.Result="All" ||
          Contract=ContractDropDown.Selected.Result
      ) &&
      CropYear = "22"
   ),
   "Date",
   Descending,
   "Ticket_x0020__x0023_",
   Descending
)

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.

Visit my blog Practical Power Apps

[1210]

Thank you for your response.  I was able to use that formula to filter by equipment and contract, but not by ticket# unfortunately.  But given that, I still am not able to see the newest data.  This doesn't seem to fix the 2000 record limit.  I have just reached it and I am not able to see any new data in my gallery

[WarrenBelz]

Hi @1210 ,

I suspected as much - you can try this and have up to 2,000 records in CropYear of "22".

With(
   {
      wList:
      Filter(
         [@Transfers],
         CropYear = "22"
      )
   },
   SortByColumns(
      Filter(
         wList,
         (
              EquipmentDropDown.Selected.Result="All" ||
              Equipment=EquipmentDropDown.Selected.Result
         ) &&
         (
              ContractDropDown.Selected.Result="All" ||
              Contract=ContractDropDown.Selected.Result
         ) 
      ),
      "Date",
      Descending,
      "Ticket_x0020__x0023_",
      Descending
   )
)

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.

Visit my blog Practical Power Apps

[1210]

Thanks for the updated attempt.  There seems to be an error in that 2nd formula.  I will work more tmw on trying to find it.  Thanks,

[WarrenBelz]

@1210 ,

Try now - bracket type error fixed (hard to see in Notepad)

[1210]

Thank you for fixing.  However, it is still limiting the records that I can see.  I only have 600ish records with Crop Year "22" so it should be pulling all those records in but it is still limiting me.  Any other ideas?

[WarrenBelz · Accepted solution]

Hi @1210 ,

Do you have your Delegation limit set to 2,000 ? If so, the code should certainly pull in all 600 with the top filter. Having said that, you are using Excel, so this may be yet another query limitation if it does not work. If you have access.

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.

Visit my blog Practical Power Apps

to SharePoint, I highly encourage you to switch to this.

[1210]

It seems to be working now;  Thanks,