When trying to use qRules in InfoPath 2007, you might run into the following error IF you also have InfoPath 2010 installed on the same machine:

An unexpected error occurred executing the rule. Action: SortTable
Arguments:
row: my:myFields/my:Items
compare: my:Group
order: ASC
type: text

Details: It is an error to mix objects from different versions of MSXML.

Stack:   at Microsoft.MsoOffice.InfoPath.MsxmlInterop.MsxmlDocument.ThrowExceptionFromMsxmlInteropError(MsxmlInteropError eError)
   at Microsoft.MsoOffice.InfoPath.MsxmlInterop.MsxmlDocument.ThrowExceptionFromHresult(Int32 hrError)
   at Microsoft.MsoOffice.InfoPath.MsxmlInterop.MsxmlNodeImpl.AppendChild(String strXml)
   at Microsoft.Office.InfoPath.MsxmlWriter.Close()
   at System.Xml.XmlWellFormedWriter.Close()
   at System.Xml.XPath.XPathNavigator.AppendChild(XmlReader newChild)
   at System.Xml.XPath.XPathNavigator.AppendChild(XPathNavigator newChild)
   at Qdabra.Tools.RulesLibrary.Commands.SortTable(String domName, String rowXPath, String fieldXPathList, String orderList, String typeList)
   at Qdabra.Tools.RulesLibrary.Commands.SortTable()
   at Qdabra.Tools.RulesLibrary.Commands.ExecuteCommand()

The issue is with MSXML interop between 2007 and 2010, which happens when a machine has InfoPath 2010 installed as well. The solution is to use InfoPath 2010 since there is no known workaround for the MSXML difference between 2007 and 2010. When you use InfoPath 2010, this error should not come up.

If you are using Database Accelerator (DBXL) v2.4 with InfoPath 2010, you might encounter an issue when opening the DBXL Administration tool (DAT).

If you click Show Details, you'll get: "Forms that require the domain permission level contain features that access information on a network".

This appears to be a caching issue in InfoPath 2010, due to the fact that InfoPath 2010 doesn’t automatically attempt to get an update for the form. This only happens if you had a previous version of DBXL installed.

The workaround is relatively simple:

1.    Close all InfoPath instances, and verify that the InfoPath process is not running.

2.    Open InfoPath.

3.    In the File tab select New. You will see the DBXL Admin Tool listed under Qdabra Tools.

4.    Right-click on the form and select ‘Get update of this form’.

5.    Close InfoPath filler.

6.    Try to launch DAT again.

DAT will update and InfoPath should no longer display errors whenever you click on the link in the DBXL page or open from your available InfoPath forms.

Qdabra Database Accelerator (DBXL) helps you quickly connect InfoPath forms to your SQL database and improve SharePoint submit and query performance. DBXL was first released in 2004 and supports many mission critical applications today. Qdabra recently released DBXL v2.4; for more information, please visit the product page on Qdabra.com.

Below is a list of available documentation for DBXL v2.4.

• Core documentation
  • DBXL v2.4 Installation Guide
  • DBXL v2.4 Release Notes
  • DBXL v2.4 Getting Started
• Installation
  • How to install DBXL v2.4 in a load balanced scenario
  • How to Configure a License in DBXL
  • Uninstalling Qdabra's QueryBuilder InfoPath form tool
  • How To Increase Web Service Timeout to Allow Large Document Upload 
  • How to upgrade from DBXL v2.3 to DBXL v2.4
• Standalone
  • Getting Started with the Standalone Dashboard
  • How to uninstall and update the Dashboard
• SharePoint integration
  • DBXL v2.4 Integration with SharePoint
  • Create a SharePoint Data View that uses GetListItems to access DBXL forms
  • Create a SQL-based SharePoint Data View to access your DBXL Forms
  • How to propagate deletes from SharePoint to DBXL
  • Qdabra's Search Web Part
  • DBXL Permissions when using SharePoint form libraries
  • How to use Document Level Permissions for browser-based Forms in Data View
• SQL Mapping
  • How to create database mappings
• QuerySharePoint
  • Implement static and dynamic queries using QuerySharePoint
  • QuerySharePoint User Manual
• QueryDB
  • Implement static and dynamic queries using QueryDB
  • QueryDB User Manual
  • How to use the QueryDB Permissions plugin interface
  • Using the Encryption Tool for web.config connection strings
• Active Directory Web Service
  • How to query the Active Directory Web Service
  • Active directory query fails in Windows XP
    
• Web methods
  • Submit data to DBXL from a non-InfoPath form
• Permissions
  • How to use permissions on DBXL v2.4
• Misc
  • Using DBXL Processing Instructions
  • How to use RefIDs
  • Using “Save As” in DAT
  • Working with locally-saved copies of DBXL documents
  • How To Migrate a Document to a New Document Type
  • How To Use Document Deployer to Import XML Documents into your DBXL Document Type
  • Side-by-side Document Types to Preserve SQL Mapping when your Form Schema Changes
  • InfoPath PIA and .NET Framework issue
  • Uploading large attachments can fail due to IIS maxRequestLength setting in web.config
  • How To See Previous versions of forms
  • How To Reference a Document by its IDs
  • Transform and migrate xml documents
  • Using DBXL Resources
  • How To Link to Previous Form Versions from Workflow section
  • How to use full trust form templates
  • How to create nodes in Taxonomy
  • How to use qForm in DBXL v2.4
    

When using QueryBuilder and QuerySharePoint, you need to enter the URL to your SharePoint list. Internet Explorer encodes spaces as %20, but DBXL produces an error if you use a URL containing a %20.

The workaround is simply to replace %20 in the URL with an actual space. This works in QueryBuilder and in the InfoPath data connection wizard.

When creating a Sharepoint mapping from the DBXL Administration Tool (DAT) you have two options: Object Model and Web Service. Web Service is required when the Sharepoint instance lies on a different server. However, we have seen instances where Object Model produces an error, even though DBXL and Sharepoint are on the same machine.

How do you know if you’re in this situation?

1. Add a Sharepoint mapping. Select Web service. Save the document type. Add documents and verify that everything works as expected.
2. Edit the same doctype to use Object Model. When you click Save in DAT you get this:

Simply using Web Service is an acceptable workaround for some people. But if you wish to use IPFS (browser enabled forms) you’ll need to use Object Model. So how do we fix this?

When faced with this scenario please check the Applications eventlog. It is our experience that we find a SharePoint error with Network Service not having permission to access the Sharepoint database.  Evidently, the object model does direct database access. To fix this, switch DBXL to use the same identity as the SharePoint app pool.

The QuerySharepoint web service allows you to retrieve data from a SharePoint list. It requires a <query> parameter, which can be built using the QueryBuilder form that comes with DBXL. QueryBuilder allows you to specify the columns you wish to retrieve.

In the screenshot above, you can see that I wish to retrieve three columns. I generate my query and proceed to create the data connections in my form. In InfoPath, I go to Tools > Data connections and create a receive-type connection. For the <query> parameter, I enter:

<query maxrows='2'><columns><column name='Category'/><column name='Unit_x0020_of_x0020_Measure'/><column name='ProductCode'/></columns></query>

Notice that the data source taskpane will show me the xml structure that was returned by this new data connection:

Where are my other two columns?! This issue was recently encountered by one of the users of DBXL (read it here).

If we look at the source SharePoint list, we’ll notice that the first two items in my list contain blank values in those columns.

Because of these blank values, the web service was unable to derive the correct schema.

The workaround is simple: increase the value of the maxrows parameter. Then the data connection “sees” that third row, and returns the columns correctly:

That’s a bit tedious, especially if you have a large list with many blank values. So the workaround becomes even simpler: remove the maxrows parameter!

Using qRules you can use the commands CopyTable and MoveTable to copy or move an entire table.

But what about copying individual repeating fields? We can do this by using Insert and a couple of InfoPath tricks. The steps below illustrate a simple example from which you can extrapolate others.

First we need to design our form.

1.    Create a new, blank InfoPath form and inject it with qRules v1.7.

2.    Create a data source as seen in the screenshot below.

3.    Go to Tools > Data Connections and create a new data connection that retrieves data from an xml file every time the form is opened. Here is a screenshot of my xml file.

Your secondary data source will look like this:

4.    Right-click on the repeating node called name and select Repeating table to insert it into the view.

5.    Next to each the text field, insert a button. Double-click on it and change its Label to “Copy over”.

6.    Right-click on the repeating node in the main data source and select Repeating table to insert it into the view.

By now, your view looks like this:

We don’t want the destination table to have any rows when the form loads.

7.    Click on Tools > Default Values, uncheck the repeating field in the main data source, and click OK.

Now we need to configure our rules.

8.    Double-click on the button and click Rules.

9.    Click Add, then click Add Action.

10.    Select Set a field’s value from the Actions dropdown.

11.    Click on the icon next to Field and select the Command node in the QdabraRules secondary data connection.

12.    For Value, enter the command we will execute: Insert /parent=/my:myFields /child=/my:field1 /count=1. Click OK.

13.    Click Add Action.

14.    Select Set a field’s value from the Actions dropdown.

15.    Click on the icon next to Field and select the repeating field in your main data source.

16.    Click on the icon next to Value, click on Insert field or group and select the repeating node in the secondary data connection.

17.    Click OK to close all dialogs.

Finally, we need to manually modify the xpath of the destination to make sure we are only copying into the node we just inserted.

18.    In InfoPath, click File > Save As Source Files and choose a destination folder in your local drive.

19.    Navigate to that folder and open manifest.xsf in a text editor.

20.    Locate the node <xsf:ruleSets> towards the end of the file. This node contains your form’s rules.

21.    Find the second rule we created. In the case of our sample form, it looks like this <xsf:assignmentAction targetField="xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:field1" expression="."></xsf:assignmentAction>

22.    Change the rule to add [last()], as seen here: <xsf:assignmentAction targetField="xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:field1[last()]" expression="."></xsf:assignmentAction>

Resave the form and test:

23.    Save manifest.xsf.

24.    Right-click on the manifest and select Design.

Double-click on the button and then click Rules to verify your rules. This is what your rules will look like once you are done:

Optionally, select the second action and click Modify. Verify that the xpath to the target field is underlined; this visual cue indicates that InfoPath has recognized our manual modification.

Now you can preview the form and test it! Click on copy over for John Doe and that value gets inserted into the destination table.

Click on copy over for Bruce Wayne and that value gets inserted into the destination table.

This blog entry assumes you have completed tasks 1, 2, 3 and 5 in the DBXL v2.3 Getting Started document. In other words, I assume that you have already:

1. Configured an InfoPath form to submit to the DBXL web service.
2. Created a document type in DBXL using that form.
3. Added SQL mapping.
4. Successfully submitted documents and verified their shredding.

Though we can always add validation in the InfoPath form to prevent users from submitting with blank fields, we can also prevent DBXL from shredding certain documents. We call this conditional mapping.

For example, the Expense Report sample that ships with InfoPath 2007 (and is used in the Getting Started document) does not require the user to enter a Purpose. Suppose we don’t want to map documents that have a blank Purpose. In this case, we don’t want the second row in our database, because of the blank Purpose field.

So we must resort to conditional SQL mapping. For this particular example, this means “let’s only send this data to SQL when a certain field is not NULL”.

Only one small change needs to be made in the mapping. Where we previously had this:

And now we change to:

We manually added a condition in the note path for the table: /my:expenseReport[my:purpose != ""]. We empty our SQL table and click the Reshred All button in DAT, and we obtain the following data shredded into SQL:

So we have successfully filtered on the xpath, excluding (from SQL) rows where the Purpose field is blank.

After installing qRules v1.7 you can get a quick glimpse into some of the commands. Simply navigate to the installation folder (usually C:\Program Files\Qdabra Software\Qdabra Rules Library) and locate the qRulesForm.xsn. Right-click on that form template and select Design.

You'll find that the form contains multiple views to illustrate the usage of the following commands:

• CopyTable
• MoveTable
• DateDiff
• Delete
• IsDirty
• SetDirty
• ExecuteAction
• GenerateGuid
• GetErrorCount
• Insert
• Replace
• SortTable
  

Click Preview and test out the commands!

If you want to see how the commands are configured, you can switch to the appropriate view in InfoPath's design mode and check the rules implemented in each button. Along with the qRules v1.7 User Guide, this will allow you to get these commands up and running in your own forms in no time!

DBXL v2.3 allows you to map your DBXL xml documents into a SharePoint form library. You can use the "Publish Library and XSN" checkbox to create a new form library or update an existing one.

However, if you promote a field as “Description”, the SharePoint form library will not display the column, even though it seems configured properly in both SharePoint and InfoPath. No errors are displayed in the Event Viewer. This is an issue that we will continue to investigate going forward and address for DBXL v2.4.

As a workaround, when you promote that field, name it something other than “Description”.

Microsoft has an exporter that allows you to save InfoPath (and Office 2007) documents as PDF files. You can download the add-in here. There is an additional add-in that allows you to save as either PDF or XPS. Click here for that.

But Microsoft also has detailed instructions on how to save as PDF in each of the Office 2007 programs. Click here to read these step-by-step instructions.

Qdabra’s Active Directory Web Service allows you to obtain the manager’s alias for a given username. This means that you can load the information for the currently logged in user, then determine his or her manager, then query again to obtain the manager’s full information.

One common issue we have encountered is the difficulty to determine the sequence and triggers for the rules that call the Active Directory web service to accomplish the goal above. A previous blog entry gave you a glimpse into the sequence of necessary steps, but used a button to query the manager’s information due to a limitation around template parts. If you don’t want a button on your form, and want all the information to load when the form opens, the steps are quite similar.

The blog post explains three data connections that are necessary.

• GetMyInfo
• GetManagerAlias
• GetUserInfo

When setting those up, make sure to check “Automatically retrieve data when the form is opened” for GetMyInfo, and uncheck it for the other two.

There are three general steps that need to execute:

1. Query GetMyInfo to obtain the Active Directory information for the user that is opening the form.
   • As a result of this query you will obtain the user's alias, which you need as the input for the query in step 2.
2. Query GetManagerAlias to retrieve the manager for the current user.
   • As a result, you’ll obtain ONLY the manager’s alias, which you’ll need as the input for the query in step 3.
3. Query GetUserInfo to obtain the Active Directory information for the manager.

However, don't use rules on a specific field. [For example, DON'T create a rule that executes when the user’s name is retrieved from Active Directory (field is not blank).]Instead, go to Tools > Form Options > Open and Save > Rules and add them there. The rules will then execute when the form opens.

Once you’ve executed these three rules, you can use the Active Directory information as you please. You can populate fields for both the user and the manager (e.g. name and email), as shown in this document or in this previous blog entry. To try the Active Directory web service, you can download a free copy of Database Accelerator, or obtain our standalone tool by checking out the Products page on Qdabra.com.

If you edit an existing Document Type in DBXL you’ll see a button labeled Save As in the header. This is an easy way to create a duplicate of your configuration, but under a different name.

Why is this useful? Here are two possible scenarios:

• Database mapping: Because this action copies the Database mappings, this is useful if you wish to avoid recreating a large mapping to SQL. This also allows you to create the same mapping but to a new database or SQL instance, by replacing the Connection String.
• Development and Testing: Save As allows you to create a test DocType. You can edit the InfoPath form, create test documents, edit the SharePoint or Database mappings, or alter Permissions. Once you have tested, you can edit your original DocType to apply whatever changes you wish to implement. This way, you avoided altering your main Document Type (which might be in use by users in a Production environment). 

These are but two scenarios where the Save As button might prove useful.

While setting up a child table database mapping in DAT (whether in DBXL v2.2 or DBXL v2.3), you might find that only the first row of the repeating table (mapped to the child table) is committed to the child table.

The reason for the problem is an error in mapping. Since it is a very subtle mistake, I wanted to briefly explain via this blog post. The explanation below assumes you have read this document.

This is the data source for the form:

As you can see, we have a repeating group (Contact) inside the group called Contacts.

The parent table is easily mapped, using the fields OwnerName, OwnerPhone and OwnerEmail. The mistake when creating a mapping for the child table is when the user maps the child table to Contacts instead of Contact. When that is done, the columns are mapped as shown below.

Incorrect

The solution is to change the child table mapping to Contact, and then remap the fields, to obtain the mapping shown below.

Correct

The reason for this is simple: the repeating group is Contact and not Contacts. Fix your mapping and you’ll be able to map all of the rows of your child table.