This blog shows you how to create external lists for SQL tables that are related to each other via a foreign key. The objective is outline below:
- Create external lists so that you can perform CRUD operations from SharePoint.
- Define association between external lists to maintain integrity with the relationship they hold in database
- Display user friendly column for foreign key instead of just the IDs.
Relationship between Staff and Department
Staff is fairly simple and you can easily create it using SharePoint Designer. I'll cover creating external list for Department but the process is same for Staff except that Staff does not need any association to be defined.
This is how my external list for Staff looks. What you'll notice here is that I've formatted the column display names to include spaces between words.
Follow steps below for creating Department external content type.
- Select the Department table in SharePoint Designer and click "Create All Operations"
- Department ID -> map to identifier and uncheck required as it is an identity field in table. Additionally, I've also change the display name to include space between words for all fields.
- Add a Limit filter, if required, and click Finish to provision the operations.
Now as there's a relation between Department and Staff, we'll create an association to define that relationship.
- Right click Department table and select "New Association"
- Give the association proper name. Click Browse button to select the Staff external content type. StaffID will map automatically then click 'Next'.
- In Input parameter configuration, select the field "StaffID" and check "Map to Identifier". If you are adding space between words in Display Name then you do not have to do that in this step. If you try to change it, you'll get a warning message that saves the Display Name changes will not be saved. Click Next.
- Click Next again as I'm not defining any filter parameter.
- In Return Parameter Configuration, uncheck the 'Required' property for field Department ID and also change the DislpayName of all fields to include space. The click Finish.
- Save the content type.
- Create an external list using this Department external content type. You should be able to perform CRUD operations on this external list.
Some improvements we could do in this list is display user friendly name instead of ID in foreign key column. E.g. Staff ID. Displaying staff ID in list view as well as when creating new department is not that user friendly. So what we are about to do is use 'Staff Description' column to be displayed.
- First thing we want to do is when creating new department, and when user selects the item from external item picker, we want to display the Staff Description instead of Staff ID.
To do this, in SharePoiint designer, select Staff external content type, go to Summary view, and then select the Staff Descirption field in the fields section. Then in the toolbar, select the option "Set as Title". Save it. You will then see description instead of id field.
- Second thing we would want to do is, in the list view, instead of displaying the "Staff ID" we will display Description.
To achieve this, what we'll have to do is create a view and include all of the above fields plus the Staff Description field. What we'll then do is replace the "Read List" and "Read Item" operations with the one created using the views. We'll then mark the "Staff Description" column to be read only so that create/update operations do not try to edit that field which really does not exist in the table. We need to be careful that the field names that are used in the view is exactly the same as defined in other operations.
Follow the process below to do this.
- Create a view in the database.
In Department external content type, remove "Read List" and "Read Item" operations. Save it
- Then in ""Operations Design View", select the view created above, right click and select "New Read List Operation".
- In the Return Parameters Configuration wizard, uncheck the "Required" parameter for Department ID field, and change the display name of all fields to include space.
- Select the Staff Description field and set is as 'Read Only'. Click Finish
- Follow similar steps to create "Read Item Operation". No need to change anything in "Input Parameter Configuration", not even display name as you'll notice a warning message appears that says display name property won't be saved.
In "Return Parameter Configuration", change the settings as outlined in step iv) and v) above. Save the content type.
- Now re-create the external list using the Department external content type
What you'll now see is 'Staff Description' column as well in list view. You can now change the view and choose not to display STAFF ID field.
Finally, this is how your external list would work.
Create new Department Item
New Department in list view