Jimmy Rishe

Interacting with DBXL from code: Qdabra.Dbxl.Client

Jimmy — Mon, 17 Sep 2012 18:30:00 GMT

Occasionally, DBXL customers will ask us for assistance with interacting with DBXL from code. Over the years, we've developed a few different utilities for using DBXL from code, and the one that we ourselves use the most often is the Qdabra.Dbxl.Client library.

The library can be obtained from the following download page:
http://www.infopathdev.com/files/folders/other_subjects/entry82744.aspx

Qdabra.Dbxl.Client is a .NET library that provides a simple interface to all of DBXL's web methods. Using it is quite simple.

You can start off by instantiating an instance of the DbxlClient class, specifying the URL for your DBXL instance:

DbxlClient client = new DbxlClient("http://servername/QdabraWebService");

once you've instantiated a DbxlClient instance with the above line, you can start using it to call webmethods. A DbxlClient object has several properties, most of them corresponding to DBXL's different web services.

So for example, to use the Document Service's GetDocument() method to retrieve a document, you could use the following:

DocumentInfo docInfo; StatusInfo result = client.DbxlDocumentService.GetDocument(1234, out docInfo);

We recommend checking the property values on the returned StatusInfo object to verify that the method call succeeded.

Likewise, if you wanted to call the ReshredDocument() method in the DbxlAdmin web service, you could do the following:

StatusInfo result = client.DbxlAdmin.ReshredDocument(1234);

That's all there is to it!

The Qdabra.Dbxl.Client library will attempt to connect to DBXL using the current user's Windows Authentication credentials. If your website uses some authentication other than Windows Authentication and it is unable to authenticate, a credential prompt will be shown prompting the user for credentials.

If the user's credentials are known ahead of time, they can be passed directly to the DbxlClient object by way of the Credentials parameter.

It is also possible to disable the credential prompt by setting the DbxlClient object's IsNoPromptMode property to true. Doing so will cause the library to simply throw an exception if authentication should fail.

So that's pretty much all there is to know about Qdabra.Dbxl.Client. How are you using code to interact with DBXL? Let us know in the comments section.

XPath Tips and Tricks: Rounding numbers to the nearest N

Jimmy — Fri, 10 Aug 2012 06:25:00 GMT

Hello everyone,

This is the first post in (hopefully) a series of posts on handy formulas that you can use to codelessly boost your forms' functionality.

A common question that comes up on our forums and elsewhere is: How do I round a number to the nearest [some number]?
XPath provides the useful round() function, but that only allows rounding to the nearest whole number. What if you want to round to the nearest 100, or 5, or half?

Luckily, round() enables us to use a simple formula to do this:

Round to the nearest N

round(value div N) * N

Here, value can be a single field in your form, or an entire formula whose result you want to round.

So to round to the nearest 100:

round(value div 100) * 100

to round to the nearest 5:

round(value div 5) * 5

to round to the nearest 1/3:

round(value div (1 div 3)) * (1 div 3)

(or mathematically simplified):

round(value * 3) div 3

It's that simple.

Another question that often comes up is how to round a value to N decimal places. To do this, we can just apply the same concept:

Round to N decimal places:

round(value div 10^-N) * 10^-N

which can be simplified to:

round(value * 10^N) div 10^N

So to round a value to 2 decimal places, you would use this:

round(value * 100) div 100

to round a value to 4 decimal places:

round(value * 10000) div 10000

As a final note, occasionally people want to round a value up or down instead of to the closest number. This is just as simple. Simply modify the above formulas to replace round with ceiling to round up or floor to round down:

Round up to the nearest 100

ceiling(value div 100) * 100

Round down to the nearest 100

floor(value div 100) * 100

That's it! Enjoy!

qRules' DecodeBase64

Jimmy — Tue, 03 Apr 2012 10:26:00 GMT

qRules 4.2 includes a nifty feature that allows you to immediately access the contents of text-based files that users attach to your form. For example, if a user attaches an ordinary text file to the form, you can extract the contents into a field bound to a textbox and immediately allow the user to edit the text, or you can just display the text in your form.

One additional feature that this new command provides is the ability to interpret the attached file as XML and add that XML to some location in one of your form's data sources. In the below screenshot, you can see a simple example of attaching an XML file with a list of clients' names, which are attached to a repeating group in a secondary data source. A textbox below also shows the extracted file text as a reference.

The command's syntax is as follows:

DecodeBase64 /sourcepath=... [/sourceds=...] [/asxml=...] [/destpath=...] [/destds=...] [/excludesourceroot=...]

Parameters surrounded with [square brackets] are optional. Below are the uses of each of the parameters:

sourcepath - The XPath location of the file to decode.

sourceds - The name of the data source where the file to decode is, if it is not in the main data source.

asxml - (Boolean, true by default) When true, treats the file contents as XML and inserts it into a location in one of the form's data sources.

destpath - When asxml is true, the XPath location where the file's XML should be inserted.

destds - When asxml is true, the name of the data source where the file's XML should be inserted, if not the main data source.

excludesourceroot - (Boolean, true by default) When true and using the asxml option, inserts everything except the file XML's root (top) node into the form's data source. When false, the root node is included.

When a file's contents have been successfully extracted as text, the text contents are placed into the QdabraRules Result field.

Do you have an InfoPath scenario that has a use for this command? Let us know in the comments.

Query REST Webservices from InfoPath 2007 with qRules!

Jimmy — Wed, 04 Jan 2012 09:04:00 GMT

It's now well known that InfoPath 2010 has the built-in ability to query REST webservices. But did you know that you can use qRules' QueryData command to query REST data even if you or your users are still using InfoPath 2007? Here's a simple example of how you can do this.

The below tutorial uses Yahoo's Geocode API as an example of a REST webservice to look up geographical detail using a zip code. It assumes you are working with an InfoPath form that already has qRules 2.4 or higher injected into it.

Open the data connection wizard to add a new data connection.
Indicate that it is a connection to Receive data and click Next.
Select XML document as the source for the data and click Next.
Enter http://where.yahooapis.com/geocode?country=us&postal=10001 as the location for the data. The objective here is to provide a valid URL that will return some data from which InfoPath can detect the structure of the returned XML. Click Next.
Select Access the data from the specified location and click Next.
Name the data source Zip Code Lookup, uncheck Automatically retrieve data when the form is opened, and click Finish.
You should now have a new data source with a structure that looks like this:
Now let's add a few controls to the form. Add a new field called ZipCode to your main data source and create a textbox with this. Add a button underneath that and change its label to say Query. Also, drag the City and State fields from the Zip Code Lookup data source and create textboxes or expression boxes with them. These will display the data returned from the webservice. Your form should look like something this when you are done:
Now it's time to add the query logic. Create a new rule on the Query button that does the following:
   Set the Command field in the QdabraRules data source to the value of the formula:
   concat("QueryData /dsname=Zip Code Lookup /url=http://where.yahooapis.com/geocode?country=us&postal=", ZipCode)


Note that this formula constructs a URL to the Yahoo webservice, concatenating in a field value from the form for the zip code.
Preview the form, enter a valid zip code in the textbox and click the Query button. The query results should be displayed down below:

That's it!

Copying specific rows to nested repeating groups

Jimmy — Wed, 21 Dec 2011 08:05:00 GMT

A qRules customer recently came to us with a qRules task that I hadn't seen before. He had a highly nested data source divided into several sections, and he needed to copy some data from a secondary data source. Some of the data belonged in certain sections, and some belonged in other sections.

Now that Insert's /firstparentonly parameter is implemented, it would be possible to copy all of the secondary data rows into every section, and then filter the view to only display the ones that belong in each section, but this isn't ideal. It bloats the main data source, which can slow down InfoPath in a multitude of ways, most significantly in the rendering of the view. In the worst case, this can even crash InfoPath!

The solution we came up with is to use a field in each section to trigger the copy operation for just that section. We use some filtering to copy just the correct number of rows to each section and appropriately copy just the required values.

A form demonstrating this technique is available here (Right-click and select "Save target as..." to download it, and then rename its extension to XSN):

http://www.infopathdev.com/blogs/jimmy/Insert/CopyIntoNested.txt

It contains a trial version of qRules. If the functionality doesn't work, please inject a newer trial or full version into it.

In this particular case, each section in the form has a Code field that corresponds to values in the secondary data source's Code field. For each section, we want to copy the secondary rows with the same code as that section, and leave all of the others.

Here are the salient details of the implementation.

CopyTrigger and StopAction

Each section group has a field called CopyTrigger, which will have rules to initiate the row copy for that particular group. There is also a field called StopAction, which serves as a dummy field which CopyTrigger can use to stop itself from executing.

CopyTrigger Rules

CopyTrigger has three rules. The idea is that CopyTrigger will execute when it is set to the value "go" and then reset itself to blank.

1. In order to stop this action from infinitely repeating, the first rule is simply a rule to stop further rules from executing when CopyTrigger is blank. In InfoPath 2010, every rule must have some action, so here we just give it the arbitrary action of setting the StopAction field to blank.

2. The second rule initiates an Insert command to copy rows into the current section. The /parent XPath is filtered to target only the section that has CopyTrigger = 'go', which should be only the currently executing section. For the row count, the command uses the number of rows in the secondary data source that have the same code as the current section:

3. The final rule simply sets CopyTrigger back to its initial blank value so that it can be run again if needed.

SubChild Rules

SubChild, the actual group that we will be inserting, has rules to copy the actual values into the rows being inserted. This is a typical technique that we use for copying values using the Insert command, but it has a bit of an extra trick in that it's doubly filtered, first to filter out only the source rows that have the same code as the current section, and then a second filter to select the row within those rows that is in the same position as the row currently being inserted. This is the result:

Finally, we add a button that deletes all of the SubChild groups in the form, and then sets all of the CopyTrigger fields to go, and off the operation goes!

I hope this can prove useful to you if you have some need for this kind of scenario.

Insert's new /firstparentonly parameter

Jimmy — Wed, 21 Dec 2011 06:52:00 GMT

In qRules 3.4, we've added a new parameter to the Insert command that is intended to allow it to be more consistent with how people are expecting it to work.

Suppose you have data source with nested repeating groups, like this:

You lay them out on the form with nested repeating sections, preview the form and add a few of the parent sections, which gives you this layout:

Then you run the following qRules command:

Insert /parent=/my:myParentChildForm/my:Parents/my:Parent/my:Children /child=my:Child /count=3

A lot of people would expect this to add three Child groups to every one of the Parent groups, but this is not the case. Instead, qRules only adds three Child groups to the first Parent group, and leaves the rest alone.

In order to achieve the behavior that people are expecting, we've added the /firstparentonly parameter. In order to maintain backwards compatibility with earlier versions, this parameter is treated as true when it's unspecified. When it's specifically specified as false, qRules will insert groups into all of the locations that match the /parent XPath

Insert /parent=/my:myParentChildForm/my:Parents/my:Parent/my:Children /child=my:Child /firstparentonly=false /count=3

Enjoy!

Bulk Upload Files and Images to DBXL

Jimmy — Sun, 07 Aug 2011 16:14:00 GMT

A new feature has been added to the DBXL Migration Tool to allow bulk uploading files and images to DBXL. Once these files are in DBXL, you can query them from DBXL using QueryDB, and include links to them in your XML forms.

Here are the simple steps to using this feature. These assume that you have installed a version of the Migration Tool from Aug. 4, 2011 or later.

1. Open the DBXL Migration Tool from your Start Menu.

2. Enter your DBXL base address (e.g. http://servername/QdabraWebService) in the DBXL Server Root box and click Connect.

3. Select the Custom tab.

4. Select UploadFiles.xml from the Scenario path dropdown box and click Connect.

5. Scroll to the bottom of the Scenario variables pane, and in the cell for the filePath variable, enter the full path of the folder containing the files you want to upload. You can leave all the other variables as they are.

6. Click Run.

7. The Migration Tool will attempt to upload each of the files in the specified folder to DBXL. It will create and submit a QdFile form for each file, and if the upload succeeds, it will create and submit a QdImage form for each of these, containing the url to the QdFile attachment.

8. The tool will display a log of its progress in the pane at the bottom of the tool. If any of the files failed to upload, these will be re-listed at the end of the log.

Using the uploaded files

Once the files have been uploaded to DBXL, you can have your InfoPath forms use QueryDB to query the QdImageDetails table in the #QdabraUtility# database (you can use this alias to access the database, regardless of what its actual name is).

You can use QueryDB to query this table and search for files by their filename. You can use the Url column in the results to provide a link to any one of these files.

Enjoy!

Using DBXL with existing data - Other approaches

Jimmy — Wed, 27 Jul 2011 07:36:00 GMT

A while back, Hilary Stoupa wrote an excellent, detailed blog post about modifying a database table with existing data to work with a DBXL solution. I highly recommend reading it if you are faced with that sort of situation, as it is the go-to guide for blending existing data with new data from DBXL. Here is the link:

http://www.infopathdev.com/blogs/hilary/archive/2009/10/13/use-dbxl-submit-with-existing-sql-data.aspx

In this blog post, I would like to present a few tweaks to her design that might work a little better for some people in some cases. The first is a modification that allows you to have a primary key on your tables and allows easily identifying which data came from your original data, and which came from DBXL. The second tweak is a third alternative to using the trigger and stored procedure approaches Hilary described in her blog.

An IsOriginalData field

Suppose you have a database table with existing data that looks like the image below, and you want to be able to (a) Add data to it using DBXL and (b) Use DBXL to modify the existing data

As in Hilary's blog post, the first step would be to add a new column to store the DBXL DocId, whenever relevant

Once you've added the DocId column, you can now add a computed column that will allow us to identify which rows are original data, and which are data from DBXL, based on whether there is a DocId present or not.
Create a column called IsOriginalData, and in the column's properties, expand the Computed Column Specification setting, and give it this formula:

ISNULL(CASE WHEN DocId IS NULL THEN CAST (1 as BIT) ELSE CAST(0 as BIT) END, CAST(0 as BIT))

The CASE statement will result in a TRUE value when DocId is null (i.e., for original data), and to FALSE when DocId is present. There is an ISNULL function wrapped around this to ensure SQL server that the computed value for this field will always be non-null. And the reason we want to do this is in order to be able to use this field as part of the table's primary key, which requires that all of its fields be non-null.

While holding the control button, select all of the columns that are currently in the table's primary key, and select the IsOriginalData field as well. Right-click any one of the, and select Set Primary Key. This will place a primary key on all of these fields.

In so doing, you will have a new primary key that is only slightly less restrictive than your current primary key. This will allow your existing data and DBXL data to coexist for a brief moment until the trigger Hilary described in her blog post has time to run, or it will allow both to coexist long-term for the approach described in the next section.

Managing DBXL data and existing data with a view

Hilary's blog post focuses on using a trigger to remove existing data whenever data from DBXL is added, and at the end, presents an alternative approach of calling a stored procedure from InfoPath to remove existing data just before corresponding data is added to DBXL.

There are people who would like to avoid using triggers in their database, and there are good reasons to avoid calling SQL directly from InfoPath (as in the case of the stored procedure approach).

One third approach is to allow the old and new data to coexist in the original table, and use a database view to only expose data when it is either (a) data saved from DBXL or (b) original data that has no corresponding DBXL data.

This carries the added benefit that you will have all of your original data, untouched, in case something should go awry at any point.

The steps belos assume that you have carried out the modifications in the first half of this blog post, but the approach can also be applied, with some small modifications, directly to Hilary's instructions.

The first step is to create a new view based off your DB table. Right-click the Views folder in SQL Server Management Studio, select New View..., select your table, click Add, and then close.

In the designer, check the (All Columns) box to include all columns in your view. This will create a query like the one below in the query editor:

Use the query editor to modify this query to make one analogous to the one below. You should be able to make a query to fit your table by replacing ExistingData with the name of your table, and modifying WHERE clause to match your original ID field(s) between the two aliases in the query.

In essence, this query selects any rows in the table that (a) are from DBXL [(IsOriginalData = 0)] or (b) are original data and have no matching DBXL data (the NOT EXISTS clause).

Here are the contents of my example table:

Note that there is a row of existing data, and a row of data from DBXL with the OriginalId value "ABCDE." When we look at the data through the view, we can see that this row of existing data is filtered out, and everything else is displayed:

That's it! Enjoy!

WebDav Security and WebDav on IIS 6.0

Jimmy — Thu, 17 Jun 2010 14:33:00 GMT

Last week I blogged about setting up WebDav on IIS 7.0+, so that you can take advantage of qRules' useful SaveToSharePoint command even if you don't have SharePoint.

This week, I would like to touch very briefly on the matter of security and provide some quick pointers on using WebDav on IIS 6.0, if that is what your web server is running.

First, security.

As with installing any new service on a server, there are risks to keep in mind when setting up WebDav. WebDav, by its nature, is designed to make it easier to read and write files from and to your web server, so you naturally want to make sure only the right people are doing the right things. Once you have gone through the simple setup steps to get SaveToSharePoint to work, it's important to tighten down security to the tightest restrictions that will work for you.

The following web page deals with security on WebDav. It is geared towards IIS 6.0, but the same concepts should apply to other versions as well.

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/4beddb35-0cba-424c-8b9b-a5832ad8e208.mspx?mfr=true

Now, about using WebDav on IIS 6.0.

My previous post went into a moderate amount of detail for the setup steps, because I was unable to locate a thorough tutorial for setting up WebDav on IIS 7.0 and above. Luckily, one has already been written for IIS 6.0:

http://www.windowsnetworking.com/articles_tutorials/WebDAV-IIS.html

Once you have installed WebDav on your server, create a virtual directory for your files, similarly to the way I described in my earlier blog post. In the Virtual Directory tab of the virtual folder's properties, just enable Read, Write, and (if desired) Directory browsing. Click ok to save the changes, and you should be all set to test out the feature.

Saving Forms as New to DBXL

Jimmy — Wed, 16 Jun 2010 06:10:00 GMT

Typically when you open a form from DBXL and re-submit it, your modified form will be saved on top of the one you opened. This is a very useful feature, and undoubtedly what you want to do most of the time, but sometimes you will want to save a new copy of a form and leave the original one unchanged. This short tutorial will teach you how to do that.

The DBXL PI

When you open a form from DBXL, the form's XML will have an XML Processing Instruction (PI) embedded in it. This PI contains the form's DocId, and some other information, and when you re-submit the form, DBXL reads this and knows which form to overwrite.

Therefore, if you remove this PI, DBXL will treat the form as a new document, and will save it as a new form, instead of overwriting the original. Below are two methods for removing this PI.

Using qRules

qRules includes a command to remove the DBXL PI from an XML form. Just use this simple command:

RemoveDbxlPi

Using Code

If you would prefer to use code, you can use the following short snippet to locate the DBXL PI, and remove it if it is present:

const string piXPath = "/processing-instruction()[name() = 'QdabraDBXL']";
XPathNavigator pi = MainDataSource.CreateNavigator().SelectSingleNode(piXPath);

if (pi != null)
{
pi.DeleteSelf();
}

Once you have used one of the two above methods, your form should be submitted as a new document the next time you submit it.

Using qRules' SaveToSharePoint Command Without SharePoint

Jimmy — Fri, 04 Jun 2010 14:19:00 GMT

qRules provides a handy command called SaveToSharePoint, that allows you to save attachments in your InfoPath forms to a SharePoint server, to reduce the size of your XML forms, and allow these files to be accessed without opening up your forms in InfoPath.

But in spite of its name, you don't have to have SharePoint to take advantage of this command. Using a module called WebDAV, it's possible to configure an ordinary IIS website to allow files to be saved to it. This tutorial goes through a few simple steps for setting up WebDAV, to accept files saved via the SaveToSharePoint command.

This tutorial assumes the three following requirements:

You will be saving the files to a server running Windows Vista, Windows Server 2008, or Windows 7
You already have an IIS website running on your server
Your website uses Windows authentication

Without further ado, here's how to set up a file repository where you can save your files.

1. Ensure you have WebDAV installed on your server.

Open up IIS manager, and in the Connections pane, expand the Sites folder and click the node for your website.

A number of icons should be shown in the center of IIS Manager. Look in the IIS section of this group of icons and look for an icon called WebDAV Authoring Rules. If this icon is present, please skip to Step 2.

If you see no WebDAV Authoring Rules icon, you will need to install or enable WebDAV. Please consult the corresponding Installing WebDAV section of the following page to get WebDAV setup on your server. Vista and Windows Server 2008 users should consult the section for IIS 7.0. Windows 7 users should consult the section for IIS 7.5.

http://learn.iis.net/page.aspx/350/installing-and-configuring-webdav-on-iis-7/

Once you have installed WebDAV, please close and reopen IIS manager, and ensure that the WebDAV Authoring Rules icon is available now.

2. Create a virtual folder for your saved files.

Since you don't want to allow users to save files just anywhere on your site, the next step is to create a virtual folder where files can be saved to your site. Right-click your site's node in the Connections pane, and select Add Virtual Directory...

Give the virtual directory a name (this is the subdirectory of your site where files will be saved), and create and/or select a physical folder on the hard disk to which this folder will correspond. Click OK.

3. Enable WebDAV on the new virtual folder.

Select your site again in the Connections pane, and double-click the WebDAV Authoring Rules icon. Click the Enable WebDAV text in the Actions pane to the right to enable WebDAV for the site.

Now, select your new virtual folder in the Connections pane so that the top of the center pane says FolderName Home, where FolderName is the name of your new folder.

Double-click the WebDAV Authoring Rules icon again. Now click the Add Authoring Rule... text in the Actions pane to create a rule to allow saving and accessing files in this virtual directory. For simplicity, just add a simple rule that allows Read, Write, and Source, for all content and all users. Click OK. You have now set up your virtual directory to use WebDAV.

4. Set up an index for your virtual folder.

One last step to allowing SaveToSharePoint to work on your site is to set up an index for your new virtual directory. By default, IIS prevents making requests directly to folders on a site, and this will cause SaveToSharePoint to not work. You have options.

a. If you would like users to be able to see a list of the files in the folder using a browser

Again select your new virtual folder in the Connections pane of IIS Manager.

Double click the Directory Browsing icon in the IIS section of the central pane.

Click the Enable text in the Actions pane.

b. If you do not want users to see a list of the files in the folder

Open a new instance of Notepad

Without entering any text, save the file with the name index.htm in the physical disk location that corresponds to your virtual directory

This will cause a blank page to be displayed if anyone navigates to this directory in a browser. If you like, you can instead use a file with HTML, to display a certain page to users who navigate to that directory.

If the above went successfully, you should now be able to use the SaveToSharePoint qRules command to save InfoPath attachments to your site. Just specify the URL to your virtual folder in the command's url parameter:

SaveToSharePoint /url=http://intranet.site/InfoPathSaveFiles/ /xpath=/my:myFields/my:files/my:file

Best of luck!

A mutually exclusive radio button in a repeating section

Jimmy — Wed, 05 Aug 2009 01:51:00 GMT

Here's a nifty trick you can use when you want to add a radio button or checkbox to a repeating section or table that can only be checked in one row of the section or table at any given time.

The Scenario
You are creating a form for a team roster for teams in a sports tournament. As a rule, each team may only designate one team captain and one vice-captain. You could enforce this using custom validation, but let's see if we can't do something a bit fancier.

Creating the form
We begin by dragging an empty repeating section into the form:

By default, this section will be created as my:group2.
We then add the fields that we want below my:group2. We create a my:name field to store each team member's name (this will not serve a real purpose in this demo, but let's include it for good measure), a my:captain field and a my:vice-captain field (let's create both of these fields as Boolean (true/false) fields.

Then we drag the fields into the repeating section from the taskpane. First we drag the name field in and create it as a text box. Then we drag the captain field with the right mouse button and create it as a checkbox, and drag the vice-captain field with the right mouse button and create it as a radio (option) button. (Ordinarily you would probably just use one or the other, but for the sake of demonstration we'll use one of each this time.
Creating the radio button should create a checked and unchecked radio button with the words Yes and No next to them. Delete the No radio button and change the "Yes" text to "Vice-captain."

The final result should look like this:

Adding rules
Now that everything's laid out, it's time to add rules to make the fields mutually exclusive. Right-click the checkbox (the captain field) and select Rules... top open up the Rules dialog box, and then click Add... to add a new rule, and name it "Clear other captains".
Click Set condition... to create a condition and set the condition to be captain is equal to TRUE. That is, whenever a user clicks this checkbox to designate a team member as the captain, we want the rule to clear all of the other captain fields.

Now add the rule action. Make the action "Set a field's value." Select the captain field itself as the field to set, and for the value, just type false.

Please repeat this process for the vice-captain field, replacing captain with vice-captain in the instructions above.

Not done yet
If you preview the form at this point, you will find that you are unable to check any of the checkboxes or radio buttons. This is to be expected. As soon as you try to set a value to TRUE, the rule is setting all of the nodes of that field to false, including the field you just checked. To get around this, we'll need to employ a little trick that Hilary Stoupa blogged here.

Editing the manifest
Save the form as its source files, close InfoPath and locate the place where you saved the files. Open the manifest.xsf file in a text editor of your choice.

In the xsf:ruleSets section, you should find the definitions for your rules:

For the first rule, edit the targetField attribute to have the value:

(../preceding-sibling::my:group2 | ../following-sibling::my:group2)/my:captain

This will tell InfoPath to set the "false" value to the my:captain field in all preceding and following repeating nodes.

Now do the same for the vice-captain rule, replacing my:captain with my:vice-captain.

Save the manifest.xsf file, right-click it in Internet Explorer and click Design to open it up in Design mode again.

Now preview the form. If you've done everything right up to this point, you should find that any time you click a captain checkbox, all of the other captain checkboxes become cleared, and the same happens for the vice-captain radio buttons.

Executing a stored procedure with OUTPUT parameters

Jimmy — Sat, 01 Aug 2009 01:09:00 GMT

Many people are aware that you can use InfoPath code or script to perform all sorts of database queries on tables, views, stored procedures and the like. I won't go into the details here, but you can read more about this at the following links:

http://www.infopathdev.com/forums/p/9467/33496.aspx#33496
http://www.infopathdev.com/forums/p/8940/31780.aspx#31780

But what happens when you want to query a stored procedure that uses OUTPUT parameters? I ran into this issue while helping a forum poster in this thread:

http://www.infopathdev.com/forums/t/12506.aspx

Suppose I have this elementary stored procedure, which returns the square and half of the input value:

CREATE PROCEDURE [dbo].[SimpleStoredProcedure]
     @inputValue int,
     @inputSquared bigint output,
     @inputHalved int output
AS
BEGIN
     SET NOCOUNT ON;
     SET @inputSquared = CAST(@inputValue as bigint) * CAST(@inputValue AS bigint)
     SET @inputHalved = @inputValue / 2

     -- return some value just for the heck of it
     return 7
END

If I try to execute this procedure from InfoPath code like this (C# 2007 here):

AdoQueryConnection conn = DataConnections["TestDatabase"] as AdoQueryConnection;
string originalCommand = conn.Command;
string query = "EXECUTE SimpleStoredProcedure 23"; // just pass in 23 as an arbitrary value
conn.Command = query;
conn.Execute();
conn.Command = originalCommand;
RunQuery(query);

I get this error:

The query cannot be run for the following DataObject: TestDatabase
InfoPath cannot run the specified query.
[0x80040E10][Microsoft OLE DB Provider for SQL Server] Procedure or function 'SimpleStoredProcedure' expects parameter '@inputSquared', which was not supplied.

this is because my query does not give the stored procedure any place to store the values of its output parameters. What's more, I have no way of accessing these parameters because by default, InfoPath only receives the value of the return statement from a stored procedure, which would always be 7 in this case.

The solution:

The thing to remember here is that you don't have to limit yourself to having just one SQL statement in your query. You can have several. And that's just what we need here.

What we can do is have the query create some SQL variables to hold the output parameters, and then SELECT them so that their values get sent back to InfoPath. First we create the SQL variables and pass them into the stored procedure:

DECLARE @squareOutput bigint, @halfOutput int
EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT

We can do this by modifying the third line of my code above to:

string query = "DECLARE @squareOutput bigint, @halfOutput int " +
"EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT";
[GOTCHA: Don't forget the space at the end of the first line of the statement or you will wind up with something like intEXECUTE in your statement]

This is still incomplete because no values come back from the query, and the secondary data source's contents look like this:

<dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"/>

To finish this solution off, we need to SELECT the value of the SQL variables so that they will be sent back to InfoPath, like this:

SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut

I've specified aliases (SquareOut and HalfOut) for the SELECT column names because otherwise they will come back without names and InfoPath will give them meaningless names like c0 and c1.

The code looks like this at this point:

string query = "DECLARE @squareOutput bigint, @halfOutput int " +
"EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT " +
"SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut";
[Again, don't forget the space at the end of the second line of the statement]

Finally, we run the query, and we get back our result, with our requested values:

<dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution">
     <dfs:dataFields>
          <d:row xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" HalfOut="529" SquareOut="11"/>
     </dfs:dataFields>
</dfs:myFields>

Programmatically adding rows to repeating sections

Jimmy — Fri, 06 Mar 2009 06:48:00 GMT

Users on the InfoPathDev forum frequently ask how they can add rows to repeating groups from code. There is a relatively simple way to do this, but it has a few limitations. I may write another blog post later that describes a more general approach that is trickier but does not have these limitations, but for now, this option is available for forms that meet the following criteria:

The form must not be browser-enabled.
The repeating group must be represented in a repeating section or repeating table in the form.
The view containing the repeating section/table must be the currently displayed view when the row is added.
The repeating group must not be inside another repeating group, or be recursive.

If your form meets all of these requirements, please read on.

XmlToEdit

Before proceeding, you must understand a bit about the XmlToEdit value. Certain controls (including repeating sections and tables) have a property called XmlToEdit, which has a unique value for each different control. You can find this value with the following steps:

Right-click the control (you can click the little tab at the bottom-left corner of the repeating section or table)
Select Repeating Table Properties... or Repeating Section Properties... (at the bottom of the context menu)
Click the Advanced tab. The XmlToEdit value should be displayed towards the bottom of the dialog box.

Throughout this blog post I will be using the text XmlToEdit as a placeholder for this value. Make sure to replace this text with the actual XmlToEdit value of your control.

With that out of the way, let's look at the actual code. It is a single line in all six language models that InfoPath supports and I will show you that code for each language.

C# 2007

CurrentView.ExecuteAction(ActionType.XCollectionInsert, "XmlToEdit");

Visual Basic 2007

CurrentView.ExecuteAction(ActionType.XCollectionInsert, "XmlToEdit")

C# 2003

thisXDocument.View.ExecuteAction("xCollection::insert", "XmlToEdit");

Visual Basic 2003

thisXDocument.View.ExecuteAction("xCollection::insert", "XmlToEdit")

JScript

XDocument.View.ExecuteAction("xCollection::insert", "XmlToEdit");

VBScript

XDocument.View.ExecuteAction "xCollection::insert", "XmlToEdit"

qRules 1.5 (codeless forms)

Forms with qRules 1.5 (free trial on Qdabra.com) can also use a similar operation to add rows to a repeating table. In this case, use the command:

ExecuteAction /action=XCollectionInsert /xmltoedit=XmlToEdit

For more information on using qRules and specifying commands, please see the qRules documentation.

XPath's confusing not() function - inverting a boolean field

Jimmy — Sat, 25 Oct 2008 03:19:00 GMT

If you've ever tried to toggle a boolean field back and forth in an InfoPath form, or perform some behavior based on the inverse of a field's value, you may have encountered a problem I found. The not() function does not behave the way one would intuitively expect. The online InfoPath function reference describes the function as follows:

not

Returns true if the argument is false or null. Returns false if the argument is true or not null.

So what happens when a field is false and not null?

In actuality, any time the not() function contains a reference to a field, it returns true if the field exists, and false if it does not exist, regardless of its value.

To demonstrate this, I've created an InfoPath form with a boolean field in an optional section, and set up a rule and expression box like this:

When I preview the form, it exhibits the following behavior:

not(field1) is only true when field1 is non-existent, and when field1 has a value, not(field1) is always false.
What's more, the button changes the value of field1 from true to false, but not from false to true. It's only half of a toggle button!

The solution:

Despite all this, we can obtain the inverse of a boolean field's value with a little finagling.

The XPath expression field1 = "true" will have the value "false" when field1 is false, and "true" when field1 is true; in other words, it will always follow field1's value whenever field1 exists. We can conclude that not(field1 = "true") will be the inverse of field1's value, and that is certainly the case. But if we are going to use an equality expression to determine field1's value, we may as well ditch the not() function entirely, and just use the expression field1 = "false", which will be "true" when field1 is false, and "false" when field1 is true. Here are the new settings for the expression box and the button's rule:

And when I preview the form, I see this behavior:

Both expressions have the inverse of the value of field1 when it exists, and the button correctly toggles field1's value. One final note is that the two expressions have different values when field1 does not exist.