• EricB
  • NEWBIE
  • 168 Points
  • Member since 2004

  • Chatter
    Feed
  • 3
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 6
    Questions
  • 58
    Replies
Hi there,

I already have the validation rule created for Phone Validation field - 10 digits with 4 digits extension? This works great. But would also like make sure, that it also checks for International Format.

AND(LEN(Phone) <> 0,
NOT (
AND(

LEFT(Phone,1)="(",
MID(Phone,5,1)=")",
MID(Phone,6,1)=" ",
MID(Phone,10,1)="-",


CONTAINS("0123456789", MID (Phone, 2, 1)),
CONTAINS("0123456789", MID (Phone, 3, 1)),
CONTAINS("0123456789", MID (Phone, 4, 1)),
CONTAINS("0123456789", MID (Phone, 7, 1)),
CONTAINS("0123456789", MID (Phone, 8, 1)),
CONTAINS("0123456789", MID (Phone, 9, 1)),
CONTAINS("0123456789", MID (Phone, 11, 1)),
CONTAINS("0123456789", MID (Phone, 12, 1)),
CONTAINS("0123456789", MID (Phone, 13, 1)),
CONTAINS("0123456789", MID (Phone, 14, 1)),

OR(AND(LEN(Phone)<=20,MID(Phone, 15,2)=" x",

CONTAINS("0123456789", MID (Phone, 17, 1)),
CONTAINS("0123456789", MID (Phone, 18, 1)),
CONTAINS("0123456789", MID (Phone, 19, 1)),
CONTAINS("0123456789", MID (Phone, 20, 1))
), LEN(Phone)<=14
)
)
)
)


What needs to be added ?

I got this from the doc:-

LEFT(Phone, 1) <> "+"

Also, if I add the above logic would it not conflict with the 10 digit rule?




Where can I find the documentation on building the "regex_text" part of the REGEX function? All I can find in Help/Training/Release Notes is  a few examples of a working formula. There is nothing that explains the different characters and codes that can be used in the "regex_text" part of this function.
 
Syntax:
REGEX(text, regex_text)
 
Instructions:
Replace text with the text field, and regex_text with the regular expression you want to match
 
Examples:
REGEX(Drivers_License__c, "([A-Z]\\d{7})?")
REGEX(BillingPostalCode, "\\d{5}(-\\d{4})?")
REGEX(Credit_Card_Number__c, "(((\\d{4}-){3}\\d{4})|\\d{16})?")
REGEX(IP_Address__c, "^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$" )
REGEX(Social_Security_Number__c, "[0-9]{3}-[0-9]{2}-[0-9]{4}")
I have a text formula to which I would like to add a carriage return/line break.  I tried concatenating chr(13) and chr(10), but the validation didn't like it.  Is this possible?
In case you haven't heard, we've added some cool new functions in the Spring '08 release to help you write more powerful validation rules.
  • VLOOKUP Function: Similar to its Excel counterpart, the VLOOKUP function lets you use data in another object as a basis for validation.  For example, to validate the zip code in a contact address, you could use the VLOOKUP function in a contact validation rule to search for a match in a custom object containing a list of valid zip codes.
  • ISNUMBER Function: The ISNUMBER function provides a simple way to tell if data entered in a text field only contains numbers, which is required for many types of identification numbers like credit cards and account numbers. 
  • REGEX Function: Regular expressions are a powerful tool used by programmers to find patterns within bodies of text.  The REGEX function gives you access to regular expressions in your validation rules.  This may be useful to validate complex number formats, like IP addresses, country-specific postal codes or phone numbers, tax IDs, and so on.
Check out the Advanced Validation Rules page on successforce for more information.
 
For your reference, here are links to the function documentation in the online help.
VLOOKUP function
ISNUMBER function
REGEX function

Cheers,
Eric Bezar
Force.com product management

  • February 13, 2008
  • Like
  • 0

In Winter '07, $Profile merge fields required “View Setup Configuration” permission to work correctly.  This meant that users who did not have View Setup Configuration permission would get run-time errors when their actions caused formula fields, validation rules, default value formulas, field updates, custom buttons, or sControls containing $Profile merge fields to execute. 

This has been fixed in the Summer ’07 release.  $Profile merge fields no longer require “View Setup Configuration” permission to work correctly.

Regards,
Eric

Eric Bezar
Platform Product Management

  • August 02, 2007
  • Like
  • 0

Prior to the Summer '07 release, the $Profile.Name merge field uses special internal values (e.g., PT1, PT2, etc.) to represent standard profiles (System Administrator, Standard User, etc.).  These special values will be replaced with the externally visible names in the Summer ’07 release.  This means that current uses of the $Profile.Name field must be changed in order to continue functioning after the Summer ’07 release. Note that the new $Profile.Name values are translated in the org's default language at runtime.

If you are only using custom profile names, there is no impact.

Standard Profile Name

Old (Spring ’07)
$Profile.Name Value

New (Summer ’07)
$Profile.Name Value

System Administrator

PT1

System Administrator

Standard User

PT2

Standard User

Read Only

PT3

Read Only

Solution Manager

PT4

Solution Manager

Marketing User

PT5

Marketing User

Contract Manager

PT6

Contract Manager

Partner User

PT7

Partner User

Standard Platform User

PT8

Standard Platform User

Standard Platform One User

PT9

Standard Platform One User

Salesforce Administrator

PT10

Salesforce Administrator

Package License Manager

PT11

Package License Manager

Standard Partner

PT12

Standard Partner

Customer Portal User

PT13

Customer Portal User

Customer Portal Manager

PT14

Customer Portal Manager

 

To ensure your formulas continue to work after the Summer ’07 release, you should modify them to handle both the old and new values of $Profile.Name.

 

For example, this validation rule formula expression:

    $Profile.Name <> "PT1"

 

Should be replaced with this expression to be forward compatible:

AND(
    $Profile.Name <> "PT1",
    $Profile.Name <> "System Administrator"
)

 

Note: After Summer ’07 goes live, there is no need to retain the old internal names (PT1, etc.).

  • August 02, 2007
  • Like
  • 1
Good news regarding the BR() function: We have changed the behavior so that it inserts a new line character rather than the dreaded _BR_ENCODED_.

You should now be able to compose formulas in Workflow Field Update & Default Value formulas like

Code:
MailingStreet & BR() &
MailingCity & ", " & MailingState & " " & MailingPostalCode & BR() &
MailingCountry

This fix is now live, so give it a try.

Regards,
Eric
  • July 31, 2007
  • Like
  • 0
Hi Formula Authors,
If you like formulas, I wanted to make a quick plug for validation rules, a cool new feature in Winter '07 that uses formulas to define data validation conditions that can prevent invalid data from getting into salesforce.

If you are already familiar with formulas, you will be able to create validation rules in a snap.

For more info, take a look at these resources:

Feature detail page:
http://blogs.salesforce.com/features/2006/12/data_validation.html

Getting Started breeze:
http://salesforce.acrobat.com/customvalidationformulas/

Example Validation Rules:
http://na1.salesforce.com/help/doc/en/salesforce_useful_validation_formulas.pdf



  • January 10, 2007
  • Like
  • 0
Hi Formula Authors,
Now that the Winter ’07 release is here, I wanted to alert you to some key formula enhancements in the release.  

Language changes
  • Max formula size increases from 1000 to 1300 characters.  (No change in the max compiled size limit of 4K)
  • No more curly braces on merge fields in formulas:  {!AccountName}  --> AccountName
  • Access to “global variables” in formulas: $Organization, $User (current user), $Profile (current user's profile), $UserRole (current user's role)
  • Access to Record Type field ($RecordType.Name)
New functions
  • DATEVALUE(datetime), DATEVALUE(text) – Convert a datetime field or a text field to a date field
  • FIND (start_text, text [, start_num]) - return the position of the string search_text within the string text
  • SUBSTITUTE (text, old_text, new_text) - find instances of the string old_text in the string text and replace with new_text
  • BR() – insert a line break in a text formula
  • MIN( num1, num2, num3,…) , MAX( num1, num2, num3…) – return the min or max value from the input parameters
  • New functions for validation rules: ISCHANGED (field), PRIORVALUE (field), ISNEW()
  • Advanced functions for S-Control scripting: LINKTO, URLFOR, INCLUDE, GETRECORDIDS
Other enhancements
  • HYPERLINK formulas can now use any protocol handler (e.g., "javascript&colon;" instead of "http:")
  • IMAGE formulas now work in email and mail merge (Microsoft Word) templates
  • TEXT function now accepts datetime values as well as numeric values
Formula Editor changes
  • Field Type picklist allows selection of object (current object, $profile, $user, etc.)
  • Insert Operator is now a menu button instead of separate buttons for each operator
New uses for formula expressions
We are now leveraging our formula language in a number of new and powerful ways. 
  • Workflow field update actions (Enterprise & Unlimited Edition) - set and store field values in currency, number, percent, text, date, and date/time fields based on a formula expression
  • Validation rules - Boolean formula expressions that can prevent incorrect data from being saved
  • Default field values - initial values to be populated in fields when new records are created
  • Server-side formula expressions for S-Controls
For more details, please check out the Winter '07 Release Notes, available from the Help and Training link in the application banner.

Regards,
Eric Bezar
Platform Product Management



  • January 08, 2007
  • Like
  • 0

Prior to the Summer '07 release, the $Profile.Name merge field uses special internal values (e.g., PT1, PT2, etc.) to represent standard profiles (System Administrator, Standard User, etc.).  These special values will be replaced with the externally visible names in the Summer ’07 release.  This means that current uses of the $Profile.Name field must be changed in order to continue functioning after the Summer ’07 release. Note that the new $Profile.Name values are translated in the org's default language at runtime.

If you are only using custom profile names, there is no impact.

Standard Profile Name

Old (Spring ’07)
$Profile.Name Value

New (Summer ’07)
$Profile.Name Value

System Administrator

PT1

System Administrator

Standard User

PT2

Standard User

Read Only

PT3

Read Only

Solution Manager

PT4

Solution Manager

Marketing User

PT5

Marketing User

Contract Manager

PT6

Contract Manager

Partner User

PT7

Partner User

Standard Platform User

PT8

Standard Platform User

Standard Platform One User

PT9

Standard Platform One User

Salesforce Administrator

PT10

Salesforce Administrator

Package License Manager

PT11

Package License Manager

Standard Partner

PT12

Standard Partner

Customer Portal User

PT13

Customer Portal User

Customer Portal Manager

PT14

Customer Portal Manager

 

To ensure your formulas continue to work after the Summer ’07 release, you should modify them to handle both the old and new values of $Profile.Name.

 

For example, this validation rule formula expression:

    $Profile.Name <> "PT1"

 

Should be replaced with this expression to be forward compatible:

AND(
    $Profile.Name <> "PT1",
    $Profile.Name <> "System Administrator"
)

 

Note: After Summer ’07 goes live, there is no need to retain the old internal names (PT1, etc.).

  • August 02, 2007
  • Like
  • 1
Hi,
 
According to help if i want to make my account name unique i need to make sure my syntax is "name" in validation rules. But it doesnt seems to work as validation rules will only return true/false. Did I make any mistake? Please help me.
 
Thanks
 
 
 
 
  • April 11, 2008
  • Like
  • 0
Hi

I get the following error message when trying to download this app.  Is there some advice you could give me in order to help?

Thank you

Andrea

--------------------------

Your requested download failed. Please try this again.

 

None of the data or setup information in your Salesforce organization should have been affected by this error.

 

If this error persists, contact Support through your normal channels.

Your requested download failed. Please fix the following error(s) or obtain a fix and try this again.

 

If this error persists, contact Support through your normal channels.

 

 

 

Problem:

 

1. Apex Class(01p70000000CimN) tom_test_pay_cycle_boundaries.test_pay_cycle_management()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_pay_cycle_boundaries.test_pay_cycle_management: line 19, column 40

 

2. Apex Class(01p70000000CimW) tom_test_overlap.test_overlap()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_overlap.test_overlap: line 8, column 40

 

3. Apex Class(01p70000000CimX) tom_test_pay_cycle_no_processing.tom_test_pay_cycle_no_processing()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_pay_cycle_no_processing.tom_test_pay_cycle_no_processing: line 13, column 40

 

4. Apex Class(01p70000000CimV) tom_test_payroll_systems.test_payroll_systems()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_payroll_systems.test_payroll_systems: line 15, column 40

 

5. Apex Class(01p70000000CimP) tom_test_pay_cycle_mgmt.test_pay_cycle_management()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_pay_cycle_mgmt.test_pay_cycle_management: line 13, column 40

 

6. Apex Class(01p70000000CimS) tom_test_pto_bal_bulk_del.test_pto_balance_validation_with_bulk_delete()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_pto_bal_bulk_del.test_pto_balance_validation_with_bulk_delete: line 8, column 40

 

7. Apex Class(01p70000000Cima) tom_test_bulk_tors.test_bulk_ops_on_time_off_requests()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_bulk_tors.test_bulk_ops_on_time_off_requests: line 8, column 40

 

8. Apex Class(01p70000000CimJ) tom_test_bulk_time_off_info.test_bulk_time_off_info()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_bulk_time_off_info.test_bulk_time_off_info: line 10, column 35

 

9. Apex Class(01p70000000CimL) tom_test_user_no_manager.test_time_off_request_for_user_without_manager()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_user_no_manager.test_time_off_request_for_user_without_manager: line 9, column 40

 

10. Apex Class(01p70000000Cimb) tom_test_error_conditions.test_package_error_conditions()

System.QueryException: List has no rows for assignment to SObject

Class.tom_test_error_conditions.test_package_error_conditions: line 10, column 31

 

11. Apex Class(01p70000000CimT) tom_test_pto_bal_validation.test_pto_balance_validation()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_pto_bal_validation.test_pto_balance_validation: line 8, column 40

 

12. Apex Class(01p70000000CimZ) tom_test_status_changes.test_time_off_request_status_changes()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_status_changes.test_time_off_request_status_changes: line 9, column 40

 

13. Apex Class(01p70000000CimR) tom_test_record_type_errors.test_record_type_errors()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_record_type_errors.test_record_type_errors: line 8, column 40

 

14. Apex Class(01p70000000CimK) tom_test_cancellation.test_cancellation()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_cancellation.test_cancellation: line 15, column 40

 

15. Apex Class(01p70000000CimO) tom_test_requested_day.test_requested_day_trigger()

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, time_off_info_trig: execution of BeforeInsert

 

caused by: ptoPackage.Time_Off_Exception: Exactly one Queue named Time_Off_Manager_Payroll must exist, and instead 0 were found.

 

Class.ptoPackage.get_payroll_queue: line 107, column 23

Trigger.time_off_info_trig: line 6, column 31

Class.pto_tests_common.get_test_user_ids: line 124, column 9

Class.tom_test_requested_day.test_requested_day_trigger: line 8, column 40

 

 

 

Thank You,

salesforce.com

experience success

http://www.salesforce.com/

  • March 07, 2008
  • Like
  • 0
Hi there,

I already have the validation rule created for Phone Validation field - 10 digits with 4 digits extension? This works great. But would also like make sure, that it also checks for International Format.

AND(LEN(Phone) <> 0,
NOT (
AND(

LEFT(Phone,1)="(",
MID(Phone,5,1)=")",
MID(Phone,6,1)=" ",
MID(Phone,10,1)="-",


CONTAINS("0123456789", MID (Phone, 2, 1)),
CONTAINS("0123456789", MID (Phone, 3, 1)),
CONTAINS("0123456789", MID (Phone, 4, 1)),
CONTAINS("0123456789", MID (Phone, 7, 1)),
CONTAINS("0123456789", MID (Phone, 8, 1)),
CONTAINS("0123456789", MID (Phone, 9, 1)),
CONTAINS("0123456789", MID (Phone, 11, 1)),
CONTAINS("0123456789", MID (Phone, 12, 1)),
CONTAINS("0123456789", MID (Phone, 13, 1)),
CONTAINS("0123456789", MID (Phone, 14, 1)),

OR(AND(LEN(Phone)<=20,MID(Phone, 15,2)=" x",

CONTAINS("0123456789", MID (Phone, 17, 1)),
CONTAINS("0123456789", MID (Phone, 18, 1)),
CONTAINS("0123456789", MID (Phone, 19, 1)),
CONTAINS("0123456789", MID (Phone, 20, 1))
), LEN(Phone)<=14
)
)
)
)


What needs to be added ?

I got this from the doc:-

LEFT(Phone, 1) <> "+"

Also, if I add the above logic would it not conflict with the 10 digit rule?




Where can I find the documentation on building the "regex_text" part of the REGEX function? All I can find in Help/Training/Release Notes is  a few examples of a working formula. There is nothing that explains the different characters and codes that can be used in the "regex_text" part of this function.
 
Syntax:
REGEX(text, regex_text)
 
Instructions:
Replace text with the text field, and regex_text with the regular expression you want to match
 
Examples:
REGEX(Drivers_License__c, "([A-Z]\\d{7})?")
REGEX(BillingPostalCode, "\\d{5}(-\\d{4})?")
REGEX(Credit_Card_Number__c, "(((\\d{4}-){3}\\d{4})|\\d{16})?")
REGEX(IP_Address__c, "^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$" )
REGEX(Social_Security_Number__c, "[0-9]{3}-[0-9]{2}-[0-9]{4}")
ppl...I have this task of modifying a lot of reports with the same custom summary formula. Finding no better way I am going thru each report making the changes. Is there a better and efficient way for this??
  • February 18, 2008
  • Like
  • 0
In case you haven't heard, we've added some cool new functions in the Spring '08 release to help you write more powerful validation rules.
  • VLOOKUP Function: Similar to its Excel counterpart, the VLOOKUP function lets you use data in another object as a basis for validation.  For example, to validate the zip code in a contact address, you could use the VLOOKUP function in a contact validation rule to search for a match in a custom object containing a list of valid zip codes.
  • ISNUMBER Function: The ISNUMBER function provides a simple way to tell if data entered in a text field only contains numbers, which is required for many types of identification numbers like credit cards and account numbers. 
  • REGEX Function: Regular expressions are a powerful tool used by programmers to find patterns within bodies of text.  The REGEX function gives you access to regular expressions in your validation rules.  This may be useful to validate complex number formats, like IP addresses, country-specific postal codes or phone numbers, tax IDs, and so on.
Check out the Advanced Validation Rules page on successforce for more information.
 
For your reference, here are links to the function documentation in the online help.
VLOOKUP function
ISNUMBER function
REGEX function

Cheers,
Eric Bezar
Force.com product management

  • February 13, 2008
  • Like
  • 0
I have a custom formula field called forecast.  My formula for it is:    IF(ProbabilityFW__c > 75, ProbabilityFW__c * Opportunity_Amount__c, Opportunity_Amount__c)
What im trying to do is to say that IF my probability field (ProbabilityFW_c) is greater then 75% then the forecast field should show that probability times the Amount (Opportunity_Amount_c).  IF the Probability is less then 75% i want the forecast field to equal the Amount (Opportunity_Amount_c).
The problem is that no matter what my (ProbabilityFW_c) equalls above 75% or below i still only get the Amount value and not Amount times Probability for above 75%.
I can not figure out what I am doing wrong.
  • February 12, 2008
  • Like
  • 0
Hello,
 
I am trying to set up an Account field validation rule that requires all accounts created after 06/01/2007  have an address in order to save the record.
 
The logic I am using is as follows:
 
And (
     CreatedDate > DATEVALUE("2007-06-01"),
OR(
  LEN(  BillingStreet  ) = 0,
  LEN(  BillingCity ) = 0,
  LEN(  BillingState  ) = 0
)
)
 
The problem is whenever I reference the "CreatedDate" field I get the following error:  Error: Incorrect parameter for function >(). Expected DateTime, received Date.  If I change this to another date based field on the account object there is no error. 
 
I appears I am doing something wrong as it pertains to the "CreatedDate" field.  Anyone have any insight?
 
Todd B.
Hello All

I am trying to create a validation rule that says:
If Date_c Today or is null and Service_c is true error is triggered. This is the formula but it doesn't work if the date field is null.

AND( Date__c TODAY(), Service__c, true)

Any help would be appreciated.
I am confused about the new Roll-up fields.  I would like to have an account field that gives me the total number of cases related to the account, can the new Roll-up fields do this?  If not how can I easily get this number on the account page?
Can anyone help me calculate a contact's age by using the Date of Birth field?
 
Thanks in advance,
 
David
Hi,
I'm trying to sum up certain fields containing dates, but I always get the syntax following syntax error: Error: Incorrect parameter for function +(). Expected Number, received Date
 
I know it must be something really simple, but I just can't seem to get it done!!
 
Any help would be greatly appreciated.
Hello -
 
I have a client who wants to add a stoplight image next to a custom "STATUS" field. I found this formula in the help section of the Salesforce site: IMAGE(image_url, alternate_text, height, width)
 
However, this formula directs you to find an image URL. Is it possible to insert an image from a saved file such as a gif or jpeg? Does the image have to be linked to a URL to insert?
 
Any input is appreciated.
 
Thanks!
Hi All,

I was looking at the screencast for PTODemo (Time Off Manager App). I am very impressed with it. I am wondering if it is available for public use. One thing I am still wondering how to do, I noticed that after the demonstrator clicked on "Submit for Approval", the button (along with a few others) will no longer showing when next time the document opens. How to do that? Anyone?


  • April 04, 2007
  • Like
  • 0
Hi Formula Authors,
Now that the Winter ’07 release is here, I wanted to alert you to some key formula enhancements in the release.  

Language changes
  • Max formula size increases from 1000 to 1300 characters.  (No change in the max compiled size limit of 4K)
  • No more curly braces on merge fields in formulas:  {!AccountName}  --> AccountName
  • Access to “global variables” in formulas: $Organization, $User (current user), $Profile (current user's profile), $UserRole (current user's role)
  • Access to Record Type field ($RecordType.Name)
New functions
  • DATEVALUE(datetime), DATEVALUE(text) – Convert a datetime field or a text field to a date field
  • FIND (start_text, text [, start_num]) - return the position of the string search_text within the string text
  • SUBSTITUTE (text, old_text, new_text) - find instances of the string old_text in the string text and replace with new_text
  • BR() – insert a line break in a text formula
  • MIN( num1, num2, num3,…) , MAX( num1, num2, num3…) – return the min or max value from the input parameters
  • New functions for validation rules: ISCHANGED (field), PRIORVALUE (field), ISNEW()
  • Advanced functions for S-Control scripting: LINKTO, URLFOR, INCLUDE, GETRECORDIDS
Other enhancements
  • HYPERLINK formulas can now use any protocol handler (e.g., "javascript&colon;" instead of "http:")
  • IMAGE formulas now work in email and mail merge (Microsoft Word) templates
  • TEXT function now accepts datetime values as well as numeric values
Formula Editor changes
  • Field Type picklist allows selection of object (current object, $profile, $user, etc.)
  • Insert Operator is now a menu button instead of separate buttons for each operator
New uses for formula expressions
We are now leveraging our formula language in a number of new and powerful ways. 
  • Workflow field update actions (Enterprise & Unlimited Edition) - set and store field values in currency, number, percent, text, date, and date/time fields based on a formula expression
  • Validation rules - Boolean formula expressions that can prevent incorrect data from being saved
  • Default field values - initial values to be populated in fields when new records are created
  • Server-side formula expressions for S-Controls
For more details, please check out the Winter '07 Release Notes, available from the Help and Training link in the application banner.

Regards,
Eric Bezar
Platform Product Management



  • January 08, 2007
  • Like
  • 0
Hi All,
 
Is it possible to reference a multi-select picklist in a formula?
 
Thanks.
 
Aiden