Calculate a field value by using functions, including custom calculations between other fields.
Formula fields are an advanced custom field and are added just like standard custom fields via the Control Panel settings (permissions required to access the Control Panel).
Access the Control Panel via the cog icon in the main menu bar > select the desired Module and then Edit Custom Fields.
Formula fields, and other custom field types, are stored underneath categories.
Use the Add button in the top right of the screen to select a custom field type to add within a category. A combination of similar or different custom field types can be used within each category type. Choose the Formula field option from the Add drop-down.
Once selected, use the Category drop-down to assign that custom field to its respective category. By default, all custom fields are assigned to Uncategorised if one is not assigned.
Label the Formula field to reflect what the calculated value will represent.
If adding a Formula field against the Contact Module, there is an extra option to choose if the field should be assigned to Individuals and/or Organisations.
Next, define the field format as Number or Date and time. A number field means the value returned will consist of numerical characters only. If decimals are required, it needs to be defined at this point. Once the Formula field is saved, the number of decimals cannot be changed. The Date and time field means the value returned will produce either a date only or a date and time result. Once defined as a Date only field or a Date and Time field, this distinction cannot be changed once the Formula field is saved.
The field format determines which fields are available to select in the Fields dropdown. Only existing Number custom fields and other number formula fields against that Module will be available for selection if Number is the chosen field format. Use the Number field to
If a Date or Date and Time field is chosen, only predefined Date fields will be available for selection.
The Fields, Operator and Custom input fields are used to build out the formula. The Fields and Operator fields are predetermined based on the Field format chosen. Available Operators are: ( ) + - * and /. Users can add numeric values and parenthesis to the formula. The Custom input field is an open numeric field used to manipulate the formula. Selections from these fields will display in the Formula field. It is important to note that the selections need to be entered in the order of how it would read if writing a formula.
Examples: using a Number type Formula field = (Field1 / Field2) * 100 to calculate Gross Profit Margin if Field1 represents Gross Profit and Field2 represents Revenue. Or use a Date type Formula field = Field1 + 30 to auto define a payment due date where Field1 represents the Date an invoice was sent.
To create the above Number Formula field example, in the following order first enter the Operator ( parenthesis, then select the first custom field, then the / Operator, then the second custom field, then the ) Operator, then the * Operator and lastly, enter the Custom input 100.
Once saved, the Formula field settings will default to the below:
The field is marked as Read Only because the field will not appear as available for selection on a record profile page. The field only populates once the fields which were included in the Formula build criteria are populated. The Formula field is not available to edit when on a record's profile page as well. Only the fields used to build the Formula are editable. Once edited, the Formula field will update to reflect the changes.
Use the option to drag and drop custom fields and categories to arrange their placement. The Add Contact window will display categories and fields in the same order as laid out in the control panel settings.
Populate a Formula field
Once the field is defined in the Control Panel, the Formula field is immediately available on the record's profile page. Since the Formula field is dependent upon other fields being populated first, the field will not show until those fields have been populated with either the required date or number. When all fields used in the Formula calculation have been populated, the Formula field will auto-populate on the profile page. This field is not manually added by the User on the profile page.
If the value of one of the fields included in the formula is updated, the Formula field value with automatically update to reflect the update.