Master- Details data handling in SharePoint (Parent Child Relation between Lists)

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(/&apos;/g, "'").replace(/&quot;/g, '"').replace(/&gt;/g, '>').replace(/&lt;/g, '<').replace(/&amp;/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

Different Migration Methods to get data from a lotus notes application

1)    Identify Scope

First step of your lotus notes migration is identity the inventory of lotus notes to be migrated. Creation of an application inventory which contain the list of lotus notes forms, structure and volume of data. This confirm what should be migrated to maximize project efficiency.  

Application inventory details may vary based on the scope and complexity of the project. However a typical Lotus notes application migration inventory can contain below fields.

Lotus Database Name	Lotus Form name	Details	Scope or out of Scope	Total Fields	Total Records	Lotus Navigator details	View Details

There should be a mapping between lotus notes application fields to destination system fields including the data type mapping. Main data types used in lotus notes application are text, number, Date/time, dialog list, computed etc. This has to be mapped with the destination application data types. Also need to consider the default value used in the corresponding lotus notes forms. This can be represent in below format for each lotus notes form.

Lotus Notes Field Name	Data Type Lotus Notes	Destination Field name	Destination Field Type	Default Value	Sample Data	Views used

2)    Choose Suitable Migration Method

2.1.                    Via Lotus Client Export Excel 

If you are looking for only few and selected data to be migrated, then you no need to go for any complex methods. You can do it using Lotus notes Client export tool exporting to excel feature. For lotus notes 8.5 client tool, this can be achieved by using by any of the methods.

1. Using Export option in file menu.
   
   
   Tabular text export can be achieved by using this method. Using this data can be exported to excel sheet for further process.

 

1. Using Edit menu option
   Open the view , go to edit menu and opt select all. Again got to edit menu opt copy as => table.
   Now open an excel sheet and paste the records. There is a restriction in number of columns can be copied at a time.

Pros : Non Code solution

Cons : Only possible for limited columns as it is  view dependent. Also data segregation is difficult.

2.2.                    By using XML services of Lotus notes. 

You can get the required application data using built-in XML Services of Lotus notes. These web services are exposed and can be retrieved in browser URL or using any other tool or program.

Pros : Lotus Notes Application Data is available through built-in XML service .

Cons : Unable to get the xsl transformation file which is required to bifurcate columns.  Creation of xslt from scratch is difficult and time consuming.

2.3.                    By using SQL server SSIS program. 

By Using S SSIS Program you can easily import data from lotus notes and export to corresponding data destination. In order to get data from lotus notes IBM Lotus Notes SQL Driver for ODBC can be used

Pros :  Non code solution and easy approach.

Cons : Some versions of  IBM Lotus Notes SQL Driver for ODBC is not compatible with all operating systems.

2.4.                    By using scripts from Lotus client tool.

Lotus script can be used to fetch records from lotus notes. Lotus script is similar to VB script. Given below a sample script to fetch the names of the fields from the lotus notes form. Below script can be used to generate application inventory. We can use this platform to write script to get data from lotus notes and export to other data sources.

Steps:

create a form in lotus and create button in that (create =>Hotspot=>Button)and on click event of a button script you can select as lotus script. And include below code in that.

go to design =>Preview in note to run this program. Enable debug lotus notes script option in tools if you want to debug the code.

Dim session As New NotesSession

                Dim db As NotesDatabase

                Dim fileName As String

                Dim fileNum As Integer

                Dim headerstring As String

                Dim values As String

                Dim selection As String

                Dim collection As NotesDocumentCollection

                Dim doc As NotesDocument

                Dim tipoField As String

                'On Error Resume Next

                Set db = session.CurrentDatabase

                Set oexcel=createobject("Excel.Application")

                oexcel.workbooks.add

                oexcel.visible=True

                frmno=1

                Forall form In db.Forms

                                'Messagebox form.Name

                               

                                intLine=1

                               

                                Set form1 = db.GetForm( form.Name)

                                If  Len(form.Name)>=30 Then

                                                frmNewname = Left(form.Name,29)

                                Else

                                                frmNewname=form.Name

                                End If

                                frmname="Sheet"&frmno

                                frmno=frmno+1

                                oexcel.Sheets(frmname).Name = frmNewname

                                intLine=1

                                Forall fie In form1.Fields

                                                oexcel.cells(intLine,1)=fie

                                               

                                                ftype  = form1.GetfieldType(fie)

                                                oexcel.cells(intLine,2)=ftype

                                                intLine=intLine+1                            

                                End Forall

                                oexcel.Sheets.add

                End Forall           

                'oexcel.SaveAs "C:\TEST.XLS"    

                Set oexcel = Nothing

2.5.                    By using Custom programs. 

We can create Custom Programs to pull data from lotus notes application. This can be achieved by using corresponding lotus notes API’s available from IBM lotus note site. For .net  applications it can be referenced to lotus notes APIs (inerop.domino.dll)

Pros :  Migration procedure is customizable . We can pull all data structure using .net script

Cons : Need to develop migration programs for that. Below is a sample program in .net using

complete code can be contacted from here

code:

    NotesSession lotusNotesSession = new NotesSession();
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            object misValue = System.Reflection.Missing.Value;
            // Opening lotus database for operation
            lotusNotesSession.Initialize("<password>");
            NotesDatabase localDatabase = lotusNotesSession.GetDatabase("<server name>", "<dbname>", false);
          

            Domino.NotesForm form1;
            foreach (Domino.NotesForm frm in localDatabase.Forms)
            {
                form1 = localDatabase.GetForm(frm.Name);
            }
            Domino.NotesDocumentCollection docs;
            Domino.NotesDocument doc;
            Domino.NotesItem Nitem;
            int i;
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            string msg = "";
            int ctr = 0;
            int cln = 0;
            docs = localDatabase.AllDocuments;
            MessageBox.Show(docs.Count.ToString());
            //looping through all documents in Domino
            doc = docs.GetFirstDocument();
            while (doc != null )
            {

                i = 1;
                foreach (Domino.NotesItem ni in doc.Items)
                {
                    Nitem = ni;
                    if (ctr == 0)
                    {
                        //getting field names to store as column header also stores in a list array
                        xlWorkSheet.Cells[ctr + 1, i] = ni.Name;
                        lstExcelColumns.Add(ni.Name);
                        xlWorkSheet.Cells[ctr + 2, i] = ni.Text;
                    }
                    else
                    {
                        cln = getColumnOfsheetfrmlst(xlWorkSheet, ni.Name);
                        xlWorkSheet.Cells[ctr + 2, cln] = ni.Text;
                    }

                    i++;
                }

                ctr++;
                doc = docs.GetNextDocument(doc);

            }