Salesforce Formulas

Spring 2019 Note: Pulsar now directly supports evaluating Salesforce formulas both online and offline.

• Supports formula fields

• Supports default value field formulas (during initial record creation)

• All numeric, logical, string, and date/time operators and functions are supported

• Most summary and advanced functions are supported (some server-side-specific functions are not supported)

• Set pulsar.formula.enableSFDCFormulas Pulsar Setting to TRUE to enable this functionality in the Pulsar mobile app

...

Notes and caveats

...

• If there is both a Salesforce formula and a PSL formula for the same field, the PSL formula takes precedence, and the Salesforce formula is ignored
• Salesforce formulas and PSL formulas across objects/fields should not interact (e.g., if FormulaFieldA__c is a Salesforce formula and FormulaFieldB__c is a PSL formula, the result is undefined if they reference each other).
• Formula fields are calculated

  When syncing records, Pulsar picks up formula field calculations from Salesforce servers, and persists them to the local database

• Formula fields are always recalculated on-the-fly every time you view a record detail page

  (but not when editing)

  in Pulsar

• Formula fields are NOT dynamically calculated in list views

  .  The values showing in the list view may not match the latest information unless you perform a step to persist the calculation to the local database

  ; instead persisted data from the local database is shown

  • For many organizations this default behavior is sufficient

  • But some organizations may encounter stale data situations (see below)

Persisting formula field values

...

• When creating or editing records, formula fields for that record are calculated and persisted to the local database

...

PSL Formulas

Spring 2019 NOTE: we recommend that you use SFDC Formulas going forward, but PSL formulas will continue to be supported.

Pulsar originally did not support evaluating SFDC formulas, and instead offered similar functionality through logic implemented in Pulsar Settings Language (PSL).

PSL Formulas are implemented via a group of flexible Pulsar Settings that define the fields affected, the order in which they are processed, and the formula logic in PSL for each field.  Just as in Salesforce formulas, default values are used during record creation and formula fields are used to calculate the proper values for fields throughout the life of the record.

Please note that regardless of the values calculated for formula fields in Pulsar, if the same field has a formula defined on Salesforce, the Salesforce formula supersedes the Pulsar formula when the record is synced to the server.

Usage

Formula fields and default value formulas follow an identical pattern when defined using Pulsar Settings. For each affected object or record type:

1. Specify which fields to process and the order in which they should be processed (Field Order Settings)
2. Describe the logic or calculation that must occur (Formula Settings)

Field Order Settings

The formula field order and default value field order settings both allow you to specify a list of field API names separated by commas, newlines, or semicolons. The setting key optionally allows you to narrow the scope to a specific record type. 

Formula Field Order Setting Format

Name: Object Name – Formula Field Order
Key: pulsar.<object API name>[.<record type developer name>].formulaFieldOrder
Value: Field1APIName, Field2APIName, Field3APIName, ...

Example:

Name: Order Line Item – Formula Order
Key: pulsar.Order_Line_Item__c.formulaFieldOrder
Value: Unit_Price__c,Total_Discount__c,Amount__c

Default Value FIeld Order Setting Format

Name: Object Name – Default Value Field Order
Key: pulsar.<object API name>[.<record type developer name>].defaultValueFieldOrder
Value: Field1APIName,
           Field2APIName,
           Field3APIName,
           ...

Example:

Formula Settings

Defining the formulas associated with the default value or formula fields requires using PSL. The only requirement is to use the SetFieldInMemory action to set the resulting value to the field in question. The setting key optionally allows you to narrow the scope to a specific record type, as with the field order settings above.

Formula Field Formula Setting Format

Name: Object Name – Formula Field – Field Name
Key: pulsar.<object API name>[.<record type developer name>].<field API Name>.formula
Value: Pulsar Settings Language

Example:

Name: Order Line Item – Formula Fields – Unit Price
Key: pulsar.Order_Line_Item__c.Unit_Price__c.formula
Value: See the code below. Notice that the last action is the SetFIeldInMemory action.

DEFAULT{
Action=SetVar;
VarName=UnitPrice;
VarValue=Product__r.Unit_Price__c;
|
Action=SetFieldInMemory;
FieldType=General;
FieldName=Unit_Price__c;
FieldValue=%%UnitPrice%%;
}

Default Value Formula Setting Format

Name: Object Name – Default Value Field – Field Name
Key: pulsar.<object API name>[.<record type developer name>].<field API Name>.defaultvalue
Value: Pulsar Settings Language

Example:

Name: Cheque – Default Value Field – Cheque_Date__c

...

• • For many organizations this default behavior is sufficient

  • But some organizations may encounter stale data situations (see below)

Local persistence of formula fields and potential stale data

When Pulsar is online and syncs, formula field calculations are picked up from Salesforce servers and persisted locally.  (NOTE that after the very first sync, there should be no stale data persisted to the local database, since all records have just been created afresh directly from Salesforce).

When online during record creation/edit, Pulsar will sync the newly created/updated record and also perform a readback that will pick up canonical formula field calculations from Salesforce servers.  Also when creating/editing a record offline, Pulsar itself persists formula fields calculations just for that record to the local database.

So whether Pulsar is online or offline, formula fields are recalculated for the created/updated record, and persisted to the local database just for that record.  So where can the stale data creep in?

It turns out that some formula fields are more complex than others.  Simple formulas (our terminology) are those that only touch data from the record in question.  Cross-record formulas (our terminology) are those that touch data across various records.

• Objects that only have simple formula fields will always have coherent data stored in the local database

• Objects that have cross-record formula fields will accumulate stale data in those formula fields because Pulsar does not recalculate formulas for all records that might be affected by a change

• NOTE that stale data is not necessarily problematic for many organizations.  The problems generally arise in the following scenarios:

  1. Showing cross-record formulas in list views

  2. Direct querying of cross-record formula fields in the database from custom code (e.g. SQL querying via PSL or via the Pulsar Javascript API).

Forcing formula field recalculations

If you are encountering stale formula data problems, luckily there is a mechanism from PSL shown here (as well as the Javascript API) to calculate and persist formula fields.

DEFAULT{
Action=CalculateSaveFormulas;
ObjectType=<ObjectAPIName>;
ObjectIds=<Optional comma-separated list of object Ids. If empty/unspecified, will operate on all records of ObjectType>;
WhereClause=<Optional WHERE clause to programmatically select specific Ids>;
FormulaFields=<Optional comma-separated list of fields.  If empty/unspecified, will operate on all formula fields of the ObjectType>;
}

Let's say, for example, you want to ensure that all formula fields on the Account object are always kept up-to-date in the local database when you are saving the Contact object.  You can create an OnSave PSL Trigger (see the next section in the wiki about PSL Validation Rules and Triggers).  The following example PSL will accomplish that:

DEFAULT{
Action=SetVar;
VarName=Account_Id;
VarValue=AccountId;
| 
Action=CalculateSaveFormulas;
ObjectType=Account;
ObjectIds=%%Account_Id%%;
}