Apex Code Development You need to sign in to do that
Don't have an account?
MichaelE Custom Formula Help: Address/dividing sales territory
This is a formula that I have written for the US, but I need to expand to the UK, Germany and France....I hope someone can help me.
IF(LEN(Country=0, "None",
IF(CONTAINS("USA:United States", Country), "Office A",
IF(CONTAINS("UK:United Kingdom",Country), "Office B",
IF(CONTAINS("France), "Office C",
IF(CONTAINS("Germany), "Office D")))))
IN BETWEEN THESE TWO FORMULAS, I NEED SOME FORM OF LOGIC TO SAY, IF COUNTRY = Oficce A, Then divide the regions further by the following criteria....not sure how to do this.
IF(LEN( State)=0, "None",
IF(CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State), "San Francisco",
IF(CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State), "Chicago",
IF(CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State), "New York","Other"))))
I would appreciate any help you can give me.
*sigh* You kill me... in a very slow, almost passive way, you kill me (like cigarettes)
I posted this VR Formula that makes the ISO 3166-2 Country Code required last week.
Like I said in my follow-up post I strongly recommend adding a set of VR up front that require the user to enter the Country and State/Province in a standardized format. Either Alpha-2, Alpha-3, otherwise you're gonna have a nightmare trying to evaluate everything they can possibly put in the Country, or State fields and create the correct Office value.
All Answers
It looks like you just need to amend the US portion of your first formula with an AND OR function. Is the second formula poplutating a seperate field or the same one as the original formula?
Which SFDC objects, fields, datatypes are you using here?
This all has to do with one field. I use this formula in both the leads object as well as accounts. I figured I needed to use some form of advaned logic but was not sure which one was more appropriate.
The reason it is set up like this is becuase for instance in country A, we only have one office, but in country B....we have 3 offices that need to be divided up based on state/province.
Okay, let me take crack at it... where do I send my bill? ;-p
Thank you. I really can't tell you how much I appreciate this assistance.
Sending beer is a pretty good way of telling me...
Try this->
IF(ISBLANK(Country), "None", IF(CONTAINS("UK:United Kingdom", Country), "Office B", IF(CONTAINS("FR:France", Country), "Office C", IF(CONTAINS("DE:Germany", Country), "Office D", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State)), "San Francisco", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State)), "Chicago", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State)), "New York", "Other")))))))If you send me your address, I will send you some beer.
But....there is a quick problem with what we have done.
Say for instance there is no country entered, but the state is entered in. It comes up as not being designated to an office. Is there a way to make sure that the US offices only have to enter in state and it will separate?
That will cost you more beer. I think you're better off by adding a VR that makes the Country required (in the ISO 3166-2 format), and also one that makes State/Province required (in Alpha-2 format) for US and CA records. Your data will be a LOT cleaner
ISO 3166-2 Country Code
US requires State VR
AND ( NOT( $Profile.Name = "System Administrator"), Country = "US", 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:PR", State) )))CA requires Province VR
AND ( NOT( $Profile.Name = "System Administrator"), Country = "CA", OR( LEN(State) <> 2, NOT( CONTAINS("AB:BC:MB:NB:NL:NS:NT:NU:ON:PE:QC:SK:YT", State) )))or if you don't want to add the VR, you could either remove the
CONTAINS("US:USA:United States", Country )include ISBLANK(Country)
but I really don't think that's a good idea.
Is this possible with Professional edition? I am not sure how to implement this formula....
I'm not sure I follow you? VR Formulas are not limited by SFDC Edition. What part are you having trouble with?
Ok. So i have been entering in the formula into a custom field. But is this an additional formula I have to enter in? I am not sure how to enter in a VR formula, or even what it is. I am not an expert....by any means
Sorry, a VR means Validation Rule ->
https://na1.salesforce.com/help/doc/en/salesforce_useful_validation_formulas.pdf
https://na1.salesforce.com/help/doc/en/salesforce_formulas_cheatsheet.pdf
Basically it's a seperate formula that you use to make sure that certian contitions are met when a user creates or edits a record. The ones that I posted basically make it impossible for a user who is not an Admin to create or edit a Lead in the Country is incorrect, or if the Country = US or CA and they don't enter a valid State or Province.
I have to go back to clearing out my fridge to make room for all that beer you're gonna ship to me...
If you can help me get this to work: I will send you a six pack of your choice. Just email me your address to mnengle@gmail.com.
You're on... oh there's just one thing,
I like to drink these -> http://beeradvocate.com/beer/profile/863/7971
Ok Pliny Elder it is.....BUT
I am getting a few errors: Error: Field $Profile.Name does not exist. Check spelling.
And it is also saying that there is no person named system administrater.
Additionally the second VR about CA is very useful, but I have not tried that one yet.
My question: Is there a way to bypass the country field if there is a valid US state entered? Then we could use the first formula.
Okay where are you entering the VR formulas that I gave you? Are you creating 3 new VR's on the Lead object? (that's what you will need to do to use them)
Managing Validation Rules
Validation rules verify that the data a user enters in a record meets the standards you specify before the user can save the record. A validation rule can contain a formula or expression that evaluates the data in one or more fields and returns a value of “True” or “False.” Validation rules also include an error message to display to the user when the rule returns a value of “True” due to an invalid value.
About Validation Rules
Improve the quality of your data using validation rules. Validation rules verify that the data a user enters in a record meets the standards you specify before the user can save the record. A validation rule can contain a formula or expression that evaluates the data in one or more fields and returns a value of “True” or “False.” Validation rules also include an error message to display to the user when the rule returns a value of “True” due to an invalid value.
I was creating two new validation rules:
One - US requires state
AND ( NOT( $Profile.Name = "System Administrator"), Country = "US", 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:PR", State) )))Second: CA requires province
AND ( NOT( $Profile.Name = "System Administrator"), Country = "CA", OR( LEN(State) <> 2, NOT( CONTAINS("AB:BC:MB:NB:NL:NS:NT:NU:ON:PE:QC:SK:YT", State) )))okay, use these instead
AND ( Country = "US", 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:PR", State) )))AND ( Country = "CA", OR( LEN(State) <> 2, NOT( CONTAINS("AB:BC:MB:NB:NL:NS:NT:NU:ON:PE:QC:SK:YT", State) )))I am not exactly sure what those validation codes did. Can you explain that?
In addition, the following scenario has not been solved.
For a lead or account, that I have a US state entered, but not a country. It shows up as "NONE". But I need it to bypass the fact that there is no country, and instead concentrate on the state. It should then divide between offices as designated in my first post. For instace AZ = Office B and so on....
Does that make sense?
The validation rules are implemented, but I am not sure what they are doing.....
I was hoping you could assist in this last step. I was thinking about adding a CASE formula to add into the previous formula, but I am not sure how to connect them:
Case(State,
"CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", "San Francisco",
"SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", "Chicago",
"PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", "New York",
"Unknown")
I was thinking something along this line. But it said that it expected 3 IF( and received one
IF(CONTAINS("UK:United Kingdom", Country), "London",IF(CONTAINS("FR:France", Country), "Paris",IF(CONTAINS("DE:Germany", Country), "Hamburg",CASE(AND(IF(CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State)), "San Francisco", CASE(AND(IF(CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State)), "Chicago", CASE(AND(IF(CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State)), "New York","Other")))))))))
Hi Mike the VR act as a firewall that forces your users to enter the data in exactly the way you want them to, like using the ISO 3166-3 Country Code an entering "US" instead of winging it and entering US, U.S., USA, U.S.A., United States, etc...
If you don't want to use them you don't have to, but I strogly recommend them for data quality. Also, as far as bypassing Country if the State is valid, agin I think that's a bad idea for data quality, but if you're okay with it we can loosen the code a bit.
I thought that this one worked?
IF(ISBLANK(Country), "None", IF(CONTAINS("UK:United Kingdom", Country), "Office B", IF(CONTAINS("FR:France", Country), "Office C", IF(CONTAINS("DE:Germany", Country), "Office D", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State)), "San Francisco", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State)), "Chicago", IF( AND( CONTAINS("US:USA:United States", Country ), CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State)), "New York", "Other")))))))try this
IF(CONTAINS("UK:United Kingdom", Country), "Office B", IF(CONTAINS("FR:France", Country), "Office C", IF(CONTAINS("DE:Germany", Country), "Office D", IF( AND( CONTAINS("US:USA:United States:", Country ), CONTAINS("CA:NV:OR:WA:MT:ID:WY:CO:UT:AZ:NM:TX:HI:AK", State)), "San Francisco", IF( AND( CONTAINS("US:USA:United States:", Country ), CONTAINS("SC:NC:VA:WV:KY:OH:ND:SD:NE:KS:OK:LA:AR:MO:IA:MN:WI:IL:IN:MI:TN:AL:MS:GA:FL", State)), "Chicago", IF( AND( CONTAINS("US:USA:United States:", Country ), CONTAINS("PA:NY:VT:NH:ME:MA:RI:CT:NJ:DE:MD:D.C.", State)), "New York", IF(ISBLANK(Country), "None", "Other")))))))Mike, do you still need help with this or are you all set?
I am trying to get it sorted right now. Can I let you know after the long weekend?
Thank you so much for your help and have a great memorial day.
No problem, if the VR's are making things too complicated you can ditch them, but I'd really only want to do that as a last resort.
Hey there,
Sorry it took so long to get back to you.
I have a problem still. Is there anyway I can require someone to enter in the country? It will not allow me to require that field. Can I do this with a VR?
The problem I am trying to solve ist that it uses "Office B" as the deffault when no country is added and there is a state or province entered. This is a problem when I enter in California (CA) and it tells me it is assigned to OFfice B.
Michael
*sigh* You kill me... in a very slow, almost passive way, you kill me (like cigarettes)
I posted this VR Formula that makes the ISO 3166-2 Country Code required last week.
Like I said in my follow-up post I strongly recommend adding a set of VR up front that require the user to enter the Country and State/Province in a standardized format. Either Alpha-2, Alpha-3, otherwise you're gonna have a nightmare trying to evaluate everything they can possibly put in the Country, or State fields and create the correct Office value.
Steve,
I know I was frustrating you. But I can't thank you enough. That just solved my whole problem. Do you have any other suggestions of VRs to make my data clean?
Thank you again.
Do you have an address for beer?
Hey no worries, I just had the feeling that we were starting to go around in circles. My VR and Formula Fields working fine on my Org and I could not recreated the problem you were having.
Anyway, these Tip Sheets are worth the weight in gold (and beer)
https://na1.salesforce.com/help/doc/en/salesforce_formulas_cheatsheet.pdf
https://na1.salesforce.com/help/doc/en/salesforce_useful_validation_formulas.pdf
https://na1.salesforce.com/help/doc/en/salesforce_useful_formula_fields.pdf