Sign Up ›
  • ManishS
  • NEWBIE
  • 0 Points
  • Member since 2007
  • Chatter
    Feed
  • 0
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 4
    Questions
  • 0
    Replies
5 replies

Two letter US State code validation (Should be validated when ONLy State field is populated)

Hi All,

I dont know if its the case of CONTAINS but when I tried using the famous two letter US State validation code then it didnt allow me to save a record when I dont have any data for address field. To be more specific following valiation is norammly used on Leads to verify if the user has correctly entered valid US code:

AND(NOT(ISNULL( State )),
OR(
LEN( State ) < 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", State )
)))


But when I dont want to fill any address info then also this formula restricts me to save Lead record making address as a required field.

Please let me know how to modify this: I only need validation when State field is filled and NOT when either State or all five address fields are empty.

Any clue is highly appreciated:)

MAnish

  
0 replies

How to Populate custom formula field (Region) based on the Sd fields Area code and State?

I have created a custom formula field by name 'Region' under Lead object with Return Type= Text
 
Now based on the Postal Code and State, Region field should be populated by Region value.
eg:
Area Code
(PostalCode)  State                Region
205AlabamaEast251AlabamaEast256AlabamaEast334AlabamaEast907AlaskaWest480ArizonaWest520ArizonaWest602ArizonaWest623ArizonaWest928ArizonaWest479ArkansasWest501ArkansasWest870ArkansasWest209CaliforniaWest213CaliforniaWest310CaliforniaWest323CaliforniaWest408CaliforniaWest415CaliforniaWest424CaliforniaWest510CaliforniaWest530CaliforniaWest559CaliforniaWest562CaliforniaWest619CaliforniaWest626CaliforniaWest650CaliforniaWest661CaliforniaWest707CaliforniaWest714CaliforniaWest760CaliforniaWest805CaliforniaWest818CaliforniaWest831CaliforniaWest858CaliforniaWest909CaliforniaWest916CaliforniaWest925CaliforniaWest949CaliforniaWest303ColoradoWest719ColoradoWest720ColoradoWest970ColoradoWest203ConnecticutEast475ConnecticutEast860ConnecticutEast959ConnecticutEast302DelawareEast239FloridaEast305FloridaEast321FloridaEast352FloridaEast386FloridaEast407FloridaEast561FloridaEast727FloridaEast754FloridaEast772FloridaEast786FloridaEast813FloridaEast850FloridaEast863FloridaEast904FloridaEast941FloridaEast954FloridaEast229GeorgiaEast404GeorgiaEast470GeorgiaEast478GeorgiaEast678GeorgiaEast706GeorgiaEast770GeorgiaEast912GeorgiaEast808HawaiiWest
I tried using combinations of  OR and AND but no help in getting desired results.
 

IF

(AND

(OR

(POSTALCODE ="205",POSTALCODE ="251",POSTALCODE ="256",POSTALCODE ="334" ) ,(State ="Alabama" ) ,"East",

(POSTALCODE ="907",POSTALCODE ="480",POSTALCODE ="520",POSTALCODE ="602" ) ,(State ="Arizona" ),"West"

)

)

Am end up getting errors while running this syntax.
 
Any clues on this would save my day :-)
 
Regrds,
manish
1 replies

How to populate Region custom formula field based on State and PostalCode?

I have created a custom formula field by name 'Region' under Lead object with Return Type= Text
 
Now based on the Postal Code and State, Region field should be populated by Region value.
eg:
Area Code
(PostalCode)  State                Region
205AlabamaEast251AlabamaEast256AlabamaEast334AlabamaEast907AlaskaWest480ArizonaWest520ArizonaWest602ArizonaWest623ArizonaWest928ArizonaWest479ArkansasWest501ArkansasWest870ArkansasWest209CaliforniaWest213CaliforniaWest310CaliforniaWest323CaliforniaWest408CaliforniaWest415CaliforniaWest424CaliforniaWest510CaliforniaWest530CaliforniaWest559CaliforniaWest562CaliforniaWest619CaliforniaWest626CaliforniaWest650CaliforniaWest661CaliforniaWest707CaliforniaWest714CaliforniaWest760CaliforniaWest805CaliforniaWest818CaliforniaWest831CaliforniaWest858CaliforniaWest909CaliforniaWest916CaliforniaWest925CaliforniaWest949CaliforniaWest303ColoradoWest719ColoradoWest720ColoradoWest970ColoradoWest203ConnecticutEast475ConnecticutEast860ConnecticutEast959ConnecticutEast302DelawareEast239FloridaEast305FloridaEast321FloridaEast352FloridaEast386FloridaEast407FloridaEast561FloridaEast727FloridaEast754FloridaEast772FloridaEast786FloridaEast813FloridaEast850FloridaEast863FloridaEast904FloridaEast941FloridaEast954FloridaEast229GeorgiaEast404GeorgiaEast470GeorgiaEast478GeorgiaEast678GeorgiaEast706GeorgiaEast770GeorgiaEast912GeorgiaEast808HawaiiWest
I tried using combinations of  OR and AND but no help in getting desired results.
 

IF

(AND

(OR

(POSTALCODE ="205",POSTALCODE ="251",POSTALCODE ="256",POSTALCODE ="334"),(State ="Alabama"),"East",

(POSTALCODE ="907",POSTALCODE ="480",POSTALCODE ="520",POSTALCODE ="602"),(State ="Arizona"),"West"

)

)

Am end up getting errors while running this syntax.
 
Any clues on this would save my day :-)
 
Regrds,
manish
0 replies

How to populate Region formula field based on State and

I have created a custom formula field by name 'Region' under Lead object with Return Type= Text
 
Now based on the Postal Code and State, Region field should be populated by Region value.
eg:
Area Code
(PostalCode)  State                Region
205AlabamaEast251AlabamaEast256AlabamaEast334AlabamaEast907AlaskaWest480ArizonaWest520ArizonaWest602ArizonaWest623ArizonaWest928ArizonaWest479ArkansasWest501ArkansasWest870ArkansasWest209CaliforniaWest213CaliforniaWest310CaliforniaWest323CaliforniaWest408CaliforniaWest415CaliforniaWest424CaliforniaWest510CaliforniaWest530CaliforniaWest559CaliforniaWest562CaliforniaWest619CaliforniaWest626CaliforniaWest650CaliforniaWest661CaliforniaWest707CaliforniaWest714CaliforniaWest760CaliforniaWest805CaliforniaWest818CaliforniaWest831CaliforniaWest858CaliforniaWest909CaliforniaWest916CaliforniaWest925CaliforniaWest949CaliforniaWest303ColoradoWest719ColoradoWest720ColoradoWest970ColoradoWest203ConnecticutEast475ConnecticutEast860ConnecticutEast959ConnecticutEast302DelawareEast239FloridaEast305FloridaEast321FloridaEast352FloridaEast386FloridaEast407FloridaEast561FloridaEast727FloridaEast754FloridaEast772FloridaEast786FloridaEast813FloridaEast850FloridaEast863FloridaEast904FloridaEast941FloridaEast954FloridaEast229GeorgiaEast404GeorgiaEast470GeorgiaEast478GeorgiaEast678GeorgiaEast706GeorgiaEast770GeorgiaEast912GeorgiaEast808HawaiiWest
I tried using combinations of  OR and AND but no help in getting desired results. Any clue on this would save my day :-)
 
Regrds,
manish
5 replies

Two letter US State code validation (Should be validated when ONLy State field is populated)

Hi All,

I dont know if its the case of CONTAINS but when I tried using the famous two letter US State validation code then it didnt allow me to save a record when I dont have any data for address field. To be more specific following valiation is norammly used on Leads to verify if the user has correctly entered valid US code:

AND(NOT(ISNULL( State )),
OR(
LEN( State ) < 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", State )
)))


But when I dont want to fill any address info then also this formula restricts me to save Lead record making address as a required field.

Please let me know how to modify this: I only need validation when State field is filled and NOT when either State or all five address fields are empty.

Any clue is highly appreciated:)

MAnish