Filter on child relationships

  • 6
  • Idea
  • Updated 4 years ago
Thanks Ben,
What I need to do is provide a way for the users to filter the records based on the values in the child relationship fields, rather than put a permant filter on them

Note: This topic was created from a reply on the Combine records from detail objects topic.
Photo of ktyler


  • 9,334 Points 5k badge 2x thumb

Posted 6 years ago

  • 6
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,828 Points 20k badge 2x thumb
Official Response
This update has been a long time coming.  We implemented various capabilities over the last 2 releases that make this possible. 

You should probably start by adding child relationship fields to your table so you can test.  Look at Step 5 of this tutorial if you need help. 

Now to build the filter.  I'm filtering our opportunities by the product name in the line items that are part of the opportunity.

1. Create a subquery condition.   The first condition looks like this: 

Field: Opportunity ID 
Operator:  IN
Value:  Result of subquery,  Join object: OpportunityLineItem,  Field:  OpportunityID
State: Filterable default off,   provide name of your choosing .

2.  Create the subcondition.
 Select the "Add Block" icon next to the condition name.  The subcondition look like this:  

Field:  The field you want to filter on. 
Operator:   Is
Value:  Leave Blank
State:  Filterable default off,  provided name of your choosing. 

3.  Add a New Model that will provide Picklist Values.
   The SubCondition field will not be able to pass its metadata into the filter to create the picklist values.  So we have to create a model for picklist values.   I use aggreate models for this. 

Model properties: Advanced,   Choose Aggregate Model
Don't choose any aggregations or conditions
Groupings:  Choose the field name for your filter.   I adjust the alias so it is exactly the same as the field name. 

4. Build the Filter.
  Add a filter to the table. 

A: Primary Filter properties. 
Type:  Select Option
Conditions to Affect:   The subquery condition (number 2 above)

B: Filter Source properties
Source Type:  Rows in a model.  Choose the source model. 
Important:  In "which conditions will this source option affect"  choose "Default Condition and others"
This exposes a Coffee Cup Icon which you should push.

C: Additional Effects Propoerties: 
Action:  Activate
Condition:  The primary condition (number 1 above).

5.  Enjoy your results.  
Now you have a filter on the child data.