Pages

12 December, 2012

Master Detail in Sharepoint (2/2)

Ok, back to continue the article about creating Master Detail relationship in Sharepoint 2010.
In my previous post Master Detail in Sharepoint (1/2) I've shown you how to prepare the Master forms in order to view the Details of a Master item.

Now you need to tweak a bit the Details table form, in order to provide an easy way for the end user to add and update Detail items. Check it out how in this article.

1. Create a new Detail New Form
Using SharePoint Designer, open your site and for the Detail table, in our case Cities, create a New Custom Form.

2. Edit the new form
Using SharePoint Designer, open your site and for the Detail table, in our case Cities and create a New Custom Form. And as explained in previous article, delete the web part and re-create it.



2. Create a parameter to receive the Country ID
Ok the goal is to fill the Country automatically with the value passed by the master form. The first step is to create a parameter, for that click Parameters in Data View Tools > Options menu and press New Parameter.


Name the new parameter, in this case CountryID, make its source as Query String and in the Query String Variable give the form query variable, in this case CountryID again.

3. Link the new parameter to the Country field
Next step, is to link this parameter to the country form field. Right click the Country field and format it as Text Box
Select the new text box and click Split to see both code and window at the same time. After that change the 
text="{@Country}"
to
text="{$CountryID}"

4. Hide the Country row
To stop the user from changing the value of the Country field, the best is to hide it. I personally prefer to hide the entiry row. Add to the <tr> tag the following code: style="display:none"

Like this:

You have now prepared the New Form for adding Cities to a specific country, this form will be able to receive as a parameter the specific country.

Save and close the form.

5. Make sure the form is marked as default.
Important step, after creating the new form make sure it is marked as default.


6. Edit the Display Form for the Master table
Ok the last step is to change the link in the Master Form. Go back to the Country table and edit the Display Form created in the previous article  Master Detail in Sharepoint (1/2). The goal is to change the URL of the link Add new item. Because the default behaviour won't pass the Country ID as a parameter, resulting in adding a City without Country.



7. Add a Content Editor Web part
At the bottom of the from add a Content Editor Web Part.

8. Inject javascript code into the Content Editor
Last step is to add a nice javascript code into the Content Editor Web Part body. Don't forget to add your site url.
<script type="text/javascript">
  var newLink = document.getElementById('idHomePageNewItem');
  newLink.onclick = function(){NewItem2(event, 'http://<your_site_url_here>/Lists/City/NewCity.aspx?CountryID=' + getQueryString('ID')); return false;};
  newLink.href = "";
  function getQueryString(name)
  {
    var query = window.location.search.substring(1);
    var parms = query.split('&');
    for (var i=0; i<parms.length; i++) {
      var pos = parms[i].indexOf('=');
      if (pos > 0) {
        var key = parms[i].substring(0,pos);
        var val = parms[i].substring(pos+1);
        if(key == name)
          return val;
      }
    }
  return '';
  }
</script>

Save the form and its done, now when you click in the Add new item link, the New Form City will received the Country ID, resulting in adding a new City into the opened Country. 


In this case, I'll add Porto city to Portugal.


Ok it was a big article, with alot of steps if you miss any it won't work. So no rush, do it slowly and enjoy the result.

If you want to keep in touch, feel free to Subscribe to How I did it - Sharepoint Foundation 2010

3 comments:

  1. Hello,
    Thank you very much for great article. It saved me a lot of time. I have only one question. I would like the detail list contain number column (ex. number of people in the city) and master list could contain total number of people from all cities in certain country. Of course the information should be updated every time when new city is added. Please give me some hint how to achieve this
    Regards

    ReplyDelete
    Replies
    1. Hi dan, welcome and glad I could help.
      You have two ways that I know to achieve what you want.

      1. (Easier) In the City DataView webpart activate the "Total Row" in SharePoint Designer. This will automaticaly place a row on top of the table with the sum or count of the visible columns. Just delete the values you don't need.

      This way your Contry people number is not persistent and is only available in the form you costumized.

      2. (Harder) The other way is to place a field in our Contry table to store the total Country's people. Then create 3 event receivers for you Cities lists: AddedItem, UpdatedItem, DeletingItem.
      And add the required code to count the cities people for a specific country and update the field in the Country table.

      This way every time you add/modify/delete a City from aCountry, you ensure that the Country people is updated.

      Delete
    2. Thanks a lot. Of course second way is much better. I was hope, that I can do it without programming. But you confirmed my presumption that event receivers are the best solution.
      Thank you once more and regards
      Dan

      Delete