Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to use script for cascading deletes in tables that have a master/detail relationship in InfoPath

How to use script for cascading deletes in tables that have a master/detail relationship in InfoPath

View products that this article applies to.

Introduction

This article describes how to use script for cascading deletions in tables that have a master/detail relationship in Microsoft Office InfoPath. You can use this code to delete detail items that are linked to a master item by using a key field when you delete the master item.

↑ Back to the top

More information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create tables that have a master/detail relationship where related detail items are deleted when the master item is deleted, follow these steps:
  1. Start InfoPath, and then open a new blank form. To do this, follow these steps:

    For InfoPath 2007
    1. In the left pane of the Fill Out a Form dialog box, click Design a Form Template.
    2. In the Design a Form Template window, click Blank, and then click OK.
    For InfoPath 2003
    1. In the left pane of the Fill Out a Form dialog box, click Design a Form.
    2. In the right pane, click New Blank Form.
  2. Insert two repeating tables. To do this, follow these steps:

    For InfoPath 2007
    1. On the Insert menu, click Repeating Table.
    2. Set the number of columns to 2, and then click OK.
    3. Repeat step a and step b to insert another repeating table.
    For InfoPath 2003
    1. On the Insert menu, click More Controls.
    2. Under Insert controls in the Controls task pane, click Repeating Table.
    3. Set the number of columns to 2, and then click OK.
    4. Repeat step b and step c to insert another repeating table.
  3. Create two more repeating tables, and then bind the two new tables to the same data as the first two repeating tables. To do this, follow these steps:
    1. On the View menu, click Data Source.
    2. In the Data Source task pane, expand group1.
    3. Right-click group2, and then click Repeating Table.
    4. In the Data Source task pane, expand group3.
    5. Right-click group4, and then click Repeating Table.
  4. Create a master/detail relationship between the two tables that you created in step 3. To do this, follow these steps:
    1. Right-click the first table, and then click Repeating Table Properties.

      Note This table is the third table from the top of the form.
    2. In the Repeating Table Properties dialog box, click the Master/Detail tab.
    3. Under Master/detail settings, click Set as master.
    4. In the Master ID box, type Master1, and then click OK.
    5. Right-click the next table, and then click Repeating Table Properties.

      Note This table is the fourth table from the top of the form.
    6. In the Repeating Table Properties dialog box, click the Master/Detail tab.
    7. Under Master/detail settings, click Set as detail.
    8. In the Link to master ID box, select Master1.
    9. Under Link master and detail, click By key field.
    10. Click the Select XPath icon to the right of the Key field (master) box, and then click field1.
    11. Click the Select XPath icon to the right of the Key field (detail) box, click field3, and then click OK.
  5. Insert code to force cascading deletes in the OnAfterChange event for the key field in the details table. To do this, follow these steps:

    For InfoPath 2007
    1. In the Data Source task pane, right-click field1 in group2, click Programming, and then click On After Change Event.
    2. Add the following code to the script-editing window.

      Note Replace the field placeholders and the group placeholders with the names of the groups and the fields that you want to use.
      function msoxd_my_field1::OnAfterChange(eventObj)
{
 // Write code here to restore the global state.
 
 if (eventObj.IsUndoRedo)
 {
  // An undo operation or a redo operation has occurred, and the DOM is read-only.
  return;
 }
 
 //Delete corresponding details if a delete operation has occurred on source group2.
 if(eventObj.Operation == "Delete" && eventObj.Source.nodeName == "my:group2")
 {
  //Block the view update feature to improve performance.
  thisXDocument.View.DisableAutoUpdate();
  
  //Obtain the  value of the keyfield of that row that you want to delete (set as field1 in Designer).
  var nodeKeyFieldMaster = eventObj.Source.selectSingleNode("my:field1");
          
  //Set the selection on all nodes in Detail.
  var nodesDetail = XDocument.DOM.selectNodes("/my:myFields/my:group3/my:group4");
 
  //View each row in Detail, and then delete the row if the key field in the detail matches the key field in the master.
  for (i=0; i< nodesDetail.length; i++)
  {
   //Obtain the row.
   var row = nodesDetail.item(i);
   
   //Obtain the  value of the keyfield for the current row  (set as field3 in Designer).
   var nodeKeyFieldDetail = row.selectSingleNode("my:field3");
 
   //Delete the row if the keyfield in the detail matches the keyfield of the deleted row in the master.
   if(nodeKeyFieldDetail.text == nodeKeyFieldMaster.text)
   {
    row.parentNode.removeChild(row); 
   }

  //Re-enable the view update.
  thisXDocument.View.EnableAutoUpdate();

  }
 } 
}
    3. On the File menu, click Save.
    4. On the File menu, click Exit.
    For InfoPath 2003
    1. In the Data Source task pane, right-click field1 in group2, and then click Properties.
    2. In the Field or Group Properties dialog box, click the Validation and Event Handlers tab.
    3. In the Events list, click OnAfterChange, and then click Edit.
    4. Add the following code to the script-editing window.

      Note Replace the field placeholders and the group placeholders with the names of the groups and the fields that you want to use.
      function msoxd_my_field1::OnAfterChange(eventObj)
{
 // Write code here to restore the global state.
 
 if (eventObj.IsUndoRedo)
 {
  // An undo operation or a redo operation has occurred, and the DOM is read-only.
  return;
 }
 
 //Delete corresponding details if a delete operation has occurred on source group2.
 if(eventObj.Operation == "Delete" && eventObj.Source.nodeName == "my:group2")
 {
  //Block the view update feature to improve performance.
  thisXDocument.View.DisableAutoUpdate();
  
  //Obtain the  value of the keyfield of that row that you want to delete (set as field1 in Designer).
  var nodeKeyFieldMaster = eventObj.Source.selectSingleNode("my:field1");
          
  //Set the selection on all nodes in Detail.
  var nodesDetail = XDocument.DOM.selectNodes("/my:myFields/my:group3/my:group4");
 
  //View each row in Detail, and then delete the row if the key field in the detail matches the key field in the master.
  for (i=0; i< nodesDetail.length; i++)
  {
   //Obtain the row.
   var row = nodesDetail.item(i);
   
   //Obtain the  value of the keyfield for the current row  (set as field3 in Designer).
   var nodeKeyFieldDetail = row.selectSingleNode("my:field3");
 
   //Delete the row if the keyfield in the detail matches the keyfield of the deleted row in the master.
   if(nodeKeyFieldDetail.text == nodeKeyFieldMaster.text)
   {
    row.parentNode.removeChild(row); 
   }

  //Re-enable the view update.
  thisXDocument.View.EnableAutoUpdate();

  }
 } 
}
    5. On the File menu, click Exit to close the editing window.
    6. Click OK to close the Field or Group Properties dialog box.

Preview the form to demonstrate how the code works.To do this, follow these steps:
  1. For InfoPath 2007

    On the File menu, point to Preview, and then click Form.

    For InfoPath 2003

    On the File menu, point to Preview Form, and then click Default
  2. Insert some test values into the first table on the form. This table is the Master table. To do this, follow these steps:
    1. In the first column of the first row, type Test1.
    2. In the second column of the first row, type Master1.
    3. Under the row where you just typed the data, click Insert item to add a new row to the first table.
    4. In the first column of the second row, type Test2.
    5. In the second column of the second row, type Master2.
    6. If you want to add more rows, repeat steps 2c through 2e.
  3. Insert some test values into the second table on the form. This table is the Details table. To do this, follow these steps:
    1. In the first column of the first row, type Test1.
    2. In the second column of the first row, type Detail1.
    3. Under the row where you just typed the data, click Insert item to add a new row to the second table.
    4. In the first column of the second row, type Test2.
    5. In the second column of the second row, type Detail2.
    6. If you want to add more rows, repeat steps 2c through 2e.
  4. Test the Master/Detail tables. Select each row in the third table, one at a time. The fourth table shows only the rows from the second table that have the same value in the first column as the selected row in the third table.
  5. Delete a row from the Master table. To do this, follow these steps:
    1. In the third table, select the second row where the first column contains "Test2".
    2. Click the arrow next to the selected row, and then click Remove group2.

      Note The rows that have the same text in the first column are automatically deleted from the fourth table by the sample code. For example, "Test2" is deleted.

↑ Back to the top

Keywords: KB892952, kbhowto, kbprogramming, kbcode, kbcodesnippet, kbxml

↑ Back to the top

Article Info
Article ID : 892952
Revision : 3
Created on : 9/22/2011
Published on : 9/22/2011
Exists online : False
Views : 448