Capture sort order on Table Component

Hi Peter, sorry for the delayed response on this. It’s a complicated question to answer, and there is one piece of functionality that doesn’t work quite as expected as of our latest available Skuid release, but we have resolved this in dev and should have a release out this week that includes the adjustment. More on this later.

I built out a basic prototype of this capability by creating a custom “Sort Order” object in one of our demo orgs. Here’s what the object looks like from Schema Builder:


Full details:

API Name: Sort_Order__c
Custom Fields:
1. Skuid_Page__c: Lookup(skuid__Page__c)
2. Model_Id__c: Text(100)
3. Sort_Order__c: Text(255)

Basically the idea is that we’ll be using this Object to store, for each User in your org, the Sort Order / Order By Clauses of particular Models on particular Skuid Pages. 

So here’s the overview of the prototype I put together:
1.  have a Skuid Opportunities list page that has an “Opportunity” Model, and a Table that displays Opportunities in that Model
2. when a running User comes to this page, initially we do NOT load any Opportunities. Rather, we check a “Sort Order” model to see if there are any saved Sort Orders for any Models on this page, e.g. for our Opportunity model.
3. Let’s assume initially there are no saved Sort Orders for this user. It’s the first time the user has viewed this Skuid Page since we built it, so no Sort Orders have been created yet for them.
4. So what we do is, we go ahead and query our Opportunity model to get in some data.
5. Now, as soon as the user goes and changes the Sort order of one of the columns, we are listening in the background (using skuid.events.subscribe(‘models.loaded’), down at the bottom of our JavaScript which will be described shortly), and so we take the new sort order, and we use it to create a new record of our Sort_Order__c object. We then save this record to the salesforce.
6. If the user does any more sorts, we’ll update this record.
7. Now say that the user comes back to this page later. On page load, we find saved Sort Orders for this user. So what we do is grab the “Sort_Order__c” field of our saved record, e.g. something like “Name ASC NULLS LAST, LeadSource ASC NULLS LAST”, and we plop it into the Opportunity model’s orderByClause, which controls the SOQL ORDER BY clause, and we then query the Model. 

So I imagine you’ll want to tweak my logic somewhat, but here are the key pieces of our JavaScript API that you’ll need to leverage to make this work:

1. model.orderByClause — this is what sets the SOQL ORDER BY of a Skuid Model, and by extension the ordering of Tables that are bound to that Model. You’ll want to read/write to this in order to influence the sort order of the Table.
2. model.updateData() — in order to actually requery a Model, you’ll need to call this function.
3. skuid.events.subscribe(‘models.loaded’,function(loadResult){ });
Essentially this is an Event listener using Skuid’s Publish/Subscribe Events API, which was introduced in our Skuid Spring 14 release (but is not well documented yet). It is fired after models have been loaded with new data — so essentially, after a model.updateData() finishes. What you can do with this is say, “Looks like one of my Models got updated — did its sort order change? If so, let’s go update our Sort Order object”.

So here’s my code example. I have added a new JavaScript Resource with Location = Inline, with the following as its body: 

(function(skuid){&nbsp; &nbsp;<br>&nbsp; &nbsp;var $ = skuid.$;<br>&nbsp; &nbsp;$(function(){<br>&nbsp; &nbsp; &nbsp; &nbsp;// Do NOT try to auto-sort these Models<br>&nbsp; &nbsp; &nbsp; &nbsp;var modelsToIgnore = {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp;};<br>&nbsp; &nbsp; &nbsp; var SortOrders = skuid.model.getModel('SortOrders');<br>&nbsp; &nbsp; &nbsp; modelsToIgnore[SortOrders.id]=1<br>&nbsp; &nbsp; &nbsp; // See if we have a saved Sort Order for the Opportunity model<br>&nbsp; &nbsp; &nbsp; var modelsToLoad = [],<br>&nbsp; &nbsp; &nbsp; &nbsp; uniqueModelsToLoad = {};<br>&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; var GetSortOrderForModel = function(model){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var target;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $.each(SortOrders.data,function(i,so){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (so.Model_Id__c===model.id) {target = so;return false;}<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!target){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; target = SortOrders.createRow();<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SortOrders.updateRow(target,{<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Model_Id__c: model.id,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Skuid_Page__c: skuid.page.id,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sort_Order__c: model.orderByClause,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;OwnerId: skuid.utils.userInfo.userId<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return target;<br>&nbsp; &nbsp; &nbsp; }; &nbsp; &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; if (SortOrders.data &amp;&amp; SortOrders.data.length) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $.each(SortOrders.data,function(i,so){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var modelId = so.Model_Id__c;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (modelId) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var model = skuid.model.getModel(modelId);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (model) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // if we found a Model, apply our Sort Order to it<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; model.orderByClause = so.Sort_Order__c;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (modelId in uniqueModelsToLoad) {}<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; modelsToLoad.push(model);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; uniqueModelsToLoad[modelId]=model;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp; } else {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Create a default Sort Order for all Models&nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // NOT being sorted by our saved sorts<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $.each(skuid.model.list(),function(i,model){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var modelId = model.id;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (modelId in uniqueModelsToLoad) return true;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (model.orderByClause) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GetSortOrderForModel(model);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Add this Model to the list of Models to load,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // if it does not yet have any data in it<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (model.data.length===0 &amp;&amp; !(modelId in modelsToIgnore)){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; modelsToLoad.push(model);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp; if (modelsToLoad.length) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; skuid.model.updateData(modelsToLoad);<br>&nbsp; &nbsp; &nbsp; }<br>&nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; // NOW, setup some listeners on all our Models<br>&nbsp; &nbsp; &nbsp; // so that whenever they are updated,<br>&nbsp; &nbsp; &nbsp; // we save our Sort Orders.<br>&nbsp; &nbsp; &nbsp; skuid.events.subscribe('models.loaded',function(loadResult){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $.each(loadResult.models,function(modelId,model){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (model.orderByClause){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var so = GetSortOrderForModel(model);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SortOrders.updateRow(so,'Sort_Order__c',model.orderByClause);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SortOrders.save();<br>&nbsp; &nbsp; &nbsp; });<br>&nbsp; &nbsp; &nbsp;&nbsp;<br>&nbsp; &nbsp;});<br>})(skuid);

And here is a full page XML that relies on the existence of the Sort_Order__c object I describe above being in your org:

https://gist.github.com/zachelrath/0ed2282c821fc8171060



So lastly, once you get this up and working, you’ll discover one “catch” that I alluded to earlier — the Table component does not rebuild its Sort/Ordering icons (the little arrows) whenever its source Model is updated. We’ve fixed this as of Skuid 4.15+, which is not out yet, but should be by the end of this week. Until then, what you can do is to manually force your Table component(s) to rebuild themselves by changing this line 

if (modelsToLoad.length) {<br>&nbsp; &nbsp; &nbsp; skuid.model.updateData(modelsToLoad);<br>}

to have a callback that manually finds Tables and forces them to do a full rerender:

if (modelsToLoad.length) {<br>&nbsp; &nbsp; &nbsp; skuid.model.updateData(modelsToLoad,function(){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;skuid.$C('MyTable').render();<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;skuid.$C('MyOtherTable').render();<br>&nbsp; &nbsp; &nbsp;});<br>}

Zach,

This is working fantastic!  Thank you very much for your help!!

Peter

Zach,

Is it possible to take the captured sort order data and load the table components on the page accordingly?  

Said differently, if I sort by “Name ASC” this time it will load by “Name ASC” next time.

Thanks!

Peter 

Zach,

It appears the simple solution I had in mind worked.  I referenced Sort_Order__c  from my Sort Order model in the “Fields to order records by” in another model and it worked.

{$Model.SortOrders.data.0.Sort_Order__c}}

I wasn’t sure if the sequence of the model load would cooperate and allow this.  

Do you see any issue with this approach?  

Peter

I am now seeing some issues with the approach.  If a record exists with a SOQL ORDER loaded it seems to work great.  I am noticing if there isn’t a record and I sort the table I get results like:

Name ASC NULLS LAST, Date ASC NULLS LAST,{{$Model.MySortOrdersModel.data.0.Sort_Order__c}}

or I get:

Name ASC NULLS LAST, undefined

I am looking at what I changes need to be made to resolve this.  Any thoughts?

Thanks!

Peter

Peter, 

Isn’t this what’s already happening for you? I’m pretty sure the JavaScript shown above is doing exactly this — on initial page load, it looks through the SortOrders model and finds any saved Sort Orders, and uses these to initially sort data in the table. Is it not working this way for you? 

Zach

Erg! I can’t read javascript! I must join the dark side! :S

I kinda already look sith like anyway. Might as well.

Ok, I reverted back and it appears to be working with one model, however we are using sort orders on multiple models now.  It’s creating sort order records perfectly but some tables are not honoring them on load. 

Do I need to populate these (or make any other changes) to support multiple models?

var modelsToLoad = [],<br>uniqueModelsToLoad = {}; 

Should I add this?

if (modelsToLoad.length) { skuid.model.updateData(modelsToLoad,function(){<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;skuid.$C('MyTable').render();<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;skuid.$C('MyOtherTable').render();<br>&nbsp; &nbsp; &nbsp;});<br>}

Hey @Peter_Herzog !

Skuid has implemented a new sort builder along with other power components in tools in the new Chicago release which is now available on the Skuid Releases page. Best practices for upgrading can be found in Upgrading Skuid on Salesforce. As a reminder, Salesforce does NOT allow reverting back to prior versions of managed packages. Skuid always recommends installing new versions in a non-business critical sandbox environment to test all mission-critical functionality before installing into a production environment.

We also recommend that you update out-of-date themes and design systems after you upgrade. Please let us know if you continue to encounter any problems with this issue after upgrading.

Thanks again for alerting us of this issue!