Add support for registering custom formulas

  • 12
  • Idea
  • Updated 3 years ago
  • Implemented
This has been discussed in various threads but I couldn't find a specific post requesting this feature.  Apologize if it's a duplicate.

Currently, Skuid provides support for many formula functions.  Unfortunately, there are often specific application needs or even just general use cases for formula features that aren't offered by the core product.

Would like to see support added for registering custom formulas.

Examples of what I've needed and added (unsupported currently of course):
1) register ISNEWID(rowId)
2) register ISEXISTINGID(rowId)
3) register ISROWUNSAVEDCLONE(modelName, rowId)
4) register FORMAT_NUMBER(value, format)
5) register FORMAT_FIELD(modelId, fieldId, value)
6) register UPPER(value)
7) register LOWER(value)
8) register LENGTH(value)
...and several others

Thank you!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb

Posted 4 years ago

  • 12
Photo of Matt Sones

Matt Sones, Champion

  • 33,040 Points 20k badge 2x thumb
Infinite flexibility!
Photo of Karen Waldschmitt

Karen Waldschmitt, Official Rep

  • 9,292 Points 5k badge 2x thumb
Barry, 
That's an interesting idea, we will consider it for a future release!!! Vote it up if you would like to see this too!

Thanks!

Karen
Photo of Dave

Dave

  • 6,814 Points 5k badge 2x thumb
Would like to add 

ISBLANK() and/or ISNULL() to that list please
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
Hey Dave -  

Just to clarify the "ask" on this post was to add support for allowing page developers to write their own Formula Functions and "register them" so that they can be used on a page.  This would be very similar to writing a custom component and registering it for use.    Being able to write and register custom formulas would provide infinite possibilities with regards to formula support without having to wait for skuid to add these to the core product.

That said, ISBLANK() and/or ISNULL would be welcome additions to the stock formula support.  If what you were asking for in your post was to have Skuid add these to the stock formulas, I'd recommend posting another issue asking for these (I'd vote for it :))!
Photo of Dave

Dave

  • 6,814 Points 5k badge 2x thumb
Thank you for clarifying that Barry,

This is even a better request that I thought! And as Matt says the infinite flexibility would be a great addon

Thx
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 12,840 Points 10k badge 2x thumb
Hello Skuid Community ~

Thank you for your thoughtful suggestion! Skuid listened to your concern and has implemented your idea in the new Brooklyn GA release (specifically, under SKUID-2402 in the release notes) which is now available in the Skuid Releases page.

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 when you upgrade.

Thanks again,
-Mark
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
Hello Mark -

This is great news, thank you all for adding this, going to be extremely useful!

I've searched docs & help sites and can't locate any docs on this new feature.  Can you provide a link to the docs describing how to use?

Thank you!
Photo of Tyler Neal

Tyler Neal

  • 760 Points 500 badge 2x thumb
I will 2nd this request, some documentation on this feature would be great.
Photo of Karen Waldschmitt

Karen Waldschmitt, Official Rep

  • 9,292 Points 5k badge 2x thumb
Barry & Tyler~

Once this documentation for custom formulas is available, we will update this post and let you know. 

Thanks and happy new year!
Karen
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
Thanks Karen.  Couple of questions:

1) Is there an ETA on when the docs will be available?
2) In the meantime, is there anything you can post here to provide guidance on how to get started with this new feature?

Thank you!
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,700 Points 4k badge 2x thumb
Is this the feature in question?
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,700 Points 4k badge 2x thumb
It is!

Here's an example of how to use this awesome new feature!

snippet of type Inline:
formulaName = 'concat_names';
formulaFunction = function(first,last){
    
    return 'Your full name is : ' + first + ' ' + last;
};
options = {numArgs : 2,returnType : 'string'};
skuid.formula.Formula(formulaName, formulaFunction, options);


Snippet of type Inline (Snippet) to load your custom function

(function(skuid){	var $ = skuid.$;
$(document.body).one('pageload',function(){
        var buildFormulas = skuid.snippet.getSnippet('customFormulas');
        buildFormulas();
});
})(skuid);

use the formula in a ui formula field:

c_concat_names('John','Apples')

And voilà - formula magic!





Of course the possibilities are endless since merge syntax is supported in formulas and javascript has access to all model data.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
Hello Shmuel -

Nice find!  It looks like the docs must have been recently updated because they didn't include skuid.formula last week.  

Regarding your sample, thanks for putting that together.  However, my guess is that Skuid would not recommend using the approach you took which registers the formula on the "document.ready" (pageload) instead of "as quickly as possible."  The reason for this is that by the time "document.ready" (pageload) fires, skuid has already processed all the model data.  In this case, it wouldn't yet know what the formula is and therefore it would fail to evaluate properly.  Not sure how your page is actually working to be honest, possibly it's a deferred model query.  In short, if you use document.ready/pageload to register the formula, you should encounter an error indicating the formula doesn't exist assuming you're models load immediately during page initialization.

My guess is the recommended approach would be to use an inline JS resource and register the formula as quickly as possible.  Using your example, it would simply mean modifying to the following:
(function(skuid){	    var $ = skuid.$;
    var buildFormulas = skuid.snippet.getSnippet('customFormulas');
    buildFormulas();
})(skuid);

Of course, you really wouldn't need a snippet so you could further simplify and simply do the following using an inline JS resource:
(function(skuid) {    
skuid.formula.Formula("concat_names", function(first,last) { return "Your full name is : ' + first + ' ' + last; }, { numArgs: 2, returnType: "string" });
})(skuid);

With all that said, I'm hopeful that Skuid is going to provide more details on custom formulas beyond what's been added to the API doc that you linked to.  My questions would be:

1) What is the recommended approach for registering formulas
2) Is there way to make the formula visible in Page Composer or does the page builder just have to "know" that there is a custom formula available by a certain name?
3) What happens when a formula name is registered more than once?  Currently, it looks like it just takes the last one registered.  My feeling on this is that if a formula is trying to be registered and there is already a formula by that name, it should throw an exception.  Not doing so could lead to many hours spent trying to figure out why something isn't working only to find out someone else wrote a formula with the same name
4) Is there a way to indicating what triggers re-evaluation of the formula beyond what would be picked up in straight merge syntax.  In other words, similar to MODEL_LOOKUP, there could be something beyond what field is in merge syntax (e.g. the name of another model) that could change that needs to result in the formula re-evaluating.  Currently, MODEL_LOOKUP doesn't do this so I'm hopeful that there is a solution available to account for this in some way (or at least something on the roadmap).

Look forward to hearing more details from Skuid on this :)
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
One other question that I forgot to include (for some reason I can't edit my previous post):

5) With the introduction of custom formulas, the problem identified at Output of formula function contains the text definition of the formula function could become a larger issue.  The fact that all custom formulas are prefixed with "c_" should help minimize but it does expose more opportunity for this defect to be surfaced. The issue was last updated 7 months ago so hopeful some insight as to when that one will be addressed could be provided.

Thanks!
(Edited)
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,700 Points 4k badge 2x thumb
Hey Barry,

My page probably wasn't running into errors because I was loading models manually after page load.

Thanks for the info on the order of execution for skuid page preparation. That'd make for some great documentation.

Thanks for the simplification and correction on the snippets.

I'm also curious about your points, most importantly 4! If your functions could subscribe to certain events to automatically recalculate (or even if there was a function to recalculate custom formulas) that could save lots of recalculation code.

Cheers!
Photo of Matt Sones

Matt Sones, Champion

  • 33,040 Points 20k badge 2x thumb
Shmuel,

There is an undocumented function on the model api to evaluate formula fields:
evaluateFormulaFields();

I run it with a snippet like this:

'evalFormulas' : function(){    	//Requires model to be passed in arguments
var model = arguments[0].model;
model.evaluateFormulaFields();
model.save();
},
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,700 Points 4k badge 2x thumb
Sweet
Photo of Matt Sones

Matt Sones, Champion

  • 33,040 Points 20k badge 2x thumb
Of course, what we really want (and what Barry is getting at with his #4), is the ability to tell skuid through the formula definition when to automatically trigger reevaluation. Otherwise, we'll be manually calling evaluateFormulaFields() every time a change is made that requires reevaluation.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
Matt is spot on with understanding my #4.  Along the lines of evaluateFormulaFields, just a couple of thoughts:

1) It's undocumented and therefore, not officially support and could be removed in future versions (unlikely of course).  I use undocumented "features" all the time, but for those reading this, it's important to note what "undocumented" means and potential ramifications.

2) I haven't looked at the code under evaluateFormulaFIelds in a long time but from what I remember, it will iterate every row in the model and re-evaluate every field that contains a formula.  This could potentially be a large amount of processing that could "slow down" your page.  

Ideally, if we had something like #4, Skuid could be "smart" about what gets re-evaluated focusing the processing on only models/rows/fields that could possibly change as a result of a data change.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,456 Points 10k badge 2x thumb
FYI - I just created https://community.skuid.com/skuid/topics/add-support-for-programmatically-triggering-formula-fields-....  Vote it up if you'd like to see this happen!
Photo of Shmuel Kamensky

Shmuel Kamensky, Champion

  • 4,700 Points 4k badge 2x thumb
Thanks! Voted