Submit Items to a SharePoint List with qRules v2.0! - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

Submit Items to a SharePoint List with qRules v2.0!

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:
Customize Sample

From the Getting Started dialog, select Sample - Expense Report:
Select 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:
ShP List

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:
Open qRules Injector

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 Form Template

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:
IPtoShPList Import Tab

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

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:
Get ShP Lists

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

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:
Is Repeating

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

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

Navigate to the repeating item node:
item Node

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

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:
Form Field

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

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:
Date column

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

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:
Save Mapping

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:
Tools / Data connections

Select Add to add a new data connection:
Add

Select Create a new connection to Receive Data:
Create New

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

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

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

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:
Submit data connection

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

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

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

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:
ListName param

The Data Connection Wizard dialog will look like this:
List Param

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

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

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

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!
Final Screen

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:
id field

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:
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:
Submit with Rules

Select the Rules button:
Rules

The Rules for Submitting Forms dialog opens. Select the Add button to open the Rule dialog:
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:
Action dialog

The action will be Set a field's value, and the field to set will be the QdabraRules Command node:
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:
Rules

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:
Preview

Check your SharePoint list after submitting:
ShP List

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:
Change Form

And submit again. Refresh (or re-open) your SharePoint list:
Refreshed 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.

Comments

 

Links (11/19/2009) « Steve Pietrek – Everything SharePoint said:

Pingback from  Links (11/19/2009) « Steve Pietrek – Everything SharePoint

November 19, 2009 4:48 PM
 

uberVU - social comments said:

This post was mentioned on Twitter by InfoPathDev: qRules ver. 2.0 is here! - http://su.pr/2imxlu

November 27, 2009 1:54 PM
 

pb45102 said:

Once you have mapped an InfoPath Form to Sharepoint and you want to add an additional field to the mapping, do you have to start all over.  Take out the Data Connectionf for the mapping and delete the ShpSubmittoList and then do another complete remapping?

Thanks,

Pam

February 22, 2010 5:38 AM
 

Hilary Stoupa said:

Hi, Pam:

You have to recreate your mapping.xml, then update the mapping.xml resource file in your form.

March 20, 2010 3:52 AM
 

rmertz said:

Great tutorial and a very useful tool. I have already put it into use.

Can you offer any further detail on adding the optional "List Item ID" field for updating existing items? I added the field  but I'm uncertain what to do from there. I assume it needs some default value.

May 19, 2010 3:37 PM
 

Hilary Stoupa said:

I think we covered this in the following thread: www.infopathdev.com/.../55177.aspx

May 26, 2010 4:44 PM
 

heguangm said:

I have several questions about this tool:

can it get other fields, ie, my:startdate?

can it map fields to lookup and user column in sharepoint lists?

can it work for browser forms?

September 7, 2010 7:26 AM
 

dubweiser said:

hi,

This is a great tool!  I've successfully deployed the demo and set up one of my forms to submit items to a list.  The only issue is only the first item of my repeating table is submitted to the list..

any ideas what may be the problem?

thank you

October 4, 2010 6:53 PM
 

dubweiser said:

figured it out.. issue was with my mapping definition.. my repeating group is called group1/group2.  i'd selected "group1" instead of the subgroup "group2".

selected "group2" (my:myFields/my:group1/my:group2) and it works perfectly now!

October 4, 2010 11:27 PM
 

Hilary Stoupa said:

Hi, heguangm, sorry I was so slow to see your comment. This command is available for browser forms. You could map to a lookup or user column, but those require a particular format, so you'd need to make sure the field you are mapping to the column follows that format. You can map whatever fields are in your form.

Hope that helps!

November 10, 2010 4:07 PM
 

essined said:

Hi Hilary,

I made my own form that doesn't have any repeating table. Just single text boxes and drop down lists. This should have been much more easier right? If I didn't choose the repeating table in the InfoPath to SharePoint List Tool, would it affect the outcome?

The items didn't appear in my list site but the form was successfully submitted.

sorry I'm not familiar with the node errors.

November 24, 2010 11:46 AM
 

chintan.desai said:

Hi Hilary,

V.Good article - Thanks.

Can you please offer to give more details on your statement:

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

I do not understand what is /id .

Thx

December 30, 2010 6:43 AM
 

chintan.desai said:

Hi Hilary,

I got solution of /id issue. I created new ID field in items sub-tree and then it work.

Hilary, Now, i am able to send data on single list, but my business requirement is to send onto multiple list.

I mean from single form, 1 textbox value should send to list1, another textbox data should send to list2.

I am trying it by creating another mapping2.xml, another list webservice and another rule (in submit button) but it fail. Error says:"Failed to select node for SharePoint List Id"

I suspect there is some problem with my /id issue again.

Please let me know if i am doing in correct way.

Thx

December 31, 2010 1:05 AM
 

chintan.desai said:

Hi Hilary,

Now, I can publish infopath form in document library and can send one field in list1 & one field in list2.

But, Now i got another requirement. Now, i need to apply update/delete operation on infopath form and that should effect list1 & list2 data accordingly.

is it possible with qDabra tool to do this operations?

My requirement:

User will open the form, user will enter data, user submitted the form. Few data went to list1 and few data went to list2. Suddenly, he realize he did mistake in form. User open the form again from document library and updates the field. Now, field in list1 & list2 should get updated accordingly.

Similarly, delete operation should be performed.

Please let me know how we can perform with qDabra tool. My data are not in repeating section.

Thx

Chintan

January 3, 2011 1:05 AM
 

walerhola said:

when setting up tns:ListName, my option is ":ListCollection" instead of "ListCollection" which results in:

/ns1:myFields/ListCollection

as opposed to

/ns1:myFields/ns1:ListCollection

is this a problem?  I am just asking as I am submitting the form but not updating the list.

January 27, 2011 8:20 AM
 

walerhola said:

ahhh, i ran it with the error node and got this:

Failed to select node for SharePoint List Id

so something on my end isnt set up correctly, just not sure what.

January 27, 2011 9:42 AM
 

Hilary Stoupa said:

Hey there Walerhola -- I think we've addressed this in your thread: www.infopathdev.com/.../63639.aspx

January 28, 2011 5:13 PM
 

gombear said:

Hi Hilary,

I wonder if this tool can just bring back one list and updates back to SharePoint.  

For example from above, just the item Pens will be displayed on InfoPath form(by using some sort of unique id), make changes to it, then submit to SharePoint for update.  So, I just want a list, not list collection.  

Is it possible and could you tell me how?

Thanks.  eric

February 9, 2011 2:15 PM
 

Hilary Stoupa said:

Hey, gombbear:

Sure, your data does not have to be repeating (which is I think what you are saying). You'd use the same mapping tool, and qRules now has a Refresh command which can update your XML with the info from your list.

Give the trial a shot and if you have issues, you can post to the qRules section of the forum, since I watch that more closely than my blog comments.

February 10, 2011 11:27 AM
 

dlgross said:

Is there a version of this article for IP 2010?

May 17, 2012 8:03 AM
 

Hilary Stoupa said:

Replied here:

www.infopathdev.com/.../78712.aspx

Now that I look at this, I guess the issue may be that the expense report sample isn't available for 2010? I'll see if I can dig a copy up and attach it to this post...

May 17, 2012 8:35 AM
 

Bambino said:

Hi Hilary,

I haved to apply your step with my work in InfoPath 2010, but it differs from you when I set up tns:listName and tns:updates. I get /myFields/ListCollection, while you get /ns1:myFields/ns1:ListCollection. Moreover, I get /myFields/Batch, while you get /ns1:myFields/Batch.

I'm not sure that is a problems that makes my submitting repeating table to list columns doesn't work.

Thanks in advance

July 12, 2012 12:35 AM
 

Hilary Stoupa said:

Hi - what version of qRules are you using? I've got a new walkthrough up for v4.2.

At any rate, the lack of namespace is intentional in later versions, so that isn't a concern. If your submit isn't working, post a question to the qRules forum with the details of the command you are using and any information in the Error node.

July 17, 2012 8:24 AM
 

rimonabantexcellence site title said:

Pingback from  rimonabantexcellence site title

June 13, 2013 11:09 PM

About Hilary Stoupa

I wandered into development after working as a business process analyst for a global manufacturing company. I create InfoPath solutions for our clients as well as work as a developer on company tools that extend InfoPath. I've also been instrumental in creating the InfoPath Master Class training provided by Qdabra.

Copyright © 2003-2018 Qdabra Software. All rights reserved.
View our Terms of Use.