Condition Child Relationship

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):

This might be a more helpful screenshot.

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?

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.  

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

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.  

Just sent it to you.

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

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

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

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.

Do I set a max number of records

Model lookup only returns one value.  Similar to the Excel VLOOKUP, it will return the first match.

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

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?

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

We used your framework and it worked.  Thanks.

Awesome.