Thursday, August 13, 2015

[Salesforce / Apex Formula] Help me testing my Apex Formula Algorithm!


This is a call to arms!


In the past months I have developed an implementation in Apex of Salesforce Formulas in order to have a way to use a formula result dynamically without the need to have a custom field on an object.

It has been a huge work and a lot has still to be done, but I need your help to create a big testbook of formulas, in order to point out bugs, errors, and wathever is bad in this implementation.

This implementation supports (syntax guide here):
  • Base object fields (e.g. MyField__c == 4)
  • Lookup fields (e.g. Relation__r.Account.Name == 'ACME')
  • Constants (e.g. $PI, EE, $TODAY, $NOW, $RANDOM
  • Logical operators in function style or inline (e.g. AND(true,false) ; true && false)
  • IF and CASE flow expressions (e.g. IF(true,1,0) )
  • Various String functions (e.g. LEFT("String",2) )
  • Various Math functions (e.g. POW(2,5) )
  • Type conversion functions (e.g. DATE("2015-05-23") )
  • DateTime functions (e.g. DATETIME(2015,12,01,0,0,0) ; ADDYEARS($TODAY, 10) )
  • Multiline comments
  • Access to Hierarchy Custom Settings (e.g. $Setup.HierarchyCS__c.Value__c)
  • Access to Hierarchy Custom Settings (e.g. $Setup.ListCS__c["aName"].Value__c)

You can set different return types:
  • BOOLEAN
  • NUMBER
  • STRING
  • DATE
  • DATETIME

For the NUMBER return type you can also set a Scale attribute.

Once released the method call will be something like:

String formula = 'IF(NumberOfEmployees > 15, NumberOfEmployees, NumberOfEmployees/2)';
String objectId = '001000000123456789AAA';
String objectType = 'Account';
String returnType = 'NUMBER';
String scale = 2;
ApexFormulaResult formulaResult = ApexFormulaParser.evaluate(formula, objectId, objectType, returnType, scale);

Here is the Apex Formula Playroom I've built with Salesforce Site.


I've create a custom object, TestObject__c, which you can select to try the algorithm, which has all kind of Salesforce fields.

This is the raw interface of the playroom:


  1. List of all custom objects you can use (if you need more tell me!). Click on the name to select it.
  2. These are the details (with the objects field API names) of the selected object
  3. You can also test custom settings as well
  4. You can select a built-in formula or you can set a specific Output Type and Scale
  5. Formula's body and any suggestion you want to give me, and the formula result as well

Every formula you input and calculate is stored in the Site, so please avoid using personal informations that you don't want to share!


Feel free to break down my code, I expect a lot of work in error handling, but the awesome Developer Salesforce Community< is here to help, isn't it?

And remember: