Skip to main content

Local Formula Fields

Enterprise

SOQL has many limitations which makes it difficult to impossible at times to extract the data/insight you are looking for. One such limitation is not being able to compare two fields to see the difference in those values.

Brobench Local Formula Fields allows you to add fields to SOQL queries whose data is derived based on a Brobench Formula expression. The syntax for adding a formula field is as {{ Formula Expression }}:DataType Alias where Alias and DataType are optional. If there is no alias, the default alias will be used.

For example, query SELECT Id, ShippingCity, BillingCity, {{ ShippingCity != BillingCity }} NotSameCity from Account returns below result.

Keep in mind
  • Local Formula Fields in the result are prefixed with an underscore. In the above example, alias is NotSameCity and the result is _NotSameCity.
  • All the fields referred in the formula expression must be selected in the Query. For ex., in the above example if you hadn't selected BillingCity, then the result would have been wrong
  • If an Alias is not provided for a formula field, then default one will be assigned. For ex., in the above example, if you had not provided alias, field would have been _Formula1

Formula Expressions​

Formula Expressions are written using Brobench Formula Language. You can read more about Brobench Formula Language. You can refer to a list of available formula functions here.

Formula Expression Data Type​

By default, app assumes the data type of string for all formula fields, which is correct most of the time. But if your formula is returning a data or datetime or number, then you might want to explicitly specify the data type.

Below is the list of data types that you can specify.

  • date
  • datetime
  • number

For example,

  • select Id, {{ add_date(CreatedDate, -5, 'minutes') }}:datetime CreatedDateMinusFive from Account
  • select Id, AnnualRevenue, {{ AnnualRevenue * 0.1 }}:number Appreciation from Account

Examples​

Compare if two fields are equal or not​

Let's say you have a set of records, and you want to check if record id last modified by owner or not. To do that, you can run the below query.

select Id, LastModifiedById, OwnerId, {{LastModifiedById == OwnerId}} LastModifiedByOwner
from Account where CreatedDate >= last_n_days:30

Number of times a field is modified​

Let's say you want to see how many times a set of fields are modified in Opportunities. To do that, you can run the below query.

select Id, StageName, Name, Sales_Rep__c, (Select Id, Field, NewValue, OldValue from Histories),
{{histories.records.filter((it) => it.Field == 'StageName').length}} StageUpdateCount,
{{histories.records.filter((it) => it.Field == 'Name').length}} NameUpdateCount,
{{histories.records.filter((it) => it.Field == 'Sales_Rep__c').length}} SalesRepUpdateCount,
from Opportunity limit 10000