Product View Calculations in the Calculations Developer Sample Form [InfoPath 2003 SDK Documentation]

Article
06/27/2007

Applies to:

Microsoft Office InfoPath 2003

For information on Service Pack 1 features for working with calculations, see the "Calculating Data" section in the InfoPath 2003 Help.

Microsoft Office InfoPath forms provide both form events and data validation events that allow the form designer to write code that responds to user actions that affect the whole form (such as loading a form), and code that responds to actions that occur as a user enters data and makes selections in the form. The Calculations developer sample form uses both types of events in Products view.

The Products view of the Calculations developer sample form uses the two types of InfoPath events to respond to actions as they occur in three different sections of the form:

Products The section that is bound to the productCategory element in the form's XML data source. This section is implemented as a repeating table nested within a repeating section.
Items The section that is bound to the itemType element in the form's XML data source. This section is implemented as a repeating table nested within a repeating section.
Calculations The section that contains multiple fields that are bound to calculated element values within the form's XML data source. All of the fields in this section are text boxes.

When you are investigating how the Products view of the Calculations developer sample form implements calculations, the main areas to examine are the form events and data validation events in the Products section, and the data validation events in the Items section.

Form Events

When you open the Calculations developer sample form, the OnLoad event is used to check if the form's underlying XML document contains any data using the selectNodes method of the XML Document Object Model (DOM), accessed through the DOM property of the XDocument object. If the form contains product data, it calls the InsertProduct custom function, which is used to populate the Products secondary data source with the product data. If there is no data in the form's underlying XML document, then a blank form is opened and the secondary data source is not populated.

The following is the JScript code for the event handler that is used to respond to the Calculation form's Onload event:

function XDocument::OnLoad(eventObj)
{
   if (XDocument.IsDOMReadOnly)
   {
      return;
   }

   var productNodes = XDocument.DOM.selectNodes(
                "/my:Calculations/my:productCategory/my:product");
   var productNode = null;

   productNodes.reset();
   while ((productNode = productNodes.nextNode()) != null)
   {
      var productIDNode = productNode.selectSingleNode("my:productID");
      var productNameNode = productNode.selectSingleNode("my:productName");

      InsertProduct(productIDNode.nodeTypedValue,
       productNameNode.nodeTypedValue);
   }
}

Note To create an OnLoad event handler in a form of your own, begin with the form in design mode. On the Tools menu, point to Script, and then click On Load Event. Enter your code in the Microsoft Script Editor (MSE) without modifying the function header.

Data Validation Events in the Products Section

When a user adds or modifies the values of the Product ID, Name, or Price fields in the Products section of the Products view, the OnAfterChange event occurs. The OnAfterChange events of the fields in the Products section all call the CalcAllItemNamePrices custom function to update items in the Items section of Products view with changes to product information that the user made in the Products section. The following is the JScript code for the OnAfterChange event handler, which is used to respond to the OnAfterChange event of the Product ID:

function msoxd_my_productID::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcAllItemNamePrices();
   UpdateProductID(eventObj);
}

The CalcAllItemNamePrices custom function sets a reference to all of the items contained in the form's underlying XML document using the selectNodes method of the XML DOM. It then loops through the collection of item nodes and calls the CalcItemNamePrice custom function, passing each item node as an argument. The following is the JScript code for the CalcAllItemNamePrices custom function:

function CalcAllItemNamePrices()
{
   var itemNodes = XDocument.DOM.selectNodes(
    "/my:Calculations/my:itemType/my:item");

   itemNodes.reset();
   while ((itemNode = itemNodes.nextNode()) != null)
      CalcItemNamePrice(itemNode);
}

The CalcItemNamePrice custom function sets references to the values contained in the item node passed in to the function. CalcItemNamePrice also sets a reference to a collection of nodes representing all of the products contained in the form's underlying XML document. The function then loops through the collection of product nodes, comparing the ID of the item node to each ID in the collection of product nodes until a match is found. When a matching product ID is found in the collection of product nodes, the values for the product name and price in the item node are updated to reflect the values of the product node. If no matching product node is found, the item node is updated with default values. The following is the JScript code for the CalcItemNamePrice custom function:

function CalcItemNamePrice(itemNode)
{
   var itemIDNode = itemNode.selectSingleNode("my:itemID");
   var itemNameNode = itemNode.selectSingleNode("my:itemName");
   var itemPriceNode = itemNode.selectSingleNode("my:itemPrice");
   var productNodes = XDocument.DOM.selectNodes(
    "/my:Calculations/my:productCategory/my:product");
   var productNode = null;

   productNodes.reset();
   while ((productNode = productNodes.nextNode()) != null)
   {
      var productIDNode = productNode.selectSingleNode("my:productID");
      var productNameNode = productNode.selectSingleNode("my:productName");
      var productPriceNode = productNode.selectSingleNode("my:productPrice");

      if (productIDNode.nodeTypedValue == itemIDNode.nodeTypedValue)
      {
         itemNameNode.nodeTypedValue = productNameNode.nodeTypedValue;
         itemPriceNode.nodeTypedValue = productPriceNode.nodeTypedValue;
         return;
      }
   }

   itemNameNode.nodeTypedValue = "";
   itemPriceNode.nodeTypedValue = 0;
}

In addition to the CalcAllItemNamePrices custom function, when the user edits the Product ID or Name fields, the OnAfterChange event handler also calls the UpdateProductID or UpdateProductName custom function to update the changes to the secondary data source containing the product list. The following is the JScript code for the UpdateProductID and the UpdateProductName custom functions:

function UpdateProductID(eventObj)
{
   var oldProductID = eventObj.OldValue;
   var newProductID = eventObj.Site.nodeTypedValue;
   var productName = eventObj.Site.parentNode.selectSingleNode("my:productName").nodeTypedValue;

   if (eventObj.Operation == "Delete")
      DeleteProduct(oldProductID);

   if (eventObj.Operation == "Insert")
      InsertProduct(newProductID, productName);
}

function UpdateProductName(eventObj)
{
   var productID = eventObj.Site.parentNode.selectSingleNode("my:productID").nodeTypedValue;
   var productName = eventObj.Site.nodeTypedValue;

   if (eventObj.Operation == "Delete")
      DeleteProduct(productID);

   if (eventObj.Operation == "Insert")
      InsertProduct(productID, productName);
}

Important The Operation property of the DataDOMEvent object is used to determine the type of XML DOM operation that is occuring. Note that if an XML DOM node is being updated, both a "Delete" and an "Insert" operation will occur. This is known as double notification.

The UpdateProductID and UpdateProductName custom functions delete the modified product node from the secondary data source and insert a new node with the modified values, using the InsertProduct and DeleteProduct custom functions.

Note To create an OnAfterChange event handler or another data validation event handler in a form of your own, open the Properties dialog box for a control in design mode, click Data Validation, select an event in the Events list, and then click Edit to create an empty function declaration in the Microsoft Script Editor (MSE). Enter your code without modifying the function header.

Data Validation Events in the Items Section

When a user adds or modifies the values of the ID, Price, or Quantity fields in the Items section of Products view, the OnAfterChange event occurs. When the user enters or edits the ID field, the OnAfterChange event calls the CalcItemNamePrice function (shown earlier) to fill in the Name and Price fields from the data stored in the secondary data source:

function msoxd_my_itemID::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcItemNamePrice(eventObj.Site.parentNode);
}

When the user enters or edits the Price or Quantity fields, the OnAfterChange event calls the CalcItemTotal function to update the line item total. This calculation includes the discount.

function msoxd_my_itemPrice::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcItemTotal(eventObj.Site.parentNode);
}

function msoxd_my_itemQuantity::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcItemTotal(eventObj.Site.parentNode);
}

function CalcItemTotal(itemNode)
{
   var totalNode = itemNode.selectSingleNode("my:itemTotal");
   var nQuantity = itemNode.selectSingleNode("my:itemQuantity").nodeTypedValue;
   var nPrice = itemNode.selectSingleNode("my:itemPrice").nodeTypedValue;
   var nDiscount = XDocument.DOM.selectSingleNode("/my:Calculations/my:discount").nodeTypedValue;

   if (isNaN(nQuantity))
      nQuantity = 0;

   if (isNaN(nPrice))
      nPrice = 0;

   if (isNaN(nDiscount))
      nDiscount = 0;

   totalNode.nodeTypedValue = nQuantity * nPrice * (1 - nDiscount);
}

When the calculated line item total stored in the Total field changes, the OnAfterChange event refreshes the order total and the other calculated values, such as average line item total, by calling the CalcTotalAverageMinMax function:

function msoxd_my_itemTotal::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcTotalAverageMinMax();
   CalcSalesSummary();
}

function CalcTotalAverageMinMax()
{
   var totalNode = XDocument.DOM.selectSingleNode("/my:Calculations/my:total");
   var averageNode = XDocument.DOM.selectSingleNode("/my:Calculations/my:average");
   var minNode = XDocument.DOM.selectSingleNode("/my:Calculations/my:min");
   var maxNode = XDocument.DOM.selectSingleNode("/my:Calculations/my:max");
   var itemTotalNodes = XDocument.DOM.selectNodes(
    "/my:Calculations/my:itemType/my:item/my:itemTotal");
   var itemTotalNode = null;
   var nTotal = 0;
   var nMin = Number.POSITIVE_INFINITY;
   var nMax = Number.NEGATIVE_INFINITY;

   var nCount = 0;

   itemTotalNodes.reset();
   while ((itemTotalNode = itemTotalNodes.nextNode()) != null)
   {
      var nItemTotal = itemTotalNode.nodeTypedValue;

      if (!isNaN(nItemTotal))
      {
         nTotal += Number(nItemTotal);
         nMin = Math.min(nMin, Number(nItemTotal));
         nMax = Math.max(nMax, Number(nItemTotal));
         nCount++;
      }
   }

   totalNode.nodeTypedValue = nTotal;

   if (nCount > 0)
   {
      averageNode.nodeTypedValue = nTotal / nCount;
      minNode.nodeTypedValue = nMin;
      maxNode.nodeTypedValue = nMax;
   }
   else
   {
      averageNode.nodeTypedValue = 0;
      minNode.nodeTypedValue = 0;
      maxNode.nodeTypedValue = 0;
   }
}

When the user edits the discount value, the OnAfterChange event applies the new discount value to all line item totals by calling the CalcAllItemTotals function, which calls the CalcItemTotal function (shown earlier) for each line item:

function msoxd_my_discount::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
      return;

   CalcAllItemTotals();
}

function CalcAllItemTotals()
{
   var itemNodes = XDocument.DOM.selectNodes("/my:Calculations/my:itemType/my:item");
   var itemNode = null;

   itemNodes.reset();
   while ((itemNode = itemNodes.nextNode()) != null)
      CalcItemTotal(itemNode);
}

Share via

Product View Calculations in the Calculations Developer Sample Form [InfoPath 2003 SDK Documentation]

Form Events

Data Validation Events in the Products Section

Data Validation Events in the Items Section

Additional resources