How to properly reference ThisRecord in nested Filters

[Anonymous]

I'm working on a search box for a gallery and need to search and reference values in multiple Collections.  There is a radio selection (HelpSearchRadio) for choosing which columns to search in and and if statement to return all if the search box is blank.  The following formula works fine for "Title" and "Location", and throws no errors, but when I select Sales, I get nothing back.  I assume that this is because the highlighted "ThisRecord.ID" is referring to the record returned in the Lookup function and not the Filter it's nested in. Is there a way to refer specifically to the record being evaluated by the Filter from within the Lookup function?

[WarrenBelz]

Hi @Anonymous ,

ThisRecord is another discussion as I do not believe you need it at all in what you are trying to achieve.

With(
   {wFind:Lower(HelpSearchBox.Text)},
   Filter(
      HelpSearch,
      IsBlank(wFind) ||
      Switch(
         HelpSearchRadio.Selected.returns,
         "Title",
         CountRows(
            wFind in Lower(Title)
         ) > 0,
         "Sales",
         CountRows(
            wFind in LookUp(
               HelpSalesInfo, 
               SOWID=ID, 
            ).Title
         ) > 0,
         "Location",
         CountRows(
            wFind in Lower(EventLocation)
         ) > 0
      )
   )
)

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.

[Anonymous]

Thanks for your help, but unfortunately, that doesn't seem to work.  CountRows() is expecting a table but "wFind in..." results in a boolean so it just throws errors.  The errors go a away if I take out the CountRows() function and replace ">0" with "true" for each condition, but I'm still stuck with the "ID" column referencing the Lookup function rather than the Filter. 

What I am needing is a way to compare the ID (this is the default record ID generated by Sharepoint) column in "HelpSearch" against my lookup column (SOWID) in "HelpSalesInfo".
Any ideas?

[Anonymous · Accepted solution]

Hi @WarrenBelz,
Got it working. I just had to temporarily rename the column in HelpSearch to something that doesn't appear in HelpSalesInfo. The following works perfectly:

If there's another way to reference that ID column without having to rename, I'd love to hear about it, but this will do the job for now. 

Thanks for pointing me in the right direction!

[WarrenBelz]

@Anonymous ,

I am glad you got it working, but you have changed the logic (you had greater than zero in the test, so I assumed you wanted the number of records hence the CountRows). I note you have used the other logic and dropped the Lower(). I find the ID issue interesting as you have a different value to match and it should not be potentially ambiguous, but rather than rename columns, you can to this

With(
   {
      wFind:HelpSearchBox.Text,
      wHelp:HelpSearch
   },
   Filter(
      HelpSearch,
      IsBlank(wFind) ||
      Switch(
         HelpSearchRadio.Selected.returns,
         "Title",
         wFind in Lower(Title),
         "Sales",
         wFind in LookUp(
            HelpSalesInfo, 
            SOWID=wHelp.ID, 
         ).Title,
         "Location",
         wFind in EventLocation
      )
   )
)

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.

[Anonymous]

@WarrenBelz 

Yeah, I realized after changing from Find() to in operator as you suggested that the Lower() was no longer needed. I tried the above, but it's throwing an invalid argument type in the lookup. Is there much benefit to doing it one way versus the other?  I do expect to have 10-20k records in this list eventually (bringing it into PowerApps via Collect() and ultimately will have to apply some date filters), so I'd definitely rather get it right now. 

[WarrenBelz · Accepted solution]

@Anonymous ,

if you have 10k records, you can forget the second part of the With() statement as this is not Delegable (it will only collect record numbers up to your Delegation limit).

I am still a little curious why you need RenameColumns although it will work, so keep it.

You have the option of also accepting the post that helped you the most (bearing in mind it could only answer what you posted).

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.