Grid - Dynamic Formula Field

Empower your Salesforce users with GridMate's Dynamic Formula fields that reference other fields and get updated automatically as the user updates the grid. This type of fields is a powerful feature for calculating values and displaying information in real-time.

Enable Create Formula

To enable the Create Formula option, edit the Lightning page in the App Builder, select the Grid and check "Enable Create Formula"

Dynamic Formula Field Setup

Once you have enabled the Create Formula option, you can start creating formula fields as an End User. The video below is a step-by-step tutorial to learn how to use the Dynamic Formula Field👇.

Grid - Dynamic Formula Fields

A formula field has a Type (Text, Checkbox, Date, Number, Currency, Percent, Url, RichText, File Upload, Custom) and a formula. The type drives how the result is displayed. Formula columns can also be defined by the administrator from the Formulas tab of the Config. Wiz. See Javascript Formulas for the list of operators and functions.

Apex Formula

A basic formula combines the fields of the row with operators and functions. The value is calculated on the browser side and refreshed automatically when the user edits the row.

When the value cannot be calculated from the fields of the row (aggregations, related records, external systems...), the APEX() function delegates the calculation to an Apex class:

APEX(handler, param1, value1, ...)

• handler is the name of the Apex class.

• The next arguments are name/value pairs. The values can be fields of the row or expressions.

Apex Class

The Apex class should be global and should implement the Callable interface. GridMate runs the class for each row with the getValue action and the name/value pairs as parameters:

Callable handler = (Callable) Type.forName(handlerClass).newInstance();
return handler.call('getValue', params);

Our use case is to flag the won opportunities on an Opportunity User Grid. Let's create the Apex class WonOpportunities which receives the record Id and returns a boolean.

WonOpportunities.cls

global class WonOpportunities implements Callable {
    global Object call(String action, Map<String, Object> args) {
        if (action == 'getValue') {
            String recordId = (String) args.get('recordId');

            List<Opportunity> opp = [
                SELECT Id, StageName
                FROM Opportunity
                WHERE Id = :recordId
            ];

            return opp.size() > 0 && opp[0]?.StageName == 'Closed Won' ? true : false;
        } else {
            throw new ExtensionMalformedCallException('Action not implemented');
        }
    }

    public class ExtensionMalformedCallException extends Exception {
    }
}

Formula Configuration

Our Apex class is ready. Let's go ahead and create the formula on the Opportunity User Grid:

1. Open the Grid Explorer, go to the Columns tab and select --Create New--.

2. Set the Type to Checkbox and the label to Won Opportunities.

3. Set the formula as below, click Check Syntax then Save.

APEX("WonOpportunities", "recordId", Id)

1. Add the new Won Opportunities column to the grid and click Apply.

The returned value is displayed according to the Type of the formula: a checkbox in our example, a formatted number for Number/Currency/Percent, a formatted date for Date... When the type is Text, the returned value is rendered as rich text, so the Apex class can return HTML like a link or a badge.

• The Apex class is called once per row when the rows are loaded. A cell displays a working indicator until the value is returned.

• Apex formula values are not recalculated when the user edits the row and are read only.

• Apex formula columns are not included in the scheduled exports.

Bulk Mode

By default, the Apex class is called for each row. When the grid displays many rows, set the batchSize parameter to call the Apex class once per batch of rows:

APEX("WonOpportunities", "batchSize", 200, "recordId", Id)

In bulk mode, GridMate calls the getValues action with a single params parameter: the JSON list of the name/value pairs of each row. The class should return the list of values in the same order.

global class WonOpportunities implements Callable {
    global Object call(String action, Map<String, Object> args) {
        if (action == 'getValues') {
            List<Object> rows = (List<Object>) JSON.deserializeUntyped((String) args.get('params'));

            Set<Id> oppIds = new Set<Id>();
            for (Object row : rows) {
                oppIds.add((String) ((Map<String, Object>) row).get('recordId'));
            }

            Map<Id, Opportunity> oppMap = new Map<Id, Opportunity>(
                [SELECT Id, StageName FROM Opportunity WHERE Id IN :oppIds]
            );

            List<Object> values = new List<Object>();
            for (Object row : rows) {
                Id oppId = (String) ((Map<String, Object>) row).get('recordId');
                values.add(oppMap.containsKey(oppId) && oppMap.get(oppId).StageName == 'Closed Won');
            }

            return values;
        } else {
            throw new ExtensionMalformedCallException('Action not implemented');
        }
    }

    public class ExtensionMalformedCallException extends Exception {
    }
}