Combine a filter and search formula

[Steffijeff]

Good morning

I have built a PowerApp which is working beautifully but I am now trying to get around the delegable functions limitations as my list will be bigger than 2000 items eventually. My gallery is linked to a SharePoint list and I currently have the following formula which is delegable;

Filter('Risk Event Log','Date Notified' >= DateAdd(Today(),-400,Days))

I also have the following formula which allows searching via 2 dropdown boxes and a text box;

Search(

If(
Dropdown3.Selected.Status ="All" And Dropdown4.Selected.Category = "All", 'Risk Event Log',
Dropdown3.Selected.Status = "All" And Dropdown4.Selected.Category <> "All", Filter('Risk Event Log', Category = Dropdown4.Selected.Category),
Dropdown3.Selected.Status <> "All" And Dropdown4.Selected.Category = "All", Filter('Risk Event Log', Status = Dropdown3.Selected.Status),
Dropdown3.Selected.Status <> "All" And Dropdown4.Selected.Category <> "All",
Filter('Risk Event Log', Category = Dropdown4.Selected.Category And Status = Dropdown3.Selected.Status)),
TextInput1.Text, "ExternalReference")

Both formulas work perfectly in isolation but I need to combine them. So I need the list to be filtered first and foremost so only items within the last 400 days pull through to the app and then we can search those items via the dropdowns and text box.

I've tried lots of options, viewed loads of posts and I just can't get over the line. Please tell me this is possible......

Thank you

Steph

[WarrenBelz]

Hi @Steffijeff ,

You might try this

With(
   {
      wStatus: Dropdown3.Selected.Status,
	  wCategory:Dropdown4.Selected.Category
   },  
   Filter(
      'Risk Event Log',
      'Date Notified' >= DateAdd(Today(),-400,Days) &&
      (wStatus="All" ||  Status=wStatus) &&
      (wCategory="All" || Category=wCategory) &&
      TextInput1.Text in ExternalReference
   )
)   

or if you want to stay with Search

With(
   {
      wStatus: Dropdown3.Selected.Status,
	  wCategory:Dropdown4.Selected.Category
   },  
   Search(
      Filter(
         'Risk Event Log',
         'Date Notified' >= DateAdd(Today(),-400,Days) &&
         (wStatus="All" ||  Status=wStatus) &&
         (wCategory="All" || Category=wCategory)
      ),
      TextInput1.Text
      "ExternalReference"
   )
)   

note however both the date filter and Search/in are not delegable.

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.

[Steffijeff]

Thanks @WarrenBelz  but neither of them worked.

For the filter option I got a couple of errors "Expected closing Bracket" & "Invocation of unknown or unsupported function" 

For the Search option I get "Invalid number of arguments" & "Expected Operator".

When you say they're not delegable, do you mean it's not possible to pull through records that meet my criteria and then search just those records?

Thanks

Steph

[WarrenBelz]

Hi @Steffijeff ,

Free typed, so more of a syntax guide - did you pick up the single quote missing in front of 'Date Notified' (now fixed. I also have to rely on your values being correct.

The issue you have mainly on Delegation is that date filters are not Delegable, which is the core of your data set. One option assuming you have less than 2,000 records in the last 400 days (and your Delegation limit is set to this) is to do this

With(
   {
      wStatus:Dropdown3.Selected.Status,
      wCategory:Dropdown4.Selected.Category,
      wLog:
      Sort(
         'Risk Event Log',
         ID,
         Descending
      )
   },  
   Filter(
      wLog,
      'Date Notified' >= 
      DateAdd(
         Today(),
         -400,
         Days
      ) &&
      (wStatus="All" || Status=wStatus) &&
      (wCategory="All" || Category=wCategory) &&
      TextInput1.Text in ExternalReference
   )
)   

The syntax is correct providing all of your values are compatible with their required purpose.

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.

[Steffijeff]

Thank you - I hadn't spotted the missing ', so thanks for that 🙂

The new formula is working but can you explain what it is doing? I've never used "With" before so finding it difficult to follow. 

It was my understanding that Date Filters are delegable or have I misunderstood the below?

Also, when I apply the date filter in isolation, I don't get the delegation warning so now I'm really confused.

Steph

[WarrenBelz]

Hi @Steffijeff ,

Firstly, I don't know where the information you posted came from (or how old it is), but this is a far better guide on SharePoint Delegation. I can assure you date filters are not Delegable currently in SharePoint.

As for the With() statement, I have done a blog on this that may help, but to explain briefly, it is nothing more than a (very) temporary variable/collection that "pre-filters" the data for the following code to execute. It in itself is not Delegable (meaning the resulting dataset has to be under your Delegation limit - but you do not get a warning), but if you do it with a Delegable query (and you get all the data you need), then the following queries are Delegable. You can actually do exactly the same thing using a collection and filtering that, but this is neater and has less overhead.

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.

[Steffijeff]

Ok thanks, I'll take a look.

The information is directly from Microsoft and can be seen here.   

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

So just to clarify, as far as you're concerned, it's not possible to achieve my goal which is to filter the list before searching on it?

[WarrenBelz]

Hi @Steffijeff ,

The search code I provided (I assume it is working now) still has a delegation issue in he date filter.

I put this together below - note free-typed as a syntax guide only. which should work as long as the statement before the search brings in less than 2,000 records, which of course probably will not happen if both dropdowns are "All"

With(
   {
      wFiltered:    
      Filter(
         'Risk Event Log',
         (Dropdown3.Selected.Status="All" ||  Status=Dropdown3.Selected.Status) &&
         (Dropdown4.Selected.Category="All" || Category=Dropdown4.Selected.Category)
      )
   },  
   Search(
      Filter(
         wFiltered,
         'Date Notified' >= DateAdd(Today(),-400,Days)
      ),
     TextInput1.Text
     "ExternalReference"
   )
)

If the "newest 2,000" option if provided previously works, then yes it will filter. Note that the "in" operator is the same as Search if you are only using on field to search on.

To answer your question, the filtering in the above is doing what you want, but the date filter still needs to be in the filtered set, in which only 2,000 items can be available.

I have also done a blog on Delegation, with a couple of items on big collections that may be helpful.

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.

[Steffijeff]

@WarrenBelz Sorry for the lengthy delay, I've been sidetracked with other things. This still isn't the logic I'm looking for unfortunately. I need only the items created within the last 400 days to be searched. Your solution applies the searches before the date filter so doesn't help me here. Is there anything I can do with Sort instead? I've got my gallery to only show the most recent by Sorting in descending order so can I combine that formula with the dropdown box searches?

[WarrenBelz · Accepted solution]

@Steffijeff ,

The code I supplied does the following: -

1. Filters Risk Event Log fields Status and Category on the basis of two drop-downs - these are fully Delegable
2. Takes the output of this and filters that for items in the last 400 days and allows a further Search on the field ExternalReference.

It is important to note here that the first filter must produce under 2,000 records for the second on to give all relevant results.

The only real improvement I can suggest is

With(
   {
      wFiltered:    
      Filter(
         Sort(
            'Risk Event Log',
            ID,
            Descending
         ),
         (Dropdown3.Selected.Status="All" ||  Status=Dropdown3.Selected.Status) &&
         (Dropdown4.Selected.Category="All" || Category=Dropdown4.Selected.Category)
      )
   },  
   Search(
      Filter(
         wFiltered,
         'Date Notified' >= DateAdd(Today(),-400,Days)
      ),
     TextInput1.Text
     "ExternalReference"
   )
)

meaning you are accessing the newest 2,000 filtered records at the top.

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.