Create New Child External List (based on ECT/ SQL server) with Name column of Primary Table instead of foreign key ID column

Scenario: Parent Child Lists. In Child list ‘List View’ mode instead of foreign key Id we should see name from Master table.

I have captured the snapshots to help you understand the flow more easiliy. Since the post is most focussed on Child List issues, I am skipping any details pertaining to how to create ECT/ External List for Parent table. I named ECT for Customer table as ‘Customer’ and saved.

Also my understanding is that, user is already aware of how to use Sharepoint designer to craete External content type for SQL server as datasource.

In my enviroment I created these using SPD 2013, SQl server 2008 R2.

Assume a scenario where While displaying ‘Orders’ as a ‘List View’ the user should see the Order details along with the Customer name for whom this order is meant for.

So in my DB I have 2 table, Customer, Orders with self explanatory meanings. Ensure that you create a ‘CustomerOrders’ View in DB on Orders Table based on CustomerID as parent child relation, and included Name column from Customer table in this DB ‘View’. All columns from Orders table are part of this DB ‘View’.

I am ready to Create ECT for Child table. Choose ‘Create All Operations’ option as per below diagram.

This will, by default, will generate all CRUD operations for you.

On this screen agree all the suggestions and just press Finish button for SharePoint to generate all CRUD operations for you.

Here starts the logic to show Customer Name in ‘List View’, ‘Item View’ for Orders table.

In Operations Design view of new created ECT for Orders table, highlight Read List operation and in top ribbon menu select ‘Remove Operation’ to remove this default generated Operation.

After confirmation for delete of ‘Read List’ repeat the same step for ‘Item View’ also, as per below snapshot.

Now starts the real logic and fun. While still in ‘Orders’ child ECT window, go to Data Source Explorer and select CustomerOrders DB View.

Right click on this DB View and Select ‘New Read List Operation’.

Set the appropriate Operation name and Display name.

As you move Next in this wizard few configurations you need to set on Return Parameter tab as per below snapshots

Press Finish.

Now again go back to CustomerOrders DB View and select ‘New Read Item Operation’.

Do the configuration settings on Read Item ‘Return Parameter’ tab as per snapshots below..

Press Finish.

Your all CRUD operations are again ready. Create an ‘External List’. Name it ‘Orders’. Done.

When you open Operations to see the data displayed for External List, you will see Name as a column in ‘List View’ and ‘Item View’.

And these View are user friendly and Done.

Advertisements

One thought on “Create New Child External List (based on ECT/ SQL server) with Name column of Primary Table instead of foreign key ID column

  1. Its a great and detailed article. Its very helpful for learner like me. I gone through some other article also in this blog. Its creating urge to me, to work in this direction. Thanks for sharing.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s