SharePoint: Extending the DVWP – Part 18: User-Managed Dropdowns – Loading the Relationship list – Part 1

Author: Jim Bob Howard

If you’ve been keeping score, last time we set up a relationship list that will relate two dropdown lists to each other in a parent-child relationship so that we can cascade (or limit) the dropdown selection lists, in the child control, based on a selection in the parent.

There are a couple techniques that I know of to simplify loading the relationship data. We have to be deliberate in how we do this with SharePoint because the Title column is required by default. Last time, I listed our options:

1. We can make the Title optional (and hide it from view, if desired)
2. We can make it optional and create a workflow that concatenates the site columns to make up a unique Title. (Not recommended.)
3. We can us Excel to create our entire Country-State Relationships table and paste the entire thing in here.

I’m going with the first option for this article. I will cover the third in the next article.

At a later date, I might tackle the second option in an article, but I’m reluctant to do so because of the way workflows work that will require lots of error checking and comparisons. The workflow could be very helpful once the initial data is loaded and the entry method is changed to a DVWP or standard one-at-a-time entry. I’ll cover that in a future article.

1. Make Title optional, by clicking on Settings -> List Settings

Settings -> List Settings

2. Click on Title under Columns

Click the column name to edit it

3. Click No  for Require that this column contains information under Additional Column Settings

Set to Optional by setting Required… to ‘No’

4. Click OK
5. Click the breadcrumb to return to the datasheet view.
6. Open another browser tab or window (Window will make it easier to Alt-Tab between the two), and browse to your States (or other ‘child’) list
7. Select all of the children for one parent (in our case, click on Wyoming; holding the Ctrl and Shift keys on a PC, press the up arrow) and press Ctrl-C to copy the list to the clipboard

Select all of the States that correspond to the USA

8. Toggle to the relationship list and click on the first cell in the child column


9. Press Ctrl-V to paste the list into relationship list

Child columns have been pasted in

10. Click on the first parent cell, click the arrow to reveal the dropdown choices, and choose the parent that corresponds to the children just pasted in

Choose the parent for the first row

11. Holding the Ctrl and Shift keys, press the down arrow key to highlight all the cells in this column. Still holding Shift, release Ctrl and press the up arrow to deselect the insert row

Select all of the cells in the parent column, except the last one

12. Press Ctrl-D to ‘fill down’ all of the cells in this column with the value in the first cell

Parent cells filled in

13. In next cell in the parent column, choose the next parent (Canada, in our case)
14. Toggle back to the States list. Select the first child for the next parent (Alberta, in our case), and holding Ctrl and Shift, press the down arrow

Canadian Provinces selected

15. Press Ctrl-C to copy them and toggle back to the relationship list
16. Move the cursor to the cell in the insert row below the last child entry (Wyoming, in this example), click once, and press Ctrl-V to paste.
17. Press Ctrl-down arrow to move to the bottom of the list. Press the left arrow to move to the parent column. Holding Ctrl and Shift, press the up arrow. (This will select everything from here to the next cell above that contains data.) Press Ctrl-D to fill down again.
18. Repeat if you have more parents to enter.

We could have also done this using Excel to get the data the way we wanted it, as well as setting the Title with something descriptive. We’ll cover that option next time we continue 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]).