SharePoint: Extending the DVWP – Part 19: User-Managed Dropdowns – Loading the Relationship list – Part 2

Author: Jim Bob Howard

So, you’ve been following along as we’ve created site columns, loaded the column data, created a relationship list, and loaded the relationship lists. But you really want to use the Title column in the relationship list to make it easier to maintain later. I don’t blame you, especially if it’s something that is likely to change or have new entries in the future.

Two options that might seem to make the job easier are:

1. A workflow that concatenates the parent and child data into the Title when the item is created.

Problem: This method won’t let us load the data with a large copy/paste action for multiple rows (like we did in the last article) because when the row is "created" it will be without one of the pieces of data. So an "on create" workflow won’t work. An "on change" workflow would require us to check to see if the Title is what it should be and changing it if not, which would also trigger the change workflow. This could hang the server until you kill the workflow.

Obviously, this would not work for the initial load. But, it could work "on create" for future one-at-a-time adds, especially if you made the parent and child required (which they should be, in theory, anyway).

2. Another "obvious" option would be to create a Calculated column in the datasheet view that would immediately concatenate the two. We could then copy/paste the entire column into the Title field.

Problem: Lookup columns aren’t visible to the Calculated column.

But, we can use this same idea using Excel instead.

Which is what we’ll cover in this article.

A More Excellent Way

Launch Excel and we’ll find an easy way to load our relationships list.

1. On a new spreadsheet, enter the first parent in the first cell of the B column.

Set up the data in Excel in the same order as the datasheet view of your list

2. Toggle to your child list, select all of entries for the first parent, and copy the list with Ctrl-C

Select all of the child items for the first parent

3. Toggle back to Excel and paste this list into the C column with Ctrl-V

Paste children into C column

4. Go to the bottom of the child list (press Ctrl – Down arrow)
5. Move the focus over one column to the left with the left arrow


6. Holding the Ctrl and Shift keys, press the up arrow to select the column up to the first cell that contains data


7. Press Ctrl-D to fill down


8. Repeat with other Parent/Child lists


9. Move to cell A1 in Excel


10. In the fx window, enter =CONCATENATE(B1, " – ", C1)


11. Press Enter and the A column will now have a ‘title’ made up of the parent and child.


12. With your cursor in the B or C column, press Ctrl – Down arrow to go to the bottom of the entered data. Then move left to the A column.


13. Holding Ctrl and Shift, press the up arrow to select all of the empty cells and the first one above this with data (which will be A1, in this case).


14. Press Ctrl-D to fill the formula down

Formula filled down so all records have a descriptive Title

15. Click once on A1. Holding Ctrl and Shift, press the right arrow, then the down arrow. Press Ctrl-C to copy all of the data.


16. Toggle back to the relationship list and click once on the first Title cell (the cursor should NOT be seen, just the outline).

Topmost cell selected

17. Paste in the entire relationship list data with Ctrl-V

Bring on the Cascades

Whether you used this method or the one from the last article, we’re now ready to bring in the jQuery to make our controls cascade, which I’ll cover in the next installment of Extending the DVWP.

Author: Jim Bob Howard

Jim Bob Howard is a web designer / webmaster in the healthcare industry. He has been working with SharePoint since March 2009 and enjoys sharing what he has learned. He is a moderator and frequent contributor to Stump the Panel, and answers SharePoint questions on Twitter (@jbhoward) and via email ([email protected]).