Can we merge two unique fields to create a third field and then make that third field an external id? Any ideas and suggestion will be appreciated. Thanks

  • 5
    Yes, this is possible, but you have to use a trigger or a workflow to set the third field. Formula fields can not be external ids. Do you have a specific problem? – rael_kid Mar 4 '14 at 9:10
  • No, I came across a Chris Barry developer training video where a trainee asks this. – MnZ Mar 4 '14 at 9:15
  • 1
    If this would be to leverage indexing benefits, consider that custom indexes can be multi-field and can be discussed/asked with a case to salesforce support. – Samuel De Rycke Mar 4 '14 at 9:32

Yes, definitely. What are you trying to accomplish?

In the scenario I am assuming based on your question, you have two truly unique but nullable fields, already populated on existing records, and you wish to create a third field that is an external ID for the purposes of indexing and uniquely identifying records with at least one of the two previously populated fields set. I am also assuming the following:

  • each of the two pre-existing fields is a number field, or a text or formula field whose value is never longer than 127 chars
  • each of the two pre-existing fields is unique (case sensitivity doesn't really matter), but null values are allowed in each
  • a row with two null values in the pre-existing fields means it cannot have a valid composite value

If those apply I would do the following:

  • create a new field, say, Composite_ID__c, as a Text(255). Mark it as External ID (unique) and if either of the source fields are case sensitive, also mark it as case sensitive, otherwise case insensitive.
  • via data loader, export all the pre-existing IDs and the two previously populated unique ID fields
  • via the means of your choosing (Excel formulas would be a popular choice), populate a new fourth column in the export with a composite ID, of the form something like "[field 1]-[field 2]". Make sure the join character ("-" in this example but could be anything; for me it's usually "-" or maybe ":" or "::") is not a legal character in either field 1 or field 2.
  • if null values are allowed in either column, then do not populate this new column if both values are null.
  • if null values are allowed in either column, then for columns with just one null value, assign the null column a simple value that isn't already in use, like "0". So a column with null for field 1 and "12345" for field 2 would be assigned "0-12345" for the new field.
  • if you aren't sure about your above assumptions, now would be a good time to check your new computed column for duplicate values. If it has any, you've done something wrong or your assumptions are wrong.
  • via data loader, update all existing rows with the new values.
  • via either an insert & update (and undelete if necessary) trigger, or a workflow, create a new trigger or workflow that does this same logic on all new rows (or updated rows if your field 1 or field 2 values are allowed to change): i.e. if both field 1 and field 2 are null, don't set field 3; otherwise set to "[field 1]-[field 2]" where a null value gets the previously selected value (my default choice would be something like "0" unless that's a legal non-null value for either field 1 or field 2).

Hope that helps. If it doesn't, please provide a LOT more information on what you're actually trying to achieve.

| improve this answer | |
  • I came across this situation while watching a training video. Thank you for explaining in detail! I am sure I wont get a better answer than this! Thanks – MnZ Mar 5 '14 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.