Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

How SQList exports lookup columns linked to external content types

If a list contains lookup columns linked to an external list, these will be exported just like any other column; however, there are a few peculiarities that you must be aware of.

This article will show how this type of columns are exported by SQList.

Note: we will not go through the steps to set up the external content type and list. For details about that, please visit this page: https://support.office.com/en-gb/article/Make-an-External-List-from-a-SQL-Azure-table-with-Business-Connectivity-Services-and-Secure-Store-466f3809-fde7-41f2-87f7-77d9fdadfc95

The source SQL table (Table1)

For this example, we will use a simple SQL table with three columns:

  • ID (int, identity)
  • Code (varchar)
  • Name (nvarchar)

The external list in the SharePoint site

Once created the external content type, the external list looks like this:

The lookup columns added to a list

For this example, we are adding a lookup column to an exiting list named "Categories"; we named the column "External Code ID":

The settings for the "External Code ID" columns

Note that we selected the "Code" column as the display column, but we also selected the columns "ID" and "Name" as additional fields:

The lookup values assigned to some Categories

We edited items ID 11 and 12 in the "Categories" list and assigned the codes "C3" and "C2" respectively:

The data exported by SQList into the destination SQL table

This is how SQList exports the "External Code ID" to the SQL table:

  1. AxioSQLTable1_ID: this is a key generated by SharePoint to link to the external data;
  2. ExternalCodeID_x00: this is the "ID" column that we selected as additional field when we configured the lookup columns; note that it contains the ID of the SQL table "Table1";
  3. ExternalCodeID_x000: this is the "Name" column that we selected as additional field when we configured the lookup columns;
As you can see, the ID column is exported and "ExternalCodeID_x00" onto the SQL table, and you can use it to link to the original SQL table "Table1".


SharePoint has its own internal way to link a list item to an external list via a lookup column, and the key that it returns when the list item is retrieved is a cryptic value that is probably of no use in the destination database. However, by using the additional fields, it is possible to export the key of the original "Table1" SQL table and use it to join the the "Categories" table.

Unfortunately the names given to the columns are not descriptive but, once identified them, using them is straight forward.