1. Download and install the Qdabra DBXL Migration Tool.
2. Download the Scenario File for documents reshredding.
3. Open the Migration Tool UI.
4. Make sure DBXL Server Root is set to your DBXL (normally http://myserver/QdabraWebService).
5. Select the "custom" tab and load the scenario you've downloaded in step 2.
6. Scroll down in the scenario variables list and set "docType" to the document type you want to clear.
7. Scroll down the Scenario Variables list and change sourceDBXL/targetDBXL.
8. Select "ReshredDocuments" in the scenario runs and click "Run". This should reshred all the documents from the specified document type:

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

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/community_uploads/entry30638.aspx

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!

1. Download a copy of the Qdabra DBXL Migration Tool from here: http://www.infopathdev.com/files/folders/dbxldownload/entry24682.aspx
    
2. Download a scenario file for documents removal:
   http://www.infopathdev.com/files/folders/dbxldownload/entry30210.aspx
   
3. 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:  

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.

Prerequisites

Qdabra DBXL system version 2.1 or later as well as DBXL Migration Tool build 20071119.4 or later. This document also requires prior familiarity with the Migration Tool.

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:

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:

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

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.

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;

In your task pane code:

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:

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.

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.

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;

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!

Getting Started

Download the tool here: http://www.infopathdev.com/files/folders/dbxldownload/entry24682.aspx

Here are a few examples of what you can do:

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

2. Deploy sample documents for the QdCatalogBase document type:

3. Publish a new document type that contains pictures:

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

5. Export the QdCatalogBase document type and all its documents

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

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:

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:

Cleanup.xml

DeployPictureForm.xml

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

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:

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.

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.

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/community_uploads/entry22764.aspx

To invoke it, call the following:

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

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:

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