1
Master- Details data handling in SharePoint - A
Background
Master-Detail
data presentation is a type of data representation requires the presence of at
least two data structures - master and detail. These tables are linked by a
one-to-many relationship, i.e., a single row in the first data store can be
related to one or more rows in the second data store, but a row in the second
table can be related to only one row in the first table. Master and detail
tables are also called parent and child tables. SharePoint prospective this
relation is from a list item to many items to other list. Here it is explained
different options to handle master and details data operations with SharePoint.
2
Master details Data Presentation in SharePoint
There is no out of the box controls
available in SharePoint to present these type of data structure however below
are some of the techniques to achieve this.
In this article it is assumed customer information stored in
Installation List acting as master. Multiple meters for a customer are stored
in ‘Meter Detail’ treat as the detail list. One to many relationship
established between Installation list and Meter details list using the list
column – Installation ID
2.1
Establish
parent child relations using Data view web part connection.
Consider
Master details are stored – ‘Installations’ list and details are stored in
‘MeterDetails’ and these lists are placed in a SharePoint Page using data view
webapart.
In
below view, multiple meter details for a customer is displayed by connecting Installation
DVWP with Meter details DVWP. (DVWP –
data view web part)
And
here is the web part connections established between the data view web parts
Customer master and Meter Details.
And
Installation ID is the field used here to connect these 2 lists.
Here
InStallationID is Lookup column in MeterDetails List, Linked to InstallationID
from Installations List as below.
2.2 Display Details in view by modifying
display templates for column.
It
will be neat and clear look if we include the required details view along with
master view itself as below.
It is
included in the master view itself by modifying column view display template.
Nintex
form repeating section data got stored in a Multiple Line of text column in XML
Format. Below is the format it stored. MeterInfo Column contain all meter
details populated from Nintex Form.
<?xml
version="1.0"
encoding="utf-8"?><RepeaterData><Version
/><Items><Item><ShowMeter
type="System.Boolean">True</ShowMeter><GMAS
type="System.String">GMAS</GMAS><ECNo
type="System.String">EC1200</ECNo><AccountNo
type="System.String">AN2300</AccountNo><MeterCoNo
type="System.String">Mtr100</MeterCoNo><ECSerialNo
type="System.String">ESSer</ECSerialNo><Rate
type="System.Double">2323</Rate><_x0033_73c86b1-3111-490e-a842-34ca757901b4
type="System.String">#Value!</_x0033_73c86b1-3111-490e-a842-34ca757901b4><GasPressure
type="System.String">GP111</GasPressure><MeterTypeCodeLKP
type="System.String">1</MeterTypeCodeLKP><Index_Type_Old
type="System.String">IT100</Index_Type_Old><RUID
type="System.String">RU1000</RUID><EndCor
type="System.String">EC200</EndCor><US
type="System.String">US111</US><Full_GMAS
type="System.String">FULL GMAS</Full_GMAS><Orig_Meter
type="System.String">OR100</Orig_Meter><f9fb0908-0383-42bd-aa00-6a9280ecf3d0
type="System.String">IT100</f9fb0908-0383-42bd-aa00-6a9280ecf3d0><TerminalServerPort
type="System.String">123</TerminalServerPort><MeterTypeCode
type="System.String">fdf</MeterTypeCode></Item></Items></RepeaterData>
This
XML need to be transformed in a tabular format to display the detail section
along with master data.
Below
is the script used to achieve this . This script is placed inside a content
editor web part/Script Editor webpart,
inside the page.
<script
type="text/javascript"
src="https://apps-dev10.piedmontng.com/sites/sitesurvey/SiteAssets/jquery-1.11.3.min.js"
></script>
<link rel="stylesheet"
type="text/css"
href="https://apps-dev10.piedmontng.com/sites/sitesurvey/SiteAssets/metertable.css">
<script
type="text/javascript">
SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
Templates: {
Fields: {
'MeterInfo': {
'View':
repeatingSectionViewTemplate
}
}
}
});
function
repeatingSectionViewTemplate(ctx) {
var xml =
ctx.CurrentItem["MeterInfo"];
var decodedxml =
xml.DecodeXMLNotation();
var htm = "";
xmlDoc = $.parseXML( decodedxml );
$xml = $( xmlDoc );
$xml.find("Item").each(function()
{
htm = htm +
"<tr><td>" + $(this).find("MeterCoNo").text() +
"</td><td>" + $(this).find("GMAS").text() +
"</td><td>" + $(this).find("ECNo").text() +
"</td><td>" + $(this).find("Rate").text() +
"</td><td>" + +$(this).find("TerminalServerPort").text()
+"</td></tr>";
});
return "<table border='1' class
='gridmtable'><tr><th><b>Meter</b></th><th><b>GMAS</b></th><th><b>ECNO</b></th><th><b>Rate</b></th><th><b>Port</b></th></tr>"
+ htm +"</table>";
};
//Replaces
html notation to their equivalent xml escape characters.
String.prototype.DecodeXMLNotation
= function () {
var output = this;
if ($.trim(output) != "") {
output = output.replace(/'/g,
"'").replace(/"/g, '"').replace(/>/g, '>').replace(/</g,
'<').replace(/&/g, '&');
}
else {
output = "";
}
return output;
};
</script>
3
Update Master-details Data structure in
SharePoint
The
best way to modify Master-detail structure is by using Nintex or Infopath form.
Here I have used Nintex Form.
3.1
Edit
data using Nintex form
Details sections can be
placed using repeating table control. This would help to manipulate items
inside the detail section at run time.
Here
Meter details are placed in repeating section, where run time can add more
entries. (Add new Meter link in above figure)
3.2
Store
Details in different list using Nintex
workflow
Now
details are stored in the Master list itself
in XML format (given in section 2.2). Using a workflow these details can be
pushed to MterDetails list , where each meter details can be stored in separate
row. This workflow can be triggered over the update of Master record so that
details are updated automatically, whenever there is a change.
Below
is the Nintex Workflow used to transfer meter details to a different list.
Steps:
1. Store the meter info details in XML collection variable in Nintex
2. Delete all existing records from Meter details
3. Traverse to each details in XML collection
4. Query XML and get field values
5. Insert field values in Meter Details as below
6. Repeat the operation for all items in collection
