Can the External ID have multiple values

Question

I would like to add multiple values in External ID. Is this possible or would I have to create a new custom field?

Example 2 Duplicate contacts have different external IDs. I want to merge them but keep both external ids.

Thanks

Answer 1

The External ID field isn't just a regular field--it allows matching by ID alone. It would be a waste to have External ID be concatenated fields, for a couple of reasons:

• You can't search easily. If you have a contact with an ID from System 1 of "123" and an ID from System 2 of "789", and you concatenate with a "|", you'd have a value of "123|789". If you search for an External ID of "%123%", you'd catch "123|789" as well as "222|123" (i.e. the record with ID 123 in System 2) and "1123|789" (i.e. any ID with those digits in order, regardless of position).
• You lose auto-matching on import. If you're doing a later import from the external system, you won't be able to identify the External ID and have it match. You'll need to use a matching rule or the CiviCRM ID. This makes it no better than a regular custom field.
• You may have trouble importing in the first place. If you're importing from System 1, you'll know that system's external ID, but you might not know the corresponding contact's ID in System 2. You'd need to do some spreadsheet tricks like Joe suggests, and you'd have to match up the two systems' records in the spreadsheet before you even start.
• You'll never be able to merge records reliably. If Sammy is ID 123 in System 1 and Samuel is ID 789 in System 2, you might not catch that they're the same guy. They might end up as separate contacts in CiviCRM, but when you merge them, you'll only be able to concatenate the IDs by remembering the one that gets lost and going back to edit the External ID manually.

All in all, there's no advantage to cramming multiple values into the External ID field, and it has distinct disadvantages (merging and searching, in particular) as compared to regular custom fields. You should simply decide that one system's ID is the External ID and the others are custom fields. If it would be ambiguous, make them all custom fields; that's still better than mixing up the External ID field.

Answer 2

I believe the External ID is a single value field. On clicking help you'll see: "Use this field to store a unique identifier. This is generally used to link contacts with records in a related or legacy application...."

As you suggested a custom field might be needed, perhaps you can use a multi-value custom field, and move all your external ids to this new custom field.

Answer 3

If you don't have a lot of these - simply add the second External ID to the merged record manually, and separate the values with a non-numeric character like - or | - then you can search using Search Builder or in the back end (using PHPMyAdmin) or in the API Explorer for values with - or |, and/or use the % "wild card" in searches, like %[second External ID value]%. This assumes the External ID field doesn't allow only numeric values but any text string - I'm not sure about that.

If you do have a lot of these - export the current and deleted/merged records as .csv, match up the two sets of records by first name and last name, say, then concatenate the values of the two External IDs into a new column/field using that function in Excel, Google Sheets, or with a query using MS Access, MySQL, whatever. Then import and update the External IDs for the records that you are keeping, matching on import using the kept record's Internal Contact ID, Civi's unique ID for that person.

Answer 4

It all depends on what you plan on doing with the External ID. Its suggested use is to use it to match on imported contributions and other transactional data. If that's your use case, then a single value probably makes the most sense.

It's also useful for just referencing back to some prior legacy data. Here a single value probably makes the most sense, though on a manual basis multiple values would probably work using delimiters as outlined in other answers.

Interestingly, if you are doing custom development (and I don't know why you wouldn't use a custom data field for it) you can put anything in the External ID field. For instance on the CiviCRM sandbox I was able to enter in a bit of json formatted values which could be used programmatically. (JSON is a standard for formatting data values, ie {"source": "sheet12","row": "22"}. This example might be used to reference back to a row in a spreadsheet.)