cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
objectivelyLost
Helper I
Helper I

How to properly reference ThisRecord in nested Filters

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?

objectivelyLost_0-1609209147462.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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:

objectivelyLost_0-1609222211774.png

 

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!

View solution in original post

@objectivelyLost ,

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.

 

View solution in original post

6 REPLIES 6
WarrenBelz
Super User III
Super User III

Hi @objectivelyLost ,

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.

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?

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:

objectivelyLost_0-1609222211774.png

 

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!

View solution in original post

@objectivelyLost ,

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.

@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. 

objectivelyLost_0-1609233404055.png

 

@objectivelyLost ,

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.

 

View solution in original post

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

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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