Another name for this post could be “Master-Detail Items”
This is a question that is asked all the time, and there are several examples to explain the concept. For example, if I have a web part page with a list of projects, and on that web part page, the lists of tasks and issues associated with that project are displayed. The problem is that out-of-box when someone clicks a button to create a new task associated with that project, it gives them the new task form, but no default project information is passed.
Another example of this type of thing came up recently, where someone was gathering information using a SharePoint list form, but wanted to be able to have the equivalent of a repeating table within the form, so that there could be multiple sub-items related to the parent item. I’ll use a very simple example of a site that has a library full of customer contracts, one for each customer. Since each customer can have multiple locations, there is a separate list on the site called Customer Locations. When we are looking at a page that shows all the data for one customer, we want it to be filtered to show only that customer’s locations, and when you add a new location, the customer field in the Customer Locations list will default to that customer name. So here’s how this is accomplished.
I’m going to do this in SharePoint 2010, which only slightly different than the way it’s done in 2007, just because the SharePoint Designer interface is so different.
- Read my blog post called Query String URLs are Magical. In this specific example, we only have a Customer Contracts library, and a Customer Locations custom list. There’s also a video and a series of 4 total posts on the query string concept.
- The Customer Locations list has the following fields:
Title (changed to “Location Name”)
Location Contact Name – single line of text
Location State – single line of text
Customer Lookup – Lookup field to the Customer Contracts library. Use the “Title” as the main field, and also select the check box next to the ID field.
- Following the instructions in the aforementioned blog post, create a workflow and a web part page called CustomerDetails.aspx, and put web parts for Customer Contracts and Customer Locations on the page. Use the query string URL filter to send a query string called Customer to both web parts:
Query String filter –> Customer Contracts – connect to send filter to the ID field.
Query String filter –> Customer Locations – connect to send filter to the Customer Lookup:ID field.
- Once the web part page, workflow and query strings are all in place and filtering correctly, and all of the instructions in the query string URL post have been followed, you should have a link in each customer’s row that says “Customer Details” that will take you to that detail page that only shows that customer’s document and that customer’s location.
- Okay, here’s where this blog post REALLY starts. How to add the form to add new customer locations on this page, where the form defaults to the current customer. Open your site in SharePoint Designer, and open up this web part page (CustomerDetail.aspx)
- Put your cursor in the bottom of the lower web part, and in SharePoint Designer 2010, click the Insert tab on the ribbon, click Display Item Form, and choose the Customer Locations list.
If you’re using SP2007, click the Insert menu, choose SharePoint Controls, click Custom List Form, and choose the name of the Customer Locations form and New Item Form
- Now that your new item form is at the bottom of the web part page, in the Options tab in the ribbon, click Add/Remove Columns, to add the 4 columns listed in step 2 above. It’s important that you add the Customer field.
- Click the Parameters button in the ribbon. Add a new parameter called CustomerParam, as so and click OK
- In your New Form, click to select the Customer Lookup field like this: and…
HIT THE DELETE KEY. YES, DELETE THE CUSTOMER LOOKUP DROP-DOWN.
If you’re in SP2007, you don’t have to delete the existing one, you can just click the little gray chevron on it and change it to a text box, and then skip step 10 and 11.
- Leave your cursor in the same spot, and click the Insert tab in the ribbon, choose SharePoint, and choose Text Box.
- On the new text box, click the Data Field drop-down box, and choose the Customer Lookup field.
- At the bottom of the screen in SharePoint Designer, click to switch to split view, because we have to do a tiny thing in the code. Once you’re in split view, click to select this new text box in the bottom pane, so that you can see its code in code view. You’ll see something like this:
- The text= is the part that needs to be changed, so change it to this:
It’s important that the @ is changed to a $, and if you didn’t do step 8, there will be problems. What we’re doing here, is sending the customer parameter straight from the URL to this field. We’ll hide this field later, after testing.
- Save this page, time to test. Go to the customer detail page for one customer, as shown in step 4. Make sure that you have a real customer ID in the URL, and you see at least one item in the Customer Contracts library on this page. Notice that you see the ID from the URL now showing in the new form web part!
- Okay, now fill out the form and submit it, and you’ll notice that your new item will then appear in the list of customer locations for that customer! Since it’s been tested, the field can now be hidden. Go back to SharePoint Designer.
- Back to SharePoint Designer, to the code view, back to the same spot you were. The following will allow you to just hide that whole “customer lookup” row. Find the <tr> which is the row of the customer lookup, and add this: style=”visibility:hidden”
- Save. Done.
Now there are some things you’re going to want to do to clean this up and look nicer and more presentable. On each of the web parts in the browser, go to the web part properties, and change the Toolbar to “none”. Also, you may want the NewForm itself to look nicer, like make all of the fields line up with the fields in the web part above. That’s a matter of adding columns to the table in your new form web part, and rearranging fields. Messy and time consuming, but sometimes it’s gotta be done.
Some things to note: I used the ID field in the main Customer library, and even though it is a lookup field in the Locations list, it is stored as an ID number. This is why a number is necessary in this field that is being automatically populated. It won’t work if you try to type the actual name of the customer in that text box.
Thanks to Mark Rackley for showing me how to do this a couple of years ago in SharePoint 2007. Another blog post of mine that is similar to this one is SharePoint List Form – Default User Information.