Condition Child Relationship

  • 1
  • Question
  • Updated 4 months ago
  • Answered
I cannot figure how to condition a child relationhip
https://drive.google.com/file/d/19oLj585PWZuCmBBfHMebnCV00c4bwNG8/view
Photo of Bill Fox

Bill Fox

  • 8,800 Points 5k badge 2x thumb

Posted 5 months ago

  • 1
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
Bill,

I believe you're looking for an approach similar to Cross-Filters in Salesforce reports.  In Skuid, there are a couple spots to work with child relationships. 

In the field selection, you can add subqueries to establish the relationship path for child records and you can set filters on that (so much more convenient than related lists).  When you set filters on this it only conditions the records that are returned in the subquery, but it doesn't affect the results of the primary query level. This is what you had in the video.

The second (and awesomely powerful spot) to work with sub-relationships in Skuid is in the condition section.  This is where you can filter the primary query based on conditions in child relationships.  What you do is set your Id (usually) as the filtered field and then set your condition to be results of a subquery.  Then you can work with the subquery itself and any desired filters to drive the records that are returned on the top level query.  Here's a screenshot of a setup we use to identify contacts that have not been marketed to, based on excluding contacts that have campaign members in selected campaign(s):


(Edited)
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
This might be a more helpful screenshot.


(Edited)
Photo of Bill Fox

Bill Fox

  • 8,800 Points 5k badge 2x thumb
I think that worked.  However, it only works if I leave "Query on Page load" unchecked and the "Max records" empty.  Does that sound correct?
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
I haven't run into a limitation on this, but that could just be a result of how we're utilizing the subquery filtering and simply not running into what you're seeing.  That said, I do have some scenarios where we're using table filters to dynamically update the values of the subquery filter, so I would think the "Query on Page" load should be flexibile.  
Photo of Bill Fox

Bill Fox

  • 8,800 Points 5k badge 2x thumb
Well I was wrong. The records were showing because I had it set as "Deactivate this Condition".  What do I have set wrong.
https://drive.google.com/file/d/1dzayAAPBQfCXKM01Qooi1obtNws5sbua/view

Photo of Bill Fox

Bill Fox

  • 8,800 Points 5k badge 2x thumb
The first table I am using the lookup on is made up of millions of records.  If I query the table I get a heap error if I DON'T put a limit on the query size.  If I put a limit on the query size the on the first Model_Lookup may not find the record in the initial query. 

It seems the Model_Lookup only queries records selected from the first query.  Is there a  method to retrieve the field by querying the entire first table without the initial lookup so I can look at all the records?
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
Bill,

There are a few ways to tackle this, but you'll need to have a filter set on CR_DNA in order to get to a workable data set so that the CR_User_Closed model records can consistently pull from the first model.  

If I recall your use case, the contact record is the bridge (i.e. parent object) between these two objects.  What I'd suggest is to add a model to pull the correct contact list based on the model you're looking to work with first and then use that as a filter for both child relationship queries.  Here's the general model structure (in this order):

Add a Contact Model with a subquery filter matching the conditions you are currently using on CR_User_Closed.

On both CR_DNA and CR_User_Closed models, change the condition to query the contact lookup fields that match the Contact Model.  

Make sure to order the models:
  1. Contact
  2. CR_DNA
  3. CR_User_Closed
Also, you probably want to make sure that any updates or requeries on the CR_User_Closed model trigger a requery of all three models to ensure your data is consistent.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
Bill,

There are a few ways to tackle this, but you'll need to have a filter set on CR_DNA in order to get to a workable data set so that the CR_User_Closed model records can consistently pull from the first model.  

If I recall your use case, the contact record is the bridge (i.e. parent object) between these two objects.  What I'd suggest is to add a model to pull the correct contact list based on the model you're looking to work with first and then use that as a filter for both child relationship queries.  Here's the general model structure (in this order):

Add a Contact Model with a subquery filter matching the conditions you are currently using on CR_User_Closed.

On both CR_DNA and CR_User_Closed models, change the condition to query the contact lookup fields that match the Contact Model.  

Make sure to order the models:
  1. Contact
  2. CR_DNA
  3. CR_User_Closed
Also, you probably want to make sure that any updates or requeries on the CR_User_Closed model trigger a requery of all three models to ensure your data is consistent.
Photo of Bill Fox

Bill Fox

  • 8,800 Points 5k badge 2x thumb
We used your framework and it worked.  Thanks.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,096 Points 4k badge 2x thumb
Awesome.