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 2 and 4 and type the answer here:
  • Post
  • Hi Grahamp,

    Very odd - I just tried the same method: Insert | Picture | From File and it still works fine.

    I have not seen an issue before where you could not paste an image into an InfoPath View while in Design View. I hate to ask this question (especially on a public forum <g>) but does the same behavior occur if you reboot, which will clear your clipboard?

    At any rate, there is something to be said for "if it's not broken, don't fix it" <g> now that it is working! :)

    Scott

  • Hi Scott

    I was wondering if I should feel embarrased, but no the problem remains. I think enough said and I'll just work around it.

    Many thanks for your help. Now back to reality!

    I have a sharepoint list with 7 columns plus an ID col. I am using the method described in this post to populate a repeating table with information contained in 3 of the columns.

    2 other columns plus the ID are used to filter the list information into the appropriate form. All works well.

    Information for the remaining 2 list columns is to be entered by the user in the form and submitted back to the sharepoint list to close the loop and allow reporting of the action taken.

    I have read your article on submitting to a sharepoint list but am having problems modifying the process to suit my application. Any help would be appreciated.

    Grahamp  

  • Hi Grahamp,

    Sorry I was not able to fully help on the hyperlink...obviously something is still different in our scenarios but not sure what.

    In regard to submitting to a SharePoint list, you will need to review all the comments as well in that post. Typically custom code is used to do anything other than an "add" and in my post there is sample code to perform an update and delete. (In fact, I think the sample code handles all options: add, update and delete.)

    The reason code is used is you need to know which list item you want to update or delete and this is done by the SharePoint ID for the specific list item. Granted you may be able to get what you need without code but this is one of those scenarios where, personally, I feel it is easier to use code to do what you need.

    If you have any additional questions on updating a SharePoint list, please be sure to post them on that blog.

    Scott

  • Is there a way to wrap text in an infopath drop down box?  The pox is populate by a SharePoint list.

    thanks, Lou

  • @ kristina.fitzgerald (Thursday, August 28, 2008 2:09 PM):

    I had the same problem, and am disappointed with the solution.  Infopath would not recognize my source view's data row as a repeating entry until I had more than one entry in the view.  That is very lame if you ask me.

    ---------------------------------------------------------------------------

    Original post:

    Hello,

       I'm experiencing a weird behavior with the way the data connection is reading the XML file in my drop down controls. When I attempt to set the "Entries" field to "z:row", I am prompted with the error "You must select a repeating group or field."

       I have another form where I followed the same directions you outlined above, but I generated a resource file, and the "z:row" field is indeed a repeating field and works just fine.

       Is there a reason why this data connection is pulling the "z:row" field as a non-repeating? Am I missing a step? Thanks in advance - K

  • Thank you for all of the great information so far.  I have been able to set up that connection and retrieve data that I have populated into a drop down list.  At this point, I am trying to create a rule to auto-populate other fields in my form based off of the selected value in the drop down list.  I past instances where I create a normal connection to Sharepoint, I would use a rule similar to...

    Field:  hoursCharged

    Value:  @hoursCharged[@projectID=projectID]

    This worked fine but when I try the same type of formula in the value field using an XML connector ti will not work.  Any suggestions?

  • Do you know of a way?

    user opens form, populates fields, infopath dropdowns are selected,form submitted. How do you take the information from this form and subtract it from the list used in the dropdown. So the next user will not have access to the items the previous user selected? Or for a lack of a better term checked out but not to be confused with checking forms in or out from sharepoint.  I find nothing but limitations with these collaborating tools? Infopath and sharepoint?

    please advise?

  • I am also having trouble removing the # from the account id name list.  My view1.xsl for my dropdown looks very different that what you have posted. Can you help me with the syntax to code my substring parameters.I only see two place to insert the substring.

    Here is my code.

    xd:xctname="dropdown" xd:CtrlId="CTRL665" xd:binding="my:Sys1BusOwner" xd:boundProp="value" value="Select..." xd:postbackModel="auto" tabIndex="0">

    <xsl:attribute name="style">WIDTH: 100%; FONT-SIZE: x-small;<xsl:choose>

    <xsl:when test="my:Sys1BusStatus = &quot;Denied&quot; or my:Sys1BusStatus = &quot;Approved&quot; or my:Sys1BusStatus = &quot;N/A&quot;">DISPLAY: none</xsl:when>

    <xsl:when test="../../my:Hidden_Section/my:CurrentUserPermissions != &quot;Admin&quot;"/>

    <xsl:when test="my:Sys1BusOwner = ../../my:Hidden_Section/my:Current_User">BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold</xsl:when>

    </xsl:choose>

    </xsl:attribute>

    <xsl:choose>

    <xsl:when test="my:Sys1BusStatus = &quot;Denied&quot; or my:Sys1BusStatus = &quot;Approved&quot; or my:Sys1BusStatus = &quot;N/A&quot;"/>

    <xsl:when test="../../my:Hidden_Section/my:CurrentUserPermissions != &quot;Admin&quot;">

    <xsl:attribute name="disabled">true</xsl:attribute>

    </xsl:when>

    <xsl:when test="my:Sys1BusOwner = ../../my:Hidden_Section/my:Current_User"/>

    </xsl:choose>

    <xsl:attribute name="value">

    <xsl:value-of select="my:Sys1BusOwner"/>

    </xsl:attribute>

    <xsl:choose>

    <xsl:when test="function-available('xdXDocument:GetDOM')">

    <option/>

    <xsl:variable name="val" select="my:Sys1BusOwner"/>

    <xsl:if test="not(xdXDocument:GetDOM(&quot;Business Owner Sorted&quot;)/xml/rs:data/z:row/@ows_Business_x0020_Owner2[.=$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:variable name="items">

    <xsl:for-each select="xdXDocument:GetDOM(&quot;Business Owner Sorted&quot;)/xml/rs:data/z:row/@ows_Business_x0020_Owner2">

    <attributeElement>

    <xsl:value-of select="."/>

    </attributeElement>

    </xsl:for-each>

    </xsl:variable>

    <xsl:variable name="uniqueItems" select="msxsl:node-set($items)/*[not(. = preceding::attributeElement)]"/>

    <xsl:for-each select="$uniqueItems">

    <option>

    <xsl:attribute name="value">

    <xsl:value-of select="."/>

    </xsl:attribute>

    <xsl:if test="$val=.">

    <xsl:attribute name="selected">selected</xsl:attribute>

    </xsl:if>

    <xsl:value-of select="."/>

    </option>

    </xsl:for-each>

    </xsl:when>

    <xsl:otherwise>

    <option>

    <xsl:value-of select="my:Sys1BusOwner"/>

    </option>

    </xsl:otherwise>

    </xsl:choose>

    </select>

  • Hi lluke,

    I think the problem you are having is the way you setup the data connection on the dropdown box control. Try these steps:

    - Open your XSN in Design View

    - Right-click on your dropdown box control and choose Properties

    - In the "Entries" box, you should only be selecting the "z:row" node from your data connection. (In my test, what I see in the Entries box is: /xml/rs:data/z:row)

    - For the "Value" and "Display name" properties, here is where you will select the "Business Owner2" node from your data connection.

    Once you have made these changes, then follow the steps I have previously for modifying the view.xsl.

    Scott

  • Scott,

    You were right.  When setting up my dropdown control I was drilling too far down for my Entries selection.  Good catch.  It is working like a charm now!

    lluke

  • That is great to hear!

    Scott

  • Scott,

    I have one more issue.  My changes work fine in preview, but when I attempt to open the form in the browser I get the message 'The form has been closed'. Any ideas.  

    lluke

  • Scott,

    I am having an issue w/the solution.  It works great however if one of the columns in the Sharepoint list has all NULL values, that column is not returning in my XML.  I have tried querying the default list and a view I created, neither one seems to work on NULL columns, is there any workaround for this?

  • Scot,

    And yet one more issue. I have removed the data connection for the substrings until I can figure out why the form won't open.  I still have one xml file data connection which works just fine, but only for me.  When other users open the form from the url, they get this error.

    "The form cannot be opened because it requires the domain permission level. Log ID 5566 Error."

    Permissions are set correctly for the list and library. I am not sure what other permissions need to be changed.  

    lluke

  • lluke: for both of your new issues, I would enable "Verbose" logging for all of the Forms Services categories, reproduce the issue and then check the SharePoint ULS logs - hopefully there will be more detailed information there about what is causing the errors.

    If not, you may want to open a support case as we want to keep the questions/comments on our various blog posts specific to their respective topics.

    Stone 13: This is expected - if there is nothing in that column in SharePoint, the corresponding XML node does not exist - hence, you get this behavior. The easiest way around this is to simply set a default value for that field.

    Scott

Page 4 of 8 (106 items) «23456»