Condition Child Relationship

  • 1
  • Question
  • Updated 5 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,880 Points 5k badge 2x thumb

Posted 6 months ago

  • 1
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,316 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,316 Points 4k badge 2x thumb
This might be a more helpful screenshot.


(Edited)
Photo of Bill Fox

Bill Fox

  • 8,880 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,316 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,880 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 John Dahlberg

John Dahlberg, Champion

  • 4,316 Points 4k badge 2x thumb
Bill - The video wen't a little fast, but I have a suspicion on where the issue might be.  If you shoot me a note to my email (jdahlberg@mbopartners.com) with your number I can give you a quick call to work through the relationship path.  
Photo of Bill Fox

Bill Fox

  • 8,880 Points 5k badge 2x thumb
Just sent it to you.
Photo of Bill Fox

Bill Fox

  • 8,880 Points 5k badge 2x thumb
The "field from another model" works well.  I want to bring in a field from the original source model into the second object table.

https://drive.google.com/file/d/1jwDYAufBPFEEjEj-87O6DCpSmtLijzpA/view

Photo of John Dahlberg

John Dahlberg, Champion

  • 4,316 Points 4k badge 2x thumb
Bill, I think the MODEL_LOOKUP formula is what you're looking for.  It works similar to a vlookup in Excel and is used in a UI-Only field.  Here's the basic syntax and a link to Skuid's documentation on it:

MODEL_LOOKUP("Model1Id","FieldIDfromModel1","KeyFieldfromModel1",{{KeyFieldfromModel2}})

https://docs.skuid.com/latest/en/skuid/models/ui-only-models-fields.html

Photo of Bill Fox

Bill Fox

  • 8,880 Points 5k badge 2x thumb
Here is what I believe I need.  I the object GET_CRDNA i assume I need to query on page load.  Do I set a Max Number?
MODEL_LOOKUP("GET_CRDNA",   "Member_DNA__r.View_Member__c",   "CR__c",  {{{Contact_Record__c}}})
https://drive.google.com/file/d/1QO3f5NUdunp8OibTCXJwb7nNgDqRkCjp/view
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,316 Points 4k badge 2x thumb
GET_CRDNA needs to be queried prior to performing the lookup, either on page load or another action.  If it's on page load, make sure it's listed before the model where you have your UI only field as Skuid queries in the order the models are listed.
Photo of Bill Fox

Bill Fox

  • 8,880 Points 5k badge 2x thumb
Do I set a max number of records
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,316 Points 4k badge 2x thumb
Model lookup only returns one value.  Similar to the Excel VLOOKUP, it will return the first match.
Photo of Bill Fox

Bill Fox

  • 8,880 Points 5k badge 2x thumb
If I leave the value blank it gives me a heap error.  If I put 1000 it does query but does not bring in any records.
The following is my setup:

https://drive.google.com/file/d/17ZTzjPXM3gcmixvVbn3t1HCFykcJq4S-/view
Photo of Bill Fox

Bill Fox

  • 8,880 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,316 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,316 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,880 Points 5k badge 2x thumb
We used your framework and it worked.  Thanks.
Photo of John Dahlberg

John Dahlberg, Champion

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