General
This page contains examples of the most commonly used Dynamic field formulas. The formulas might not be suitable for every situation as they are written here, but they serve as models to write the dynamic field formulas.
The formula instructions are color-coded to illustrate which parts of the formula are most likely to change according to different needs. The explanation of the colours is as follows:
- Blue identifies the module field IDs. These should be changed to match the IDs of the fields that are used in the formula.
- Turquoise represents the value of the specific field.
- Red represents variables that affect the final value of the target field. These variables could be for example a number to which the content of the field is compared or a text that is written in the field.
Quick Tips for Writing Dynamic Field Formulas
This section discusses things to consider when writing formulas and it also includes some tips to help you write formulas. If you don’t want to write the formulas yourself, you can always contact support to order the formulas you need.
Things to consider
Fields are between double hyphens ” whereas otherwise single hyphens ‘ are used. Only variables between double hyphens are processed, e.g. counted together, while the information inside single hyphens is read and written as it is.
Example of a situation where single hyphens are used to read field data:
{if $phone== ''}Phone number is missing{/if}
The formula above reads the data in the phone field, and if the field is empty, a message is returned indicating that the phone number is missing. This kind of informative formula is also one of the exceptions where if-clause can be used without else-clause, which is required in most cases or the formulas don’t work properly. If in doubt, always add else.
When using date fields, Y= whole year (for example 2023) and y= last two digits of the year (for example 23).
The calculation formulas must have |default:0 added to them for the calculation to work.
For information on the most common errors in dynamic field formulas, see Common Errors in Field Formulas.
Quick if-tips
{if $contact_id} – The contact field must have value.
{if !$contact_id} – The contact field must not contain a value.
{if $account_type==’customer’} – The account type must be a customer.
{if !$account_type!=’customer’} – The type of account must be something other than a customer
Note! When making if-clauses, always check whether or not the if-clause should be continued with an else-clause. In practice, in most cases if must be accompanied by else, because the else statement tells the system what to do if the condition in the if clause is not fulfilled.
Operators
| == | Check that values are equal. |
| != | Check that values are not equal. |
| < | Check that the value on the left is less than the value on the right. |
| > | Check that the value on the left is greater than the value on the right. |
| <= | Check that the value on the left is less than or equal to the value on the right. |
| >= | Check that the value on the left is greater than or equal to the value on the right. |
| === | Check that the values are equal and that their data type is the same. |
| && | Check that both of the given conditions are met. |
| || | Check whether one of the given conditions is met. |
Field-name variants
Some field names have variants when they are used in dynamic field formulas. You can check these from the article Field name variants Legacy vs REST/CRM2.
Simple Dynamic Field Formulas
Simple Dynamic Field Formulas do not contain complex structures. They make it easy to get started writing formulas and are mainly simple validation formulas, informative formulas, and different formulas used for retrieving data from fields.
Filling a subject for a quote
Quote {$account_id|getEntityFieldValue:'accountname'}
The formula fills the field (in this example, the Subject field of a quote) with the text “Quote” and the account’s name.
Updating the probability of the potential to 100% if the sales stage is changed to won
{if $sales_stage=='Closed Won'}100{else}{$probability}{/if}
The formula updates the value of the probability in the field “probability” to 100 if the status of the field “sales_stage” is “Closed Won”. Otherwise, no changes are made.
Fetching the price of a product which is linked to an entity
{$product_id|getEntityFieldValue:'unit_purchase_price'}
The formula fetches the price of the product “product_id” from the field “unit_purchase_price” and copies the value to the target field.
A similar function can also be done on the row level with the following format:
Row Formula: Fetching the price of a product which is linked to an entity
{$row.hdnProductId|getEntityFieldValue:'unit_purchase_price'}
Row formula: Show row sequence number
{$row.sequence_no}
The formula is used on save. A new field (number or text) must be created for the row where the information will be stored.
This works on save also if a row is deleted or otherwise moved up or down on rows.
Note: If a product row is copied, however, the formula does not work on the first save. The entity has to be taken again into edit mode and saved again – then the row sequence number is updated.
Fetching the contact from a project, if the contact field is empty
{if !$contact_id}{$project_id|getEntityFieldValue:'contact_id'}{else}{$contact_id}{/if}
If the contact field on e.g. an invoice is empty, the formula fetches the contact in a project linked to the invoice. If the field is not empty, no changes are made.
Fetching the email address of the assignee to a text field
{$assigned_user_id|getUserFieldValue:'email1'}
The formula fetches the email address of the user selected in the Assigned To field.
Informative formulas
Information formula: Notifying the user of a filled field
{if $account_id|getEntityFieldValue:'ovt' == ''}Customer doesn't have OVT{/if}
The formula checks if the field “ovt” is empty and if it is, it notifies the user with the information text “Customer doesn’t have OVT”.
Information formula: Notifying the user of a filled field
{if $passivation_date!= ''}The contact has a passivation date{/if}
The formula checks if the field “passivation_date” is empty. If it is not, it notifies the user with an information text “The contact has a passivation date”.
Validation formulas
Validation formula: Giving notice of an invalid country code
{if $bill_country|count_characters > 2}The country code of the billing country is invalid. Finland = FI, Sweden = SE, Norway = NO and Denmark = DK{/if}
The formula verifies the length of the given country code and if the length is more than two characters, it notifies the user with a warning “The country code of the billing country is invalid…”. The entity is not saved then.
Validation formula: The entered bank account number is of a certain length
{if $cf_bankaccount1|count_characters > 18}The bank account is too long{elseif $cf_bankaccount1|count_characters < 18}The bank account is too short{/if}
In the example case, the validation formula checks the value entered into the self-created (cf-field) field named bankaccount1 and prevents it from being saved, if the value is less than 18 or greater than 18 characters. The text between brackets is displayed to the user in the notification section if the value is not in the allowed range. The count_characters can be used to validate any field that has specific length requirements by just changing the field name and numbers of the example formula.
Basic Dynamic Field Formulas
Basic dynamic field formulas use slightly more complex processes and calculations than simple dynamic field formulas. They can be any type and, e.g., most formulas involving dates and mathematical calculations are basic formulas.
Remove spaces from the email field
{$email|regex_replace:"/\s+/":""}
The formula removes all spaces from the field.
Allowing only the use of letters and numbers
{$cf_1234|regex_replace:"/[^a-zA-Z0-9]/":""}
The formula verifies that there are only letters or numbers used in the field and replaces any other characters with a blank.
Updating and correcting the form of a bank account number
FI{$cf_bankaccount1|regex_replace:"/[^0-9]/":""}
The first two letters of the formula are added to the beginning of the field data. The partition inside the brackets ensures that only numbers between 0-9 are used in the field value and no spaces or other characters remain in the formula. If the content of the field is already in the right format, no changes are made. In the validation formula section, there is an example of a formula, that can be used to verify the required length of the bank account number and does not allow the entity to save in other cases.
Updating and correcting the form of a phone number
{assign var=pno value=$mobile|regex_replace:"/[^0-9]/":""}{if $pno|strpos:'0' === 0}358{$pno|substr:1}{else}{$pno}{/if}
The first part of this formula verifies that there are only numbers 0-9 in the field and if there is an invalid character used, it is replaced with a blank. The second part replaces the first number of a phone number starting with a zero with Finland’s country calling code 358. If the content of the field is already in the right format, no changes are made.
Generate number sequence example member number
{'y'|date}{sprintf("%'.08d\n", $id)}
This formula uses the current year and adds the CRM ID with it.
Multiplying two numbers from separate fields to a third field + determining the number of decimals
{math equation="(x/z)*y" x=$cf_1100|default:0 y=$cf_1200|default:0 z=100 format="%.2f"}
The formula multiplies the numbers in the two fields and places the outcome in the target field (one of the values is also divided by 100). Lastly, it is defined that the outcome will show two decimals.
Date-type Dynamic Field Formulas
Date field type when adding today:
{'Y-m-d'|date}
Setting date for the wanted day:
{assign var='wanted_day' value='next monday'|strtotime}{'Y-m-d'|date:$wanted_day}
Instead of next Monday different values can be used example: first day of next month, +1 week, – 3 day
Last day of the current year:
{"Y-12-31"|date}
Setting a valid until date 14 days apart if the field is empty
{if $validtill ==''}{assign var='is_valid_till value='+ 14 days'|strtotime}{'Y-m-d'|date:$is_valid_till}{else}{$validtill}{/if}
The formula checks if the field “validtill” is empty. If it is, the formula sets the value of the field to a date that is 14 days after the current date. If the field is not empty, no changes are made.
Counting days from multiple fields
{if $payment_term} {assign var='count_date' value="$invoicedate + $payment_term days"|strtotime}{'Y-m-d'|date:$count_date}{else}{$invoicedate}{/if}
The formula adds the number of days defined in the payment_term field to the date in the field invoicedate, and then places this new date to the target field.
Compare dates
{assign var="thisdate" value="now"|strtotime}{assign var="invoiceday" value=$invoicedate|strtotime}{if $thisdate < $invoiceday}Invoice date is greater than today's date.{/if}
Formula fetches the data from wanted field and compares it today’s date. In this example date_format:’Y’ it compares if it is current year.
Fetching a date from a date field and saving it in a text field
{$createdtime|date_format:"d.m.Y"}
The formula fetches the creating time of the entity in the field createdtime and adds it in the target text field in a format dd.mm.yyyy.
Filling the passivation date if the entity is set as passive
{if $passive && !$passivation_date}{'Y-m-d'|date}{else}{$passivation_date}{/if}
Formula first checks if the entity is passive and if the passivation_date field is empty. The formula fills up the field with the current date, if “passivation_date” is empty and the entity is set passive. Otherwise, no changes are made.
Automatic check if the product is still under warranty
{assign var=today value="midnight"|strtotime}{assign var=date value="$cf_1234 midnight"|strtotime}{if $today <= $date}1{/if}
The formula compares the value of a separate “Warranty valid till” field cf_1234 with the current date. If the current date is less or equal to the value of the field, the field “Warranty valid” is set to active. Midnight refers to synchronizing the time to midnight so they are comparative.
Calculating the time between two dates (in days)
{math equation="(y-x) / (3600*24)" x=$createdtime|strtotime y=$modifiedtime|strtotime format="%d"}
The values of the variables start and end are set as the creation time and modified time of the entity. The time in days between these dates is then calculated in the last part of the formula.
Validation formulas
Validation formula: Giving notice of an existing account with the same name
{if $id == ''}{assign var="id" value="0"}{/if}{assign var="check_name" value="crmid != `$id` AND crm_account.accountname"}{assign var="count_name" value='Accounts'|entity_count_by_field_value:$check_name:$accountname}{if $id && $count_name > 0}You are editing an entity and making a duplicate{elseif !$id && $count_name > 0}You are creating a new entity and making a duplicate{/if}
The validation formula checks and prevents saving an account with the same name as some other account. The value of the variable count_name is calculated by the number of the same account names. If there are more than one account with the same name, the system notifies the user and prevents saving the account. There is first a check for the situation that an existing account (id exists) is being edited and saved with a duplicate name, and then a check for the brand new entities (there is no ID yet).
Advanced Dynamic Field Formulas
Certain functions may require very advanced dynamic field formulas – you can try these yourself if you wish, but you can also order them directly by contacting CRM-service Support.
Generate number sequence example member number by using entity creation year and adding the CRM ID with it
{$createdtime|date_format:"%y"}{sprintf("%'.08d\n", $id)}
An example of the more advanced formula that checks if the Account has a price book price:
{if $row.product_id && $account_id}{assign var="PR" value=$row.product_id|get_pricebook_price:$account_id}{else}{assign var="PR" value=$row.listPrice}{/if}{if $PR}{$PR}{else}{$row.listPrice}{/if}
The price book is needed for this formula to function. More about price books and dynamic field formulas associated with them can be found in the instructions about Price Books.
Creating variables and using them in field formulas
It is possible to create your own variables, which can then be used in dynamic field formulas.
For example, we can create a variable “pi” which gives the value of pi with two decimal numbers. This would be created using the following formula:
{assign_static var=”pi” value=”3.14”}
After this, the variable can then be used as follows:
{get_static var=”pi”}
Placing the value of the static variable to the local variable
Alternatively, the created variable value (in this example, pi) can be placed to a local variable as follows:
{get_static var=”pi” assign=”variable_name”}
After this, the local variable can then be used as follows:
{$variable_name}