One of the many fantastic things about InfoPath forms is that we can dictate a data type for a field. If we set a field to be a decimal, for example, the poor user determined to enter an 'a' will always be thwarted.

But if you don't understand what the data for a data type really looks like, it is pretty easy to find yourself in a mess of validation errors. You know better than to set a decimal field to 'a', of course, but what about a date? What should a date look like if you want to set an InfoPath date field? What happens if you try to set a date field to the value of the function now()? Why is the letter "T" even in the title of this post?

All gripping questions that we are now going explore.

First, don't be fooled by form controls. Given the following data structure:

We can add date pickers to our form for any of those data types:

And, I bet if I asked you right now what the data looked like, you'd say "Month, slash, Day, slash, Year". If I asked you to set a rule to set that field's value, you'd use "9/8/2010", for example, and feel very pleased until you saw the data validation errors:

Only the date picker bound to the text field doesn't care about the value. But that control and the field it is bound to are not very discerning. You can type whatever you want in there:

Why is InfoPath being mean to me? If I save the XML from my original image, where I had no validation errors: 

Try to look past the xsi namespace declaration and focus on the values:

Now, let's look at the XML from the sample with the validation errors (this time I'll exclude the namespace declarations):

Especially interesting is the dateTime field. Even when we don't have a time filled out, that datatype requires "T00:00:00".

No matter how we format our control to display, the date and datetime date types will have a specific format. "yyyy-MM-dd" for date, "yyyy-MM-ddTHH:mm:ss" for datetime. And if you set a datetime to the today() function value (which returns a date) or a date to the now() function value (which returns a datetime), you will have validation errors. If you are uncertain why you have an error, you can always save the XML and take a look in a text editor. Select a value using a date picker, and compare to the value you are trying to set the field to.

You can also change your control to show the XML value of the field. Right click the control and open its properties, then click the Format button:

Select the top option:

Preview and select some dates:

Keep this in mind when you are copying data into your form fields from other data sources as well. A SharePoint list may have a field that looks like a date, but is actually a datetime.... and missing the "T" separator between the date and the time! Copy that into a date or datetime field in an InfoPath form and it will behave as if you have surgically removed its birthday.

So remember -- with form controls and data types, you may not be seeing what you are getting at all. If you are troubleshooting a validation error, look at the XML data, make sure the controls you are debugging with are showing you the actual data, and you'll be able to make everything work nicely in no time.

Wouldn't it be nice to be able to use images attached to an InfoPath form in a SQL report? If you are using DBXL to submit your InfoPath data to a SQL database, you are in luck.

InfoPath stores image files in Base64 encoding. Using your document type mapping in DAT, you can shred the value of your picture attachment field to a varchar or nvarchar field in your database. SQL report picture controls can then turn that Base64 back into a picture for display in your report!

For this demo, I'm going to modify the sample Asset Tracking form that ships with InfoPath 2007. I'm going to assume you know how to use InfoPath and modify forms in design mode, that you have DBXL installed and know how to create a document type and a SQL mapping, and that you know how to create a basic SQL report.

First, add a field for pictures to the main data source:

Next, add a control to the form for the pictures:
 

After the form is ready to go, set up your tables for shredding, including a field for submitting your pictures:

Next, in DAT, make sure your form's picture field is mapped to shred to your table:

Create a new report that uses the tables you are shredding your data to. Mine has a simple table bound to my dataset:

Then, in your report, add a picture control for the image data. If you are using SQL 2008, good on you, this is going to be super easy:

But hey, if you are working with SQL 2005 (and lots of us are, don't worry about it), it is still pretty easy -- you just need to use an expression and convert your Base64 string. After adding your image control to your report, modify the properties to use the expression =System.Convert.FromBase64String(your picture field here):

Fill out and submit a few forms to DBXL:

And, after you have data with pictures in your SQL tables, run your new report to see the images:

The best of all worlds -- InfoPath to fill out your forms, DBXL for saving your data to SQL, and SQL reporting to give you aggregated info, including your images!

Adding qRules to your form is simple -- just browse to your form and click Inject and you are done! But what if you want to remove qRules for some reason?

Currently, that is a manual process, but it can be done.

First, use the Logic Inspector to find any rules that are using the Command node from the QdabraRules data connection:

That will help you better understand what will need to change after you remove qRules. Next, you will need to remove one or two data connections, depending on your version of qRules. If you have the trial version, you'll need to remove both the QdabraRules and the QdabraRulesValidation data connections. If you are using a purchased version, you will only need to remove the QdabraRules data connection:

Next, navigate to Tools / Resource Files:

Remove the QdabraRules.xml file:

Finally, we need to remove the .dll that contains the qRules logic. The easiest way to do this is to add code to your form. For example, add a loading event:

Close the Microsoft Visual Studio Tools for Applications window that opens after adding the event, then navigate to Tools / Form Options:

Go to the Programming category, select the Remove Code buttton to remove all code from your form:

This is going to be short and sweet. The other day I ran into an issue that made me aware of a little ... inconsistency .... in Visual Studio that I'd like to make sure everyone else is aware of to.

I have a table that looks like this:

I have a form template with a data connection to that table. 

I have a bit of code that looks like this:

What you can't see is that I have a trailing space in my Orders table for PO Number def-456. And if you look in the XML visualizer for the data source, you won't see it there, either....

Opening the Text visualizer for the OuterXml for the nav variable tells a different story:

So, if you are troubleshooting some code, trying to figure out why you aren't getting back a node that you are just sure should be in your data source... take a minute to look at both visualizers. It may save you some time and at least a bit of your sanity.

Licenses can be added for DBXL using the DBXL Admin Tool (DAT) or using the DBXL Licensing web service.

To add a license via web service, you must open a web browser on the server where DBXL is installed. Copy your license file to that server, and make a note of the file path.

In your web browser, navigate to:
http://<servername>/QdabraWebService/default.htm
with <servername> replaced with the correct address for your install.

Select DBXL License Management from the Web Service descriptions:

Select AddLicenseFromFile from the DBXL Licensing page:

The method page opens:

Enter the contact email for your license. Enter the file path to the local copy of the license:

Click the Invoke button:

The XML response will show Success as true or false. If there are any errors configuring the license, those will be returned as well. For example, if you add a license that is alread installed:

Be sure to check out the other methods for licensing as well. You can use GetLicenseInfo2, for example, to return the information about installed licenses, and then use the serial number returned for the RemoveLicense method.

If you've ever tried to speed up a slow form, you've probably come across this link at MSDN. It is great and has some useful advice and troubleshooting.

However, I have found one single culprit that has caused an unbelievable performance hit in some of my forms, and recently talked to another designer who was able to fix a slow form by de-selecting a single checkbox in a dropdown control:

In both cases, de-selecting the checkbox to show only entries with unique display names quickly resolved performance issues. So, if you have a slow form, by all means, check out the suggestions on MSDN, but first, try changing your drop down controls and see if it makes a difference for you as well.

qRules 2.0 is here, and it has a whole host of cool new commands. One of the real superstars is SubmitToSharePointList. While this command takes a bit more set up than the standard qRules commands, I assure you it is worth the trouble. Once you've walked through this demo with me, I think you'll agree.

Pre-requisites

You will need to have installed qRules v2.0 on your machine. You'll also need to install the InfoPath to SharePoint List Tool that ships with qRules v2.0, as we will use that tool to create the form to library mapping. And, of course, you'll need InfoPath 2007.

The Form

Let's make life easy and start with the Sample Expense Report that shipped with InfoPath 2007. Open InfoPath and in the Design a Form Template dialog, select Customize a Sample:

From the Getting Started dialog, select Sample - Expense Report:

When the template opens in design mode, save a copy some place where you'll be able to find it again. Then close the template. We'll want to inject it with qRules before we go any further, but first, let's create ....

The List

Create a list on a SharePoint site for your Expense Items from your Expense Report form. I've created a custom list that has columns for the Item, the Item Date, the Category and the Amount:

Note the data types for the list items! Amount is currency, Item Date is Date and Time.

Injecting

We need to inject our form template with qRules in order to be able to use it. Make sure your form template is closed, and then open qRules:

Browse to your form template, or drag and drop the template file onto the injector window and select the Inject! button. Logging information will be written to the injector's output window, and you'll get a message that the injection was successful:

Select Ok in the success message dialog, and close the qRules Injector.

Mapping

Next we need to create the mapping.xml file that qRules will use to know what form field should be submitted to what list column. For this activity, we will use the InfoPath To SharePoint List Tool that was included with your qRules v 2.0 purchase (fun side note -- you can also use this tool without qRules for bulk list data loading operations!)

You can open the tool from the install location (the default is C:\Program Files\Qdabra Software\Qdabra InfoPath to SharePoint List Tool) or from InfoPath. The form will open to the Import Forms tab. That tab is for bulk upload operations -- for simple mapping, select the Define Mapping tab:

Attach the Expense Report template that we just injected with qRules to the Source XSN field:

Fill in the site URL for the SharePoint site where you created your list for your Expense Report items, and select the Get SharePoint Lists button:

Select the list you created, and select the Extract Schemas button:

Our Item data in our Expense Report form is repeating, so select the Data is from Repeating Group check box in the Mapping Definition section:

Select the ... button next to the Repeating Group field:

A custom task pane will open, displaying the schema of our attached template:

Navigate to the repeating item node:

And double-click to select and populate the Repeating Group field with the correct XPath:

We can now use the task pane to map the form fields from the item group to the correct columns in our list. Select the ... button next to the Form Field column field:

And double-click the date node inside the item group:

From the drop down list control in the SharePoint Column column, select the column you wish to save the date information to -- in my sample list, that would be the Item Date column:

Add another row to the table and continue mapping in this manner until all your fields and columns are mapped:

Select the Save As qRules Mapping button to save the mapping.xml somewhere you can easily find it in the next steps. You can name the file whatever you choose, but mapping.xml is the default:

Add Data Connections to the Form

Open your injected Expense Report in design mode. We need to add two data connections to our form. First, we need to add a receive data connection to the mapping.xml file we just created.

Navigate to Tools > Data Connections to open the Data Connections dialog:

Select Add to add a new data connection:

Select Create a new connection to Receive Data:

Select Next, and then leave the source as XML Document:

Select Next, then browse to the mapping file we just created:

Select Next, leaving the default as is, to add the XML file as a resource file to the form:

Select Next for the final time, and either leave the default name or give the data connection a new name (make a note of it! You'll need it in a few short minutes!). Be sure to leave the default selected to automatically retrieve the data when the form is opened and select Finish to complete this data connection.

The Data Connection dialog should still be open, so save yourself a click or two, and select the Add button again. This time we are going to add a submit data connection to the SharePoint List we want to submit our data to:

Select (you guessed it, you clever thing) Next, and leave the default of To a Web service:

Enter the URL to the lists web service for the site where you created your Expenses list:

Select Next and select the UpdateListItems operation (no, the operations are not in alphabetical order. Yes, that does bother me, too):

Select Next. We are going to use values from our mapping data connection for our operation parameters. For the tns:ListName parameter, select the ListCollection node from our mapping data connection:

The Data Connection Wizard dialog will look like this:

For the tns:updates parameter, we'll select the entire Batch node from our mapping data connection:

The Include drop down needs to be changed to Entire XML subtree, including selected element:

Before selecting Next, take a minute to verify that your Data Connection Wizard looks like mine:

Select Next. You can leave the default data connection name, or enter one of your own choosing before clicking Finish. Make sure you name it something you can remember, because we'll need that information for our qRules command!

Close the Data Connection dialog -- we have all our data connections in place.

Add a Field for the List Item ID

This bit is optional. But if we want to connect our form items to our SharePoint list, so that we can update existing items rather than always creating new items, we need an ID field in our repeating group to hold the value.

Open the Data Source Task Pane in the form and navigate to the repeating item node. Add a field to hold the ID -- I named mine 'id', but you are welcome to get all creative. I also moved mine to the top of the group -- if you are less neurotic than I (likely), feel free to leave the field at the bottom of the group:

Add the qRules Command

After all that setup, adding the actual command may be a bit anti-climatic, and I apologize for that. However, now that all the pieces are in place, we have it pretty easy.

Navigate to Tools > Submit Options:

This form is already set up to submit via email to the email entered in the managerEmailAddress, so we are going to modify the submit options to use rules. We'll submit to our SharePoint List using a qRules command, then send the form to the manager in email.

Select Perform custom action using Rules in the Submit Options dialog:

Select the Rules button:

The Rules for Submitting Forms dialog opens. Select the Add button to open the Rule dialog:

Give your rule a meaningful name (trust me, you do NOT want to get me started on why 'Rule1' is a miserable naming convention), and select the Add Action button to open the Action dialog:

The action will be Set a field's value, and the field to set will be the QdabraRules Command node:

And finally, the value will be:
SubmitToSharePointList /submit=ShPListSubmit /mapping=mapping /id=my:id

The /submit parameter takes as a value the name of the data connection we set up to the lists webservice for our SharePoint site. The /mapping parameter takes the name of the data connection we set up to the mapping file we created with the InfoPath To SharePoint List Tool. The /id parameter takes the relative XPath to our Id node (if we weren't working with repeating data, it would take the full path).

Set up one more rule with the action of submitting using a data connection to include the email submit the sample came with.You'll have two actions now in your rule:

Select OK until all the dialogs are closed.

And now, to test!

Previewing the Form

Preview your expense report. Fill in an email for the 'manager' (I always use my own when testing) and then add some items to the report:

Check your SharePoint list after submitting:

Ta-da! Items!

But wait, there's more. Perhaps we didn't really go to Disneyland, we went someplace a little less...exotic. Change the value in the form:

And submit again. Refresh (or re-open) your SharePoint list:

Who's got your back, baby? Why, Qdabra, that's who. qRules populated our id node, and now those expense items are connected to the list items.

Sure, sure, you can do this with a CAML query as outlined here. Of course, if you do that, you've got to go off and decode the list GUID. You've got to know what SharePoint is calling your columns. For example, in my list, the Item column started out as the Title column, but I renamed it. SharePoint still thinks of it as Title. The Item Date column -- I initially called that Item, so SharePoint will call it Item from now till eternity. And, if you want to take the CAML approach and you have repeating data, you are going to have to write some code... or have some little button on every single row for single items submits.

For simpler set up, for no code, and for the nifty update feature, I'd take the qRules route. I hope you will, too!

Errors

What? Something went wrong? If you followed the tutorial but when you use the SubmitToSharePointList command in qRules you don't have any items auto-magically appearing in your list, be sure you check the QdabraRules data source error node. We return errors to a node to ensure that you can use qRules in browser forms, but it does mean that you need to take responsibility for error handling. For testing purposes, you can just drag the error node onto your form canvas, click the submit button and go from there. For production, you can return the error in an expression box or a dialog box expression (depending if your form needs to be browser compatible). For additional information on this and other great qRules commands, check out the documentation or the sample form that you'll find in your install location (generally C:\Program Files\Qdabra Software\Qdabra Rules Library).

Also, both the SubmitToSharePointList command and the InfoPath to SharePoint List Tool will not work with attributes. The fields you are trying to submit to SharePoint need to be elements.

So, you've invested in DBXL, which is great news for your organization -- all of our web services can speed your form development, help you create more performant forms, give you a great deal of flexibility for saving the data from your forms to different SQL tables and generally make you look like an InfoPath super-hero.

There's just one problem. You have a legacy form, built the old-fashioned way, based off a database. You have one (or more) tables that this form has been submitting to. You want to be able to update your existing data, like you can with your legacy form, but you want to start using DBXL for submitting your data to SQL. Patrick Halstead describes the current DBXL architecture here, and since DBXL takes a delete then insert approach to SQL, you don't have a way to update your existing rows..... Now what?

Never fear. A little SQL magic and you'll be back in your cape, saving the day yet again.

At the highest level, we'll create a new form, starting with a blank form. We'll use Qdabra's QueryDB web service to return existing data to the new form and add a trigger to the main table we shred to that will delete the loaded data when we submit via DBXL. Other Qdabra tools like qRules can assist for special cases, like forms with repeating data sections.

The Scenario 

For our sample case, I've created a simple database bound expense report form:

The tables have auto incrementing IDs and a foreign key relationship:

I've already submitted to these tables using my SQL bound form:

ExpenseReports (Parent Table)

ExpenseItems (Child Table)

The New Form

In order to make use of this existing data with a form that has been created to submit to DBXL, we will first want to recreate the form, starting from a blank template:

My new main data source collects the same data that my old SQL bound form did:

Using Qdabra's QueryDB web service, I've added data connections to first return data from the ExpenseReports table, so that I can select from an existing record:

My drop down is bound to the ID field in my form, and while the display value is the name, the value is the record's original ID.

I've added rules to my ID field to populate the Name and Purpose when an ID is selected. I've also added a rule to run another QueryDB query to return the items for the selected expense report. Since InfoPath doesn't currently include a way to copy data from one repeating table to another, you will need to either use code or qRules to copy the returned data if you have a repeating section. I've chosen to use qRules:

I've added a web service Submit connection to my form, and I'm using the Submit method of the DBXLDocumentService to sumit my form. At this point, then, my new form can load data previously submitted by my SQL bound form. If I create a new document type in DBXL and create a SQL mapping for my form and database as they currently exist:

I'll be in for a surprise when I submit my form after having loaded it with existing data and modified some information. I'll end up with new SQL rows for the same data!

The Workaround

First, we need to make some database changes. Some of you will not be overy excited about what I'm going to suggest here, so please bear with me -- I have another solution that may sit more comfortably with you, but does involve code, and I'll talk about that at the end of this post. For those of you who don't want the overhead of adding, writing and maintaining code, this workaround may be acceptable.

We need to set our ID on our main table to not auto increment, remove the foreign key relationship between our two tables, and change the ID column on our main table to not be the primary key. In fact, our main table will be without a primary key all together.

I'm not crazy about removing keys and relationships from my tables. However, since I’m only going to allow data to be entered into my tables from DBXL, and I know that I’m setting up my mapping with the DocId (an int unique to my DBXL install) as the primary key for my parent table and the foreign key to my child table, I feel like I am minimizing my risks. If there comes a day that all of my pre-existing data has been loaded into XML and resaved to the database, I’ll be able to set the DocId as my key in my SQL tables.

Disclaimers now in place, we'll forge ahead. We need to add a column to both our parent and child tables for the DBXL DocId and remove the key and auto increment on the ID field in our parent table. We also need to remove our foreign key relationship:

I'll modify our database mapping to incorporate the DocId. I'm going to manually select the DocId as the key for the parent table. I'll use the DocId field to create a relationship between my tables. I also want to be sure I'm still shredding the original ID from forms that had been previously submitted, because I'll need that information from my inserted data for my trigger:

Finally, I'm adding a trigger to the parent table. This trigger checks to see if there is a row in our parent table that has the same ID as the data being inserted, but a null value in the DocId field. If such a row exists, we know we are dealing with data that has not been inserted by DBXL before, so we need to delete the original rows:

USE [QdabraSamples]

GO

 

/****** Object:  Trigger [dbo].[DemoDelete]    Script Date: 10/12/2009 15:12:40 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

-- =============================================

-- Author:                                Hilary Stoupa

-- Create date: 2009-10-02

-- Description:         Demo Delete Trigger

-- =============================================

Create TRIGGER [dbo].[DemoDelete]

   ON  [dbo].[ExpenseReports]

   AFTER INSERT

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;

               

                Declare @id int;

                Set @id = (Select ID from Inserted);

               

                Delete from ExpenseItems

                Where ReportID = @id

                AND DocId is null;

 

                Delete from ExpenseReports

                Where ID = @id

                AND DocId is null;

 

END

 

GO

Now, if we use our new form, and populate with existing data, we don't end up with duplicate rows:

We also now have an XML file in the DBXL Administrative Tool that corresponds with our database row.

Tidying Up

Remember that drop down in the new form that allows us to select and load an existing row? That should be modified to only display rows where the DocId field is null in our parent table. Once we have an XML file for a row, we want to open that file to make updates to our data, and allow DBXL to handle the SQL tables as usual.

An Alternate Route

For the programmer, or if you don't mind the maintenance costs of adding code to your forms, there are other alternatives. For example, you can use custom code for your submit that will run a stored procedure or SQL statement to delete any rows that have the same ID as the data loaded into your form prior to the web service submit. This works well for data that has been using unique identifiers for keys.

Perhaps you are an accidental programmer. You are the person at your organization who was in charge of some laborious business process and you thought "Gee, we have this InfoPath thing. Maybe I can build a little form for this?". You build the form...and suddenly everyone wants to know if you can do this or if you can do that. You run into a couple of the things that InfoPath doesn't do out of the box, and you see some code samples hanging around....

Next thing you know, you're writing managed code. Or at least copying and pasting managed code and trying to get it to work for your form.

With InfoPath 2007 came VSTA (Visual Studio Tools for Applications). This can be an incredible boon to the accidental programmer, because you can leverage the rich debugging experience of Visual Studio to find your code issues and errors. If you are trying to adapt someone else's code sample for your form, or follow a tutuorial that just isn't working for you, one of the first things you can do is run the project in debug mode.

For the purposes of this simple sample, I've added a loading event to my form.

VSTA opens with the event added:

I've written a little code to set a field's value:

Now, I'm going to add a breakpoint. Click in the margin of the code editor window by the line you want to stop at, and you'll see a big burgundy dot in the margin, as well as a highlight on the line:

We need to run the form from VSTA, so click the toolbar button that looks like an old school tape player's Play button (also under the Debug menu, Start Debugging):

When the form hits that line of code, you'll see it hightlighted:

And now we can have a little fun. Use the Step Over button to go to the next line:

And hover your mouse over the variable that you just set to see what value it has. You can also right click the variable and either Add Watch or Quick Watch to see its values:

Let's see what happens if I have the XPath wrong in my code (a frequent cause of errors -- I'd really have thought that the IP 2007 Copy XPath feature would have prevented those, but we all love our copy / paste too much when it comes to code....):

Not too surprisingly, my XPathNavigator is null, and look what happens when I try to set its value:

Everyone's favorite, the NullReferenceException! But now I at least know the source of my troubles -- that the XPathNavigator I'd created was null. I can focus on the line that is the problem, make adjustments and run my code again.

Setting breakpoints and walking through your code while it runs will not only help you find and understand errors, it will help you understand just what the code is doing in the first place. If you are an accidental programmer, watching your code run can help you learn the syntax and logic of the code sample you've found someplace online, and help you see what you need to do to get that sample to work with your unique form. Happy Debugging!

Let's say you have a field in a repeating group that you'd like to set to the value of another field in your form. Pretty straight-forward, right? Set a rule on the field with the desired value, and when you change that value, all the instances of the repeating field get set to that value.

What? That wasn't your desired result? You only wanted to set some of those fields to the new value? All I have to say is we should be very grateful to Jimmy Rishe, who has posted an ingenious solution to this problem!

I thought it was worth a walkthrough, with a sample and some screenshots, so that's what we are going to do today.

Here is the sample form we are going to use (without the hack -- you get to do that part yourself). Right click the link and select Save Target As... to save the file locally. Once you've done that, right click the .xsn file and select Design to open the form in design mode.

A big shout out to Toby for the data structure inspiration, which looks like:

I've set up our sample form with some default values so that when you preview it, it will look like this:

I'm using a button for our rule to give us more control over when the values change. Currently, the button doesn't have any rules associated with it yet, so it won't do anything. You can click it though, if you like. It does say 'Click Me', after all.

Our goal is to set any baseCost nodes to the value in the newValue field ... but only if the itemType is equal to 'Labor'. The first step is to add a rule to the 'Click Me' button. Double-click the button to open the Button Properties window (unless you are working in Visual Studio, at which point you've just added a 'Clicked' event to your button -- one of those behavior inconsistencies that makes me mildly daffy).

Click the Rules button to open the Rules dialog and click Add to add a new rule:

Give your new rule a meaningful name:

And click Add Action and select Set a field's value for your action:

Select the baseCost field for the target field:

And click the fx button, then use the Insert field or group button to set the value to the newValue field:

Now that our base rule is in place, I want you to preview the form again. The defaults are set with $12.60 as the baseCost for the items that have 'Labor' as the itemType. Other items have different costs associated with them. Before we click the button, our data looks like:

Put a new value in the newValue field and click the 'Click Me' button. All the values in the baseCost fields get changed:

While this is exactly what we'd expect, it isn't what we want. But before we modify the manifest.xsf file, we have one more important step: SAVE YOUR FORM. When working with form files, there is always the chance you may make a change that will prevent you from being able to open your form ever again, in design mode or to fill out. It is never worth the risk. Always save a copy before saving as source files.

Next, from the File menu, select Save As Source Files... and save the files someplace you can find them. If you are using InfoPath 2003, I believe the command is Export to Source Files, but it has been long enough that I can't really remember. (As an aside, if you are still using 2003, I encourage you to upgrade, if for nothing else than the ability to copy the xpath of a field from the Data Source Task Pane. It is invaluable. You can still design forms for 2003 -- the rest of your organization doesn't have to upgrade, but if you are doing very much design work, I think 2007 will save you time and headaches.)

Open the manifest.xsf with your favorite text editor. (Another aside: I really really really love Notepad2. It has parenthetical highlighting. If you have a formula that is giving you errors, you can paste it into Notepad2 and quickly find your missing parenthesis. It also has syntax highlighting for a variety of file types.) Since the form is very simple, our rule will be easy to find -- it is at the bottom of the file. If your form is more complex, you can search on your rule name to find it in the manifest.

Our rule looks like this:
        <xsf:ruleSet name="ruleSet_1">
            <xsf:rule caption="Set Base Cost for Labor Items">
                <xsf:assignmentAction targetField="my:items/my:baseCost" expression="my:newValue"></xsf:assignmentAction>
            </xsf:rule>
        </xsf:ruleSet>

Next, we add an XPath filter to the targetField attribute of the assignmentAction element to specify that this action should only apply to our items with an itemType of 'Labor':
        <xsf:assignmentAction targetField="my:items/my:baseCost[../my:itemType='Labor']" expression="my:newValue"></xsf:assignmentAction>

Save your changes and close manifest.xsf. Right click the file and select Designto re-open the form in design mode. If it doesn't open, you've done something bad and either have to figure it out and fix it, or go find your saved version and start again.

Check your rule. If your XPath is valid, you'll see the expression underlined in the rule action:

Preview your form, and enter a new value in the newValue field. Click 'Click Me', and voilà!

To repack it all up, close the preview and select Save As  from the File menu. Have fun, amaze your friends and co-workers with your new code-free magic and don't forget to thank Jimmy!

A recent conversation (okay, a conversation over a month ago, it has been a busy spring) with an InfoPathDev user reminded me of these wonderful tutorials on MSDN. Please, please don't be afraid of MSDN. I know there is scary object model blah blah this and method and properties blah blah that hanging about, but really, you can do these and learn a lot. Even though they were written for 2003 many of the basics apply to 2007.

I especially recommend:

Lab 2: Create Forms and Layout (Level 200)
Lab 3: Form Deployment (Level 200)
Lab 4: Work with Controls (Levels 200 and 300)
Lab 5: Business Logic (Levels 200 and 400) and
Lab 8: Working with Data Sources (Levels 300 and 400)

If you are new to InfoPath, taking the time to work through some of these labs can make all the difference in figuring out the basics. Go, learn, have fun!

It is always nice to alert people via email that there is a form they should look at -- maybe you are using some built in InfoPath capabilities for your work flow rather than having to build one in SharePoint, maybe you just want to make sure that the right people have the right information at the right times -- let's face it... modern humans use their email in-boxes as their task scheduler and to-do list, and if you want people to go look at data, you have to make it as painless as possible.

Adding a link to a filled out form to an email can be a great way to facilitate people actually opening and reading filled out forms. With DBXL, we have the option of shredding the link to the form to the same data table as the form data... which means we could use QueryDB to get that link back!

Here's the sky-high view of what we are going to do -- using submit rules, we'll submit our form, query a data source to get the link of our form back, display that link in the form, and email the form to the person we think should care about it, with the link to the form in the email, so that if there is action they need to take, they can open the form with a single click.

Set up Database and Mapping

For the purpose of this blog post, I'm going to assume you've already got a DocType set up in DBXL that has a data connection that uses the SubmitDocument method of the DBXLDocumentService, and you have database mapping set up. The reason I'm going to make that assumption is we already have lots of documentation on how to do that and I don't feel like writing more, just to cover this one technique. I'm sorry if that is disappointing, but we can't start from scratch on every blog post (i.e., I've also assumed you can design a simple form and create a simple SQL table...) 

When mapping to a database in DBXL, we can map form data to the table, or we can map DBXL Mapping Tokens, like DocId, DocType or the Link to the document:

For the document you'd like to email a link for, add a column to your SQL table for the link, and add mapping:

Save your changes.

Set up Data Connections

Open your form in design mode. Under Tools, select Data Connections. The Data Connections dialog opens. You'll see any existing data connections for the form. Add a new Submit data connection, and submit as an email:

While you are testing, you may want to set the 'To' field to your own email address.

Add another data connection -- this time it will be a recieve data connection. Using the QueryDB web service's  GetColumnsXMLStrQuery method, query the same table your form shreds to. Don't set this data connection to get data on load -- we'll want to tweak the query xml in our submit rules later so we get only the data we need (after all, we have this power to get filtered data, let's harness it!).

My form now has three data connections -- one for my DBXL submit, one for my Email submit, and one to get my link back from the table my form data shreds to:

Add Conditional Formatting

Since we are form designers, we are probably picky about aesthetics. We don't want to clutter up our canvas with stuff the user doesn't need. And, if the user has the form open, they don't need the link. However, it is nicest to put the link at the top of the form, so that it shows up at the top of the email (no scrolling for the lazy recipient - just click and open).

A simple way to do this is with a boolean field. I'm calling mine IsEmailing. Set the default to false. Now, at the tippy top of your form, add an expression box:

Set the value to either just the column in your secondary data source connection you set up earlier to get your link back from your database table....Or do something fancy, like use concat to add some additional verbiage:

Conditionally format the expression box to hide if IsEmailing is FALSE:

Set up Form Submit Rules

If you want to get fancy and also be able to close the form after all these rules, you probably want to create your own button, label it submit, and use ordinary button rules, so you have the option to select 'Close the Form' as an action. Otherwise, you can use submit rules.  

Under Tools, select Submit Options:

Select Perform custom action using Rules:

and click the Rules button. The Rules for Submitting Forms dialog opens:

Select Add:

The Rule dialog opens. Give your rule a meaningful name (if not for yourself for future you, who will thank current you in 10 months when you open this thing to make a change and all your rules are called Rule 1 and future you calls current [actually, at that point, it will be current you and past you] you a bad name) and select Add Action. Make the first rule action to submit the form to DBXL, using the data connection in your form that currently does that:

Add Action and set the queryXml field for the GetLink dataconnection to get back just your form link.... now, this may take a little trickery. You have to know what form you just submitted. In general, if you are shredding a DateTime from the form you have a pretty good chance of using that for a match. Keep in mind though, you can filter on as many fields as you are shredding... so you could match on Name, Date and Description (or whatever your data looks like):

AddAction and query with your data source that uses QueryDB to return data from your table.

AddAction and set IsEmailing to true(), using the formula editor to select the true() function.

AddAction and submit with the email data connection.

AddAction and set IsEmailing to false(), using the formula editor to select the false() function. In the end, you'll have rule actions a lot like these:

Test the Form

Save your changes and publish the template to DAT. Open the form, fill it out and submit it. Check your email....

Ta-da!

Sometimes we want to create a new template off an existing schema. Microsoft has a great article here, but for those of you who have already created a form off an existing schema, and just want to know how to unlock it, it can be a bit much to dredge through (also, they don't even mention the little .xsf hack I'm going to show you....). Fortunately, you have me, and I feel your pain.

The Problem

You saved a beloved form as source files, because its schema was perfect, and you want to base another form off of it. You create a new form template, chosing XML or Schema for the source:

and you happily design away... until you want to add a field. Or change the name of an existing field. That's when you realize.... you're stuck:

See those little locks on the data fields? And at the bottom of the Data Source Task Pane, Add a Field or Group is grayed out....

The Reason

If you read the article I've linked in the first paragraph, it says:

When you design a form that is based on an external schema, Microsoft Office InfoPath 2007, or Microsoft Office InfoPath 2003 assumes that the schema is finalized and therefore prevents any modification of the schema in the Data Source task pane.

So there you have it. InfoPath figured you were perfectly happy with the schema when you built the form off it.

One Solution

You can manually add elements to your schema. Save your form as source files and open myschema.xsd in a text editor. Here is my original schema:

And now I've added another string field named String2:

When I save my change and open manifest.xsf in design mode, I can see my newly added field:

Another Solution

But what if you just want it unlocked, so you can add fields like a normal old data source? For that, again, we need to save as source files. Open manifest.xsf with a text editor. Look for your schema file in the files element. You'll see a property named editability which has helpfully been set to 'none':

Set this property to 'full':

Save your changes, and open your template in design mode again:

All your fields will be fully editable.

The Usual Warnings Apply

Save a copy of your form someplace safe before manipulating form files. This is by no means a full discussion of potential pitfalls that can be encountered -- more information on xsf structure is available here. Happy unlocking!
 

... to existing xml files. We've all had to make modifications to production forms, and some of us have failed to test the form with existing xml files until after we've redeployed our changes. I stand among the guilty. I think I thought if I clicked Preview often enough, and entered some likely looking dummy data, all would be well. And I'd find myself rolling back to a previous version of the form because *something* didn't work as anticipated when I opened the existing xml with the new template.

InfoPath has a feature that can protect us from these little mishaps--it will let you preview your form with an existing xml file. In InfoPath 2003, under File/Preview you can select With Data File and in InfoPath 2007 you can go to Tools/Form Options/Preview and enter a path to the data file you wish to preview with:

What kinds of mishaps will this save you from? Well, imagine you have added a new field to your form. Perhaps you have some conditional formatting that was based on the value of a different field, and you've changed it to use the new field....what value will your old xml have in that field? Did you remember to set your form to upgrade exsisting xml files? (Under Versioning in the screenshot above, just in case you forgot....) Maybe you changed an open rule, not realizing it did something vital -- all of these things can be hard to catch when you are previewing a new blank form each time.

So preview without data during design, but also take the time to preview with data -- production quality, if you have it. When you go to deploy your changes, you won't have any surprises....and unlike, say, birthday cake, this is a *good* thing in the form design world.

Ever design a form with 'The Blues'? You preview the form (or publish it out for your users) and it seems like no matter where you click on the form the whole form is selected, highlighted blue?

This is, after all, pretty much an aesthetic issue. It doesn't really hurt anyone. But frankly, I find it annoying, and I bet you do too. And your users will very likely be at least perplexed. Fortunately, the fix is pretty straightforward. Here is a link to the simple sample form I'll be using for this post, in case you'd like to follow along at home.

First off, you will find that it is a repeating group or field causing your problem. You can find out which one by right-clicking on your blue highlighted section:

Notice the "Insert group2" menu item? Taking a look at the data source for this form, we can see:

And, if we look at our troublesome view in design mode:

This form has a main section bound to the root of the data source, and then has a repeating group bound to a control that has been placed into that section. The 'insert' action for our repeating table is causing the problem in this sample -- repeating sections or repeating fields can also cause this problem. We have some choices: 1) place the repeating table inside another section to 'contain' the insert action or 2) take everything out of the section bound to the root node. Since I really like putting stuff in sections (if I don't I always regret it later -- I can almost bet there will be some reason I'll want to conditionally show / hide the section), I tend to take the first route:

In this view, I've put the repeating section (bound to group2) into a new section that is bound to group1. I removed the borders from the section and set the margins and the padding to zero. When I preview this view, the table's insert event will highlight the section bound to group1:

Unfortunately, this alone will not solve our problem. The section bound to the root node will still highlight. If you modify the binding of the main section, however, so it is connected to a field or group that does not contain a repeating group, your blues will be resolved:

Another method is to take the repeating table out of sections entirely in the form:

Make it a habit to tuck those repeating sections, tables and fields into non-repeating sections all by themselves, and you can lose the blues!