Examples of Dynamic Field Formulas
On this page, there is a small collection of examples of some commonly used dynamic field formulas. The formulas might not be fit for every need as they are, but they show you the way of how the similar formulas can be written.
The formulas in this guide are written using colour coding to visualize, which parts of the formula are most likely replaced to meet the specific need. The parts marked with blue are IDs of the fields in the module and they should be replaced to match the IDs of the fields actually used. The parts marked with orange represent a value of a certain field. The parts marked with red are 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 to, or a text that is written in the field.
Quick basic IF -tips
{if $contact_id} - Contact -field needs some value
{if !$contact_id} - Contact - field needs to be empty
{if account_type=='customer'} - Account type needs be Customer
{if account_type!='customer'} - Account type needs be something else than Customer
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.
Information formula: Notifying the user of an empty 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 an 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 is not, it notifies the user with an information text “The contact has a passivation date”.
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.
NB. A similar function can also be done on row level with the following format:
{$row.hdnProductId|getEntityFieldValue:'unit_purchase_price'}
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.
Updating and correcting the form of a phone number
{assign var=pno value='/[^0-9]/'|preg_replace:'':$mobile}{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 a right format, no changes are made. NB! Replace “$mobile” with the ID of the field the formula is created for (if it is not the Mobile field).
Updating and correcting the form of a bank account number
FI{assign var=pno value='/[^0-9]/'|preg_replace:'':$cf_pankkitili1}{$pno}
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 a 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.
Allowing only the use of letters and numbers
{assign var=pno value='/[^a-z0-9]/'|preg_replace:'':$cf_1234}{$pno}
The formula verifies that there are only letters or numbers used in the field and replaces any other characters with a blank.
Remove spaces from email field
{$email|regex_replace:"/\s+/":""}
The formula removes all spaces from the field.
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.
{"$createdtime"|date_format:"%y"}{sprintf("%'.08d\n", $id)}
This formula uses entity creation year and adds the CRM ID with it.
Saving activity type and the names of the linked contacts in a subject field
{if $subject ==''}{$activitytype} {foreach item=contact from=$Contacts name=cnt}{if !$smarty.foreach.cnt.first}, {/if}{$contact|getContactName}{/foreach}{else}{$subject}{/if}
If the subject field of the event is empty, the event type and the names of every contact linked to the event are set as the subject. The contact names are separated with commas (,) and blank spaces. If no contacts are linked to the event, the subject will only be the event type.
Multiplying two numbers from separate fields to a third third field + determining 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.
Note! With mathematical formulas it is necessary to use |default:0
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 to.
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.
Formulas handeling date -type of fields
Date field type when adding today
{'Y-m-d'|date}
Instead of next monday different values can be used example: first day of next month, +1 week, – 3 day
{assign var='t' value='next monday'|strtotime}{'Y-m-d'|date:$t}
Last day of the current year
{strtotime('Dec 31')|date_format:'Y-m-d'}
Setting a valid until date 14 days apart if the field is empty
{if $validtill ==''}{assign var='t' value='+ 14 days'|strtotime}{'Y-m-d'|date:$t}{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
{assign var='t' value="$invoicedate + $payment_term days"|strtotime}{'Y-m-d'|date:$t}
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.
Note that fiels are between double uppermarks and other single ” ‘
Counting days
{assign var='a' value='now'|strtotime}
{assign var='b' value=$invoicedate|strtotime}
{if $a < $b}Invoice date is greater than current date{/if}
The formula compares to values that are setted using assign var -method.
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.
Calculating the time between two dates (in days)
{assign var="start" value=$createdtime|strtotime}{assign var="end" value=$modifiedtime|strtotime}{floor(($end - $start) / (3600 * 24))}
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.
Filling the passivation date if the entity is set passive
{if $passivation_date=='' && $passive=='1'}{'Y-m-d'|date}{else}{$passivation_date}{/if}
The formula fills up the field with 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='Y-m-d'|date}{assign var=date value=$cf_1234|getDBInsertDateValue}{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 active.
Validation type of formulas
Validation formula: Only numbers can be entered in the field
{if !is_numeric($cf_field)}Only numbers can be entered in the field{/if}
In the example, the validation formula checks the value entered in the self-created text box (cf-field) and prevents it from being saved if it contains characters other than numbers. The text in parentheses will be displayed to the user in the notification area if the entered value is not allowed.
Validation formula: The entered bank account number is of a certain lenght
{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.
Validation formula: The entered value is within a certain range
{if $description <3}The value must be at least 3{elseif $description >28}The value must not exceed 28{/if}
In the example case, the validation formula checks the value entered in the description field and prevents it from being saved if it is less than 3 or greater than 28. The text between the parentheses is displayed to the user in the notification section if the value is not allowed (not within the certain range).
Validation formula: Giving notice of an existing account with the same name
{if $id == ''}{assign var="id" value="0"}{/if}{assign var="c" value="crmid != `$id` AND crm_account.accountname"}{assign var="cnt" value='Accounts'|entity_count_by_field_value:$c:$accountname}{if $id && $cnt > 0}You are editing an entity and making a duplicate{elseif !$id && $cnt > 0}You are creating a new entity and making a duplicate{/if}
The validation formula checks and prevents saving an account with a same name as some other account. The value of the variable cnt 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).
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.
Works only with CRM-service legacy
Adding the name of the first attached account in a subject field
$subject {$Accounts.0|getEntityFieldValue:'accountname'}
The formula saves the text in the Subject field and after it, adds a blank space and the name of the account that has been attached to the activity first. The number 0 refers to the first attached account; by increasing the number, you can add the name of e.g. the second, third, etc. account.
Creating a public sign up link for an invitation event, if public visibility has been activated
{if $anonymous_visibility==1}https://SELF-SERVICE PORTAL URL/public/events/{$id}{else} {/if}
If the field Public visibility of an invitation event has been activated, the formula writes a link for the public sign up page. Otherwise the field will be empty.