Filter by user on child relationships

  • 2
  • Question
  • Updated 5 years ago
  • Answered
I'm trying to filter a list of records by user that a child record may contain. I've followed this guide and tried numerous variations to see if there's something different about filtering by user but I can't seem to get it working. Any tips?
Photo of Oliver Sinfield

Oliver Sinfield

  • 922 Points 500 badge 2x thumb

Posted 5 years ago

  • 2
Photo of Oliver Sinfield

Oliver Sinfield

  • 922 Points 500 badge 2x thumb
Figured it out. I have a service job object (for header information) and a service job item object (for items to be serviced), which are related to a single job. I have a primary user assigned at the job level and each item can also have a user assigned. I now have a single filter which will show all service jobs assigned to a user and any service jobs that contain items that are assigned to the same user, even if they are not the user assigned at the job level.

In this screenshot, you can see I have one filter for the user, in this instance "Oliver". Even though Jeff (in green) is the primary on the job level, the job still shows up, because Oliver is the primary on one (or any) of the related service job item records.

Here's the model condition that the filter will use (note the sub-condition logic)

And the filter is setup like so

The rows for the second source are in a model using the Users object, with Id & Name fields selected. I had to create a manual "None" option as it wasn't working using the default - I think due to it not deactivating the right conditions, which you can see i've done manually.

Hope this helps others! If there's a better way to accomplish this, I'm all ears!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,828 Points 20k badge 2x thumb
I think you are right on track here.  The subquery conditions are key.  Then you have to activate the primary condition and pass values into your subquery condition.  You have to set up your own "None Selected" entry with a manual source that turns all the conditions off.  And you have to after a "user" model to show the names in the drop down. 

Zach explained this in a video we did last year about advanced filter use cases.  Here is the link. 

And I wrote a cheat sheet explaining lots of filter possibilities.  One of these is "junction object filters"   I think it is use case number 7.  Here is that link. 
Photo of James Bujold

James Bujold

  • 112 Points 100 badge 2x thumb
Nice work Oliver.  This was a huge help for me. Thanks for posting.