PART 2======>>> Formulas for::
Contact Management:
Contact Age (years old)
Calculates current age of contact in years. Subtracts birth date from today’s date, resulting in a number in decimal days. Divides this number by 365 to get years. Uses floor function to drop the decimal portion
|
|
Formula------>FLOOR((TODAY() - {!Birth date})/365).
Birthday This Month Indicator
Displays the value "Yes" if the contact’s birthday falls in the current calendar month
|
|
|
Formula----> IF ( MONTH({!Birthdate}) = MONTH(TODAY()), "Yes", "")
Preferred Contact Phone Number
In a contact-related list, displays the contact’s preferred contact method -- work phone, home phone, or mobile phone–based on selected option in a Preferred Phone pick list.
|
|
Formula-----> IF( ISPICKVAL( {!Preferred_Phone__c} , "Work"), "w. " & {!Phone} ), IF( ISPICKVAL( {!Preferred_Phone__c} , "Home"), "h. " & {!HomePhone} ), IF( ISPICKVAL( {!Preferred_Phone__c} , "Mobile"), "m. " & {!MobilePhone} ), "No Preferred Phone")))
Contract Aging
Calculates the number of days since the contract was activated. If the contract status is not "Activated," this field is blank.
|
|
Formula-----> IF(ISPICKVAL( {!Status} , "Activated"), NOW()-{!ActivatedDate}, null)
Contact Identification Numbering
Displays the first 5 characters of the contact’s last name and the last 4 characters of the contact’s social security number separated by a dash. Note that this example uses a text custom field called SSN on contacts.
|
|
Formula------>TRIM(LEFT({!LastName}, 5))&"-"&TRIM(RIGHT({!SSN__c}, 4))
Expiration Month
Returns the month that your service-level agreement expires. This example uses a custom date field called SLA Expiration Date.
|
|
Formula------> MONTH({!SLAExpirationDate__c} )
Telephone Country Code
Determines the telephone country code based on the country of the mailing address. Could be modified to use Billing country or other country field.
|
|
Formula-------> CASE( {!MailingCountry} , "USA", "1", "Canada", "1", "France", "33", "UK", "44", "Australia", "61", "Japan", "81", "?")
Dynamic Address Formatting
Displays City, State, Zip, or Postal Code City in standard
format -- depending on country.
|
|
Formula-------> CASE( {!ShippingCountry} , "USA", {!ShippingCity} & ", " & {!ShippingState} & " " & {!ShippingPostalCode} , "France", {!ShippingPostalCode} & " " & {!ShippingCity} , "etc")
Contact Autodial
Creates a dynamic hyperlink that passes the contact record ID and phone number to the Sforce TAP toolkit for phone dialed integration.
|
|
Formula-------> HYPERLINK("http://localhost:8541/call?id=" & {!Id} & "&phone=" & {!Phone} & "&sid=" & GETSESSIONID(), {!Phone} )
Unformatted Phone Number
Removes the parentheses and dash format characters from North American phone numbers. This is necessary for some auto dialed software.
|
|
Formula------> IF({!Country_Code__c} = "1" , MID( {!Phone} ,2, 3) & MID({!Phone},7,3) & MID({!Phone},11,4), {!Phone} )