Using Joomla usernames as External ID

CiviCRMCiviCRM has the very useful External ID field that can be used as a unique identifier. If your CiviCRM Contacts have a username, I've often found it useful to use the username as the External ID. This article describes how to import usernames.


Fabrik is often the reason why I benefit from having usernames as the External ID. Fabrik subsequently becomes the tool to facilitate this solution.


Create a list

Start by logging on to your Joomla back-end with an admin account.

  1. Go to Components > Fabrik > Lists.
  2. Click the New button to create a Fabrik list.
  3. With Details selected:
    1. Label: ContactIDs Usernames.
  4. Select Data on the left pane.
    1. Connection: Site database.
    2. Database table: civicrm_uf_match
  5. Click Save.

Now you will be able to create the data join from the civicrm_uf_match table to the Joomla users table.

  1. With the Fabrik list open for editing, click Data from the left pane.
  2. Select the Joins tab.
  3. Click the Add button, to create a new join.
    1. From: civicrm_uf_match
    2. To: abcde_users.
    3. From column: uf_id
    4. To column: id
  4. Click Save.

Now you can configure the data that can be downloaded as a .csv file.

  1. With the Fabrik list open for editing, click Publishing from the left pane.
  2. Select the CSV tab.
  3. Next to Elements, click the Select button and add the following elements:
    1. contact id
    2. abcde_users.username
  4. Include raw data: No.
  5. File name: ContactUsernames.csv

Now you can export the .csv file with CiviCRM Contact IDs and Joomla usernames.

  1. Go to Components > Fabrik > Lists.
  2. Click View data for the ContactIDs Usernames list.
  3. Click the CSV > Export to CSV button.
  4. In the dialog box that appears, click the Download here button.


Contact Import

You now have a spreadsheet of Contact IDs with usernames. This can be used to import usernames as the External ID. Note that it's always a good idea to backup your website before performing tasks like this that cannot easily be undone.

  1. Go to Components > CiviCRM.
  2. Go to Contacts > Import Contacts.
  3. Click the Choose file button, and select the .csv file that you created in the previous step.
  4. Select First row contains column headers.
  5. For Duplicate Contacts, choose Update.
  6. Click Continue.
    1. The contact_id field should automatically map to the Contact ID field.
    2. The username field needs to be mapped to External Identifier.
  7. Click Continue.
  8. Click Import Now.
  9. Click Done when the import is finished.

You will now have everyone's username saved as their External ID.


Updating External IDs

Unfortunately this procedure does not happen automatically so unless you update External IDs every time you create a new Contact (who also has a username), you will need to rerun this task periodically.


Thanks for visiting,