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.
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:
- AxioSQLTable1_ID: this is a key generated by SharePoint to link to the external data;
- 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";
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.