David Airapetyan

Reshredding all documents for a document type using the Qdabra DBXL Migration Tool v2

Greg Collins — Tue, 19 Aug 2008 19:28:00 GMT

If you have to reshred a lot of documents, there is an easier, automated way of doing this rather than using Qdabra DBXL Administration Tool (aka DAT).

Download and install the Qdabra DBXL Migration Tool.
Download the Scenario File for documents reshredding. (Scenario file no longer needed for recent versions of the Migration tool, as it is incorporated into the tool).
Open the Migration Tool UI.
Make sure DBXL Server Root is set to your DBXL (normally http://myserver/QdabraWebService).
Select the "custom" tab and load the scenario you've downloaded in step 2.
Scroll down in the scenario variables list and set "docType" to the document type you want to clear.
Scroll down the Scenario Variables list and change sourceDBXL/targetDBXL.
Select "ReshredDocuments" in the scenario runs and click "Run". This should reshred all the documents from the specified document type:

Codeless way of getting DBXL document properties (docid, doctype and DBXL URL)

davidair — Tue, 22 Jul 2008 03:08:00 GMT

A DBXL solution that is designed to be generic needs to be able to access several DBXL properties. For example, a solution that wants to display a hyperlink in an e-mail needs to be able to determine its DocId and the DBXL URL.

While it's easy to write code to retrieve this data, it is often not desirable to do so. For instance, browser-based solutions are much harder to deploy when they contain code.

This blog post describes a way of getting this information using only rules. Here is what you need to do.

To get the DBXL Document ID, add a DocID field to your data source and set it on load via this rule:

substring-before(substring-after(/processing-instruction()[local-name(.) = "QdabraDBXL"], 'docid="'), '"')

Note that this value will not be set for a new document.

To get the DBXL URL, add a url field to your data source and set it on load via this rule:

substring-before(substring-after(/processing-instruction()[local-name(.) = "mso-infoPathSolution"], 'href="'), "/Forms")

Finally, to get the DBXL document type, add a DocType field to your data source and set it on load via this rule:

substring-before(substring-after(/processing-instruction()[local-name(.) = "mso-infoPathSolution"], concat(<url>, "/Forms/")), "/template.xsn")

Note how the last rule builds on the URL value we've gotten previously. You’ll need to replace <url> with the xpath for the URL saved previously.

It is important to understand that these rules will only work after the InfoPath solution has been published to DBXL.

You can download a full sample here: http://www.infopathdev.com/files/folders/examples/entry41179.aspx

As an alternative, you could use qRules. You can find documentation for qRules by clicking here.

InfoPath data connection: what if secondary data source does not display the structure?

davidair — Thu, 17 Jul 2008 20:46:00 GMT

When a data connection is added in InfoPath the data structure is inferred from the data. This functionality is very important because we want to be able to design against the secondary data source and we need the data to show in the Data Source Pane (DSP) for that.

In the case of web services, InfoPath will use the parameters provided by the user during the data connection configuration to retrieve a sample data and generate the data source based on it. In some cases this may fail to work. For example, suppose your web method returns an XmlDocument called "resultDocument". All you may see in the DSP is the node "resultDocument" without anything underneath even though InfoPath has successfully downloaded the data.

Warning: the following steps require advanced knowledge and may render your solution unusable.

When adding a web service connection, InfoPath will usually generate three schema files:

ConnectionName.xsd – will contain the same structure as the DSP (query fields and data fields).
ConnectionName1.xsd – will contain the details of the web service parameters and return types. If an XML node is returned, the type will be listed as "any".
ConnectionName2.xsd – will contain the schema that was automatically inferred from the sample XML that was downloaded when the data connection was created.

Note that the schema stored in ConnectionName2.xsd is not directly connected to the other schemas. Instead, a special node is inserted into sampledata.xml that links them, namely xd:RequiredAny. In the case when no data structure is displayed, this node will be missing. Here are the full steps to fix the problem:

1. Inspect ConnectionName1.xsd for the return type of your web method (in our example, resultDocument). You are likely to see that the any element is listed as non-required:

<s:element minOccurs="0" maxOccurs="1" name="resourceXml">
<s:complexType mixed="true">
<s:sequence>
<s:any minOccurs="0"></s:any>
</s:sequence>
</s:complexType>
</s:element>

Remove the minOccurs element to make it required.

2. You now need to specify what schema replaces the "any". Inspect ConnectionName2.xsd and locate the root element of the XML that is being returned. Edit sampledata.xml and add the following node under <xd:RequiredAnys/>:

<xd:RequiredAny SOM_Path="{namespace}nodeName/{namespace}nodeName/##any[1]" NamespaceURI="namespace" LocalName="rootNodeName"/>

Here the SOM_Path will be the path to the response node, the NamespaceURI will be the namespace of the node being returned and LocalName will be the name of the root node.

You can find an example of this path by generating a data connection that does not have the issue that this document is trying to address.

3. In sampledata.xml, locate the section of the data that corresponds to the returned result. It will be an empty node (such as <resultDocument/>). Replace it by <resultDocument><ns:rootNode/></resultDocument> where rootNode refers to the root node of the sample data. Make sure to define the namespace prefix somewhere in the sampledata.xml file (can do this at the root). You should be now able to design against the data source!

Huge thanks to Tom for discovering this workaround.

Excel to XML – automatically generating InfoPath forms from Excel data

davidair — Wed, 16 Jul 2008 19:35:00 GMT

Does your data live in Excel? Would you like to move it to InfoPath? Qdabra Excel to XML Tool to the rescue! This simple command-line based tool will take an existing Excel spreadsheet and generate XML documents with your data. The tool will also optionally upload the documents to Qdabra DBXL to further streamline your data processing.

The tool is driven by a simple configuration file that maps Excel columns into fields on an InfoPath solution. Suppose, for example, that you have a spreadsheet with two columns, "name" and "phone". You also have a ContactInfo InfoPath form with those two fields. In order to generate multiple ContactInfo forms, just add the following to the MappingInfo section of the configuration file of the tool:<add key="name" value="/my:myFields/my:name" />
<add key="phone" value="/my:myFields/my:phone" />Once you run the tool, it will generate one line for each row in the spreadsheet. The tool skips already generated files so it can be re-run incrementally whenever additions are made to the spreadsheet.

Things to watch out for:

- "nillable" - this optional attribute of the mapping info should be used if a datatype in InfoPath cannot be null (bool, date/time, integer/double) but your spreadsheet contains blank values for some rows

- namespaces: make sure that you have correctly listed all namespaces used in the xpaths. InfoPath uses the "my" prefix but it may refer to different namespaces so check carefully or you will end up with empty documents!

Installing DBXL – beware of multiple hostnames!

davidair — Wed, 16 Jul 2008 18:54:00 GMT

When you install Qdabra DBXL on your server you may encounter errors such as "Error initializing DBXL database" -->The request failed with HTTP status 404: Not Found". One of the reasons this happens is that your site is configured to use multiple hostnames or multiple IP addresses. If DBXL picks the wrong hostname/IP, the installation will fail.

The easiest workaround is to go to your site settings (IIS management console) and temporarily remove all hostnames/IP addresses that will not work. Make sure only to leave the hostname that the currently logged-in user can access. After DBXL installs successfully you can re-add the additional hostnames and/or IP addresses.

Deleting all documents for a document type using the Qdabra DBXL Migration Tool v2

davidair — Tue, 08 Jul 2008 18:36:00 GMT

The Qdabra DBXL Administration Tool (DAT) allows you to quickly delete all documents from a document type: simply open the configuration for the document type, select the "Documents" tab and click on the "Delete All" button. However, you may encounter an error due to timeout if your document type contains too many documents (usually in the order of thousands).The good news is that you can use the Qdabra DBXL Migration Tool to individually delete all the documents one by one so that the timeout does not occur. Here is how you do it:

Download a copy of the Qdabra DBXL Migration Tool from here: http://www.infopathdev.com/files/folders/document_deployer/default.aspx
Download a scenario file for documents removal: http://www.infopathdev.com/files/folders/document_deployer/entry31629.aspx
Open the Migration Tool UI, select the "custom" tab and load the scenario you've downloaded in step 2. Scroll down in the scenario variables list and set "docTypeName" to the document type you want to clear. Select "RemoveDocuments" in the scenario runs and click "Run". This should delete all the documents from the specified document type:

Browser forms and DBXL

davidair — Mon, 05 May 2008 19:02:00 GMT

Getting browser forms to work with Qdabra DBXL is pretty straightforward. The most important part of it is to install DBXL into the same web application as the SharePoint front end (typically into the web site that runs on port 80). This is needed for the authentication to work properly – otherwise more advanced authentication schemes such as Kerberos delegation may need to be used.

Once DBXL is installed it is sufficient to convert existing DBXL data connections into UDC files and everything will work as-is. However, there is one challenge with the DBXL/SharePoint integration and this is the fact the InfoPath Forms Server can only open documents from SharePoint document libraries.

Luckily, browser forms allow users to pass in custom parameters via the URL. This gives rise to the following solution for listing and opening documents from DBXL (these steps require SharePoint Designer):

1.       The first step is to add a data view to SharePoint site that would list all documents that the user is interested in (one way would be to list the contents of the DBXL's Document table filtering by Type). Jeff Tangen's post provides steps on how to add a data view.

2.       Add a column to the data view (or modify an existing one) to link to the form template, passing in the docid as a parameter.

3.       In the form code's Loading event, inspect the LoadingEventArgs.InputParameters member to check for the docid. If passed, use a regular data connection to DBXL (the GetDocument method) to retrieve the document and replace the main DOM with the retrieved XML.

Here is how the hyperlink to the form can look like in the data view:

<a target="_blank"><xsl:attribute name="href">http://server/_layouts/FormServer.aspx?XsnLocation=xsnLocation &DefaultItemOpen=1&docid=<xsl:value-of select="@DocID" /></xsl:attribute><xsl:value-of select="@Name"/></a>

Then the docId can be accessed in the form code as e.InputParameters["docId"]

Finally, once the document is downloaded, obtain an XPathNavigator from the secondary data source for GetDocument and replace main data source's inner XML with the downloaded inner XML.

For example, if the navigator is called downloadedDocumentNavigator, use this:

XPathNavigator documentElement = this.MainDataSource.CreateNavigator().SelectSingleNode("/*");
documentElement.InnerXml = downloadedDocumentNavigator.InnerXml;

How to automatically build and deploy DBXL solutions

davidair — Mon, 26 Nov 2007 14:08:00 GMT

A DBXL solution typically is a complex system consisting of a database schema as well as several InfoPath templates along with their corresponding DBXL settings and database mappings. Maintaining and deploying those can be a challenging task. The DBXL Migration Tool (also known as Document Deployer) allows automating the process of building and deploying a DBXL solution.

Step one: creating an installer
The first step is to create a Document Deployer installer. Let us assume that a DBXL solution consists of several InfoPath templates saved as source files (typical setup when creating InfoPath templates with business logic). Furthermore, let us assume that the templates have already been deployed to DBXL and mappings have been created. To create the installer, follow these steps:

1. Create a Mappings\Samples folder next to the solutions
2. For each InfoPath Template, use the Migration Tool to export the configuration and save the mapping.xml into individual folders under the Mappings\Samples folder.
3. (Optional) Manually edit each mapping.xml file and remove the contents of the template node. This is done to keep the mappings files small; the build process will replace the template node with the fresh bits anyway.
4. Add an install.sql to one of the folders, it does not matter which one but make sure that the mapping with the SQL installer gets executed first. It is a best practice to have the install.sql create the database schema only if one does not exist already, this enables the upgrade scenario.
5. Add whatever documents needing to be deployed alongside the mappings in the corresponding SampleDocs folder.
6. Within the Mappings folder, create a Document Deployer install script (see example in the appendix).
7. (Optional) Create batch files to facilitate the deployment

Step 2: creating a build for the template DLLs
If you have an InfoPath solution with business logic, it has to be compiled before the XSN can be generated. Create an empty Visual Studio solution that will be used to build all of your InfoPath templates. Then create one C# project for each template and add the source files (typically FormCode.cs) as link to those solutions. You will need to add the missing references manually such as Microsoft.Office.Interop.InfoPath.SemiTrust. Make sure to edit the project properties to make sure that the names of the DLLs generated match the ones build by InfoPath.

Step 3: creating a Document Deployer build
Now that you can build the InfoPath business logic and that you have an installer structure in place, creating a Document Deployer build is simply a matter of creating a Document Deployer script that will combine the expanded template files with the DLLs and put the result into the mapping.xml files. See the appendix for the example of such a script.

Appendix: Example
Suppose that your DBXL system has two solutions, Users and Expense Reports (first one allows to edit the users in the system while the second one allows the users to edit their expense reports). Initially, you will have the following directory structure:

ExpenseSystem
Users
InfoPath Form Template
ExpenseReport
InfoPathFormTemplate

Add the Mappings\Samples folder and save the mappings for the two solutions. Assuming there is an install.sql file for each of the solutions that creates the database tables, the new folder structure will look like this:

ExpenseSystem
Users
  InfoPath Form Template
ExpenseReport
  InfoPathFormTemplate
Mappings
  Samples
   Users
    install.sql
    mapping.xml
   ExpenseReport
    install.sql
    mapping.xml

Now create a DocumentDeployer scenario called InstallSamples.xml in the Mappings folder:

<?xml version="1.0" encoding="utf-8" ?>
<ScenarioDefinition>
<Imports useXpath="true">
<ImportActionDefinitions>concat($ScenariosPath, '\MigrateCore.xml')</ImportActionDefinitions>
</Imports>
<GlobalVariables>
<GlobalVariable name="installSql" value="True"/>
</GlobalVariables>
<Runs>
<Run name="InstallExpenseSystem">
   <Action name="InstallDocType">
    <param name="path">Samples\Users</param>
    <param name="database">ExpenseDatabase</param>
    <param name="installDatabase" useXpath="true">$installSql</param>
   </Action>
   <Action name="InstallDocType">
    <param name="path">Samples\ExpenseReport</param>
    <param name="database">ExpenseDatabase</param>
    <param name="installDatabase" useXpath="true">$installSql</param>
   </Action>
</Run>
</Runs>
</ScenarioDefinition>

It defines a global variable called installSql which specifies whether or not to execute the install.sql scripts. This variable when specified of DocumentDeployer's command-line allows to tweak the process to enable update scenarios. To invoke the installation, run the following command: "C:\Program Files\Qdabra Software\DBXLMigrationTool\DocumentDeployer.exe" InstallSamples.xml InstallExpenseSystem --databaseServer=yourDbServer --DBXLUrl=yourDbxlUrl --installSql=True|False

The Build scenario will look like this:

<?xml version="1.0" encoding="utf-8"?>
<ScenarioDefinition>
<GlobalVariables>
<GlobalVariable name="mappingRoot" value="C:\ExpenseSystem \Mappings\Samples\"/>
<GlobalVariable name="binariesRoot" value="path to the compiled dlls"/>
<GlobalVariable name="solutionsRoot" value=" C:\ExpenseSystem \"/>
</GlobalVariables>
<Classes xmlns:dd="http://www.qdabra.com/DocumentDeployer">
<dd:Class Assembly="Qdabra.DocumentDeployer.InfoPathTools" Name="Qdabra.DocumentDeployer.InfoPathTools.SolutionTools" Alias="Tools">
</dd:Class>
</Classes>
<ActionDefinitions>
<ActionDefinition name="AssembleAndUpdateMapping">
   <Variables>
    <Variable name="solutionSourcePath" output="false"/>
    <Variable name="solutionDllPath" output="false"/>
    <Variable name="templateXsnName" output="false"/>
    <Variable name="mappingToUpdatePath" output="false"/>
   </Variables>
   <Methods>
    <Method class="Tools" name="UpdateMapping">
     <XPathParameter>$solutionSourcePath</XPathParameter>
     <XPathParameter>$solutionDllPath</XPathParameter>
     <XPathParameter>$templateXsnName</XPathParameter>
     <XPathParameter>$mappingToUpdatePath</XPathParameter>
    </Method>
   </Methods>
</ActionDefinition>
</ActionDefinitions>
<Runs>
<Run name="AssembleExpenseSystem">
   <Action name="AssembleAndUpdateMapping">
    <param name="solutionSourcePath" useXpath="true">concat($solutionsRoot, "Users\InfoPath Form Template\manifest.xsf")</param>
    <param name="solutionDllPath" useXpath="true">$binariesRoot</param>
    <param name="templateXsnName">Users</param>
    <param name="mappingToUpdatePath" useXpath="true">concat($mappingRoot, "Users\mapping.xml")</param>
   </Action>
   <Action name="AssembleAndUpdateMapping">
    <param name="solutionSourcePath" useXpath="true">concat($solutionsRoot, "ExpenseReport\ InfoPath Form Template\manifest.xsf")</param>
    <param name="solutionDllPath" useXpath="true">$binariesRoot</param>
    <param name="templateXsnName">ExpenseReport</param>
    <param name="mappingToUpdatePath" useXpath="true">concat($mappingRoot, "ExpenseReport\mapping.xml")</param>
   </Action>
</Run>
</Runs>
</ScenarioDefinition>

Once you specify the location of the binaries, Document Deployer will assemble the solutions under the mappings folder which will create the final installer.

Announcement: Migration Tool v2 released

davidair — Mon, 26 Nov 2007 14:05:00 GMT

The second version of the Qdabra DBXL Migration Tool (aka Document Deployer) has been released as a public Beta and can be downloaded here: http://www.infopathdev.com/files/folders/dbxldownload/entry24682.aspx

Here are new features in v2:

1. Authentication: in v1, Document Deployer would fail if the currently logged in user failed to authenticate with DBXL. As of v2, Document Deployer will detect this situation and will prompt the user to enter the credentials (caching the username only).

This unblocks scenarios where a user connects via RAS/VPN when the machine is not joined to the domain.

2. Built-in variables support. Now several system values are available anywhere in the system:

a) DocumentDeployerPath – specifies the root of the Document Deployer installation

b) ScenariosPath – specifies the folder that contains the out-of-the-box scenarios

c) DBXLUrl – Defaults to the configuration setting, can be changed dynamically

d) DocServiceTimeout – Defaults to .NET setting, can be changed dynamically

e) EnumDBTimeout - Defaults to .NET setting, can be changed dynamically

Built-ins enable scenarios where you can switch DBXL location mid-flight and tweak the parameters such as timeout.

3. Global variables and command-line variables: you can finally provide arbitrary arguments via command-line and you can use global variables to provide defaults. With command-line arguments, you can specify the DBXL location, the database name etc... this allows much greater reuse of existing scenarios – no more need to modify the scenario file to change 5 to 10!

4. New Core methods:

a. Trace (for debugging purposes)

b. SetVariable – allows to set xpath variables dynamically in a declarative way

5. Importing previous runs: Document Deployer v2 allows importing XML files from previous runs enabling abort-and-resume scenarios. Importing XML files also enables very generic input mechanisms.

6. Minor tweaks:

a. Actions can now fail without failing the entire run

b. A few additional checks to prevent faulty scenarios from crashing later

c. File version changed to 2.0

New Migration Tool Features

In addition to Document Deployer changes, the Migration Tool has been updated to use the new features. The Migration Tool features include:

1. Ability to specify timeouts via command-line (including sql timeout)

2. Ability to specify different source/target DBXL paths via command-line. This allows performing migration in one run. It is no longer needed to modify the configuration of the tool.

3. Ability to resume the download of the documents.

4. Ability to resume the upload of the documents.

5. Most methods got another, richer, version (see the documentation for details)

User interface

The Migration Tool has been augmented with a graphical user interface which makes running scenarios much easier.

Implementing reusable OM that can be exposed to a task pane

davidair — Mon, 26 Nov 2007 13:49:00 GMT

When developing InfoPath solutions with a task pane, it is possible to call into form's code (business logic) via the window.external.Window.XDocument.Extension mechanism. For example, if you have a public method GetUserName() in your form code, you can call it as window.external.Window.XDocument.Extension.GetUserName().

What if you want to build a reusable library that can be exposed to the taskpane? It turns out that it's pretty easy to do:

1. Create a regular C# library project, implement the OM as you see fit (make sure the assembly has a strong name)

2. In project Assembly properties, mark the assembly as COM-visible

3. Reference the library in your InfoPath project the usual way

4. Create an OM member and expose it as a public property

5. You can now call methods via the new OM property

The key here is step #2, marking the assembly as COM-visible. This allows Javascript to access its members and properties.

For example, if your library is called MyObjectModel and it has a method GetData(), you can expose and call it as follows:

In your FormCode.cs:

// Initialize it when appropriate, probably in OnLoad()
private MyObjectModel _objectModel;

public MyObjectModel ObjectModel
{
get {return _objectModel;}
}

In your task pane code:

window.external.Window.XDocument.Extension.ObjectModel.GetData();

If you form needs to run in the Forms Server this becomes a little trickier because Forms Server business logic does not support member variables. Instead you have to store all state in the FormState object. Because of that you have to make your OM class serializable by adding [System.SerializableAttribute()] to the class definition.

Your public property then becomes this:

public ObjectModel ObjectModel
{
get { return (ObjectModel)FormState["ObjectModel"]; }
}

This assumes you have initialized FormState["ObjectModel"] to an instance of your OM class, for example, during OnLoad().

Important: if you are using InfoPath 2007 OM, you need to mark both your business logic and the OM assemblies with the [assembly: AllowPartiallyTrustedCallers()]attribute.

Conditional Shredding in DBXL

davidair — Wed, 17 Oct 2007 15:35:00 GMT

The DBXL shredding allows mapping the data fields of each saved document into a database table. But what if we do not want all data to be mapped? It turns out that one can leverage the power of XPath to create arbitrary conditions on when the shredding happens.

The way DBXL shredding works is as follows: first the mapping XPath is executed, then if it results in a nodeset it is inserted into the database. So in order to prevent shredding, simply provide an xpath that does not evaluate to a nodeset! To ensure that the entire document does not get shredded into a table based on some condition, set that condition on the very top xpath, the one that maps to the table.

Here is a specific example. Consider a very simple solution with two fields: "data" and a Boolean "shred" that specifies whether or not we want to shred the data for that particular document:

Assume a table named ConditionalShredding exists in the database, one that contains a docid column and a column for the data:

Usually, when mapping to this table, one would map /my:myFields to the ConditionalShredding table, DBXL::docId special value to the docId column and the my:data field to the data column. With conditional shredding, it's all the same, only you specify the condition on the root node:
/my:myFields[my:shred = 'true']
Now only documents which have my:shred set to true will shred into the ConditionalShredding database even though all documents will still be stored in DBXL! Here is a screenshot of the complete mapping for this example:

Note: if using a condition on the root node, the current version of DBXL can no longer detect that a child node is under the root path. This just means that you have to type all the column xpaths in manually. However, there is a trick to make this easier:
First, do all of your mapping as you would usually do it, and only then add the condition to the root node, as the very last step.

How to migrate complex solutions using the Migration Tool

davidair — Thu, 06 Sep 2007 06:48:00 GMT

In a previous blog post I have introduce the DBXL Migration Tool that allowed automating many DBXL operations. Today I will outline a general approach for migrating complex solutions involving multiple document types (i.e. InfoPath Templates) and database tables.

The Migration Tool typically works with the following folder structure:

Samples
                DocTypeName
                                mapping.xml
                                install.sql
                                SampleDocs

(In the example scenario that comes with the tool, the full path is C:\Temp\Samples\DocType)

While both mapping.xml and SampleDocs are automatically created by the export runs, the install.sql has to be created manually. We will discuss how this is done as we go through the migration process. This process can be broken up into logical steps:

1. Analyze your solution

Suppose you need to migrate a Timecards solution. The first step is to edit the Timecard configuration in DBXL and to inspect what tables it is being shred into. Let's say that Timecards shred into two tables: Hours and WorkItems.

The second step is to figure out the dependencies. For this, design the form in InfoPath Designer and inspect the secondary data sources. In a typical DBXL solution, data connections such as QueryDocuments are indicative of a dependency. Verify the web service arguments to find out what the doctypes we depend upon are.

Finally, the third step is to repeat the same process for all the doctypes we've found in the step 3.

When done, you will end up with a sort of a Solution Manifest (you can create a text file for convenience, for example) that will list all doctypes and dependencies. For example, in the Timecard case this could be:

Main Document Type: Timecard
Shreds into: Hours, WorkItems
Depends on: TimecardConfig
Depends on: Employees
Shreds into: Employees
Depends on: Projects
Shreds into: Projects

2. Create SQL scripts

Now that you know what your solution is composed of, it is time to create the SQL scripts. If you are lucky you already have them around, otherwise you'll need to re-create them. The easiest way to do so is by using the SQL Server Management Studio. Create one install.sql file per doctype that creates all the tables this doctype needs. Place the SQL scripts in the locations where the Migration Tool will drop the mappings and actual documents. In our example, those will be:

C:\Temp\Samples\Timecard\install.sql – contains SQL code to create Hours and WorkItems tables
C:\Temp\Samples\Employees\ install.sql – contains SQL code to the Employees table
C:\Temp\Samples\Projects\ install.sql – contains SQL code to create the Projects table

3. Configure the tool

Configure the Migration Tool to export the four document types (Timecard, TimecardConfig, Employees and Projects) along with their documents into C:\Temp\Samples (see the previous blog post for more details)

4. Run the tool - export

Execute the tool with the Export run. This should export all the data you need.

Caveat: Often enough, complex solutions shred into a separate database which might not exist on the target DBXL machine. Because of this, install.sql scripts will fail to run. To solve this problem, create a fake doctype called ensured in the Samples folder and put an install.sql script (without the mapping). The code in it could look like this:

IF NOT EXISTS( SELECT * FROM sys.databases WHERE name = 'YourDB' ) CREATE DATABASE YourDB
GO

USE YourDB;

IF NOT EXISTS (SELECT * FROM dbo.sysusers where name='NT AUTHORITY\NETWORK SERVICE')
BEGIN
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N'db_datareader', N'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_addrolemember N'db_datawriter', N'NT AUTHORITY\NETWORK SERVICE'
END
GO

5. Reconfigure the tool

Configure the Migration Tool to import the four document types along with their mappings. Make sure that you pass the correct DB connection striong to the ImportMapping method!

6. Run the tool - import

Execute the tool with the Import run. This should complete the migration by importing all the data into the new DBXL instance. Voila!

Automating DBXL tasks with the DBXL Migration Tool

davidair — Thu, 30 Aug 2007 04:29:00 GMT

Two commonly requested features for Qdabra DBXL are automatically populating the database and migrating content from one site to another. We have developed a tool that allows you to do both.The DBXL Document Deployer is an XML-driven, command-line tool with pluggable architecture that is simple to configure and that can automate many operations you'd typically do manually via the DBXL Administration Tool (also known as DAT). A Document Deployer plug-in called the MigrationTool allows you to easily export document types along with their settings and documents from a DBXL instance and then subsequently re-import them into another one.

Getting Started

Download the tool here: http://www.infopathdev.com/files/folders/document_deployer/default.aspx

The first step is to configure the tool to point to your local DBXL. Do this by selecting Programs/Qdabra DBXL Migration Tool/Configure from your start menu, changing the value of DbxlRoot to your local instance and saving the configuration file.The Document Deployer tool uses XML Scenario files that define the actions that need to be executed. Each scenario file is split into two sections: the complex ActionDefinitions part that defines various DBXL actions and the Runs section which invokes those actions.Defining action definitions can be a daunting task but do not worry, you can do a lot with the existing sample action definitions that ship with Document Deployer! To start the Document Deployer shell, select Programs/Qdabra DBXL Migration Tool/DBXL Migration Tool.

Here are a few examples of what you can do:

Create sample taxonomy for the QdCatalogBase document type that ships with DBXL 2.1:

DocumentDeployer.exe Scenarios\QdCatBaseTaxonomy.xml CreateTaxonomy Note: taxonomies are used to browse forms via the QdCatalogBase taskpane.

Deploy sample documents for the QdCatalogBase document type:

DocumentDeployer.exe Scenarios\DeployQdCatBaseInstances.xml PopulateCatalogBase

Publish a new document type that contains pictures:

DocumentDeployer.exe Scenarios\DeployPictureForm.xml PublishPictures

Bulk deployment of documents with pictures (requires a previous PublishPictures deployment, requires Vista):

DocumentDeployer.exe Scenarios\DeployPictureForm.xml BulkDeploy

Export the QdCatalogBase document type and all its documents

DocumentDeployer.exe Scenarios\Migrate.xml ExportCatBase

Export the QdCatalogBase document type, all its documents and the complete taxonomy

DocumentDeployer.exe Scenarios\Migrate.xml ExportFullCatBaseIf you want to achieve other tasks either create a new scenario file or modify an existing one. Mind you that you can import action definitions from other scenario files without having to copy them over! See Scenarios\Migrate.xml for an example.DocumentDeployer comes with two documentation files (available from the Start menu via Programs/Qdabra DBXL Migration Tool) that contain more details on the tool itself and the MigrationTool plug-in.

List of all Scenario files and their Action Definitions

QdCatBaseTaxonomy.xml

This scenario defines actions that can populate the taxonomy for the qForm solution that ships as part of DBXL 2.1. It contains the following action definitions:

-          AddHierarchy: adds a hierarchy to a specified doctype
-          AddCategory: adds a category for a specified hierarchy
-          ChainCategory: puts one category under another
-          EnumerateDocumentIds: lists documents matching specific criteria
-          RemoveEnumeratedDocuments: removes listed documents

DeployQdCatBaseInstances.xml

This scenario defines actions that can create instances of the qForm solution and accomplish complex tasks such as tagging documents and assigning workflow. It contains the following action definitions:

-          GetHierarchy: gets the docid of a specific QdCatalogBase hierarchy
-          EnumerateDocumentIds: lists documents matching specific criteria
-          RemoveEnumeratedDocuments: removes listed documents
-          RemoveAllDocuments: removes all documents for a doctype
-          TagDocument: tags a document with a specified category
-          AddEmailFlow: adds e-mail flow to a document
-          AddCatalogBase: adds an instance of the qForm

Cleanup.xml

This scenario contains only one generic RemoveAllDocuments action definition that eases DBXL cleanup.

DeployPictureForm.xml

This scenario demonstrates the use of compile-on-the-fly plugins and bulk deployment. It contains the following action definitions:

-          UploadDoctype: uploads a hardcoded doctype (can be modified to be made more generic)
-          AddPictureDocument: uploads one document with a base64-encoded image
-          BulkDeploy: uses an external plug-in to upload all sample pictures (requires Vista to work as-is)

MigrateCore.xml

This scenario defines action definitions for the MigrationTool plugin and allows to export/import documents and doctypes. It contains the following action definitions:

-          ExportDocType: exports a specified doctype to a given folder
-          ListDocumentsForDoctype: enumerates all documents for the specified doctype
-          DownloadListedDocuments: exports documents listed by the previous action definition
-          ImportDocType: imports a specified doctype into DBXL
-          ImportDocuments: imports specified documents into DBXL
-          ExportDoctypeWithTaxonomyAndFlow: a complex action definition that can export a qForm-derived solution along with all its taxonomy and flow
-          ImportDoctypeWithTaxonomyAndFlow: a complex action definition that can import a qForm-derived solution along with all its taxonomy and flow

Each of the scenario files (other than MigrateCore.xml) contains examples of how to use the actions. For document migration, the usage examples are in a separate file, Migrate.xml. This file also demonstrates how you can easily build your own scenario by importing action definitions from existing scenario files.

Showing custom modal dialogs in managed code

davidair — Wed, 29 Aug 2007 22:34:00 GMT

A common question is to how to show custom dialogs using the InfoPath 2007 OM. In InfoPath 2003 one could use the ShowModalDialog OM function that would take an arbitrary HTML and show it. Although you can still use the old IP2003 OM in InfoPath 2007, there is a better option if you are writing in managed code, namely WinForms!

If you are writing using IP2007 OM, you don't need to do anything special, System.Windows.Forms has already been referenced for you and the using statement has been included.

If you are writing using IP2003 OM, make sure to select "Add Reference" for your project and add System.Windows.Forms. Now you can start showing message boxes or create your custom winforms and show them when needed.

Important: you cannot use WinForms when developing for InfoPath Forms Server. This is because no .NET code can run on the client and running WinForms on the server makes little sense. If you cannot use WinForms, make sure your compatibility is not set to "Browser" in Design Checker.

Scenario 1 - Show a simple confirmation box

if (MessageBox.Show(
   "Are you sure",
   "Question",
   MessageBoxButtons.OKCancel) == DialogResult.OK)
{      // do something
}

Note: in IP2003 OM, you will have to prefix each WinForms type with "System.Windows.Forms". You cannot simply add a "using" statement because WinForms types will clash with the IP2003 ones. However you could use aliases in your using, for example:

using WF=System.Windows.Forms;

Scenario 2 – Show a custom dialog box

Showing a custom dialog box is as simple as adding a new WinForm class to your project. Simply right-click the project in SolutionExplorer, select "Add" and then "Windows Form".

Here is source code example for a Message Box that can show/hide additional details:

http://www.infopathdev.com/files/folders/examples/entry41183.aspx

To invoke it, call the following: new MessageBoxWithDetails(caption, message, details).ShowDialog();

MessageBoxWithDetails is good to show exception data with the stack trace going into details, for example.

Implementing Cascading Dropdowns in Forms Server

davidair — Fri, 24 Aug 2007 00:15:00 GMT

The InfoPath Team Blog has a great article on how to implement cascading dropdowns in InfoPath Forms Server (IPFS) without writing code: http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx. Unfortunately, this approach does not work in repeating context for the same reason Cascading Dropdowns are so hard in IPFS: there is no filtering on dropdown values.

However, if you absolutely have to have those in a repeating table or a section, there is a workaround. It is not pretty because it requires you to write code (hence the form needs admin deployment which is much harder), it pollutes your main DOM and it is less performant (postbacks are needed any time you change the "master" dropdown). However it works!

The approach relies on the fact that the data source for a dropdown can reside in the main DOM as well. Consider the following schema:

myFields
myRepeatingTable
myCategory
mySubCategory

There are two dropdowns in the repeating table, one bound to myCategory and one to mySubCategory. If the values for the second dropdown come from a secondary data source, they will be the same for all rows which is obviously not desired. However, adding the source under myRepeatingTable solves the problem:

myFields
myRepeatingTable
    myCategory
    mySubCategory
    mySubCategoryValues

Now all you need to do is to bind the second dropdown values to mySubCategoryValues and then write an OnChange handler for myCategory to modify those dynamically.

You can find a fully functional example here:

http://www.infopathdev.com/files/folders/examples/entry41178.aspx

The zip file contains the ready-to-deploy XSN as well as the source code.