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

Delegation warning for Share Point List vs Excel

Hi -

 

I get a delegation warning when I tried a search function using sharepoint list vs excel. The list contain 248 records. 

how do I create a search option using the sharepoint list? my sharepoint list is from an access database. I tried search and filter.  Search(DrugTBL,SearchBox.Text,”DrugName”

please see attached.

 

thank you 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @Bunly11 ,

You need something like this - you need to "wrap" functions inside each other when you build multi-function filters.

With(
   {TBL:DrugTBL},
   Search(
      Sort(
         TBL,
         PageNumber,
         If(
            SortAscending1,
            Descending,
            Ascending
         )
      ),
      SearchBox.Text,
      "DrugName"
   )
)

As I mentioned, you will only get row numbers up to your Delegation limit in TBL to search from but will get no Delegation warning. I did a blog on using the With() statement covering this and it may be useful to you.

 

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.

View solution in original post

@Bunly11 ,

This will get you all the page numbers up to 250 - I am assuming PageNumber is a Numeric field.

With(
   {TBL:DrugTBL},
   Search(
      Sort(
         Filter(
            TBL,
            PageNumber<=250
         ),
         PageNumber,
         If(
            SortAscending1,
            Descending,
            Ascending
         )
      ),
      SearchBox.Text,
      "DrugName"
   )
)

 

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.

View solution in original post

9 REPLIES 9
WarrenBelz
Super User III
Super User III

@Bunly11 ,

You get a warning as Search is not Delegable and there is nothing to stop to you adding more records after the app is written.

If you want to get rid of the warning, do this

With(
   {TBL:DrugTBL},
   Search(
      TBL, 
      SearchBox.Text
   )
)

but (depending on your Delegation settings) you will only search the first 500-2000 records.

 

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.

@WarrenBelz @Thank you but what would be the proper way to search with a sharepoint list to avoid the delegation? When I tried filter, my gallery was blank and nothing came up. 

Hey @Bunly11 

 

For a filter, you could use the following:

Filter(
       DrugTBL,
       Len(SearchBox.Text)=0
       ||
       StartsWith(DrugName, SearchBox.Text)
)

 

Search is currently not a delegable function with Sharepoint, and 'in' is also not delegable, however StartsWith is delegable (this means that the search is performed at the data source rather than inside the App). The only limit to this is that its not a free-form search (text appears anywhere within the value), so what you are looking for has to Start with whatever you are typing. The team have made many improvements to delegation however its not at a point where you can do free-form searches over 2000 items from Sharepoint.

 

The formula I've given above also checks for the length of text in the searchbox and if its zero then don't filter, otherwise filter by anything starting with what you are typing.

 

Let me know if this helps,

Thanks,

Sancho

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Hi - @iAm_ManCat . ok so that equation did pullup the gallery but when I try to type the drug name nothing comes up. Is there a function that would allow the user to search anywhere in the text? the user may search by brand or generic. I was hoping for a search function similar to what I could do in excel but with no delegation warning

 

Thank you . I have tried for hours watching YouTube videos and haven't been able to fix this problem

 

Hi @WarrenBelz

The code seemed did get rid of the warning. How would I be able to add a sort to by a column I have for PageNumber?

I tried to add on to what you wrote but I got another error.

Hi @Bunly11 ,

You need something like this - you need to "wrap" functions inside each other when you build multi-function filters.

With(
   {TBL:DrugTBL},
   Search(
      Sort(
         TBL,
         PageNumber,
         If(
            SortAscending1,
            Descending,
            Ascending
         )
      ),
      SearchBox.Text,
      "DrugName"
   )
)

As I mentioned, you will only get row numbers up to your Delegation limit in TBL to search from but will get no Delegation warning. I did a blog on using the With() statement covering this and it may be useful to you.

 

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.

View solution in original post

@WarrenBelz thank you for the link. Is it possible to only display certain page numbers? In my data I numbered records 999 that were not longer used. I only want the gallery to contaain pages 1 to 250.

@Bunly11 ,

This will get you all the page numbers up to 250 - I am assuming PageNumber is a Numeric field.

With(
   {TBL:DrugTBL},
   Search(
      Sort(
         Filter(
            TBL,
            PageNumber<=250
         ),
         PageNumber,
         If(
            SortAscending1,
            Descending,
            Ascending
         )
      ),
      SearchBox.Text,
      "DrugName"
   )
)

 

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.

View solution in original post

@WarrenBelz 😁 Thank you! I tried to replace Sort with Filter but now I see that I need search, sort, and filter. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (39,751)