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
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.