function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
guido59guido59 

Winter '07 Field Validation Questions

First off, congratulations to everyone at Salesforce.com responsible for the FANTASTIC Dreaforce '06 event you just hosted.  What I learned will be very useful to me and our organization in general.  THANK YOU!
 
My questions are related to the field validation functionality that Eric Bezar covered in one of his sessions.  One of the examples Eric gave concerned checking the Billing State to insure that it was a proper two digit state value.  There are several such examples I want to implement, and I am wondering:

1) Is it possible through standard validation rules (not APEX code) to create a reusable/callable formula?  For example, I would like to check the state/province value on the Lead address fields, Account address fields (billing and shipping), Contract address fields, etc. and would like to call (and maintain) one formula that validates state and province, not one per field that I need to validate.

2) Along the same lines - would it be possible to create "standard" formulas that could be posted and shared throughout the Salesforce community (i.e. "valid USState/CAProvince" formula)?

3) Is it possible, again through validation rules, to validate one field value against another field value - with the possibility of having the secondary field "off screen" (i.e. not visible).  One use case here is that I have created a "Country Picklist" field of all 190+ ISO 3166 countries.  I would like to validate that when someone enters a value in the standard 'Country' field in the address block that the value entered matches one of the values contained in the 'Country Picklist' field.

The above features would be huge productivity savings, and I would really appreciate your thoughts and feedback.

Message Edited by guido59 on 10-13-2006 08:54 AM

The_FoxThe_Fox
Hello guido,

Hey nice of you for your list of 190 Countries. I have built some snippet that allows me to replace in the edit page the County standard field on Leads/Account/Contacts and my working on it to be able to make it required. In fact I would like to have the community to give me a hand with A list of country with their respective State. So I would like to Have US+All the States, idem for Canada, Australia (Carl if you see me)...and all the countries taht have states /or province or whatever)

so If you can send me your list or make it available here

Regard
guido59guido59

Hey Fox,

Glad to hear that others would find what I am asking for useful.  The country list that I leverage is the ISO sanctioned list.  A good link to explore related information is http://www.iso.org/iso/en/prods-services/iso3166ma/index.html.  Since this is a "standards" sanctioned and maintained list it would be great if Salesforce directly, or the Salesforce community at large, could somehow leverage this body of data for validation purposes.

What sort of code snippet do you leverage?  Would it be possible to share that back with me, as well as a general feel for how it is invoked?

Thanks,

Guido
guido59guido59

One other thing Fox...

I have also created a custom object (Sales_Territory__c) that contains all Countries in the world, all US States, all Canadian Provinces, all US Area Codes, all Canadian Area Codes.  I utilize this object throughout several Java-based applications I have built that leverage the Salesforce API set.  The object is used to: (a) distribute leads; (b) determine account/opportunity sales territory make-up and boundries, etc.  If this database object would be of interest to you let me know and I would be happy to download the main structure and content into Excel and send you a copy.  If you are interested please provide me with your email address so I can send the Excel file to you via email.

Regards,

Guido (a.k.a. Gary Triassi/Five9, Inc. - gtriassi@five9.com)
EricBEricB
Hi Gary,
Here are answers to your questions.
 
1)  Not yet possible to create shared validation rules that could be called from multiple places.  A nice enhancement idea, though.
 
2) Validation rules can be packaged on published on the AppExchange directory, so it will be possible for the community to share this way.  We plan to put together a bunch of sample validation rules (like the "100 Sample Formulas" document) and post them when Winter '07 is released.
 
3) We don't currently have the concept of a LOOKUP() function in the formula language, but that is something we are evaluating for the future.  I agree this would be a very common use case to validate against a picklist or custom object containing a list of codes.
 
To whet your appetite, here is a sneak peak at a couple sample validation rules for Winter '07.  Not as elegant as doing the lookup against a picklist, but it gets the job done...
- Eric
 
Validate that Account Billing Country is a valid ISO 3166 3-letter code:
 
Code:
OR( 
LEN(BillingCountry) = 1, 
LEN(BillingCountry) = 2, 
NOT( 
CONTAINS( 
"AFG:ALB:DZA:ASM:AND:AGO:AIA:ATA:ATG:ARG:ARM:ABW:" & 
"AUS:AUT:AZE:BHS:BHR:BGD:BRB:BLR:BEL:BLZ:BEN:BMU:BTN:BOL:" & 
"BIH:BWA:BVT:BRA:IOT:BRN:BGR:BFA:BDI:KHM:CMR:CAN:CPV:CYM:" & 
"CAF:TCD:CHL:CHN:CXR:CCK:COL:COM:COG:COD:COK:CRI:CIV:HRV:" & 
"CUB:CYP:CZE:DNK:DJI:DMA:DOM:ECU:EGY:SLV:GNQ:ERI:EST:ETH:FLK:" & 
"FRO:FJI:FIN:FRA:GUF:PYF:ATF:GAB:GMB:GEO:DEU:GHA:GIB:GRC:GRL:" & 
"GRD:GLP:GUM:GTM:GIN:GNB:GUY:HTI:HMD:VAT:HND:HKG:HUN:ISL:" & 
"IND:IDN:IRN:IRQ:IRL:ISR:ITA:JAM:JPN:JOR:KAZ:KEN:KIR:PRK:KOR:KWT:" & 
"KGZ:LAO:LVA:LBN:LSO:LBR:LBY:LIE:LTU:LUX:MAC:MKD:MDG:MWI:MYS:" & 
"MDV:MLI:MLT:MHL:MTQ:MRT:MUS:MYT:MEX:FSM:MDA:MCO:MNG:MSR:MAR:" & 
"MOZ:MMR:NAM:NRU:NPL:NLD:ANT:NCL:NZL:NIC:NER:NGA:NIU:NFK:MNP:" & 
"NOR:OMN:PAK:PLW:PSE:PAN:PNG:PRY:PER:PHL:PCN:POL:PRT:PRI:QAT:" & 
"REU:ROU:RUS:RWA:SHN:KNA:LCA:SPM:VCT:WSM:SMR:STP:SAU:SEN:SCG:" & 
"SYC:SLE:SGP:SVK:SVN:SLB:SOM:ZAF:SGS:ESP:LKA:SDN:SUR:SJM:SWZ:" & 
"SWE:CHE:SYR:TWN:TJK:TZA:THA:TLS:TGO:TKL:TON:TTO:TUN:TUR:TKM:" & 
"TCA:TUV:UGA:UKR:ARE:GBR:USA:UMI:URY:UZB:VUT:VEN:VNM:VGB:VIR:" & 
"WLF:ESH:YEM:ZMB:ZWE:", BillingCountry )))

 
Validate that Account Billing State/Province is a valid 2-letter code, if Country is USA or US or blank:
 
Code:
AND ( 
OR(BillingCountry = "US", BillingCountry="USA", LEN(BillingCountry)=0), 
OR( 
LEN(BillingState) < 2, 
NOT( 
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY", BillingState) )))

 
guido59guido59

Eric,

Thanks for the prompt follow-up, feedback and sample code!!

Guido
The_FoxThe_Fox
Hello

Thanks for the example and the sample code any ihint on the availability of winter'07

Thanks

Guido my email is chumbert at odyssey-group dot com
guido59guido59

Fox,

I am not a Salesforce.com employee so I can not speak authoritatively on the availability of Winter '07.  What was communicated at Dreamforce 06 is that it will be available within the next 2-3 months (i.e. before the end of the year).

Guido59
The_FoxThe_Fox
Thanks for the hint

regards
dragonladydragonlady

Thanks, Eric, Fox, et al -

Vaidation rules going to save this power Admin two tons of time! Really appreciated the code snippets, getting ready for that release!

Maura
The_FoxThe_Fox
Hi Mister G,

I have finished my s-controls (no iso code, just plain english) for having any Country in any Objects to be a dropdown list and required if you want it by a not supported way (yet) to include inline s-controls because validation rules does not provide a dropdown list with authorized value.

Regards
guido59guido59

Fox,

Would you be willing to share your s-controls with me...?  If so, please send email to gtriassi@five9.com.

Thanks!

Guido59