15 October, 2011

Updating SharePoint list by using InfoPath form


Step 1: Create the list

1. Create a new custom list named: MyContacts
2. From the Settings button choose List Settings
3. Click Create Column
4. Add the following columns/data types:
• FirstName, Single line of text
• LastName, Single line of text
• Email, Single line of text
• Phone, Single line of text

The next step is to create a Collaborative Application Markup Language (CAML) template that contains the same XML nodes as the columns we added to our list – this will be used as a secondary data connection in the InfoPath Form Template.

Step 2: Create the CAML template

1. Launch Notepad (or any text editor)
2. Copy and paste the following code to the Notepad document:
<?xml version="1.0" encoding="UTF-8"?>
<Batch OnError="Continue">
    <Method ID="1" Cmd="New">
        <Field Name='Title'></Field>
        <Field Name="FirstName"></Field>
        <Field Name="LastName"></Field>
        <Field Name="Email"></Field>
        <Field Name="Phone"></Field>
    </Method>
</Batch>
3. Save this as: Add List Item Template.xml

Step 3: Create the InfoPath Form Template

1. Create a new, blank, browser-compatible form template
2. Add a “Receive” type secondary data connection to the Add List Item Template.xml file created in Step #2. (Make sure the options “Include the data as a resource file in the form template” and “Automatically retrieve data when the form is opened” are enabled.)
3. Add a “Receive” type secondary data connection to the “MyContacts” SharePoint List created in Step #1 and make sure the option “Automatically retrieve data when the form is opened” is enabled.

Step 4: Identify the list GUID

1. Open a browser and navigate to the MyContacts list
2. From the Settings menu choose List Settings
3. In the browser’s address bar you will see the URL appended with “List=”
4. Copy everything after the equals sign and paste this into a text editor as we will need this value later on.

NOTE: We will need the list GUID to be formatted as “{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}”. If your GUID appears as above with the hyphens and braces “escaped” (i.e. %7B for the braces and %2D for the hyphens) then you will need to replace the escaped characters with the braces and hyphens so when we use this GUID later, it will appear as: {0D73FBB5-88E1-4435-B607-51ADC5DA6D58}

Step 5: Add controls to the InfoPath Form Template

1. Open the Data Source Task Pane
2. From the Data Source dropdown box choose the “MyContacts” connection
3. Right-click on “MyContacts” and choose Repeating Table – this will add a repeating table bound to that connection to your View
4. From the Data Source dropdown box choose Main
5. Add a text box to the “myFields” node named: ListName and set the Default Value property to the GUID value of your list – we will use this node to specify the list when we perform the Submit operation


To update the SharePoint list we will be using the “UpdateListItems” method of the SharePoint “lists.asmx” web service. This web method requires a couple of parameters (the list name and what gets updated) – now that we have added the “ListName” node which contains the GUID and we have the Add List Item Template XML data connection which describes our data structure we have all the necessary information to add the “UpdateListItems” web method!

Step 6: Add the “lists.asmx” web service

1. Add a new “Submit” type of web service data connection to the “lists.asmx” web service – this is typically located at: http://servername/_vti_bin/lists.asmx
2. Select the “UpdateListItems” operation
3. Double-click the “listname” parameter and assign the “my:ListName” node as the value

4. Double-click the “updates” parameter, select the “Add list Item Parameter” data source, select “Batch” and click OK
5. For the “Include” option select “XML subtree, including selected element”

6. Complete the Data Connection Wizard

Now that we have all the connections that are required, we now need to add the “submit” functionality to the form template.

Step 7: Add Submit functionality

1. Display the Data Source Task Pane
2. From the Data Source dropdown box choose the Add List Item Template data connection
3. Drill down through the data source, right-click on the Field node and choose Repeating Table

We need to change the text box control in the “Name” column to Read-only – if these values were to be changed it would affect the CAML and the submit would fail.
• Double-click on the text box in the Name column of the Repeating Table
• Select the Display tab
• Enable the Read-only property and click OK
• Add a new Button control to the View
• Double-click the button to display the Properties
• Click the Submit Options button
• Enable the option “Allow users to submit this form”
• Enable the option “Perform custom action using Rules” and click the Rules button
• Click the Add button
• Click the Add Action button
• From the Action dropdown box choose “Submit using a data connection”, select the “Web Service Submit” data connection and click OK
• Click the Add Action button
• From the Action dropdown box choose “Query using a data connection”, select the “MyContacts” data connection and click OK (this will automatically refresh the list in InfoPath so you can see the newly added record)
• Click OK until you are back to your form



Step 8: Test
1. Click the Preview button to preview the form
2. Fill in values for each of the fields: Title, First Name, Last Name, E-mail and Phone
3. Click the Submit button – the contact information should be submitted successfully and the “MyContacts” list automatically updated!


If you have any queries/questions regarding the above mentioned information then please let me know..
I would be more than HAPPY 2 HELP you as well as RESOLVES your issues, Thank you

42 comments:

  1. Hello Amol,

    Thanks for the post http://sharepointknowledgebase.blogspot.com/2011/10/updating-sharepoint-list-by-using.html , it's very useful. Unfortunately, I did exactly the same as your instruction but it doesn't work. Can I send you the form what I did for help please?

    Thanks you very much for your help.

    ReplyDelete
  2. Please send that form across to me so that i can check the same and revert back, Thank you

    ReplyDelete
  3. Hi Amol,

    Thank you for your post and it's very useful.
    I have created the form and data connections as you said and tried to publish the form to sharepoint list but I couldn't select list name since it was showing only library names.

    Is there any way to publish the form to list and show as webpart in sharepoint site?

    Any help would be appreciated.

    Reagrds.

    ReplyDelete
  4. Infopath forms will be published on libraries only. if i need to be very specific then it requires form library as a base.

    it will never be published on sharepoint list. its by design/product behaviour.

    Reference for details: http://office.microsoft.com/en-us/sharepoint-server-help/introduction-to-infopath-forms-services-HA010167284.aspx

    Please let me know in case of any further queries/questions, thanks for your patience.

    ReplyDelete
  5. I am missing something i have followed about 6 blogs on this subject. everything seems to work fine except nothing shows up in my sharepoint list after submit. The form says date was submitted but it's not showing in SP. I've verified the date connectiosn and GUID a bunch of times. I don't even need to refresh data in infopath just submit to a SP list. any help would be appreciated

    ReplyDelete
  6. Hi Amol
    Greate article.
    You instruction to show how add new item to SP list. I must edit item and have the problem.
    My XML:
    <?xml version='1.0' encoding='UTF-8'?>
    <Batch OnError='Continue'>
    <Method ID='1' Cmd='Update'>
    <Field Name='ID'></Field>
    <Field Name='Title'></Field>
    <Field Name='FirstName'></Field>
    </Method>
    </Batch>
    In <Field Name='ID'> I must put item id (I have this ID, user does not see field ID) but I don't know how set value in XML Field in Infopath.
    I use SharePoint Infopath template and I no have Developer tab.

    ReplyDelete
  7. Can you please cross check again: http://msdn.microsoft.com/en-us/library/office/cc162745(v=office.12).aspx

    ReplyDelete
    Replies
    1. Thanks for reply.
      In this article use template: "blank form". I use template: "SharePoint List" and can't use management code.
      Solution is change template or find how to change xml node without management code.
      One more time thanks for reply.

      Delete
  8. Hi,

    I'm a Business Analyst ex's application engineer so my SharePoint skills aren't the strongest. However, the SharePoint Admin we have and Developer are not up to par SharePoint, let alone in infopath, and deter us from using it by saying it wasn't meant to be used with SharePoint. However, I have a question or use case that I want to ask you about. Are we able to email a browser-based form with a submit to reviewer button which will email the completed form to a SharePoint user/Admin of the site that contains the list. The form will have an additional button "approved"... which will submit to the SharePoint list since that user has rights. They tell me this is not possible but can't explain why. The reason the outside users cannot submit directly is because they will not have access to SharePoint but we can have data miners verifying the accuracy of the information before publishing to the list. Thank you in advance for any insight.

    ReplyDelete
  9. first of all- whatever said by yours developers and admins is wrong becoz InfoPath is a very important and enhanced in SP2010 as compare to SP2007.

    There are so many things by which you can utilize by Infopath forms in SharePoint. One of the most vital thing is you can publish the infopath forms in browser mode. the advantage of this is- your users/colleagues don't require to install infopath client on their machines.

    Regarding outside users-yes, thats true. they should have thier accounts to be present in the active directory if you are using the SP in intranet.

    1st query- i think its possible if we use the 'mail to' + workflow functionality.

    2nd query: submit to the SharePoint list- if you refer this article "updating sharepoint list by using infopath form"- this is excatly the same and perform the same functionality which you have requested. infact there is technet article also present based on this for better clarity.

    thanks for your patience

    ReplyDelete
    Replies
    1. Thank you very much. It's nice to know that the workflow I envision is possible.

      I knew they where totally wrong. Just because they do not know InfoPath does not mean they should right off the benefits it supplies used in conjunction with SharePoint.

      Thanks again... I guess I'll be learning InfoPath in order to accomplish this.

      Delete
    2. Please reach out to me in case of any queries so that we can discuss and resolve the issue, Thank you.

      Delete
  10. Hello Amol, I followed the steps in your post and successfully submit test data to a test list using the form. When I try this with a list that has a lookup field no data is submitted (The list originates from an Access web database). For example, if my list has three columns, the first of which is a lookup field called Project then I assume my CAML xml file should read:










    I have tried to include and not include the Project field but no matter what I choose no data is submitted to the list. Nothing happens.

    Thanks,

    Rob

    ReplyDelete
    Replies
    1. Sorry, the code above disappeared after publishing:

      Batch OnError="Continue"
      Method ID="1" Cmd="New"
      Field Name='Project'/Field
      Field Name="FirstItem"/Field
      Field Name="SecondItem"/Field
      /Method
      /Batch

      Delete
  11. Hello Amol,
    I have tried your steps, the item is being inserted to the SharePoint list but without data.
    Whenever I submits the data it adds a new item but without any data.
    Please help me with this as soon as possible.

    ReplyDelete
    Replies
    1. are you getting errors in event logs as well as in the SP ULS logs?

      Reference for details: http://office.microsoft.com/en-us/sharepoint-server-help/introduction-to-infopath-forms-services-HA010167284.aspx

      can you please cross check the information from the above reference and make sure that you have followed all the steps correctly. Thanks for your patience...

      Delete
  12. This is brilliant, thanks!
    Any ideas on how to set a default value for one of the fields?

    ReplyDelete
  13. Hi Amol,

    I have a requirement where I need to autopopulate values in list form from other Sharepoint list based on the value of Drop down selected. The drop down column is a unique ID in the other sharepoint list from where the data should be read out.
    Could you please advise how this could be achieved using infopath ?

    Thanks in advance.

    ReplyDelete
    Replies
    1. I think your requirement can be accomplished by using the following links:
      http://basquang.wordpress.com/2010/03/29/cascading-drop-down-list-in-sharepoint-2010-using-infopath-2010/

      http://koobarspoint.blogspot.fi/2010/06/cascading-drop-downs-with-infopath-2010.html

      Delete
  14. Hello Amol,

    I have the same situation as some of those above. I followed the CAML route and my form says it submits successfully (when using preview) but nothing show's up in the list. I see no errors at all. I tried every variation I've seen on the web but nothing shows up in the list. I'm using InfoPath 2013 SHarePoint 2013, any thoughts on if it is security or functional related? Another piece that may be helpful is that I get the current user by preferred name on load which works in preview mode, but it fails when using the published version.

    ReplyDelete
    Replies
    1. - If you have renamed the "Title" field to be your own name, the CAML query will still need to reference that field as "Title."

      - Check the Application Event log and see if there are any errors or warnings when you test this process - we could be hitting a permissions issue based on the Application Pool Identity Account being used in the Application Pool for your site

      Have you renamed the "Title" field in your list? Are there any errors or warnings in the Application Event log or the server log?

      Delete
    2. If the above things does not give you any results then follow this-

      1. Create a new, blank browser-compatible form template

      - Launch InfoPath

      - Click Design a Form Template

      - Choose Blank and enable the option: "Enable browser-compatible features only"

      2. You add data connections to an InfoPath Form Template by selecting Data Connections from the Tools menu and clicking Add. There you can choose whether the connection type is Submit or Receive

      3. This is going to be challenging as I am assuming the "public" site is going to allow Anonymous Access. If so, then SharePoint security is designed to *not* allow any submissions to a library or list with Anonymous access enabled. As such, this process will change as the data will need to be submitted to a custom web service that you create to take the XML and create the list item for you.

      Delete
  15. Αpρгecіatіng thе time and еffoгt yοu put into youг site anԁ ԁetaіlеd infоrmаtіon уou рresent.
    Ιt's goοd to come acrοsѕ а blοg evеry οnce іn а whilе
    thаt isn't the ѕame out of dаte rеhasheԁ materіal.
    Gгеat read! I'vе saved your site аnd I'm addіng your
    RSS fеeds to mу Google account.


    my ѕіtе ... νae (www.sosdroits.com)

    ReplyDelete
  16. Hi Amol,
    great Post!
    I have one question: I Need to populate all the fields automatically using Actions and rules, but cant find out how to set the values in the Batch datasource without using a gui for them. Do you know the trick?
    Best regards,
    Robert

    ReplyDelete
    Replies
    1. Ah and I forgot: it's a web form...
      already tried to set a filter on the target nodes (manifest hack), but that doesn't work web based

      Delete
    2. Please use the following references to get this accomplished:

      Use the SharePoint Lists web service together with a CAML batch update to add an item to a SharePoint list:
      http://www.bizsupportonline.net/infopath2007/how-to-submit-items-rows-repeating-table-infopath-sharepoint-list.htm

      Use a SharePoint Designer workflow to get data:http://www.bizsupportonline.net/browserforms/automatically-add-item-sharepoint-list-infopath-workflow.htm

      Use a SharePoint event handler feature to make calls directly into the SharePoint object model to add an item to the SharePoint list when an InfoPath form is added to a Form Library on which the event handler has been activated:http://www.bizsupportonline.net/infopath2007/how-to-use-sharepoint-event-handler-object-model-submit-data-infopath-form-sharepoint-list.htm

      Use a custom Visual Studio workflow to add an item to the SharePoint list when an InfoPath form is added to a Form Library on which the workflow has been set to run: http://www.bizsupportonline.net/infopath2007/how-to-use-visual-studio-workflow-submit-data-infopath-form-sharepoint-list.htm

      Delete
    3. I have mentioned different references by which you can try using different methods. give it a try and let me know in case of any queries. All the above references has been tested and working properly at my end.

      Delete
    4. Hi Amol,
      just wanted to let you know how it has been solved in this case.
      Workflow within SharePoint doesn't work, because we don't save the form XML to the library - any data comes from and goes to an external System (SAP) via web services. So we don't have a list item as initial context.
      We already set up some custom web services (Sitrion), just added another one which creates list entries using SharePoint Client Script. It seemed for us to be the easiest solution in this case - now this datasource can also be called as datarequest (without form submit) within rules which is also required.
      Thank you a lot,
      Robert

      Delete
  17. Have you ever thought about creating an ebook or guest authoring on other sites?

    I have a blog based on the same ideas you discuss and would
    love to have you share some stories/information. I know
    my subscribers would value your work. If you are even remotely
    interested, feel free to shoot me an e mail.


    Feel free to visit my blog web page

    ReplyDelete
  18. Great woгk! Тhiѕ is the type of information that should be ѕhared
    around the internet. Shame on Goοgle for now not рoѕitionіng thіs ρublish highеr!
    Come on over and ԁіscuss with mу websitе .
    Thanks =)

    Feel fгee to viѕit my web-site: hаwkbill ()

    ReplyDelete
  19. Hi Amol, I totally agree! I was giving up when I found your page. We are not allowed to use code, but I've tried your method and it works brilliantly, thank you!!! If you could help me some more that would be so great. What I am trying to do is to update the item instead. Fields in my infopath form (which have default values are: EmployeeIPN, and H Key(concatenated field of 2 values). The corresponding list has 3 values of Employee IPN, Key and Updated. By default the Updated field in the list is set to Y. What I need is when the user submits the form, the corresponding item (EmployeeIPN=IPN and H Key=Key) is selected from the list and has the Updated value set to No. For background info, a workflow then runs, updates a different list and then resets the Updated field value back to Y. When the next form is submitted, the updated value is set by Infopath to N, and around and around we go. Please can you help me?

    ReplyDelete
  20. Hi, this is a great post ! Though I have one question : I would like to use this as a part of a form, where I have other fields that require validation. So, if I use a submit connection, it won't work till I have filled all mandatory fields in my form, and entered valid values. I need to get the ID of the newly created list item in order to complete my form. Is there any chance to achieve this ?
    I wondered if I could create a "receive" connection using the lists.asmx webservice and updatelistitem method, but it seems that I can't find the syntax for the updates parameter when creating the connection : entering the batch xml on one line doesn't work...

    What I am trying to do is using the separate list to create items, get their IDs, and use these IDs as an identifier for the InfoPath form. And I would like to display the ID value at form opening, of course...

    My other option is to open the form without any ID, and asking the user to save the form in order to get its ID. Which I am probably going to do, but I just wanted to ask, in case there is any way to call lists.asmx from a non-valid or a not-yet-valid form... :)

    Many thanks and best regards !

    ReplyDelete
  21. I need your help for the below situation:

    I had to improve a list and I modified on the SharePoint the form on which a new item is added (on the list). I added some new fields (including new people picker fields) and I submitted and uploaded the form on the SharePoint. Now I cannot use the Person/Group picker on Internet Explorer. I do not receive any error, it simply doesn't appear the menu for people picker - I have a blank fields instead of people picker menu. I tested the list on other PC's (with IE8, IE9 and IE10) and it is the same issue. The people picker feature is not working on internet explorer since I modified the form.

    I want to mention that this people picker was functional before editing the form and also that the preview from Microsoft InfoPath is functional. I don't know if this info is relevant but on the form I have 4 people picker fields and previously I had only one field.

    On the other hand, in Mozilla and in Google Chrome the Person/Group picker works fine.

    I use Internet Explorer 8 and SharePoint2010. Also, I don't have access on SharePoint Designer and I have to make all the changes directly from SharePoint.

    Please let me know what I should do in order to have this people picker functional in Internet Explorer 8 again.

    Thank you!

    ReplyDelete
  22. Is there a way to prepopulate the input fields and update automatically instead of manually typing in ? When you update or add?

    ReplyDelete
  23. HI There,

    GREAT work, this is exactly what I need in my environment. I do have a problem however, please see if you can assist. I followed all the steps exactly as stated, but when I preview the InfoPath form and click on the submit button, I get the following error:

    InfoPath cannot submit the form.
    Some rules were not applied.
    InfoPath cannot submit the form.
    An error occurred while the form was being submitted.
    The SOAP response indicates that an error occurred on the server:

    Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.

    List does not exist.
    The page you selected contains a list that does not exist. It may have been deleted by another user.
    0x82000006

    Please will you see if you can assist?

    Many Thanks,

    ReplyDelete
    Replies
    1. Ok so I found the issue. I had the GUI formatted incorrectly. Upion fixing that, the form submits successfully, but the data I entered does not update in the SharePoint list. Nothing happens...

      Any help would be greatly appreciated!

      Thanks
      Riaan

      Delete
  24. Hi Amol,

    This is an excellent article. I have managed to follow it exactly.
    The issue I am having is that I am trying to have a function which updates items currently in my list.
    I don't suppose you could guide me in the steps to achieving this could you?

    Thanks
    Ruskin

    ReplyDelete
  25. Great Article. I was able to make it work. Though, I have a list which has existing records. One of the field in this list is called "UserComments". I am trying to implement this functionality to update User Comments. This field is Appendable field, so it keeps a track of all the user comments kind of like a thread. Currently, it adds comments as a separate record.
    How do I accomplish this?

    BTW: To decode the List GUID here is a tool which gives you exactly the GUID -
    http://meyerweb.com/eric/tools/dencoder/

    ReplyDelete
  26. my CAML doesnot function when the form is published in the form library

    ReplyDelete

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

Note: Only a member of this blog may post a comment.