Field Formulas

On the field creation screen, enter a formula into the Formula text box to apply it to the field value.

Arithmetic

These formulas are available for Number type fields only. Fields which contribute to a formula field must be within the same record template. In other words, related records don’t work. Use field api names, not the display names.

Additive Syntax: =$field1 + $field2
Subtractive Syntax: =$field1 - $field2
Multiplicative Syntax: =$field1 * $field2
Division Syntax: =$field1 / $field2

To combine operations into longer formulas, follow standard mathematical order of operations processing. PMDAS (Parentheses, Multiplication, Division, Addition, Subtraction)
You can affect this by using parenthesis which will cause the order of operations to also respect FOIL (first outer inner last).

= ($field1 / $field2) * 100

Note: Similar to calculations in MS Excel, because of rounding and truncation used in floating-point arithmetic, the results of some formulas may be affected. Please see this article for more information when creating formulas with very fine accuracy required.

Logical Operators

IF, NOT, OR, XOR are supported logical operators. Please note that while ISPICKVAL was chosen as an example in many cases, other equality check operators, such as EQ, LT, LTE, GT, GTE, can be used.

IF

An IF statement checks whether a condition is met, returns one value if true and another value if false

// using a boolean field
=IF($booleanfield1, "Success", "Fallback")

// using an equality check
=IF(EQ($name, "Santa”), "Ho Ho Ho", "Coal")

NOT(boolean)

NOT is a logical operation that outputs true when the input is false.

// using a boolean field
=NOT($booleanfield1)

// using a dropdown
=NOT(ISPICKVAL($dropdown, "option1")

OR(boolean, boolean)

OR is a logical operation that outputs true when either input is true.

// using boolean fields
=OR($booleanfield1, $booleanfield2)

// using a dropdown
=OR(ISPICKVAL($dropdown, "option1"), ISPICKVAL($dropdown, "option2"))

// mix and match
=OR(ISPICKVAL($dropdown, "option1"), $booleanfield1)

XOR(boolean, boolean)

Exclusive or (XOR) is a logical operation that outputs true only when inputs differ (one is true, the other is false).

// using boolean fields
=OR($booleanfield1, $booleanfield2)

// using a dropdown
=OR(ISPICKVAL($dropdown, "option1"), ISPICKVAL($dropdown, "option2"))

// mix and match
=OR(ISPICKVAL($dropdown, "option1"), $booleanfield1)

Formulas with parent/child records

Scenario 1:

Parent record type: opportunity
Child record type: opportunity_line_item
Child record fields:
fieldA: decimal
Goal is to write a formula field on opportunity (parent record) that sums all fieldA values on child record.

=SUM($opportunity_line_item_set.$fieldA)

Scenario 2:

Parent record type: opportunity
Child record type: opportunity_line_item
Child record fields:
fieldA: decimal
fieldB: drop down with two values (work, party)
Goal is to write a formula field on opportunity that sums all
fieldA values on child record if fieldB == 'party'

=SUM(IIF( $opportunity_line_item_set.$fieldB == 'party',$opportunity_line_item_set.$fieldA, 0))

Concatenation

String fields (text) have a special feature which can be used to automatically concatenate a value from other fields on this record type. In this example, the “Name” field is assigned a value by the system using the first_name and last_name values.

$field $field

Use the field API names, not the “pretty” or display names, to add two or more string fields together to make a longer string. Begin each with a dollar sign and place exactly one space between, with no extra spaces or characters.

Additional characters can be added verbatim if the $ is not used. For example:

Full name is $firstName $Lastname

Note: if you have a large number of records that you’ll be concatenating fields from related records into the “name” field on another record, it’s not advisable to use formulas to do this (because it will greatly slow down the Lanetix environment) - it’s better to keep the ref-join extension in place.

System Fields

Here’s more info on how to incorporate the following system fields into your formula:

$lanetix.id
$lanetix.archived
$lanetix.created_at
$lanetix.updated_at

You’ll likely need to cast these to text to use them like:

${TEXT($lanetix.id)}

Note: on system records - owner_id won’t be available to use in a formula on the “name” field if you want the formula to run upon creation of the record - basically, it’s because owner_id isn’t set/saved until the record is actually created.

Accessing Information from Related Records 

Scenario: I’d like to have the Opportunity information field be a concatenation of the account name (available via account_id picker field), opportunity description (description field on the opportunity) and a region (region dropdown field on the opportunity), separated by dashes:

$account_id.name - $description - ${TEXT($region)}

Accesses are currently limited to going through a maximum of two pickers, and system fields can still be accessed after that

$some_picker.other_picker.lanetix.id

Validations

Validations allow administrators to configure constraints on field values. They are checked when a field is opened for editing in any layout including tasks, and prevent saving if the conditions are not met. For example, setting a minimum allowable dollar amount for expected revenue on an opportunity. This prevents saving the record when the value doesn’t meet a threshold.

Note: When a validation sends an error message upon violating the validation, as in the above example, don’t click on the pop-up to confirm the error, as you will lose your work in the creation layout. Simply update the value in the field and click “Create” again.

Best practice recommendations

• Keep it simple. Include as few validation fields as possible on a task or layout group. With multiple validations preventing a save at once, the complexity for the user to meet the requirements increases, along with frustration and potential for conflicts and error.
• Clean your data to respect any validations before importing. Validation requirements are not respected by the Data Importer, and invalid values can be set this way.
• Don’t mix defaults and validations. Since a default field value can populate a field on creation which violates the validation, don’t use a default value for a field if there is a validation on the field.
• Make a plan. The field template has no indicator for custom validations, so you must keep track of which fields have validations applied.
• Don’t mix formulas and validations. The fields involved with custom validations cannot, in any way, be used in a formula (either a product of a formula, an input in another formula, etc).
• Display all factors in the formula. When a field with validation is displayed, all fields that are used in the validation must be included in the layout and group on the same page of your record layout, including creation layouts, or within the same task on a WF layout. All fields must be on the same record type, cannot use fields from related or child records.

Custom Validations

Launch the custom validations editor from the Studio>Record Builder>Fields page and click the "Custom Validations" link. 

Then select “New Validation”

Fill in the fields here and click save. The validation is applied to all fields in the record type meeting these conditions.

Error messages are displayed at the bottom of the screen when attempting to save the field.

String

Must use quotes around string value

=$name = "stan"

Number

Set value

=$number = 5

Equal to

=$field_1 = $field_2

Comparison

=$field_1 <= $field_2
=$field_1 > $field_2

Not equal to

=$field_1 <> $field_2

Date

Currently no support for rolling time frames, i.e. “today”
All operators work as numbers above

=$date_field_1 < $date_field_2

Boolean

TRUE()
FALSE()
AND(boolean, boolean)
OR(boolean, boolean)
NOT(boolean)
XOR(boolean, boolean) ONLY one must be true

Field Validations

These are set in the field creation screen, and they can’t be edited after field creation. Use these only when the validation conditions will not change at any point in the future. Error messages will be displayed below the field in the layout when attempting to save.

Number

Minimum = field value must be greater than this number
Maximum = field value must be less than this number
Multiple of = field value must be a multiple of this number

String (text)

Min length = field value must have greater number of characters than this value
Max length = field value must have less characters than this value
Pattern = field value must match the pattern of this regular expression (regex)
See http://www.rexegg.com/regex-quickstart.html for some help with regex