Yesterday, a user of Qdabra's Active Directory Web Service reported an issue via the forum.

When calling the operation GetEmployeesForManager, the user's alias was being retrieved for both values in the dropdown.

We have quickly investigated and fixed this such that the displayName property is returned for Display and the alias for Value. In the newest version of the tool, all APIs that return a Dropdown list will return the AD “displayName” rather than “cn” for the display value.  This was corrected for GetEmployeesForUser, FindUserByAlias and FindUserByName

Please refer to the product page for more information on the Active Directory web service Standalone.

Sometimes it is useful to know the DocID of a document that was just submitted to DBXL. For example, you might want to send an email immediately after submission. So how can we retrieve the DocID of the document? Follow this tutorial to find out!

These steps assume that you have an InfoPath form template (XSN) configured as a Document Type that submits to DBXL. The document type must have at least one document for the purposes of these steps (it can be a test document and it can be deleted once you are done). For more information on how to achieve this pre-requisite, refer to the Getting Started document.

1. Open your XSN in design mode.
2. Go to Data > Data Connections.
3. Click Add. Follow the Data Connection Wizard to create a data connection that receives data from http://<servername>/qdabrawebservice/ADUserInfo.asmx using the method GetMyInfo. Be sure to uncheck the option to “Automatically retrieve data when form is opened”.
4. Add a new field and call it key.
5. Add one more node to the schema, and call it DocID.
   
   
   
   
6. Click Data > Form Load.
7. Click New > Action. Rename the rule to “Set the value of key”.
8. Add a condition such that this rule will only execute when key is blank.
   
   
   
   
9. Add an action that queries the GetMyInfo data connection that was created in step 3.
10. Add a second action that that sets the value of the key to the following:
    
    concat(xdDate:Now(), "- ", xdXDocument:GetDOM("GetMyInfo")/dfs:myFields/dfs:dataFields/tns:GetMyInfoResponse/tns:GetMyInfoResult/tns:ADProp/tns:Value[../tns:Key = "sAMAccountName"])
    
    Note that you will need to click the fx button to enter the above formula. Your rule will look like this.
    
    
    
    What is this rule doing? We are creating a unique key by concatenating the timestamp and the username. This creates a unique key because we can assume that one user won’t be able to submit two documents at the same, exact moment.
    
    For information on how to use Qdabra’s Active Directory web service, refer to this document.
    
    
11. Next we will change the rules in the submit button. Set the button so that Action is set to Rules instead of Submit.
12. Go to Data > Data Connections > Add. Follow the Data Connection Wizard to add a data connection that receives data from http://<servername>/qdabrawebservice/dbxldocumentservice.asmx. This data connection will call the method QueryDocumentNodeSet and will not be set to “Automatically retrieve data when form is opened”. In the parameter screen, when asked for the value of the tns:query parameter, enter the following:
    
    <query doctypeName="YOURDOCTYPENAME"><columns repeatingPath="/my:myFields"><column name="DocId" type="token" /></columns></query>
    
    You can confirm that this data connection successfully obtained a schema by checking the dataFields:
    
    
    
    
13. For the submit button, click New > Action > Add > Submit Data. In the dialog that appears, select the data connection that submits to DBXL.
14. Add a second action that sets the value of the query node:
    
    
    
    The value will be:
    
    concat('<query doctypeName="YOURDOCTYPENAME"><columns repeatingPath="/my:myFields"><column name="DocId" type="token" /></columns><filter><eq> <column name="/my:myFields/my:key" /><value>',key,'</value></eq></filter></query>')
    
    You will need to use the fx button when entering this value. In it, you’ll see YOUR document type name and key, which is the field we added earlier in the steps.
    
    
15. The third action will query data from the QueryDocumentNodeSet data connection.
    
    
    
    
16. The fourth action will set the value of my:DocID to the value returned by the data connection.
    
    
    
    
    
    
17. Save the XSN, re-attach it to your document type in DAT, and Save.
    

You can now test by submitting a new document and verifying that the DocID is returned to the form immediately after submitting.

This DocID can be used to generate a direct URL to your form, for example. The DocID, along with concat, can be used to set a field’s value to the URL, like this:
concat("http://YOURSERVERNAME/qdabrawebservice/documents/",DocID,"/",DocID,".xml")

Note that this method is only necessary to get the DocID for a newly submitted document. To obtain the DocID for a document that already exists in DBXL, you can use the processing instructions, as explained in this document.

The DBXL Administration Tool provides you controls to export the documents stored in your DBXL Document Types. However, this method may make InfoPath time out if you have a large number of documents (100+).
 
As a workaround for exporting a large number of documents, Qdabra provides the Migration Tool (Document Deployer), which allows you to export documents one by one, in the background.
 
However, this method operates in the background, exporting one document at a time. This might not be ideal for thousands of documents.
There is a third method that administrators can use to export a large number of documents. The web service used is the same as the Import/Export in DAT, but you can invoke these methods on the server by opening IE and browsing to the DbxlImportExport.asmx page.

1. On the machine where DBXL is installed, open Internet Explorer.
2. Navigate to the URL: http://<servername>/qdabrawebservice/DbxlImportExport.asmx
3. Click on ExportDocuments.
4. Enter the docTypeName and the folderPath for export.
5. Click Invoke.

This will export the documents to folderPath specified. A new Internet Explorer window will open showing the results. For example:

<?xml version="1.0" encoding="utf-8" ?>
<StatusInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://qdabra.com/webservices/">
<Success>true</Success>
<Errors>
<ErrorInfo>
<Code>I0001</Code>
<Description>2 documents exported.</Description>
</ErrorInfo>
</Errors>
<ExecutionTime>00:00:00.1340000</ExecutionTime>
</StatusInfo>

Note that you might need to increase the executionTimeout element. For more, read this: http://msdn.microsoft.com/en-us/library/ms824641.aspx.

DBXL v2.4 has recently been updated to add “String(InnerXml)” and “String(OuterXml)” in the DAT database mapping dropdowns. This allows you to map rich text data into SQL.

We’ll illustrate the difference between InnerXml and OuterXml with two examples.

Example #1:

String(OuterXML) result: <my:Title><div><br/>InnerXML</div></my:Title>

String(InnerXML) result: <div><br/>InnerXML</div>

Example#2:

String(OuterXML) result:

<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-10-12T19:13:08" xml:lang="en-us">

  <my:Title>Title1</my:Title>

  <my:RichText1>

    <strong xmlns="http://www.w3.org/1999/xhtml">

      <font style="BACKGROUND-COLOR: #ffff00" color="#ff0000">Rich Text 1</font>

    </strong>

  </my:RichText1>

  <my:RichText2>Rich Text 2</my:RichText2>

</my:myFields>

String(InnerXML) result:

<my:Title xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-10-12T19:13:08">Title1</my:Title>

<my:RichText1 xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-10-12T19:13:08">

  <strong xmlns="http://www.w3.org/1999/xhtml">

    <font style="BACKGROUND-COLOR: #ffff00" color="#ff0000">Rich Text 1</font>

  </strong>

</my:RichText1>

<my:RichText2 xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-10-12T19:13:08">Rich Text 2</my:RichText2>

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) 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.

Click here to see a list of available documentation for DBXL v2.4.

Edit: You can also refer to DBXL v2.5 documentation.

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.

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.

Starting with qRules 2.0, you can use the qRules SetValue command in steps 15-16, and skip modifying the manifest (steps 18-24).  The corresponding SetValue command for this tutorial would be something like this: concat("SetValue /xpath=/my:myFields/my:group1/my:group2/my:field1[last()] /value=", . )

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 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”.