Subquery Coaching

Hi Bill,

If you could verbalize what the end result should be in a little more detail, that will be helpful for us.

For example, is your end goal to pull the Organization__c field into the model you built on CR_Use_DNA__c? If so, you might not be able to use a subquery for this. The subquery condition would be useful if you were trying to filter CR_Use_DNA__c records based on the results of a subquery, but it wouldn’t pull in a field from another object into the model.

For example, if we want to pull in all Contacts whose Accounts are located in Japan, the subquery piece would create a temporary a list of Accounts who have a Billing Country = Japan, and then the Contacts model would pull in only Contacts whose Accounts were returned by the subquery 

If the subquery isn’t working out, a UI-only formula field that uses a MODEL_LOOKUP might also be useful. See here: https://docs.skuid.com/latest/en/skuid/models/ui-only-models-fields.html?highlight=model_lookup#refe…

Because you’re traversing 3 objects (CR_User_DNA__c –> Contact_Record_DNA__c –> Member_DNA__c), the solution might involve the same number of layers, so understanding your goal more clearly will be important. 

Yes.  I am trying to pull in the Organization__c field into the model I built on CR_Use_DNA__c.

In that case you will need to use a Model Lookup in a UI-only formula field on CR_Use_DNA__c model.

You will also need to build a second model that connects to the Contact_record_DNA__c object, and pulls in the field Member_DNA__c.Organization__c. This second model may need some conditions so that it only pulls in records that relate to the first model. Once the second model is pulling in the Organization field, you’ll be able to set up a model lookup that brings that field into the first model

Here’s the template for that kind of formula:

MODEL_LOOKUP(“Model1Id”,“FieldIDfromModel1”,“KeyFieldfromModel1”,{{{KeyFieldfromModel2}}})

Here’s how that will apply to your use case. 

• Model1Id is the name of the new model you’ve built on Contact_record_DNA__c.
• FieldIDfromModel1 is the name of the field you’re wanting to bring into the original model. In this case, it’s Member_DNA__c.Organization__c. 
• KeyFieldfromModel1 is the name of the reference id field on the Contact_record_DNA__c model. Here, it should be CR__c.
• {{KeyFieldfromModel2}} is the value of the reference field. It will be {{{Contact_Record__c}}} here. This is the only item that has mustache brackets around it, because it’s pulling in the actual Id value from the model row you’re on, instead of referring to the names of the fields.