Formulas | Validation rule library
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.
Absence must be a full day
A formula for a validation rule to permit team members to book absences as full days only.
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.
fHCM2__Employment__r.fHCM2__Team_Member__r.fHCM2__Department__r.Name ='China'
&& MOD(fHCM2__Days__c,0.5) <> 0Absences 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).
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.
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.
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.
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 > 3Application 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.
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.
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.
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.
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.
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.
AND(
NOT(ISBLANK(fHCM2__Revealed_Date__c)),
fHCM2__Revealed_Date__c < DATE(2024,10,16)Prevent manager review before self review is revealed
A formula for a validation rule to prevent managers from submitting their performance review about a team member before the team member has revealed the self review:
Prevent manager reviews before self review
NOT(ISBLANK(fHCM2__Revealed_Date__c ))
&&
ISBLANK( fHCM2__Self_Performance_Review__r.fHCM2__Revealed_Date__c )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.
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.
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.
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)
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 ) > 16Validate 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.
LEN(fHCM2__Home_Address_Region__c ) > 2Validate 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).
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.
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.
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.
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.
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.
The following screenshots detail the pay code and overtime condition setup. Make sure you create the overtime condition in the overtime rule linked from the timesheet pattern.
Pay code field values as shown in the screenshot:
| Field | Description |
|---|---|
| Pay Code | Holiday Work Allocation TS |
| Code | HOLWA |
| Rate | 0.00 |
| Description | Used to designate time entered on a public holiday on the Work Allocations timesheet pattern. |
| Hide on Timesheet | Not checked |
Overtime rule field values as shown in the screenshot:
| Field | What to enter |
|---|---|
| Overtime Rule | Defaults to the overtime rule from where you created the condition. In the screenshot: No OT Rules |
| Overtime Condition | The number of the overtime condition, automatically completed. |
| Timespan | Day |
| Apply To | Weekdays |
| Part Of | Not entered |
| Include Public Holidays | Selected |
| Holiday Name |
The list of public holidays in scope for the condition. In the screenshot: New Year's Day,Martin Luther King Jr. Day,Presidents' Day,Patriots' Day,Memorial Day,Juneteenth Day,Independence Day,Day after Independence Day,Labor Day,Indigenous Peoples' Day,Thanksgiving Day,Day after Thanksgiving,Christmas Day |
| Absence Reason | Not entered |
| Start Time | Not entered |
| Hours Greater Than | 0.00 |
| Pay Code | Holiday Work Allowance TS |
| Rollup Gropu | Not entered |
| Order | Not entered |
| Ignore Following Rules If Matched | Not selected |
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.