Formulas | Email templates

You can insert formulas directly into email templates. Like merge fields in email templates, formulas are wrapped in curly brackets and start with an exclamation mark:

{!formula}

Points to consider before you use a formula in an email template: 

• Do you want email templates to encourage users to log into Sage People and view data there, or to provide specific information in the message?

• Can you use a formula field in the object triggering the email rather than in the email template? The benefits of using a formula field can include: 

  • A formula field is easier to test for different scenarios than an email template.

  • A formula field does not risk being accidentally changed when the email template is edited.

  • A formula field can be reused in multiple email templates.

  • A formula field has fewer limitations on accessing fields from linked records than email templates.

  • A formula field in an email template avoids potential formula formatting issues in HTML email templates, and having to troubleshoot them.

  • A combination of multiple formula operators, such as BLANKVALUE with date formatting can be easier to handle if you do the date formatting in a formula field rather than both in the email template.

The BLANKVALUE function

A common use case for formulas in email templates is to use the BLANKVALUE function to replace any missing data. For example, in an email triggered from the Employment Record, you can use a formula with BLANKVALUE to check if the manager name is populated and if not, use the default value from the formula, as follows:

If you have any questions please speak to 
{!BLANKVALUE(fHCM2__Team_Member__r.fHCM2__Manager__r.Name, 
"your manager")}

You can even omit the BLANKVALUE syntax and have Salesforce convert the shortened version for you by entering the following: 

{!fHCM2__Team_Member__r.fHCM2__Manager__r.Name,"your manager"}

If the shorthand syntax does not work in an email template, revert back to the full BLANKVALUE syntax for the formula.

The IF function

Another common use of formulas in email templates is when you want to insert information only if certain fields are populated. For example, insert information about the bonus scheme only if the team member is eligible. To do this, you can use an IF function in the email template. 

For example, to return a paragraph about the bonus scheme if the Bonus Potential Percentage on Employment Record is greater than zero you need to: 

• Define the condition for the IF statement

• Insert the wording and values from merge fields to use when the statement is true.

• Insert what is included when the statement is false. In this case nothing is displayed in the email.

The resulting formula in the email template looks like the following example (line breaks added for readability): 

{!IF(fHCM2__Employment__c.Bonus__c > 0, 
"The employee shall be entitled to be considered for a bonus under 
the Company's bonus scheme. The decision as to whether to pay a bonus,  
the amount of any bonus, are entirely at the Company's discretion. Bonuses 
are usually paid in December each year. Your bonus potential is 
"& TEXT(fHCM2__Employment__c.Bonus__c)&"percent of your salary.","")}

To make sure the formula works correctly:

• Test the formula thoroughly.

• If you copy and paste the formula into the email template editor, do it from a plain text editor, or preferably type the formula into the editor to avoid issues with special characters such as ampersands (&) and quotation marks, and formatting. Typical issues seen when copying and pasting are: 

  • *Error: Argument cannot be null.

  • *Error: Comma expected in ###### position 72

• Try to avoid extra spaces in the formula

Date conversions

The following examples provide ways to express dates in other formats: 

TEXT(MONTH(appropriate date field here )) 
+"/"+ 
TEXT(DAY(appropriate date field here)) 
+"/"+ 
TEXT(YEAR(appropriate date field here)) 

{!dateformat(enter the appropriate date field here,'MMM/dd/yyyy')} 

{!dateformat(fHCM2__Start_Date__c,'MMM dd yyyy')} 

{!dateformat(fHCM2__Employment__c.fHCM2__Start_Date_c, 'MMMM dd, yyyy')}

{!dateformat(fHCM2__Absence__c.fHCM2__Start_Date_c, 'EEE dd MMMM yyyy')}

When specifying the format with dateformat, remember:

• Uppercase M stands for months, lowercase m stands for minutes

• The number of Ms matters as follows:

  • MM: the month as a number. For example: 11

  • MMM: the month's short name. For example: Nov

  • MMMM: the month name in full. For example: November

• EEE or EEEE can be used for the day of the week of the date:

  • EEE: the abbreviation of the day of the week. For example: Mon

  • EEEE: the day of the week in full. For example: Monday