Sort a table based on a child relationship field

  • 1
  • Question
  • Updated 1 month ago
  • Answered
How can I sort a table based on a Child Relationship field? 
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb

Posted 2 months ago

  • 1
Photo of Pat Vachon

Pat Vachon, Champion

  • 45,036 Points 20k badge 2x thumb
Is there only 1 child record?
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Yes only one child record.

I am trying to do it through J/S like so:

skuid.$M('MyModel').orderByClause = 'Child_Relations__r.Date__c DESC';
Photo of Pat Vachon

Pat Vachon, Champion

  • 45,036 Points 20k badge 2x thumb
Not sure that would work since child record fields aren't supported in SOQL where clause.
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
You are correct it is not working.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
I haven't tried this, but it may be worth testing out to see if it will work.  

Setup a UI-only field to reference the field in the subquery you're interested in on the main model field list.  Then use the action framework to sort model client side. The UI field should also be able to be used for sorts in a table.

Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Thanks for the idea John. I tried accessing the child relationship via the Ui-Only field with "Child_Relations__r.Date__c" but it doesn't display the data. 


Photo of Matt Brown

Matt Brown, Official Rep

  • 1,986 Points 1k badge 2x thumb
I've done this before - it should work. You can use merge values to bring child relationship data into formula fields, and yes, to John's point, client-side sorting should work fine. There are some examples of the syntax here: https://docs.skuid.com/latest/en/skuid/merge-syntax/#looping-over-arrays
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
You have to reference the record and index of the child relationship.  It should read 
{{Child_Relations__c.records.0.Date__c}}.  
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Thanks for the help guys! So if I put this merge syntax in a template it displays data
{{#Abel_Supplier_Catalogues__r.records}}
{{Catalog_Item_Count__c}}
{{/Abel_Supplier_Catalogues__r.records}}

If I put that same merge syntax in a UI Only field it doesn't work. 

Also, I think our Skuid version may be to old as I don't have a sorting sequence option.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
The syntax you have there is for conditionally displaying a field, which I believe is a different use case from what you're looking for.  Setup the UI field with the syntax from my last response with the index number and the field name in the merge.  The use a model field rather than a template.  
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
Also in later versions of Skuid, you can tie a template's sort order to a field in a model, but you still need the field defined to run the sort.  In this case it's cleaner to use the UI model.
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Thanks this syntax worked: {{Child_Relations__r.records.0.Date__c}}.  Still trying to figure out the sorting issue, if possible. Thought I could use JavaScript on the UI only field. No go so far.
Photo of Pat Vachon

Pat Vachon, Champion

  • 45,036 Points 20k badge 2x thumb
You can for sure use Dlrs to concat the value from the related record. That way you can use standard sort functionality in the model.
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Yeah I know but I was trying to not have another DLRS job on this object.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
So it looks like (at least on my version of Skuid), you can't sort by UI fields in a table.  This is likely because sorting will re-query the database so you get refreshed records of interest.

However I did just setup an action sequence that was able to successfully sort by a UI field and it was really fast.  So this is totally possible with an Action, but it looks like you might need to get creative on the UI.

A couple thoughts: first, you can use an event driven action sequence to run the sort on page load, then everything will load in the correct order.  Second, If you want to have the user drive the sort you can fire the action off of button(s).

One important note here, sorting on the client side will not go back to the database to retrieve a refreshed set of records.  Also, as Pat mentioned earlier, you can't sort on a subquery in SOQL.  What I suggest is to use a subquery condition to scope your data in alignment to what you're sorting on.  If you do client side sorting, you need to make sure you have all the records pulled down to the browser or the user will be interacting with inconsistent data.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
One last thought.  You can use an aggregate query to do your sort and align the data scope, then use the results of the aggregate as the filter for you main model.  This won't help with the UI, but it will at least get you an ordered list back that aligns to the data. 

For example, you could run an aggregate query on the Child_Relation__c table, aggregate by the ParentId and order by the Max(Date__c).  Then your interactive model would be filtered by field from another model and use the aggregate query results.
Photo of John Dahlberg

John Dahlberg, Champion

  • 4,610 Points 4k badge 2x thumb
One last thought.  You can use an aggregate query to do your sort and align the data scope, then use the results of the aggregate as the filter for you main model.  This won't help with the UI, but it will at least get you an ordered list back that aligns to the data. 

For example, you could run an aggregate query on the Child_Relation__c table, aggregate by the ParentId and order by the Max(Date__c).  Then your interactive model would be filtered by field from another model and use the aggregate query results.
Photo of Pat Vachon

Pat Vachon, Champion

  • 45,036 Points 20k badge 2x thumb
You can also use PB & Flow to set the value. Assuming you don't have too many records for this. You can also schedule to the flow to run once in order to get the values on the parent record.
Photo of Tami Lust

Tami Lust

  • 5,932 Points 5k badge 2x thumb
Guys thanks for all the suggestions. I ended up going about it a little differently because of the requirements.

So, there is a master list that the person can sort but Item count.

So I have two tabs the "Master" list and "Account" list (which I was trying to get the sorting to work on). 

So when they sort the "Master" list. I put a button on the "Account" list that will update the list with only the Accounts from the "Master" list.