Below is a list of all pre-built formulas currently available in the Modify module. Those that are most commonly used are denoted.
Formula | Definition | Syntax Example |
---|---|---|
AllTextAfterChar | Allows the return of text only if it is after a specific character in the string |
Formula:
|
AllTextBeforeChar | Allows the return of text only if it is before a specific character in the string |
Formula:
|
ConvertDate | Allows a date string (yyyy-mm-dd) to be converted to date/time format for insertion into Salesforce |
Formula:
|
CHAR | Allows the ability to insert a carriage return/line feed character (\r\n) when concatenating fields |
Formula: Example: {billingstreet} + CHAR() + {billingcity} + ", " + {billingstate} + " " + {billingpostalcode}
Hingham, MA 02043 |
CountryMatchReturn3CharacterName | Converts country to ISO 3 character name |
Formula:
|
CountryMatchReturnISONumber | Converts country to ISO 3 digit number | Formula: CountryMatchReturnISONumber(CountryValue, UseFuzzy) Example: CountryMatchReturnISONumber( ${billingcountry}, true) |
CountryMatchReturnLongName | Converts country to long form |
Formula:
|
Date | Populate a field with a specified year, month, and day Input: 2021,05,01 Result: 05/01/2021 |
Formula: Date(year, month, day) Example: Date(2021,05,01) |
DateAddValue | Allows for the increase or decrease of a date/time by a set interval |
Formula: DateAddValue("IntervalString", ValueToAdd, DateToEffect)
|
FirstWord | Returns the first word from a text string |
Formula:
|
Int | Rounds a number DOWN to the nearest integer |
Formula:
|
LastWord | Returns the last word from a text string |
Formula:
|
Lower | Takes a field and lower cases letters |
Formula:
|
Left | Returns the specified number of characters starting from the left in a text string |
Formula:
|
len | Returns the number of characters in a text string |
Formula:
|
Mid_1 | Returns all characters from the middle of a text sting after a given starting position |
Formula:
|
Mid_2 | Returns the characters from the middle of a text string, given a starting position and length (number of characters to return) |
Formula:
|
NaPhoneFix | Takes a phone number and formats it to match the format as entered in Salesforce. Will also standardize an abbreviation for extension to x |
Formula: NaPhoneFix(phone)
|
Normalized_US_Address | Attempts to standardize a street address to match the USPS preferred format |
Formula:
Note: We highly recommend that address normalization formulas not be scheduled as the results will vary based on the validity of the supplied address. For example, street addresses that contain building names (i.e. 123 Main St Suite 100 One Prudential Tower) may return unwanted results. **All results should be manually reviewed and unwanted results unchecked before processing.** |
Normalized_US_Address2LineSuiteFirst | Attempts to standardize the address returning the "address line 2" data separate (above) from the street address |
Formula: Input: One North Main Street, Suite 100
Note: We highly recommend that address normalization formulas not be scheduled as the results will vary based on the validity of the supplied address. |
Normalized_US_Address2LineSuiteLast | Attempts to standardize the address returning the "address line 2" data separate (below) from the street address |
Formula: Input: One North Main Street, Suite 100 |
Now | Updates a date and/or date/time field to the current day and/or current day and time |
Formula: |
ParseNameReturnFirstName | Parses the first name into the specified field to be updated |
Formula:
|
ParseNameReturnLastName | Parses the last name into the specified field to be updated | Formula: ParseNameReturnLastName(FullName, IncludeSuffix)
ParseNameReturnLastName( ${name} , true) Example:
|
ParseNameReturnSuffix | Parses the last name suffix (e.g. Sr., II, DDS) into the specified field to be updated |
Formula:
|
ParseNameReturnTitle | Parses the title (e.g. Mr. Mrs. etc) into the specified field to be updated Designed to break a field containing a full name into Salutation, First, Last, and custom suffix name fields Includes logic to identify titles, suffixes, first initials, hyphenated last names, 2 word last names etc. |
Formula: ParseNameReturnTitle(FullName) |
Proper | Takes a field and upper cases just the first letter and lower cases all remaining letters in the string. |
Formula:
Example: PROPER({description})
|
ProperCaseName | Proper Case a "Name" field, typically a Company/Account Name, or First and Last Name. Instead of just upper casing the first letter of each word, will apply logic to handle common prefixes (e.g. Mc and Mac), abbreviations, and allows for user overrides to always punctuate a particular string in a preferred manner |
Formula:
|
ProperCaseNameForceAll | Allows proper casing all data regardless of existing case |
Formula:
|
RAND | Assigns a random decimal number between 0 & 1 |
Formula:
Example: Populate the random_c field with a number between 0 & 1 RAND(0,1)
|
RANDBETWEEN | Assigns a random integer between specified values |
Formula:
Example: Populate the random_c field with a random number from 0 to 100 RANDBETWEEN(0,100)
|
RegExMatch | Allows users to build their own custom regular expressions to manipulate data in a specific way. Knowledge of regular expressions is required to use this formula and customers are responsible for building their own expressions. There are several free regex tutorial websites online that users may want to reference to learn more. |
Formula: RegExMatch(InputString, RegularExpression) |
RegExReplace | Search a field, use a regular expression to find data, replace with desired string Knowledge of regular expressions is required to use this formula and customers are responsible for building their own expressions. |
Formula: Input: 55 Best Place |
RemoveLastWord | Removes the last word from a specified text string | Formula:
RemoveLastWord(StringValue) Example: RemoveLastWord({lastname})
|
Right | Returns the specified number of characters starting from the right in a text string |
Formula:
Right(StringValue, NumberOfChars)
Example: Right( {accountnumber} , 5)
|
Round | Rounds a number to the nearest number |
Formula: Round(NumberValue, NumberDigits)
Example: Round( {random__c},2) *round the random_c field to the nearest integer
|
StateMatchLongName |
Updates all US states & Canadian provinces to the long form |
Formula: StateMatchLongName(StateValue, UseFuzzy) Example: StateMatchLongName({billingstate}, true)
|
StateMatchShortName | Updates all US states & Canadian provinces to the ISO two character abbreviation. |
Formula: StateMatchShortName(StateValue, UseFuzzy)
Example: StateMatchShortName({billingstate}, true)
|
StringReplace | Search a field, find a particular string, replace with desired string |
Formula: StringReplace(StringValue, "Find", "Replacement")
Example: Could be used to remove "Delete***" from an Account Name. StringReplace({name}, "Delete***", "")
|
StringReplaceCS | Same as StringReplace with case sensitivity |
Formula: StringReplaceCS(StringValue, "Find", "Replacement")
Example: StringReplaceCS({name}, "Dr. ", "")
|
Today | Update a date and/or date/time field to the current day |
Formula: today() Formula = today
Note: No additional parameters are required when using this formula |
TrimStartSpaces | Trim all leading spaces from a text field |
Formula: TrimStartSpaces(StringValue)
Example: TrimStartSpaces({firstname})
|
TrimEndSpaces | Trim all trailing spaces from a text field |
Formula: TrimEndSpaces(StringValue)
Example: TrimEndSpaces({firstname})
|
Trim | Trim all leading AND trailing spaces from a text field |
Formula:
Example: Trim({firstname})
|
Upper | Takes a field and upper cases letters | Formula:
UPPER(StringValue)
Example: UCase({billingstate})
|
ZipCodeClean |
Attempts to reformat US zip codes adding a leading zero if needed
|
Formula: ZipCodeClean(StringValue)
Example: ZipCodeClean({billingpostalcode})
Note: This is JUST for US zip codes and specifically those in states that typically have a leading zero. May cause unwanted results with non US zip codes. |