Master-detail with JSGrid

• You can download the source code as a ZIP archive. - 5.9 KB

Introduction

Master-details forms are very common. Classic example would be an order and a list of items in this order...

So from SharePoint point of view, what you need to do is to embed editable Details list into the Master edit form, like this:

And often it is very convenient if the Details data table is something Excel-like. Excel is generally very familiar to users, and allows adding information quickly and efficiently.

Fortunately, in SharePoint, there is a client-side control JSGrid, that is very similar to an Excel worksheet table.

So in this article I'm going to leverage JSGrid to implement a master-details form.

Switching XLV into Quick Edit mode

In SharePoint 2013, JSGrid is used for displaying a list view in Quick Edit mode. List views are represented by XsltListViewWebPart (aka XLV).

So the idea is to add details list XLV to your master list edit form page and then switch it to Quick Edit mode somehow.

The form page can be either a normal list form (EditForm), or a custom page layout. The latter is usually a very good approach if you want to create a printable form.

So first step is to add XLV to your form page. This is elementary: just switch the page into the edit mode in browser and then add list with details data there.

Now, usually list views have a link that allows switching them into the Quick Edit mode:

So I just snooped the script behind this link. Here it is:

_spBodyOnLoadFunctions.push(function()
{
    EnsureScriptParams('inplview', 'InitGridFromView', '{YOUR-GUID-HERE}');
});

The guid is of the XLV view. You can snoop it e.g. from page source. Search for g_ViewIdToViewCounterMap:

There will be only one such place in case you have one XLV on the page. The GUID to the right is exactly what you need.

Alternatively, you can snoop the View ID in SharePoint Designer or get it any other way you like.

So if you put the EnsureParams script to the page, then when the page loads, the list will then be shown in Quick Edit mode.

If you're customizing a normal list form, put the code to EditForm page e.g. via Script Editor WebPart (of course don't forget to wrap the js into <script> tag).

Note: if you have MDS turned on on your site, please refer to the section "Minimal Download Strategy" below.

If you're creating a custom page layout, then put the code into the page source and don't forget to wrap the script into <PublishingWebControls:EditModePanel runat="server"> tag, so that it only appears when page is in edit mode.

So we have the details XLV on the page, and we have it switched to edit mode now. But it shows all the items, rather than only those that are related to the current master list item. Let's fix that!

Filtering XLV to show only relevant items

Filtering can be done easily in SharePoint Designer.

Edit form

If you're customizing Edit list form page: find your list in SPD and open the list form page editor, and then do the following:

1. Locate the XLV (search for XsltListViewWebPart tag). LocateInside the XLV, locate ParameterBindings tag and add the following code inside it:
   XML

   <ParameterBinding Name="itemId" Location="QueryString(ID)" />

2. Modify Query tag (inside the View tag inside XLV). Instead of "YourLookupFieldInternalName" pass the internal name of your master lookup field, the one that points from Details to Master.
   XML

   <Where><Eq><FieldRef Name="YourLookupFieldInternalName" LookupId="TRUE" /><Value Type="Integer">{itemId}</Value></Eq></Where>

Save the page and check it out. Done! The XLV is filtered now.

Result:

Custom page layout

If you're creating a custom page layout, then locate your page layout in the masterpage folder in SPD. Open the page layout editor and do the following:

1. Locate the XLV (search for XsltListViewWebPart tag). Right above XLV, add following code: 
   ASP.NET

   <div style="display:none;">
      <SharePointWebControls:NumberField ID="ItemID" ControlMode="Display" FieldName="1d22ea11-1e32-424e-89ab-9fedbadb6ce1" runat="server"/>
   </div>

2. Inside the XLV, locate ParameterBindings tag and add the following code inside it:
   XML

   <ParameterBinding Name="itemId" Location="Control(ItemID,ItemFieldValue)" />

3. Finally, modify Query tag (inside the View tag inside XLV). Instead of "YourLookupFieldInternalName" pass the internal name of your master lookup field, the one that points from Details to Master.
   XML

   <Where><Eq><FieldRef Name="YourLookupFieldInternalName" LookupId="TRUE" /><Value Type="Integer">{itemId}</Value></Eq></Where>

In my case, the result looked like this:

New item problem

Ok, you have filtered and editable grid, sounds great!... But how about adding new items, do we really have to force users to manually select the order every time? Sure we don't.

In order to solve this issue, obviously I need to do something like this:

1. Determine the moment when new record is added to the JSGrid
2. Fill in correct value into "Order" column
3. Hide the column itself so that user cannot edit it

JSGrid is a largely undocumented control with a huge client-side API, and it's hard to deal with. When trying to figure out how to do something with JSGrid, I usually end up somewhere very deep in the SharePoint JS guts :)

Good news about JSGrid is that it has big amount of events and you can attach handlers to these events. I found out that SP.JsGrid.EventType.OnEntryRecordPropertyChanged event fires when something is changed in the entry record row, which is the last row used for adding new records.

In order to subscribe to events, jsGrid.AttachEvent method is used.

The jsGrid object itself can be fetched from a property of the JSGrid container element. ID of this container element can be determined via g_SPGridInitInfo global variable.

var viewId = "{GUID-OF-YOUR-VIEW}";
var jsGridContainer = $get("spgridcontainer_" + g_SPGridInitInfo[viewId].jsInitObj.qualifier)
var jsGrid = jsGridContainer.jsgrid;
jsGrid.AttachEvent(SP.JsGrid.EventType.OnEntryRecordPropertyChanged, function() {
  debugger;
});

I ran this code in my browser JS console, and then tried changing the entry record row. As soon as I clicked the "BMW W5" value, debugger popped out:

Nice, now we can explore stack trace, arguments, and think of what else we can do. For example, here's the screenshot of arguments that were passed to this event handler:

As you can see, the arguments describe the change: the name of the field, old value and new value, type of this field, and some other information. JSGrid is not quite documented, but it's API is a very good one!

Up in the stack trace, I found the UpdateProperties method. It turned out, this method can be used for updating any property in a given row. So after some time, I came up with the following code in order to update the master lookup column (lock is used to prevent recursion):

var viewId = '{YOUR-VIEW-GUID-HERE}';
var orderFieldValue = 7; // ID of the master item
var orderFieldInternalName = "Order0"; // Internal name of the lookup field
var jsGridContainer = $get("spgridcontainer_" + g_SPGridInitInfo[viewId].jsInitObj.qualifier)
var jsGrid = jsGridContainer.jsgrid;
var lock = 0;
jsGrid.AttachEvent(SP.JsGrid.EventType.OnEntryRecordPropertyChanged, function(args) {
    if (lock == 0) {
        lock = 1;
        var update = SP.JsGrid.CreateUnvalidatedPropertyUpdate(args.recordKey,orderFieldInternalName,orderFieldValue,false);
        jsGrid.UpdateProperties([update], SP.JsGrid.UserAction.UserEdit);
        lock = 0;
    }
});

I tested the code right from the console, and it worked!

Final step was to hide the lookup column. This was quite simple:

jsGrid.HideColumn(orderFieldInternalName);

Polishing off

Alright, now it works, but some cleanup is definitely needed:

1. "Add column" button is not required.
2. JSGrid columns can be filtered and sorted, and this feature somehow overrides my Order filter.
3. "Stop editing this list" link should be hidden.

For hiding the "Add column", I had to modify the script that I used to switch the form into edit view.

_spBodyOnLoadFunctions.push(function()
{
    var viewId = "{YOUR-VIEW-GUID-HERE}";
    g_SPGridInitInfo[viewId].jsInitObj.canUserAddColumn = false;
    g_SPGridInitInfo[viewId].jsInitObj.showAddColumn = false;
    EnsureScriptParams('inplview', 'InitGridFromView', viewId);
});

For preventing the columns from sorting and filtering, I came up with the following code:

var columns = jsGrid.GetColumns();
for (var i in columns)
{
    columns[i].isSortable = false;
    columns[i].isAutoFilterable = false;
}
jsGrid.UpdateColumns(new SP.JsGrid.ColumnInfoCollection(columns));

In order to hide the "Stop editing this list" row, I had to wrap it into a div and set style.display='none' for this div:

var hero = $get('Hero-' + ctx.wpq);
var wrapper = document.createElement("div");
wrapper.style.display = 'none';
hero.parentNode.insertBefore(wrapper, hero);
wrapper.appendChild(hero);

Finally, some wrapping is required in order to ensure that the timing of applying customization is correct. I ended up using CSR OnPostRender event for that.

Final code

So, here's the final complete JS code for Script Editor Web Part, for making all the changes I described in the article (XLV filtering is not included because it is done declaratively, see the chapter "Filtering XLV to show only relevant items"):

<script type="text/javascript">
_spBodyOnLoadFunctions.push(function()
{
    var viewId = '{YOUR-VIEW-GUID-HERE}';
    var orderFieldValue = GetUrlKeyValue("ID"); // ID of the master item
    var orderFieldInternalName = "Order0"; // Internal name of the lookup field

    // Switch to edit view and hide "Add column" button
    SP.SOD.executeFunc('inplview', 'InitGridFromView', function() {
        g_SPGridInitInfo[viewId].jsInitObj.canUserAddColumn = false;
        g_SPGridInitInfo[viewId].jsInitObj.showAddColumn = false;
        InitGridFromView(viewId);
    });


    SP.SOD.executeFunc('clienttemplates.js', 'SPClientTemplates.TemplateManager.RegisterTemplateOverrides', function() {
    
        var done = false;
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
        
            OnPostRender: function(ctx) {
            
                if (ctx.view != viewId || ctx.enteringGridMode || !ctx.inGridMode || done)
                  return;
                
                // Hide quick links (aka "hero") row
                var hero = $get('Hero-' + ctx.wpq);
                var wrapper = document.createElement("div");
                wrapper.style.display = 'none';
                hero.parentNode.insertBefore(wrapper, hero);
                wrapper.appendChild(hero);
                
                // Fetch JSGrid object
                var jsGridContainer = $get("spgridcontainer_" + g_SPGridInitInfo[viewId].jsInitObj.qualifier)
                var jsGrid = jsGridContainer.jsgrid;
                
                // Automatically update master lookup column
                var lock = 0;
                jsGrid.AttachEvent(SP.JsGrid.EventType.OnEntryRecordPropertyChanged, function(args) {
                    if (lock == 0) {
                        lock = 1;
                        var update = SP.JsGrid.CreateUnvalidatedPropertyUpdate(args.recordKey,orderFieldInternalName,orderFieldValue,false);
                        jsGrid.UpdateProperties([update], SP.JsGrid.UserAction.UserEdit);
                        lock = 0;
                    }
                });
                
                // Make columns non-sortable and non-filterable
                var columns = jsGrid.GetColumns();
                for (var i in columns)
                {
                    columns[i].isSortable = false;
                    columns[i].isAutoFilterable = false;
                }
                jsGrid.UpdateColumns(new SP.JsGrid.ColumnInfoCollection(columns));
        
                // Hide master lookup column
                jsGrid.HideColumn(orderFieldInternalName);
                
                done = true;
            }
            
        });
        
    });
    
});
</script>

Note: if you're creating a custom page layout, instead of the line:

var orderFieldValue = GetUrlKeyValue("ID"); // ID of the master item

please use this line:

var orderFieldValue = <SharePointWebControls:NumberField ControlMode="Display" FieldName="1d22ea11-1e32-424e-89ab-9fedbadb6ce1" runat="server"/>; // ID of the current page

Minimal Download Strategy

With minimal download strategy turned on, Script Editor Web Part doesn't work very well. So for ordinary list forms, we need to change the code a little, and also put it to a separate file, and then attach this file to the page somehow, e.g. via JSLink.

So I created a js file /Style Library/JSGrid-masterDetails.js and modified is as follows:

SP.SOD.executeFunc('clienttemplates.js', 'SPClientTemplates.Utility.ReplaceUrlTokens', function() {

    function init()
    {

       // all the code here, starting from _spBodyOnLoadFunctions.push(... and so on.

    }

    RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~site/Style Library/JSGrid-masterDetails.js"), init);
    init();

});

Then, to attach it to your list form, edit the list form page, then edit the XLV webpart:

In the Miscellaneous section, locate the JSLink property, and put the link to your js file there:

The link should be like this: ~site/Style Library/JSGrid-masterDetails.js.

Source code

• You can download the source code as a ZIP archive. - 5.9 KB

The archive contains following files:

• JSGrid-masterDetails.js - code for attaching to list edit form via JSLink
• XLV_ListEditForm.aspx - sample markup for XLV on a list form
• CustomPageLayout.aspx - sample markup for the custom page layout. In addition to things explained in the article, it also uses custom display template via CSR, so that list is printer-friendly in display mode

Please don't forget that the markup in aspx files is dependent on actual lists, so these files won't work on your system and are provided mostly as a reference.

Conclusion

JSGrid has great and flexible API and can provide very convenient, Excel-like interface for users. You might have noticed, that I didn't have much problems with JSGrid itself. The wrapping is difficult, but JSGrid itself is amazing!

In case of master-details forms, JSGrid provides wonderful opportunity to use Excel-like interface both with custom page layouts and ordinary list forms.