Monday, March 7, 2011

Submitting data to a SharePoint 2010 List using InfoPath - CAML

After moving around with some heavy tasks in SharePoint I wanted to Submit data to a SharePoint list through a InfoPath web form which has a Submit button in it. Did some googling and saw a way of submitting data through SharePoint in built web services. Thought of trying it and started to work on it. But after some time trust me, what i felt was this way is not hard, neither so easy.

So I thought of giving you all a step by step guide to learn how to do this without much frustration.
For this task, you will need SharePoint 2010 and Microsoft Office InfoPath Designer 2010 installed in your machine. You will need to have good understanding about CAML(Collaborative Application Markup Language), knowledge of the methods of the SharePoint Lists web service and that would be all and you are ready to go.

First of all CAML is an XML based markup language used with the family of Microsoft SharePoint technologies. Unlike plain XML, CAML contains specific groups of tags to both define and display (render) data. To get more information visit,
http://en.wikipedia.org/wiki/Collaborative_Application_Markup_Language

To get more information about the SharePoint Lists web service, in your IE navigate to "http://servername/_vti_bin/lists.asmx?WSDL" and you will be able to see all the web methods associated with the Lists web service. For our purpose we will be using UpdateListItems web method.

Now let's start. I am assuming you as a beginner, so I am adding a lot of step by step screen shots to ease your work.

1. Create a Custom List in a SharePoint Site. I have named it as Employee. Now create some columns as per your need. Note that there will be a default column called 'Title' and lets not change it and keep it that way to store EmployeeName, I have created EmployeeAddress, EmployeeNo, EmployeeEmail as columns which are Single text. Note that when Column names does not contain any spaces and later I will describe the reason to put them in that way.

2. Open Microsoft Office InfoPath Designer 2010 and select a Blank Form and click Design Form. Then do as follows.

Form Options

Security and Trust

Full Trust

Programming

Design

Design

Design

Renaming the button


Add fields






3. In SharePoint site under Employee list, in ribbon under List Tools select List and then select List Settings. In that page click Advanced Settings and select Allow management of content types to Yes.

List Settings



4.  Again go to List Settings click Metadata navigation settings and copy the list GUID.

Copy list GUID

5. Go back to InfoPath form right click on listID and paste GUID under value field.




6. Open Notepad and type,

<?xml version="1.0" encoding="UTF-8" >?
<Batch>
<Method ID="1" Cmd="New">
     <Field Name='Title' />
     <Field Name="EmployeeAddress" />
     <Field Name="EmployeeNo" />
     <Field Name="EmployeeEmail" />
</Method>
</Batch>

Save the file as an xml file and name it Submit.xml.

7. Now lets create Data Connections. Go to InfoPath form and click Manage Data Connections. First we need to create a Receive Data connection. I am naming it as 'Submit'.


Receive Data

XML Document

Browse for the created Submit.xml file


Naming the connection as 'Submit'

8. Now we need to create a Submit Data Connection. Again in Manage Data Connections click Add.

Add

Submit Data

To a Web Service

http://servername/_vti_bin/lists.asmx?WSDL

UpdateListItems

listname

listID

Include : Text and child elements only

Updates

Secondary


Batch

Include : XML Subtree, including selected element


Web Service Submit


Edit Form Code

9. Click Edit Form Code and btnSubmit_Clicked event will be created. In that block, type the following code.

try
{
    XPathNavigator root = MainDataSource.CreateNavigator();

    // Retrieve the values for the separation list item
    string listID = root.SelectSingleNode("/my:myFields/my:listID", NamespaceManager).Value;
    string eNameDS = root.SelectSingleNode("/my:myFields/my:EmployeeName", NamespaceManager).Value;
    string eAddressDS = root.SelectSingleNode("/my:myFields/my:EmployeeAddress", NamespaceManager).Value;
    string eNoDS = root.SelectSingleNode("/my:myFields/my:EmployeeNo", NamespaceManager).Value;
    string eEmailDS = root.SelectSingleNode("/my:myFields/my:EmployeeEmail", NamespaceManager).Value;

    if (listID == null)
    {
    
    }
    else
    {
        //This is CAML xml file. it contains batch and method nodes
        XPathNavigator batch = DataSources["Submit"].CreateNavigator();

        batch.SelectSingleNode("/Batch/Method/Field[@Name='Title']", NamespaceManager).SetValue(eNameDS);
        batch.SelectSingleNode("/Batch/Method/Field[@Name='EmployeeAddress']", NamespaceManager).SetValue(eAddressDS);
        batch.SelectSingleNode("/Batch/Method/Field[@Name='EmployeeNo']", NamespaceManager).SetValue(eNoDS);
        batch.SelectSingleNode("/Batch/Method/Field[@Name='EmployeeEmail']", NamespaceManager).SetValue(eEmailDS);

        DataConnections["Web Service Submit"].Execute();
}
catch (Exception ex)
{
    ex.Message.ToString();
}

10. And finally thats all. Preview the form and try submitting the data. After that publish the form and upload the form template into SharePoint and start using.

There are few important things to remember carefully. 
  • There is a significant difference between Display name and the Field name. When we are doing the coding we are using the value under the Field name. If you can remember, I have mentioned to keep the default 'Title' column as it is, because if we rename the 'Title' column it will just change the display name. The Field name will be remain as 'Title'.
  • No of updating list fields should be the same as no xml field names.
  • When I am creating columns in SharePoint list, if you can remember I have included no spaces between words. Thats because it will add characters into Field names. So when we are referring to Field names from looking at Display names, the Field name is actually differs from the Display name.
  • List fieldname should be same as xml fieldname.
  • Make sure when creating the Submit Data connection 'Web Service Submit' to select following things,
    • listName - listID : Include : Text and Child elements only
    • updates - Batch : Include : XML subtree, including selected element

Appreciate your feedback. 

Happy Coding.

Regards,
Jaliya

22 comments:

  1. Where exactly in VSTA do I paste the code? Pasted in several places now and it' still now working. Not even submitting but the code is fine, no errors.

    Thanks

    ReplyDelete
  2. Hi Latte, As I mentioned above, paste the code in the btnSubmit_Clicked event. Just do the 9th step properly and you will be fine.

    Happy Coding.

    Regards,
    Jaliya

    ReplyDelete
  3. Hi Jaliya, thanks for your reply. I'm not a developer at all so all I know how to do with this is cut and paste. Please hint as to what line I'm supposed to paste this under.

    There's a line at the end of the VSTA that says
    //Enter your code here
    it's at the end of the page, I've put it in this line and it's not working. So I'm assuming I'm doing something wrong.

    Also, I have an already designed list, how do I get this infopath form to lookup data from the list and upload a picture with each form?

    Thanks again.

    Latte.

    ReplyDelete
  4. Hi Latte,

    In generated btnSubmit_Clicked event there is also a comment saying,
    //Enter your code here
    So make sure to place your code there.

    Uploading a picture is same as above. Just drag and drop a attachment control to your form and change the above code.

    Getting data from a list to the Infopath form is pretty much easier than this. Since right now I have not powered on my Virtual Machine, I will rather a give you a link to help you with that. Hope this will do,
    http://blog-sharepoint.blogspot.com/2009/07/using-secondary-infopath-data.html

    Happy to help and Happy Coding.

    Regards,
    Jaliya

    ReplyDelete
  5. Hi, thanks for your replies so far. Unfortunately though when I click on the submit button nothing happens.

    Please the code sample below. Columns in my list are Title, FirstName, LastName, Email, Phone.
    Am I missing a step? Thanks.

    ((ButtonEvent)EventManager.ControlEvents["btnSubmit"]).Clicked += new ClickedEventHandler(btnSubmit_Clicked);
    EventManager.FormEvents.Submit += new SubmitEventHandler(FormEvents_Submit);
    }

    public void btnSubmit_Clicked(object sender, ClickedEventArgs e)
    {
    try
    {
    XPathNavigator root = MainDataSource.CreateNavigator();

    // Retrieve the values for the separation list item
    string listID = root.SelectSingleNode("/my:myFields/my:listID", NamespaceManager).Value;
    string titleDS = root.SelectSingleNode("/my:myFields/my:Title", NamespaceManager).Value;
    string fnameDS = root.SelectSingleNode("/my:myFields/my:FirstName", NamespaceManager).Value;
    string lnameDS = root.SelectSingleNode("/my:myFields/my:LastName", NamespaceManager).Value;
    string phoneDS = root.SelectSingleNode("/my:myFields/my:Phone", NamespaceManager).Value;
    string EmailDS = root.SelectSingleNode("/my:myFields/my:Email", NamespaceManager).Value;


    if (listID == null)
    {
    }

    else
    {
    //This is CAML xml file. it contains batch and method nodes
    XPathNavigator batch = DataSources["submit"].CreateNavigator();

    batch.SelectSingleNode("/Batch/Method/Field[@Name='Title']", NamespaceManager).SetValue(titleDS);
    batch.SelectSingleNode("/Batch/Method/Field[@Name='FirstName']", NamespaceManager).SetValue(fnameDS);
    batch.SelectSingleNode("/Batch/Method/Field[@Name='LastName']", NamespaceManager).SetValue(lnameDS);
    batch.SelectSingleNode("/Batch/Method/Field[@Name='Phone']", NamespaceManager).SetValue(phoneDS);
    batch.SelectSingleNode("/Batch/Method/Field[@Name='Email']", NamespaceManager).SetValue(EmailDS);


    DataConnections["Submit to MyContacts"].Execute();
    }

    }
    catch (Exception ex)
    {
    ex.Message.ToString();

    }
    }

    public void FormEvents_Submit(object sender, SubmitEventArgs e)
    {
    // If the submit operation is successful, set
    // e.CancelableArgs.Cancel = false;
    // Write your code here.
    }
    }
    }

    ReplyDelete
  6. This is a really good read for me. Must admit that you are one of the best bloggers I ever saw. Thanks for posting this useful article.

    ReplyDelete
  7. Hi SharePoint Consulting,

    Thank you for your nice comment. It is really encouraging.

    Happy Coding.

    Regards,
    Jaliya

    ReplyDelete
  8. Hi jaliya.. i m unable to get the results... whn i start debugging i got this on immediate window "A first chance exception of type 'System.NullReferenceException' occurred in Submitlast.DLL "

    ReplyDelete
  9. Jaliya, thank you for the post. This code runs for me perfectly in design mode, however once published the SharePoint list is never updated. No error messages are shown. Any ideas?

    ReplyDelete
  10. This article is really good. Now I have attachment control on the my form. How do I get the attachment uploaded?

    ReplyDelete
  11. First of All: Thanks für this post!

    Unfortunately there is no new entry after push the "Submit" Button.

    I have analysed every step by debuging. Looks nice, but nothing happens after the step "DataConnections["Web Service Submit"].Execute();"

    Is there any kind of logging to find out why the webservice isn't working?

    thanks!
    Patrick

    ReplyDelete
    Replies
    1. Hi Patrick,

      You can debug using Visual Studio attach to process.

      Happy Coding.

      Regards,
      Jaliya

      Delete
  12. I am also not able to get it to create a new record in the list. From the code the values load properly into the variables and I retrieve them correctly from the Nodes. How do you test the DataConnections["Web Service Submit"].Execute(); to make sure it is trying to push the values to the right place?

    ReplyDelete
  13. great work ! Thanks a lot !
    Definitely saved me couple of hours.

    Thanks
    Sangeet Ahuja
    www.sangeetahuja.com

    ReplyDelete
  14. Hi Jaliya,

    First i'd like to say thanks for your instructions, everything worked perfectly for me. I was hoping you could help assist me further with a bit of added functionality? Currently it submits to list fine, but I would like it not to duplicate list items. Like if Title field already exists in list just update existing record, else if title doesn't exist add new list item. Could you assist with this functionality? I really appreciate any help you can provide me, thank you!

    ReplyDelete
  15. Can this methose work for an image? suppose I have a rich text box on the infopath form where a user can add text and/or image and I want to send that data to an enhanced rich text field of a list, can I use this method?

    ReplyDelete
  16. Hello Jaliya,

    Good Read! Works great after following your instructions.This would definitely help with my project.

    I have another question, would you have steps to have it work as well with the same results as above for Repeating Table?

    Regards,
    Rodrigo Jaime Rodriguez

    ReplyDelete
  17. Hello Jaliya,

    Nice Post!

    I was wondering if you can tell me if it is possible to construct a form template that is able to Delete AND add list items in the same template. It is not clear to me whether I should load two separate CAML templates (one with cmd='Delete' and one with cmd='New'), or if a single CAML template should be built and loaded to the form. I am not able to write code, so it would be useful for me to learn how to do this using the methods that you describe in your Cookbook 2.

    Thank you in advance for your reply!

    Al Williams

    ReplyDelete
  18. You just made something that I thought was so difficult be, truly, so easy! Thanks for the post!

    ReplyDelete
    Replies
    1. Hi Mr. Digital, i am trying this script and solution as well. I am wondering how you could made this work because i get the same results as other have posted: Nothing happens after the button is clicked. I am really looking forward to your response.

      Delete