2

It is often useful to have parent-child data when entering form data. Consider a typical job application. Under Work Experience you will add n number of entries each having a start and end date and a description of the your responsibilities. SharePoint makes creating forms simple for many scenarios, but the master-detail relationship is elusive. For a time InfoPath has filled this spot, but it's being deprecated and the replacement form technologies are not mature.

In researching SharePoint master-detail relationships I haven't found a gold standard but rather a smattering of approaches. What is a good option for using SharePoint lists for master-detail relationships?

4

I've tried a number of approaches for using SharePoint lists as the foundation of master-detail relationships. There are pros and cons to each, but because parent-child relational data is so central to creating structured form data, it was worth the time to figure out how to do this effectively. SharePoint lists are extremely useful because they enable one to quickly set up simple applications.

Lists have become a foundation to much of the work we do at my organization and we wanted to use them as much as possible. We do not want to use third-party tools in order to avoid ongoing licensing. Finally, it's nice to stick with established Microsoft technologies in order to avoid learning how to do yet one more thing using some other platform.

Although it is possible to create customized list forms on the server we have avoided that. We have chose to use client-side rendering (CSR) techniques via jQuery (but any DOM/AJAX library could be used). By taking this approach, when we make adjustments to the list itself (add/remove columns, etc.) our forms are regenerated automatically courtesy of SharePoint. We simply accept the generic forms and use CSR to enhance them. Toss in a heavy dose of AJAX for requesting and updating related list data via SharePoint's REST api and you can build most anything.

The various master-detail approaches I've tried:

The Master-First Approach

Using this approach the user creates the master record (in one list) first. It's only after this that he can create the details (in another list). The reason being is that creating the master generates the primary key by which the details may be related to the master.

In this workflow, the user creates the master record. Then, the master record is edited. The edit form using CSR dynamically inserts any related child records into the form (formatting them with a table). This table provides controls for adding/removing children.

The add button calls the SharePoint api for displaying the child NewForm in a popup window. The PK (primary key) is passed to it via the query string. The child form accepts the PK and plugs it into a readonly (hidden if you like) field (e.g. "Parent ID") used for binding the master-detail relationship. When the popup disappears we refresh the children table. The useful part is that we're able to reuse the feature SharePoint natively provides for popping up existing forms.

The remove button uses the REST api to delete the given record after confirming that the user is okay with the removal. As with the add, we refresh the children table.

This approach is straightforward, but it doesn't provide the best user experience. The user cannot create the master and details at once. There is a dependency of first creating the master and then, via an edit, creating the children. Furthermore, if you reuse the popup api, the processing of creating children is one popup after another. It's not horrible, but it's not ideal. This is approach we initially used. Because the person who creates the details was different from the one who entered the master record, there wasn't the awkwardness of having to select the list item and edit it after having just created it. Thus, in a multi-person workflow the edit step is less intrusive. And the popups aren't as much of a problem if one isn't creating many child records.

Again, the master-first dependency is simply a matter of needing to generate a PK so that the children when created had a valid ID at which to point. What I had hoped for was to force the master to have a certain PK, but SharePoint doesn't allow this. I devised a workaround.

The Artificial-PK Approach

To overcome the dependency on a PK that isn't immediately available, I decided we could create a hidden Identity field on the form. This field is defined in the list but hidden via CSR. On the NewForm we supply this field with a GUID, a PK that we can determine before the record is saved. Using this approach, we are able to enter the child records from the master NewForm whereas before we could only have done this from the EditForm.

What's awkward is that we are actually creating the child records before we create the master record. If we never actually save the master record we have a bunch of orphans. Now we probably need some process for removing orphans. This problem exists because we have no means of executing all of the changes in a single transaction.

This lead me to considering how to implement master-details in what is effectively a transaction. I reflected on how InfoPath stores it's data in a structured XML document and that provided me with some insight for my final approach.

The Embedded-Document Approach

What if the children records could simply be serialized/deserialized. Initially, I was thinking XML, but I later realized JSON was equally workable. Here's what I now do.

On the master list I create a multi-line Work Experience text field (a textarea) which represents one flavor of detail records for my Job Application form though there could be others. When the form comes up the textarea is hidden via CSR and then an interface is generated in its place. Initially it is a table with one blank row where each cell provides some kind of user input field. I use CSR to clone a copy of the template for a single record; in the case of a table it's a row.

There are two key moments. When the form first comes up, and the moment after the Save button is clicked. When the form comes up, I use CSR to read the data inside the hidden textarea, deserialize it, and render it into the interface. (That's where having the copy of a blank row comes in handy.) For the Save, I provide a custom PreSaveAction (a function that SharePoint calls immediately before saving). In my function, I take the data as it exists in the table fields and serialize it back to the hidden textarea field.

This all works like a charm. It permits the master-details to be saved as a single transaction. Effectively, we're just converting the details section to what might be considered a document in a document database. This does have consequences. The data here, while human readable is not human friendly. It will show up in the revision history as JSON (or whatever format you use).

If this is not an issue, this approach provides the best overall experience for the user. There are no popups and there is no need to save the master before editing and creating children records. Using CSR it is possible to eliminate the popup issue using the other two approaches, so the big win is being able to save both master and child data in what is effectively a transaction.

I reduced this approach to a jQuery repeatingSection plugin but I'm not sure if and when I can release it because it was developed for a client. My main objective was to impart the knowledge for how to elegantly handle master-detail records in SharePoint.

One key detail is the template. This is what gets substituted as the interface for our hidden textarea. I used a table but the container itself isn't important. What's important is how the controls that make editing the data possible are organized.

<table>
  <thead>
    <tr>
      <th>Start Date</td>
      <th>End Date</th>
      <th>Position</th>
      <th>Responsibilities</th>
      <th class='controls'></th>
    </tr>
  </thead>
  <tbody class='rs-container'>
    <tr>
      <td><input type='date' name='start-date[]' /></td>
      <td><input type='date' name='end-date[]' /></td>
      <td><input type='text' name='position[]' /></td>
      <td><textarea name='responsibilities[]'></textarea></td>
      <td class='controls'>
        <img src='/SiteAssets/icons/plus.png' tabindex='-1' class='rs-add' />
        <img src='/SiteAssets/icons/minus.png' tabindex='-1' class='rs-remove' />
        <img src='/SiteAssets/icons/arrow-up.png' tabindex='-1' class='rs-up' />
        <img src='/SiteAssets/icons/arrow-down.png' tabindex='-1' class='rs-down' />
      </td>
    </tr>
  </tbody>
  <tfoot>
    <tr>
      <td colspan='4'></td>
      <td class='controls'><img src='/hr/hrp/SiteAssets/icons/plus.png' class='rs-add' /></td>
    </tr>
  </tfoot>
</table>

Notice that the class rs-container marks the container for an item. The lone item provides an item-level template. This item-level template is cloned (I referred to cloning above), removed from the DOM, and held so that it can be used as the basis for rendering an item. The entire template is static; it's only the details that get added and removed within the container (via the item-level template) that are dynamic.

Notice that each input name has a [] suffix. One would never actually render an input that kept this suffix intact. Each time the template is cloned and inserted into the DOM the [] would be replaced with the next available index. Thus, the first row added would have start-date[0], end-date[0], position[0], responsibilities[0] and second start-date[1], end-date[1], position[1], responsibilities[1]. This makes it possible to group related facts together and is an important part of the serialization/deserialization process.

I use all the classes starting with rs- for traversal cues and for adding event handlers. Now that this is encapsulated in a jQuery plugin I can readily support the technique in other forms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.