Update a Triggered Table

2006-12-08T20:44:00Z

Querying and submitting to a database is a common scenario using InfoPath. In fact, it is very easy to develop a solution by designing a form from a SQL Server data connection, and then dropping the desired fields from Data Source task pane into the view. Then we can query the database and modify the desired records, or create and fill in a new record to submit to the database.

Following is an example of a form based on Employees table of the Northwind database:

While connecting to a database is quite simple, suppose you want to add a simple insert or update trigger to the database table to identify whether LastName is unique:

CREATE TRIGGER [Surname Check] ON [dbo].[Employees]
FOR INSERT, UPDATE
AS
update employees
set LastName = LastName + ' (not unique)'
where LastName in
    (select LastName
     from employees
     group by LastName
     having count(*) > 1)

But now, if we attempt to add an employee with a non-unique LastName (e.g. “Davolio”) we get the following error message:

Clicking Show Details on this error message reveals that the information on primary key are not sufficient, because there are too many rows affected by the update. While we might be adding just one row, the trigger could update more than one row. InfoPath catches that the total number of rows affected by the modification does not match what it expected, and concludes that the information on the primary key is not enough. Because of this, the solution fails. What we need to do is disable the trigger’s count option, so that the total number of modifications returned from the database is equal to the number of rows submitted from InfoPath. This is easy to do using the nocount command.

CREATE TRIGGER [Surname Check] ON [dbo].[Employees]
FOR INSERT, UPDATE
AS
set nocount on
update employees
set LastName = LastName + ' (not unique)'
where LastName in
    (select LastName
     from employees
     group by LastName
     having count(*) > 1)
set nocount off

With this modification in place, it is now possible to add and update rows without errors. InfoPath correctly matches the total number of rows affected by the database as equal to the number of rows the form submitted.

Special thanks to my associate, Davide Tarfanelli, that spent his time with me to solve this task.

Dynamically Alter the Choices of a List Control

2006-10-30T22:40:00Z

Have you ever found yourself wanting to bind the same control to different nodes? One solution to achieve the desired behavior is to create two controls with separate bindings and conditional visibility to hide and show them. This approach doesn't work well for drop-down lists when you have numerous choices because it creates too many permutations of drop-down lists. Instead you can use the xdExtension prefix with code-behind to update the values. This article demonstrates how to use xdExtension to dynamically alter the choices in a list control.

Design the schema:

To create a sample form to demonstrate this technique, we'll need a sample schema. Copy and paste the the schema below into a text editor, and then save the file as FormSchema.xsd.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-10-31T03:54:57" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-10-31T03:54:57" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Sample">
    <xsd:complexType>
        <xsd:sequence>
            <xsd:element name="TextField" type="xsd:string"/>
            <xsd:element name="Set1" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:element name="Set1Item" type="xsd:string"/>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
            <xsd:element name="Set2" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:element name="Set2Item" type="xsd:string"/>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
            <xsd:element name="SelectedDataSource" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:element>
</xsd:schema>

Create the sample form:

Launch InfoPath.
Click Design A Form Template, and then click New From XML Document Or Schema.
In the Data Source Wizard, click Browse, locate and select the FormSchema.xsd file, click Open, and then click Next.
Select No for the question about whether to add another XML Schema, and then click Finish.

Add controls to the form:

Open the Data Source Task Pane
Right-click TextField, and then select Drop-Down List Box.
Right-click SelectedDataSource, and then select Drop-Down List Box.
Right-click Set1, and then select Repeating Table.
Right-click Set2, and then select Repeating Table.

You can add values to the Set1 and Set2 tables to test the form.

To switch between data sources, we will use the SelectedDataSource drop-down list. You will have to manually add two entries (see picture below), one with the value 1 and display name Set1, and another with the value 2 and display name Set2.

Establish the set of default values for the drop-down list:

Right-click the TextField drop-down list box, and then choose Drop-Down List Box Properties.
Select Look Up Values In The Form's Data Source.
Click the Select XPath button to the right of the Entries field.
Select Sample/Set1/Set1Item, and then click OK. Note: this is just the default value; the actual switch will happen in code dynamically.

Here is what the form looks like with some layout and formatting:

Create the function to obtain the data source items:

Choose Programming | Microsoft Script Editor from the Tools menu (or press Alt+Shift+F11).
Add the following function to your code:

function findDataCollection()
{
var selectedDataSource = XDocument.DOM.selectSingleNode("/ns1:Sample/SelectedDataSource");
return XDocument.DOM.selectNodes("/ns1:Sample/Set"+selectedDataSource.text+"/Set"+selectedDataSource.text+"Item");
}

Save, and then close the Microsoft Script Editor.

This function returns a set of items which we will use to fill the drop-down entries.

Modify the XSL file:

Choose Extract Form Files from the File menu.
Close InfoPath.
Using a text editor, open View1.xsl.
Find:

<xsl:if test="not(Set1/Set1Item[.=$val] or $val='')">
    <option selected="selected">
        <xsl:attribute name="value">
            <xsl:value-of select="$val"/>
        </xsl:attribute>
        <xsl:value-of select="$val"/>
    </option>
</xsl:if>
<xsl:for-each select="Set1/Set1Item">
    <option>
        <xsl:attribute name="value">
            <xsl:value-of select="."/>
        </xsl:attribute>
        <xsl:if test="$val=.">
            <xsl:attribute name="selected">selected</xsl:attribute>
        </xsl:if>
        <xsl:value-of select="."/>
    </option>
</xsl:for-each>

Replace with:

<xsl:if test="$val=''"> 
    <option selected="selected">
        <xsl:attribute name="value">
            <xsl:value-of select="$val"/>
        </xsl:attribute>
        <xsl:value-of select="$val"/>
    </option>
</xsl:if>
<xsl:for-each select="xdExtension:findDataCollection()">
    <option>
        <xsl:attribute name="value">
            <xsl:value-of select="."/>
        </xsl:attribute>
        <xsl:if test="$val=.">
            <xsl:attribute name="selected">selected</xsl:attribute>
        </xsl:if>
        <xsl:value-of select="."/>
    </option>
</xsl:for-each>

At this point the drop-down uses the findDataCollection() function to determine the data source of its entries. This causes the list of items to be dependant on the value selected in SelectedDataSource. With this solution, if we select a value and then switch the data source, then the old value remains selected. If we want to clear TextField, we can easily use a rule or the onAfterChange event on the SelectedDataSource field.

Conclusion:

After these simple steps, the values that can be selected for TextField depends on the value of the SelectedDataSource field. The procedure is almost the same as if you wanted to dynamically bind a field to a control (e.g. Drop-Down List Box, Expression Box, Text Box, etc.). However, there is a side effect: after the change is done in the XSL, it is no longer possible to use the InfoPath designer to modify the dynamically bound-control. With this in mind, it is better to set everything you need (data validation, rules, conditional formatting, etc.) before you manually edit the XSL file.

Antonio Gaetani

Update a Triggered Table

Dynamically Alter the Choices of a List Control