When a user enters a product number, you want it to be checked against the product catalog to determine whether it is valid. The script functions shown in Example 11-2 will accomplish this. This script can be seen in action using the order_xml.xsn example file. The OnValidate function for the item number field calls another function, lookupCatalog, to determine whether the product number exists in the catalog. The line if (!desc) determines whether the desc variable contains a null value. If it does, it was not found in the catalog and an error is reported.

function msoxd__item_number_attr::OnValidate(eventObj) {
  var desc = lookupCatalog(eventObj.Site.nodeTypedValue)
  if (!desc)
    eventObj.ReportError(eventObj.Site,
      "Invalid product number.", false)
}

function lookupCatalog(prodNum) {
  var catDom = XDocument.GetDOM("catalog");
  var nameNode = catDom.selectSingleNode(
        "//product[@num='" + prodNum + "']/name");
  if (nameNode)
    return nameNode.nodeTypedValue;
}

The lookupCatalog function first assigns the DOM of the catalog document to the variable catDom using the GetDOM method and referring to the document by the name specified for the secondary data source (catalog). It then selects from the catalog the name node that has that product number. It does this by stringing together an XPath expression that will look, for example, like: //product[@num='334']/name

The brackets in the XPath expression surround what is known as a predicate, which limits results to nodes that meet a certain criterion; in this case, those whose num attributes are equal to 334.^[1]

If the function finds an appropriate node, it returns the node’s typed value. If it does not find such a node, it returns a null value, signaling to the OnValidate function that the product was not found in the catalog.

Suppose that in addition to validating the product number, you want to fill in the product description automatically with the name data found in the catalog. The script function shown in Example 11-3 will accomplish this.

function msoxd__item_number_attr::OnAfterChange(eventObj) {
  if (eventObj.IsUndoRedo) return;
  var descNode = eventObj.Site.selectSingleNode("../ns1:desc")
  var desc = lookupCatalog(eventObj.Site.nodeTypedValue)
  if (desc)
    descNode.nodeTypedValue = desc
  else
    descNode.nodeTypedValue = ""
}

The function is triggered by the OnAfterChange event, which occurs after the OnValidate event. It is very similar to the OnValidate function, in that it calls the lookupCatalog to retrieve the product description. This function, however, updates the description node with the name retrieved from the catalog. Note that it uses the relative XPath expression ../ns1:desc to retrieve the description. The .. step accesses the parent, while the ns1:desc step accesses the desc child of the parent.

Rather than validating a product number after it is entered, you could forestall errors by including the valid values in a drop-down list. This technique does not require a script; it is simply a matter of setting up the list control.

To test this out on our order form example:

For example, suppose we have an Access database, catalog.mdb, which has a table named CATALOG, shown in Figure 11-3.

Figure 11-3. The CATALOG table

This process can be seen in action using the order_db.xsn example form. First, we set up a secondary data source for the database. This process is identical to the process for setting up a database as a primary data source, described in 9.3.6.4, “Starting from a database”, on page 206. In our example, we specified a secondary data source named catalogdb that pointed to the catalog.mdb database, and chose all the table columns.

Tip

In general, you’ll want to set up a secondary data source for each type of query that you will be performing on the data, rather than for each database or table. Doing so allows you to select only the tables, columns and joins that are relevant to the query, reducing the amount of data returned and ensuring that the data is joined appropriately.

Once a secondary data source is set up, we can use a very similar script to what we used when selecting from a standalone XML document. The only changes required are to the lookupCatalog function, which is shown in Example 11-4.

function lookupCatalog(prodNum)
{
  var catdbDom = XDocument.GetDOM("catalogdb");
  catdbDom.setProperty("SelectionNamespaces",
  'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/'
           + 'ado/dataFields"'),
  var nameNode = catdbDom.selectSingleNode(
                 "//d:CATALOG[@NUM='" + prodNum + "']/@NAME");
  if (nameNode)
    return nameNode.nodeTypedValue;
}

InfoPath will convert the Access results into XML that can then be queried using the same methods we used to query the catalog XML document. An example of the XML output of the query is shown in Example 11-5.

<dfs:myFields xmlns:dfs=
"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:d=
"http://schemas.microsoft.com/office/infopath/2003/ado/dataFields">
  <dfs:dataFields>
    <d:CATALOG NUM="334" NAME="Ten-Gallon Hat" DEPT="ACC"/>
    <d:CATALOG NUM="356" NAME="White Linen Blouse" DEPT="WMN"/>
    <d:CATALOG NUM="522" NAME="Deluxe Golf Umbrella" DEPT="ACC"/>
    <d:CATALOG NUM="999" NAME="Miscellaneous" DEPT="NA"/>
  </dfs:dataFields>
</dfs:myFields>

The output document has a CATALOG element for each row in the CATALOG table, and each column is represented as an attribute. As this is a different structure from that of the catalog.xml file in Example 11-1, we use a different XPath expression in line 8 of Example 11-4 to retrieve the name.

In addition, because the CATALOG element is in its own namespace, we also need to use a prefix in the XPath expression. In order to map the prefix to the namespace, we call the setProperty method to set the SelectionNamespaces property, as shown in lines 4 through 6 of Example 11-4.

The built-in database support described in the previous section will work for both Access and SQL Server databases. There are a number of techniques for accessing other kinds of databases using InfoPath, such as:

We won’t be using the Office Web Services Toolkit that we used with Word. It doesn’t – and doesn’t need to – support InfoPath. That’s because InfoPath hides the complexity of SOAP natively, with built-in support for parsing and generating SOAP messages.

InfoPath communicates with the Web service using SOAP documents whose envelopes contain the input and output message elements. To provide a SOAP-free interface to your scripts, it incorporates those message elements in a document that we refer to as a “Web services interface document”.^[4]

In our case, the document looks something like Example 11-6. It has two namespaces: one with the prefix ws for the Web service message elements, and one with the prefix dfs for InfoPath’s housekeeping elements.

<dfs:myFields xmlns:dfs=
"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
              xmlns:ws="http://www.webserviceX.NET">
  <dfs:queryFields>
    <ws:GetInfoByZIP>
      <ws:USZip>49684</ws:USZip>
    </ws:GetInfoByZIP>
  </dfs:queryFields>
  <dfs:dataFields>
    <ws:GetInfoByZIPResponse>
      <ws:GetInfoByZIPResult/>
    </ws:GetInfoByZIPResponse>
  </dfs:dataFields>
</dfs:myFields>

We’ll describe the document first, then show you how to write the script that creates and reads it.

<dfs:myFields xmlns:dfs=
"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
              xmlns:ws="http://www.webserviceX.NET">
  <dfs:queryFields>
    <ws:GetInfoByZIP>
      <ws:USZip>49684</ws:USZip>
    </ws:GetInfoByZIP>
  </dfs:queryFields>
  <dfs:dataFields>
    <ws:GetInfoByZIPResponse>
      <ws:GetInfoByZIPResult>
        <NewDataSet>
          <Table>
            <CITY>Traverse City</CITY>
            <STATE>MI</STATE>
            <ZIP>49684</ZIP>
            <AREA_CODE>616</AREA_CODE>
            <TIME_ZONE>E</TIME_ZONE>
          </Table>
        </NewDataSet>
      </ws:GetInfoByZIPResult>
    </ws:GetInfoByZIPResponse>
  </dfs:dataFields>
</dfs:myFields>

Example 11-8 shows the script that causes InfoPath to invoke the Web service and update the form with the returned city and state.

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

  //Get a reference to the Web service data source
  var webService = XDocument.DataObjects.Item("GetInfoByZIP")

  webService.DOM.setProperty("SelectionNamespaces", "xmlns:dfs=" +
    "'http://schemas.microsoft.com/office/infopath/" +
    "2003/dataFormSolution'" +
    " xmlns:ws='http://www.webserviceX.NET' ")

  //Set the value of the ZIP code node in the input message
  var zipParm = webService.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/ws:GetInfoByZIP/ws:USZip" )
  zipParm.text = eventObj.Site.text

  //call the Web service
  webService.Query()

  //Set the city and state values in the form
  XDocument.DOM.selectSingleNode( "//ns1:city" ).text =
    webService.DOM.selectSingleNode( "//CITY" ).text
  XDocument.DOM.selectSingleNode( "//ns1:state" ).text =
    webService.DOM.selectSingleNode( "//STATE" ).text
}

line 1

line 3

line 6

lines 8 through 11

lines 14 and 15

line 16

line 19

lines 21 through 24