cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
1210
Frequent Visitor

Power apps delegation and 2000 item limit using excel as datasource

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,

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

 

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

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
Frequent Visitor

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

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
Frequent Visitor

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,

@1210 ,

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

1210
Frequent Visitor

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?

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
Frequent Visitor

It seems to be working now;  Thanks,

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,756)