EDIT: If you are using qRules v4.2 or greater, you should follow this blog post instead, as this command has had a bit of an overhaul!
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.
EDITED: Adding a link to the Expense Report sample that shipped with IP 2007.