Sort and Filter Table Row By Sub-queried Object Field Values

This reply was created from a merged topic originally titled Ability to add UI only fields calculated off of Sub-Queried rows. It would be great if you could add a UI only field to an object/field returned by a child query. The use case for us is to check whether or not the child object’s status matches the parent object’s status and highlight the child row conditionally. I can make a Salesforce formula field to check that, but I’d prefer to add this as  UI field instead of adding it to the data model.

On the same vein as this idea. Maybe they can be merged (if that’s a thing on the community?).

https://community.skuid.com/t/filter-table-row-by-sub-queried-object-field-values

Thanks for merging. The ideas are not exactly the same. One is filtering and one is adding UI fields.

Is this idea on the roadmap?

I ended up having to do this with custom Javascript.

Here’s the general outline. If anyone has detailed questions on how it was implemented, I’d be more than happy to add some detail. Additionally, this can certainly be improved upon and made to be more dynamic (extension of the table component, maybe?).

Sorting:

1. On page load create and store a settings object somewhere on the page (I did it as a JavaScript object variable in the window scope). It should have some representation of the following attributes:

• childRelationshipName (Custom_Object__r)
• orderByClause ('Contract_Amount__c DESC NULLS Last')
• parentOrderByClause ('Largest_Contract_Amount_Roll_Up__c DESC NULLS LAST')
• orderByClauseOpposite ('Contract_Amount_Roll_Up__c ASC NULLS LAST')
• parentOrderByClauseOpposite ('Smallest_Contract_Amount_Roll_Up__c ASC NULLS AST')
• lastUsedClause ('Contract_Amount__c DESC NULLS Last')
• hasEverBeenSorted (false)

sortHtml = '<div class="nx-skootable-order-icon-wrapper">      <span class="nx-skootable-order-icon ui-icon ui-icon-carat-2-n-s"></span>   </div>   <span class="nx-skootable-order-number" style="opacity: 0;">&amp;nbsp;</span><span class="nx-skootable-has-ordering">COLUMNNAMEDEMARCATOR</span>'; 

3. Add an event listener to listen for the click of the newly created sort button. Using the name of the column clicked, and the Javascript object created at the beginning, the event listener callback should find which orderByClause (this is where most of the attributes in the above object come into play) to use for both the subquery sorting and the parent query sorting.
   Then, use

skuid.model.getModel(modelId).orderByClause = parentOrderByClause;

for the parent object, and

skuid.model.getModel(modelId).fieldsMap[childRelationshipNamerelationshipName].orderByClause = orderByClause; 

for the child object.
4. query the model, and viola!

You have a subsorting model!

Filtering is a little more difficult.

I have a method I’m using now, but it’s slow and has limits.

1. Have the user use a filter set on a model of the same object as the subqueried model
2. retrieve the record id's from that model once they are filtered. Make sure to load all records using loadAllRemainingRecords so your main model can filter properly.
3. Feed them into the subqueried field conditions of the model you are actually displaying (parent model).
4. Query the parent model

This is very slow since it has to load the entire table each time you query the child object (which is done in order to get accurate results when combining parent and child filters).

I plan on switching the method to:

1. Have the user use a filter set on a model of the same object as the subqueried model but limit the record to 1
2. retrieve the model's conditions from that model once they are filtered
3. replace the subqueried field’s conditions of the model you are actually displaying with the conditions obtained from the filter set.
4. Query the parent model

I haven’t used the second method yet so I can’t say with certainty that it’s possible. I’ll post updates here once I’ve attempted it. Additionally, if you want to extend this, as I did, to filter the parent records as well you will need to decide whether or not to abort the parent model’s query if there are no child records. You can do this by modifying the noValueBehavior attribute on the model object. Possible options are currently ‘noquery’ and ‘deactivate.’