Map a custom lead field to 2 places

There have been many instances on projects where I have needed/wanted to map a lead field to 2 places. For example, on my most recent project we are using custom address fields. We wanted to do this to have better control over data quality. Using a picklist value for country coupled with Winter 07 Validation Rules, we have the ability to put pretty tight restrictions on the data that gets entered (e.g. forcing a 2 character state/province code when the country = “United States” or “Canada”). This aids in overall reporting, Territory Manager rules once the lead is converted and in Lead Assignment rules.

One of the things you give up when you do this is the ability to map those custom address fields on a Lead to both the Account and Contact like the standard Address fields do.

Pre-Winter 07

In the pre-Winter 07 world, doing this required a user to run an s-control or to have a polling application look for recently converted records and copy the address to the 2nd location. I had been able to use Formula Fields to get painfully close to making creating a 2nd copy of a field and mapping it, but when trying to save a mapping from a formula field on a Lead to a text field, I’d get an error that field sizes didn’t match. Big headache.

With Winter 07

Now that Winter 07 is here, I am able to use Field Update workflow rules to make a second copy of Lead data so that I can map that second copy to my Contact record. To do this:

  1. Create a copy of each Lead field you want mapped to a 2nd object
  2. Map the copied Lead fields to the Account, Contact or Opportunity fields they should be mapped to
  3. Create Workflow Rules on the Lead object that use Field Updates to update these new fields with the values of your original fields
  4. Test it out. It should work

Some tips:

  • Choose to evaluate your workflow rule “Every time a record is created or edited” so that your rule is always evaluated.
  • Use criteria that will always be met like Created Date > 1/1/2000so that the data is always copied to those new fields.
  • To avoid confusion for end-users, use Field Level Security to hide those copied fields from view. This way your users won’t see them on report screens and wonder what they are. Even if you hide the field from everyone, workflow can still see it and update it.
Doh!

There is 1 snag. Formula fields (which you need to do the Field Updates) have to use the ISPICKVAL function to get a value from a picklist. If you have a sizable amount of picklist values, you won’t have enough formula space to make this work. I hit that on my Country picklist.

Future

In the future, you should be able to use APEX code to intercept the Convert logic and map those custom lead fields to 2 places.

Better yet is a simpler approach that will make it so you aren’t doing all kinds of workarounds or coding in your configuration. Vote for these Ideas that Salesforce customers have already requested:

5 Comments

  1. michaelforce Said,

    January 9, 2007 @ 7:55 am

    Scott,

    I am working on a project now in which I was planning to use formula fields to map to multiple objects… I never would have expected size mismatch errors. Thanks for the heads up! And for the solution. Brilliant.

  2. Chris Said,

    January 11, 2007 @ 4:00 pm

    Slick new workaround, Scott! 🙂 Hopefully it wouldn’t need to be used for too many fields, and hopefully we’ll see the above ideas implemented someday soon.

  3. Josh Said,

    March 15, 2007 @ 7:42 am

    Thanks for the workaround. It worked awesome.

    To get around the ISPICKVAL gotcha, you can use a case which allows you to basically handle every line item in your picklist using one line in the formula field. Here is an example of how I did it with state:

    CASE(State__c,”AL”,”AL”,”AK”,”AK”,”AZ”,”AZ”,”AR”,”AR”,”CA”,”CA”,”CO”,”CO”,”CT”,”CT”,”DE”, “DE”,”DC”,”DC”,”FL”,”FL”,”GA”,”GA”,”HI”,”HI”,”ID”,”ID”,”IL”,”IL”,”IN”,”IN”,”IA”,”IA”,”KS”,”KS”,”KY”, “KY”,”LA”,”LA”,”ME”,”ME”,”MD”,”MD”,”MA”,”MA”,”MI”,”MI”,”MN”,”MN”,”MS”,”MS”,”MO”,”MO”, “MT”,”MT”,”NE”,”NE”,”NV”,”NV”,”NH”,”NH”,”NJ”,”NJ”,”NM”,”NM”,”NY”,”NY”,”NC”,”NC”,”ND”, “ND”,”OH”,”OH”,”OK”,”OK”,”OR”,”OR”,”PA”,”PA”,”RI”,”RI”,”SC”,”SC”,”SD”,”SD”,”TN”,”TN”,”TX”, “TX”,”UT”,”UT”,”VT”,”VT”,”VA”,”VA”,”WA”,”WA”,”WV”,”WV”,”WI”,”WI”,”WY”,”WY”,””)

  4. Amanda Said,

    October 1, 2007 @ 1:01 pm

    Scott,
    I am having trouble getting the workflow formula to copy any picklist values to a text field. I tried the Case formula that Josh put on your page (eg. CASE(Bill_to_Country__c,Brazil,”Brazil”,Australia,”Australia”,””) ) but all I got was a “Syntax Error” for the whole thing. I tried using ISPICKVAL( Bill_to_Country__c , “Brazil”) but then I got the error “Formula result is data type (Boolean), incompatible with expected data type (Text).” Can you tell me how to get a picklist value to copy to a text field through a workflow field update?

    Thanks so much!
    Amanda

  5. Scott Hemmeter Said,

    October 3, 2007 @ 9:35 am

    Here’s a sample formula I’ve used before to get values from picklists. It’s a combo of CASE and ISPICKVAL.


    CASE(1,
    IF(ISPICKVAL({!Status__c},"Active"), 1, 0),"Active",
    IF(ISPICKVAL({!Status__c},"Inactive"), 1, 0),"Inactive",
    IF(ISPICKVAL({!Status__c},"On Hold"), 1, 0),"On Hold",
    "")

    I hope this helps.

RSS feed for comments on this post