PART3======>>>Formulas for:::
• Data Categorization
• Data Validation
Data Categorization:
Aging of Contract Approval Process
Calculates how many days a contract is in the approval process. This example is a number formula field on contracts that uses a custom date field called Date in Approval.
|
|
Formula-----> TODAY()-{!Date_in_approval__c}
Abbreviation Codes
Displays the text "Medical" for any Product Code that begins with "ICU." For all other products, displays "Technical."
|
|
Formula---->IF(BEGINS({!Product Code} , "ICU"), "Medical", "Technical")
Revenue Categorization
Assigns the value "1" to accounts with revenues of less than one million dollars and the value "2" to accounts with revenues greater than one million.
|
|
Formula---->IF({!Annual Revenue} < 1000000, 1, 2)
Deal Size Categorization (small deal examples)
Displays "Small" if the price and quantity are less than one. This field is blank if the asset has a price or quantity greater than one.
|
|
Formula----->IF(AND({!Price}<1,{!Quantity}<1),"Small", null)
Product Categorization
Displays "Parts" for any product with the word "part" in it. Otherwise, displays "Service."
|
|
Formula----->IF(CONTAINS({!ProductCode} , "part"), "Parts", "Service")
Deal Size Categorization (large deals)
Displays "Large" for deals over one million dollars.
|
|
Formula----->IF({!Price__c} > 1000000, "Large Deal", "Small Deal")
DATA VALIDATION
Case Data Completeness Tracking
Calculates the percent of your important case fields that are being filled by your support personnel. This formula field checks 2 fields to see if they are blank. If so, a zero is counted for that field. A "1" is counted for any field that contains a value and this total is divided by 2 (the number of fields evaluated). The fields Problem_Num and Severity_Num must be numeric.
|
|
Formula------>(IF(ISNULL({!Problem_Num__c}), 0, 1) + IF(ISNULL({!Severity_Num__c} ), 0,1)) / 2Invalid
Invalid Renewal Expiration Date
Checks if Renewal Expiration Date is after Close Date (custom field). If true, it displays an error message; otherwise it is blank. This formula can be used in a custom report that lists error records, or it can be used in a workflow rule to assign a task to the owner to correct the error.
|
|
Formula----->IF ({!Expiration_Date__c} > {!CloseDate}, "Error: renewal expiration date cannot be after Close Date", "")
Invalid Discount
Checks if discount percent field (custom field) is between 0 and 50%. If not, it displays an error message; otherwise it is blank. This formula can be used in a custom report that lists error records, or it can be used in a workflow rule to assign a task to the owner to correct the error.
|
|
Formula------>IF ({!Discount_Percent__c} > 0.50, "Error: Discount cannot exceed 50%",IF ({!Discount_Percent__c} < 0, "Error: Discount cannot be less than 0", ""))
Gratitude Checker
Checks if message field (custom field) contains the words "Thanks" or "Thank you". If not, it displays an error message; otherwise it is blank. This formula can be used in a custom report that lists error records, or it can be used in a workflow rule to assign a task to the owner to correct the rudeness.
|
|
Formula----->IF( OR( CONTAINS({!Message__c}, "Thanks"), CONTAINS({!Message__c}, "Thank you")), "", "Did not express gratitude in message.")
