Formula Fields

Formula fields are a special type of field where the value is calculated rather than being entered directly. Formula fields are created like any other field, the formula is added as part of the field definition.

To add a formula field navigate to Contacts > Fields, click the Create New Field button and select ‘Formula’ as the field type.

This will display the formula field creation page where you can name your field and add the formula.

The formula name can be anything you like, this is how you will find the field when you’re using it later.

Formulas are made up of fields, functions and arithmetic operators. Like formulas in a spreadsheet there’s no limit to what you create. Formula fields can be used in filters or merged into a communication like any other field.

Operators

Formula can contain the following operators:

+ addition

* multiplication

/ division

- subtraction

% modulo (remainder after division)

Functions

Formulas can contain the following functions. If you would like to use a function that isn’t on this list please let us know at support@impactdata.com.au


years_after(date_field)

Returns the number of whole years since date_field. For example, if an account has the field membership_start_date then the formula years_after(membership_start_date) will return the numbers of years of membership for each contact. This function could be used to calculate a contact’s age given their date of birth (years_after(date_of_birth)). Note that TalkBox accounts have an age field by default so there’s no need to create a formula to calculate age like this.


years_before(date_field)

Returns the number of years before date_field. For example, the formula years_before(membership_expiry) will return the number of whole years until the membership_expiry date for each contact.


days_after(date_field)

Returns the number of whole days since date_field. For example, days_after(last_visit) will return the number of whole days since the contact’s last visit.


days_before(date_field)

Returns the number of whole days before date_field. For example, days_before(financial_until) will return the number of whole days until the contact becomes non-financial.

Examples

Points until next level

If your TalkBox account stores a loyalty points balance in a field called points_balance you could calculate the points required to reach a particular loyalty tier level.

5000 - points_balance

If a contact has 4700 points this field will have the value 300. This could then be used for targeting and merging into emails and SMS messages.

Average spend per transaction

If your TalkBox account is integrated with your POS system and you have fields for transaction_count and lifetime_spend you could calculate the average transaction spec for each contact with the formula

lifetime_spend/transaction_count

Updated on April 29, 2021

Was this article helpful?

Related Articles