Formulas | Validation rule library

Note

In addition to the examples listed in this topic, see Salesforce help: Examples of Validation Rules.

Absence object

Absence duration in multiples of 15 minutes

A formula for a validation rule to check that the duration of the absence can be divided by 15 minutes, which is represented by 0.25 in the formula.

Copy
Duration in multiples of 15 minutes
MOD( fHCM2_Days_c ,0.25) !=0.00 

Absence must be a full day

A formula for a validation rule to permit team members to book absences as full days only.

Copy

Absence duration must be a full day

fHCM2__Days__c <> fta__Calculated_Length__c

Absences must be half days or full days

A formula for a validation rule to prevent team members from requesting absences if the duration is not a half day, full day or a multiple of them. The formula also specifies the HR Department to whom the rule applies.

Copy
Absence duration must be half day or full day
fHCM2__Employment__r.fHCM2__Team_Member__r.fHCM2__Department__r.Name ='China' 
&& MOD(fHCM2__Days__c,0.5) <> 0

Absences must be in the future

A formula for a validation rule to check the absence start date and end date are not earlier than today for absences with the reason specified detailed in the rule (Vacation Leave in the example).

Copy
Absences in the future only
ISPICKVAL(fHCM2__Reason__c, "Vacation Leave") 
&& 
fHCM2__Start_Date__c < TODAY() 
&& 
fHCM2__End_Date__c < TODAY() 

Rejecting an absence requires notes

A formula for a validation rule to require the manager to enter notes in a custom Manager Notes field (UDF_Manager_Notes__c) when rejecting a team member's absence request.

Copy
Absence rejection to require notes
NOT(fHCM2__Approved__c )&&  
NOT(ISNULL(fHCM2__Rejected_Date__c)) 
&& 
ISBLANK( UDF_Manager_Notes__c ) 

Prevent team members from booking vacation during their probation periods

A formula for a validation rule to prevent team members assigned a specific absence accrual pattern (Lebanon in the example) from booking absences with the reason "Vacation Leave" until their probation period has passed. The team members are identified with the value of the custom Probation Status field.

Copy
No absence bookings in probation
CONTAINS (fHCM2__Absence_Accrual_Seniority__r.fHCM2__Absence_Accrual_Rule__r.fHCM2__Absence_Accrual_Pattern__r.Name, "Lebanon") 
&& 
ISPICKVAL(fHCM2__Reason__c, "Vacation Leave") 
&& 
ISPICKVAL(fHCM2__Employment__r.Probation_Status__c, "In Probation") 

Marriage leave in China

A formula for a validation rule to ensure only the team members entitled to marriage leave in China can take it, and only take a correct amount. In this example, team members associated with the HR Department called China and who are 25 years old or younger can take up to 3 days of marriage leave.

Copy
Marriage leave validation
fHCM2__Employment__r.fHCM2__Team_Member__r.fHCM2__Department__r.Name = 'China'
 && 
fHCM2__Employment__r.fHCM2__Team_Member__r.fHCM2__Age__c  <= 25
&& 
fHCM2__Days__c  > 3

Application object

Desired salary

A formula for a validation rule for a custom Desired Salary field in the Application object in Talent Acquisition requires the candidate to enter a value of 0 or greater.

Copy
Must enter a desired salary
Desired_Salary__c  <= 0

Bank Detail object

Validate routing numbers

A formula for a validation rule to check the routing number is correctly constructed, but not the validity of the bank in which the routing number is assigned.

When you write a check, the bank that cashes it uses the routing number to determine which bank the check is coming from. The American Banking Association assigns routing numbers to banks, credit unions and other financial institutions. Routing numbers have been used since 1910. The routing number on a check is found in the lower left-hand corner of the check and is a nine-digit number. The final digit of the routing number is a check digit, meaning it can be calculated from the first eight digits as a way to prevent bad checks.

Copy

Routing number format

NOT(LEN( fHCM2__IBAN__c)=9)||
    VALUE(RIGHT(fHCM2__IBAN__c,1))<>
    IF(MOD(
    VALUE(MID(fHCM2__IBAN__c,1,1))*3+
    VALUE(MID(fHCM2__IBAN__c,4,1))*3+
    VALUE(MID(fHCM2__IBAN__c,7,1))*3+
    VALUE(MID(fHCM2__IBAN__c,2,1))*7+
    VALUE(MID(fHCM2__IBAN__c,5,1))*7+
    VALUE(MID(fHCM2__IBAN__c,8,1))*7+
    VALUE(MID(fHCM2__IBAN__c,3,1))+
    VALUE(MID(fHCM2__IBAN__c,6,1)),10)=0,0,
    CEILING((
    VALUE(MID(fHCM2__IBAN__c,1,1))*3+
    VALUE(MID(fHCM2__IBAN__c,4,1))*3+
    VALUE(MID(fHCM2__IBAN__c,7,1))*3+
    VALUE(MID(fHCM2__IBAN__c,2,1))*7+
    VALUE(MID(fHCM2__IBAN__c,5,1))*7+
    VALUE(MID(fHCM2__IBAN__c,8,1))*7+
    VALUE(MID(fHCM2__IBAN__c,3,1))+
    VALUE(MID(fHCM2__IBAN__c,6,1)))/10)*10-
    (VALUE(MID(fHCM2__IBAN__c,1,1))*3+
    VALUE(MID(fHCM2__IBAN__c,4,1))*3+
    VALUE(MID(fHCM2__IBAN__c,7,1))*3+
    VALUE(MID(fHCM2__IBAN__c,2,1))*7+
    VALUE(MID(fHCM2__IBAN__c,5,1))*7+
    VALUE(MID(fHCM2__IBAN__c,8,1))*7+
    VALUE(MID(fHCM2__IBAN__c,3,1))+
    VALUE(MID(fHCM2__IBAN__c,6,1))
    ))

Employment Record object

Require additional fields for contractors and temporary staff

A formula for a validation rule to require values for additional custom Employment Record fields for contractors and temporary staff. The formula checks if the employment basis matches what is specified (Temp/Contractor (Agency Contract) in the example), and then makes 3 custom fields required.

Copy
Code
TEXT (fHCM2__Basis__c) = "Temp/Contractor (Agency Contract)"
&&
(ISBLANK (UDF_Temp_Agency_Company_Name__c)
||
ISBLANK (UDF_Temp_Contractor_Base_Rate__c)
||
ISBLANK (UDF_Temp_Agency_Markup__c)
||
ISBLANK (TEXT(UDF_Temp_Contractor_Base_Rate_Type__c)))

Contract end date validation

An example of a validation rule with a formula to check the contract end date is not blank when the employment basis value is Fixed Term.

Field Description
Object

Employment Record

Description

Ensures that Contract End Date is required if Basis equals Fixed Term

Formula ISPICKVAL(fHCM2__Basis__c, "Fixed Term") && ISBLANK(fHCM2__Contract_End_Date__c)
Error Message

As the employee is on a fixed term contract please enter the Contract End Date

Error Location Field: Contract End Date

Validate sort code for bank account

An example of a validation rule with a formula to check a sort code entered for a bank account does not include spaces of hyphens.

Field Description
Object

Employment Record

Description

Ensures Bank Sort Code does not spaces or hyphens

Formula CONTAINS(fHCM2__Sort_Code__c, " ") || CONTAINS(fHCM2__Sort_Code__c, "-")
Error Message

Please remove any spaces or "-" from the bank sort code

Error Location Field: Sort Code

Enhanced Objective object

Prevent creation of group objectives

A formula for a validation rule to prevent team members from creating group objectives. Using this formula in a validation rule prevents objectives from being created if the Group fields is not blank. The field has a value if the objective is a group objective and blank if not.

Copy

Check if the Group field has a value

NOT(ISBLANK( spperfmgmt__Group__c ))

HR Request object

Team member cannot be their own new manager

A formula for a validation rule to prevent the selection of a team member as their own new manager, based on the unique Salesforce ID of the team member record.

Copy
Check the team member is not their own manager
CASESAFEID(fHCM2__T_Manager__c)=
CASESAFEID(fHCM2__Employment__r.fHCM2__Team_Member__c) 

Performance Review object

Set the first day when performance reviews can be revealed

A formula for a validation rule to prevent managers from revealing performance reviews until on the date specified in the validation rule at the earliest. The date in the example is expressed in the yyyy,MM,dd format.

Copy
Set the earliest possible Revealed Date
AND(
NOT(ISBLANK(fHCM2__Revealed_Date__c)),
fHCM2__Revealed_Date__c < DATE(2024,10,16)

Team Member object

Home email is populated

An example of a validation rule with a formula to check the Home Email field has value when the employment status is Active Employee.

Field Description
Object

Team Member

Description

Checks home email is populated for active employees

Formula ISBLANK(fHCM2__Home_Email__c) && ISPICKVAL(fHCM2__Employment_Status__c, "Active Employee")
Error Message

Check private email is populated for active employees

Error Location Field: Home Email

Validate the format of National Insurance (NI) number

A formula for a validation rule to check the value of the custom NI or SS Number field against the National Insurance number format used in the United Kingdom.

Copy

Validate the format of UK NI number

NOT(REGEX( NI_or_SS_Number__c , "[A-Z]{2}[0-9]{6}[A-Z]{1}"))

Validate the social security number

A formula for a validation rule to check the value of the custom NI or SS Number field against the social security number (SSN) format used in the United States, the 999-99-9999 format.

Copy
Validate the format of the United States SSN
NOT(REGEX( NI_or_SS_Number__c , " [0-9]{3}-[0-9]{2}-[0-9]{4}"))    

Validate the AHV

A formula for a validation rule to check the format of the custom AHV field, against the format used for the Old-Age and Survivors Insurance Number (AHV) in Switzerland.

Copy

Validate the format of AHV

NOT(REGEX( AHV__c , ("756.[0-9]{4}.[0-9]{4}.[0-9]{2}")))

Validate the personal mobile phone number format

A formula for a validation rule to checke the Personal Mobile field is not blank, and then checks the number format. In the REGEX function, the formula checks that:

  • the first character is a "+"

  • the next character is a number from 1 to 9 (but not 0)

  • the remaining characters are numbers between 0 and 9

  • the number does not exceed 16 characters (+ followed by 15 numbers)

Copy

Validate the format of mobile phone number

NOT(ISBLANK( fHCM2__Personal_Mobile__c )) 
    && 
    NOT(REGEX( fHCM2__Personal_Mobile__c , "[+]{1}[1-9]{1}[0-9]*" )) 
    || 
    LEN( fHCM2__Personal_Mobile__c ) > 16

Validate how state is given in an address

A formula for a validation rule to restrict the length of the value given for the Home Address Region field to be 2 characters.

Copy
Restrict the number of characters for a state or region
LEN(fHCM2__Home_Address_Region__c ) > 2

Validate team member's age

A formula for a validation rule to check the team member's age is between a lower and an upper limit (16 and 90 in the example).

Copy
Age range validation
fHCM2__Age__c < 16 || fHCM2__Age__c > 90

Validate the length of an email address

A formula to check that the email address in the Team Member record has a maximum of 30 characters.

Copy

Formula for length validation rule

LEN( fHCM2__Email__c ) > 30

 

Timesheet and Time Report objects

Length cannot exceed 24 hours

A formula for a validation rule to check the duration of a timesheet entry does not exceed 24 hours.

Copy
Timesheet entry maximum duration
fHCM2__Duration__c >24 

Maximum duration for a work allocation timesheet entry

A formula for a validation rule to prevent timesheet entries with work allocations having a duration exceeding 8 hours. Set this validation rule in the Time Report object to have access to the Work Allocation field.

Copy
Maximum duration for a work allocation
fHCM2__Work_Allocation__c <>"" 
&& 
fHCM2__Duration__c >8 

No timesheet entries for work allocations on Saturdays or Sundays

A formula for a validation rule to prevent timesheet entries for work allocations where the Report Date field value is either 1 (Sunday) or 7 (Saturday). Set this validation rule in the Time Report object to have access to the Work Allocation field.

Copy
Work allocation entries at weekends
NOT(ISBLANK(fHCM2__Work_Allocation__c)) 
&&  
  ((WEEKDAY(fHCM2__Report_Date__c ) =1 
||  
  WEEKDAY(fHCM2__Report_Date__c ) =7)) 

Prevent timesheet entries on public holidays

A formula for a validation rule to check if the pay code value on a timesheet entry matches the one specified, and prevents the timesheet from being saved if it does. This method requires setting up a pay code (called HOLWA in the example) and an overtime condition to operate on the public holidays when you want to apply the restriction. Set this validation rule in the Time Report object to have access to the Code field.

Copy
Check pay code in a timesheet entry
fHCM2__Code__c ='HOLWA'

Permit timesheet submission only for current week

A formula for a validation rule to ensure the timesheet is submitted within the week it is for. This method requires you to create a custom formula field, Timesheet_within_this_week__c (see Formula Library), and then setting up the validation rule against that field.

Copy

Timesheet submission for the current week

Timesheet_within_this_week__c=false