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.
rounddown(numeric_field)
Rounds down to the next whole number. For example rounddown(12.345) will return 12.
roundup(numeric_field)
Rounds up to the next whole number. For example roundup(12.345) will return 13.
round(numeric_field, precision)
Rounds to the specified precision. For example round(12.345,2) will return 12.35.
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_balanceIf 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