Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

Populating form data from SharePoint List Views

Populating form data from SharePoint List Views

Rate This

The SharePoint list data connection in InfoPath is a great declarative way to bring in data from a SharePoint list, but it does have some limitations.  For those willing to put in a little extra effort, there’s much more that can be done with SharePoint lists: you can bind to views based on these lists. Here are the steps for creating the connection to a list view.

First, let’s get the URL:

1. Navigate to the SharePoint site that contains the list, for example:
http://contoso/sites/sales/Sales%20Contacts/Forms/AllItems.aspx
2. Go to “Modify settings and columns”
3. Copy the List={GUID} portion of the URL and paste this into a buffer like a Notepad window.
4. In Notepad, create the following URL (the blue portion us taken from the step 1 URL, and the red portion must be added.

http://contoso/sites/sales/_vti_bin/owssvr.dll?Cmd=Display&List={GUID}&XMLDATA=TRUE&noredirect=true

This will return an xml file that can be used in an XML file data connection, as if it came from a file system.  After this, you can use the URL as the location of an XML data file when creating a data connection.

 

Two caveats:
- Form users must have read access to the SharePoint list.
- During creation of the data connection, do not include the file in the form template, as it should be dynamically generated from the SharePoint list.

 

Some tricks:
1. When you’re in “Modify settings and columns,” if you click on one of the views in the list at the bottom, you should note that the URL is “enriched” with &View={ANOTHER_GUID}.  If you would prefer to use the columns from that view, you should similarly enhance the URL you use above.
2. You can also use the url to filter data rows based on column values. For example:

http://contoso/sites/sales/_vti_bin/owssvr.dll?Cmd=Display&List={115BC7B7-0A82-403E-9327-F3C73E6D37F3}&XMLDATA=TRUE&noredirect=true&FilterField1=xd__x007b_52AE1EF8_x002d_28E7_x002d_4CE4_x002d_AE23_x002d_54E23E80DDB5_x007d_&FilterValue1=Approved

Note: be sure to remove the “ows_” from the beginning of the FilterField ID.

 

With this filter, the XML file returned will be filtered to only display those projects that have been approved.  Without the green portion, you would see all the projects.

 

In order to populate other fields with data from WSS, you create the secondary data source just like above, then in form code or script, you can use GetDOM(“dataSource”) and walk the DOM normally, updating the main DOM as appropriate.

 

Ed Essey
Program Manager

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • Hi! I am having similar issues to Vinod and Amanda. I tried the suggestion that Scott suggested to Vinod, there was no change in the browser behavior. Amanda's suggestion might works, but I do not have access to publish administrator-approved templates - as I will never get server administration access. Is there a solution to have results sorted alphabetically in a browser on SP2007 with InfoPath 2007 that I can implement??

  • Hi Keith,

    Try this:

    - Navigate to your list of items in the browser and modify your default SharePoint view by adding a "sort" to the view to sort on the column that stores your data

    - Add a data connection to that list, using the method described here, and then publish and test your form in the browser

    When I test this, I see the data sorted in my dropdown box - both in the InfoPath client *and* in the browser.

    Scott

  • Hi,

      Really this thread help me alot. Thank you.

  • This article helps me a lot.

    Extra I want to set a filter in the url on the current user "to filter 'My projects'".

    Is this also possible? Did someone find a solution for this?

  • Hi Guys,

    Is there a way to count the number of items returned using this method?  count() seems to be counting the number of columns in the view and not the list items itself?

    Thanks

    JP

  • Hi CubixVision,

    Sure - you want to use the "count" function on the repeating node that is returned. So your data connection should look like this:

    xml

     s:Schema

     rs:data

       z:row

    "z:row" should be a repeating node with your list columns under it. As such, your expression to count those rows would look like this:

    count(row)

    If you were to click the "Edit XPath" option in the Formula screen, it would look like this:

    count(xdXDocument:GetDOM("owssvr")/xml/rs:data/z:row)

    Scott

  • Hi.

    Can't get it to work. Here is my URL

    http://ServerName/SubSite/_vti_bin/owssvr.dll?Cmd=Display&List={3EF4226D-0470-4237-ACE9-179F3D82D08B}&XMLDATA=TRUE&noredirect=true

    A couple of things:

    1) How do I add it to InfoPath? What type of connection type is best?

    2) I have created the URL and tried it in several of the data connection types (XMl..) and I get the following error "Internet Address "my url above" is not valid". What am I missing? Do I need to register the owssvr.dll file?

    Any help would be appreciated.

    Thanks.

  • ----Edit to above.----

    I think the error that I was receiving was due to the fact that I am try to connect to a list that is an external content type. It works with a regular list.

    Any idea how to dynamically filter an external list?

  • I am new to this blog, my apologies if the answer is here and I missed it. I'm working with InfoPath Desiger 2010 and SP Server 2010. I was able to add an XML data connection to my form to sort the SP list, but when I change or update the list, the changes do not appeaer to carry over to the InfoPath form. The data connections via a SP list update accordingly, but not via XML connection.

    Any help would be greatly appreciated!

    Joni

  • Hi-

    I successfully connected the XML file to my dropdown list, modified the view1.xsl file as directed in an earlier post and successfully previewed the clean- non 'string#;' prefix for my information.  However, when I publish the form back to my SharePoint list and attempt to add a new item, I get an error that says:

    ERROR:

    The "BrowserFormWebPart" Web Part appears to be causing a problem. Expression must evaluate to a node-set.

    Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

    Anyone else see this or have any idea why it would happen????

  • Hi,

    I have tried to use the view as a data connection:

    http://servername/_vti_bin/owssvr.dll?Cmd=Display&List={4A837605-81CD-4F27-9C3B-4A6EEF62B379}&View={D826BE04-4346-44DB-9277-018B59FDAFB0}&XMLDATA=TRUE&noredirect=true

    (formed the url corresponding to my list and view guids)

    The data connection works but it loads only the First 30 Items of the view and my view has got more than 30 items. The rest of the items are truncated.

    Same is the case when I paste the above url in browser, it doesnt show all items of the view.

    Please help.

    THanks

    Anil Bingu

  • Hi

    I'm using the following URL to return data to Excel:

    http://<myserver>/.../_vti_bin/owssvr.dll?Cmd=Display&List=%7B22332672%2D8AD2%2D4A9D%2DB2B7%2D28D7416D5A8D%7D&View=%7BA454B55A%2DBC9E%2D4F77%2D8C0C%2DA74ED95F906E%7D&Query=*&XMLDATA=TRUE&FilterField1=MyNumber&FilterValue1=1

    This works fine, but does not return columns from my list which are null.  Is there a way of returning all columns regardless of their data content?

    I've tried specifying the column in the &Query parameter but that didn't work.  Any ideas?

    Thanks

    Charles

  • Hi Scott,

    I tried below changes in my code, however I am getting the error "Infopath cannot open the form".

    ******************************************

    <xsl:if test="not(xdXDocument:GetDOM("test")/xml/rs:data/z:row/[substring-after(@ows_Authorised_x0020_User_x0020_Name,"#")[.=$val]] or $val='')">

    <option selected="selected">

    <xsl:attribute name="value">

    <xsl:value-of select="$val"/>

    </xsl:attribute>

    <xsl:value-of select="$val"/>

    </option>

    </xsl:if> <xsl:for-each select="xdXDocument:GetDOM("test")/xml/rs:data/z:row/[substring-after(@ows_Authorised_x0020_User_x0020_Name,"#")]">

    <option>

    ********************************************************

    Please guide me through. Also these are browser enabled forms.

  • This is a great post. Thank you!

    Unfortunately, the XML Secondary DataSource only refreshes when all instances of Infopath are closed. I have a submit-rule to open up another instance of the same form. However, the XML datasource does not update even if i add a rule to the form_load event to re-query the secondary XML File datasource. If i look at the XML file in the browser windows, the data is there and correct, however infopath's version of this xml data was from when the first form opened and not when the second form opened and i requeried using the rule "Query using a data connection" to the XML data source. I know that the xml file should be changing, but it is not. However, if i open the form from sharepoint's list's "New" button, the xml file is queried correctly.

    Since i am using the XML file as a means to get the MAX(ID) of the main sharepoint list (This would essentially filter/limit the list to just the latest ID), this solution does not work.

    Without a solution using an XML File, i have to query the document library. My document library is very large and without an XML File solution, the infopath form loads very slow. However, with the XML file solution, i don't get a unique ID from the MAX(ID) function.

    So essentially, i am stuck between a rock and a hard place and it is infopath is proving to be a bad solution for large document library lists without it.

  • i checked.. filtering works like this:

    &FilterField1=Project_x0020_ID&FilterValue1=2007650B75

Page 7 of 7 (105 items) «34567