Table: allow ordering for ui-only formula fields

  • 8
  • Idea
  • Updated 2 months ago
  • Under Consideration
It would be handy for user to be able to sort tables based on the output of ui-only formula fields. At least when those fields can be easily sorted.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 2 years ago

  • 8
Photo of Karen Waldschmitt

Karen Waldschmitt, Official Rep

  • 8,260 Points 5k badge 2x thumb
Matt~

I can see how that would be helpful. 

Thanks for the suggestion!
Karen
Photo of Damien Murray

Damien Murray

  • 474 Points 250 badge 2x thumb
Hello Karen,

Is this idea likely to be included in an upcoming release? If not, are you aware of any alternatives to make this possible?

I use UI only fields for a lot of calculations that breach the Salesforce formula size.

Regards,

Damien
Photo of David Garrity

David Garrity

  • 856 Points 500 badge 2x thumb
Is there any way that individuals have been successful in allowing a table to sort on a UI formula field?
Photo of Javid Igani

Javid Igani, Internal Solutions Engineer

  • 186 Points 100 badge 2x thumb
Client-side / UI-only Sorting in Skuid
The way I've been getting around this is using a Javascript snippet for now. I believe client-side sorting is definitely on the radar, but this will have to do for now. As with any Javascript in your Skuid implementation, use at your own risk! 

Filter Functions
//First define your sort functions before you need to use them. Here are two you can copy/paste for ascending or descending

//Change this to the field you need to sort
var field = 'FieldToSort';
function sort__ByField_ASC(a,b){
    if( a[field] < b[field] ) return  1;
    if( a[field] > b[field] ) return -1;
    return 0;
}
function sort__ByField_DESC(a,b){
    if( a[field] < b[field] ) return -1;
    if( a[field] > b[field] ) return 1;
    return 0;
}

There are two approaches to this:

Two Model Approach
Use this approach if you would like to have a Data model and a separate Display model. I prefer this approach for a variety of debugging reasons, even though it can result in more models than necessary. This is the "show me a picture of the seed that is now my flower" option. 
var sourceModel = skuid.$M('YourDataModel'); //Your source data that you wish to sort
var targetModel = skuid.$M('YourDisplayModel'); //Generally a UI-Only Model with same name fields
var rows = sourceModel.getRows();
var sortedRows = rows.sort(sort__ByField_ASC); // or sort__ByField_DESC
targetModel.adoptRows(sortedRows);

Now you have two models, one with your source data and one with your sorted data!

One Model Approach
Use this approach if you simply want the model to be sorted and do not wish to see the before/after picture. This is more "just give me the flower, who wants a picture of a seed!?" option. 
var model = skuid.$M('YourDataModel');
var rows = model.getRows();
model.abandonAllRows(); //remove rows from the original model
var sortedRows = rows.sort(sort__ByField_ASC); // or sort__ByField_DESC
model.adoptRows(sortedRows); //add them back sorted
One model. Sorted. That's all.

Bonus Content!
"But Javid! What if I need to sort by two or more UI-only fields?" 

Have no fear! There is still a solution! Look back up at the functions we wrote at the beginning (sort__ByField_*). Notice the first two lines in each function that return either -1 or 1? Let's add more!

//Change these to the fields you wish to sort in the order you wish to have them sorted
var field1 = 'FirstFieldToSort';
var field2 = 'SecondFieldToSort';
function sort__ByMultipleFields_ASC(a,b){ //Sort the first field
if( a[field1] < b[field1] ) return  1;
if( a[field1] > b[field1] ) return -1;

//field1 in a and b were the same. Tiebreaker!
if( a[field2] < b[field2] ) return  1;
if( a[field2] > b[field2] ) return -1;

return 0;
}

function sort__ByMultipleFields_DESC(a,b){ //Sort the first field
if( a[field1] < b[field1] ) return -1;
if( a[field1] > b[field1] ) return 1;

//field1 in a and b were the same. Tiebreaker!
if( a[field2] < b[field2] ) return -1;
if( a[field2] > b[field2] ) return 1;

return 0;
}

For sorting by more than two rows, add more of the "Tiebreaker" rows in sequence as you need them.

Bonus Content 2: The Return of Bonus Content!
Wow, it just keeps coming! Good for you, my lunch ride is running late!

"What if I need to sort by two or more rows, but differing in the sort order?"

This is where you can look at the comparisons in the if statements and the actual return values in the functions. Notice how in each of the above ASC functions we return positive 1 if the a field is less than the b field. Similarly in each of the above DESC functions we return negative 1 if the a field is greater than the b field

You can mix and match these return values to your heart's desire so you can achieve the correct sorting algorithm for your needs!
(Edited)
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
I am not worthy of this knowledge Javid! I can't figure out how to even implement your instructions. Are these Inline JavaScript? Two separate or a single script? I want to sort a model based on the date value in a UI only formula field using the single model approach because I like flowers and hate seeds. 
Photo of Javid Igani

Javid Igani, Internal Solutions Engineer

  • 186 Points 100 badge 2x thumb
Raymond,

This could be implemented as Inline JavaScript if you would like to be able to call it as part of an action sequence (such as when clicking a button, etc.).

As for the number of scripts necessary, that depends on where you need the client-side sorting performed. If you have multiple JavaScript snippets in which you need client-side sorting performed, a sorting function will need to be defined in each of them so the snippet knows what you're trying to do.

Let's work through your use case as I understand it:

I want to sort a model based on the date value in a UI only formula field using the single model approach.

  1. Create a JavaScript snippet named something like "Sort_ByUIOnlyDate".
  2. In that snippet, insert the following code:
    //Begin contents of snippet
    //Declare the field I want to sort upon, as well as the sorting function
    var field = 'NameOfYourUIOnlyDateFormula';
    //Sort by the specified field with values ascending from least to greatest
    function sort__ByField_ASC(a,b){
      if ( a[field] < b[field] ) return 1;
      if ( a[field] > b[field] ) return -1;
      return 0;
    }

    //Perform the sorting
    var model = skuid.$M('YourModel');
    var rows = model.getRows(); //Get list of unsorted rows
    model.abandonAllRows(); //Remove unsorted rows from the original model
    var sortedRows = rows.sort(sort__ByField_ASC); //perform the sorting function defined above on the unsorted rows
    model.adoptRows(sortedRows); //Adopt the newly sorted rows back into the model//End contents of snippet


  3. Attach this script (Sort_ByUIOnlyDate) to an action (button, model action, etc)
The only things you should have to change in step 2 are the value for field and model, where these names will be specific to your set up.

If you need to have the dates sorting from most current to least (descending), swap out the sort function with the one defined in my previous post (named sort__ByField_DESC).

I hope this helps clear things up!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
It does, thanks!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Hi Javid,
I implemented this, but the when the script is run, the model returns no rows. There is this error in the console:
"skuid__JQueryJS:2 Uncaught RangeError: Maximum call stack size exceeded"
Thoughts?
Photo of Javid Igani

Javid Igani, Internal Solutions Engineer

  • 186 Points 100 badge 2x thumb
Somehow your response got lost in my email! Hopefully you've been able to resolve this by now. If not, seeing your code would be helpful to find out what might be wrong.
Photo of Javid Igani

Javid Igani, Internal Solutions Engineer

  • 186 Points 100 badge 2x thumb
For anyone following this thread, great news! Sorting your models client-side is no longer in the realm of code! With the release of Skuid Millau, there is now a declarative "Sort Model" action! See the following documentation for details:

https://docs.skuid.com/v11.0.1/en/skuid/action-framework/#sort-model

I personally cannot wait to replace the multiple places I've used the above JS.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Yes!!!
Photo of Michael Barnes

Michael Barnes

  • 200 Points 100 badge 2x thumb
This worked great.  I just used a row action to sort a UI-Only field.  Trick is you must click "client-side" checkbox on the Sort action, else the UI-field will not show up as a choice.  UI-field only exists client side, so this makes perfect sense!