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 8 and 4 and type the answer here:
  • Post
  • Hi, Scott - thanks for the reply.  I will try Fiddl'ing.

    Jan

  • Scott - I was able to successfully create the data connection to the list with the URL, but the multi-line field is still missing.  I tried your suggestion of setting up a data connection to another list with multi-line columns, and bingo, those columns are selectable.

    Another one of those mysteries.  Thanks for the Fiddler link--it pointed out to me that while http: worked in IE, I needed https in Infopath.  Duh.  But still no multi-line column from the list I need--all the other columns are there, waiting to be selected.  Sigh.

    Thanks again for the tips.

    Jan

  • Hi Jan,

    Anything different (property wise) with the multi-line column in the list that fails?

    Scott

  • Happy Friday, Scott - the multi-line column that I was able to retrieve is set to plain text, 5 lines.  The missing column is set to rich text, 15 lines.  So, I added a new multi-line column, went to Infopath and created the data connection, selected the new column, and voila, it appears as it should.  Set the field in the Infopath form to wrap, and all is well.  Finally.  Don't know why it took so long to think of that option, but at least we have a solution.

    The "failing" column was chosen from existing site columns, All Groups, as there are no options to change it to another information type (single line, choice, number, etc).

    Thanks again for your guidance.

    Take care,

    Jan

  • Good article... I've created my data connection into a list, but the field that I want to display in my InfoPath dropdown is a calculated field in the list.  It won't give me the option to show that field.  

  • Hi,

    good article.

    I've created my data connection, and works fine, but, in a calculated field display "string;# " in the begging of the field. How i can remove this? thanks

  • Hi jccarvalho,

    This can be done but you will need to hand-modify the transform (the XSL file) for the View where you are using that connection.

    Before you try these steps with your XSN, please make a back up copy of your XSN!! :)

    NOTE: These steps assume you are using the data connection as the source for a dropdown box.

    1) Create a new, blank XSN

        - Add the data connection to your list

        - Add a dropdown box to the View

        - For the "Entries" property of the dropdown, select the "z:row" node from the data connection

        - For the Value and Display Name properties, select your calculated column

        - Save the XSN as: Test.XSN

    2) Create a new folder on your Desktop called: TestXSNSourceFiles

    3) With Test.XSN open in Design View, from the File menu choose Save As Source Files and save these to the new folder, TestXSNSourceFiles

    4) In the TestXSNSourceFiles folder, open "View1.xsl" with Notepad

    5) You need to now locate the XSL for the dropdown box - it will lool similar to this (NOTE: my calculated column is called: calcDate and you see it here as: ows_calcDate.)

    <select class="xdComboBox xdBehavior_Select" title="" style="WIDTH: 130px" size="1" xd:binding="my:field1" xd:boundProp="value" value="" xd:xctname="dropdown" xd:CtrlId="CTRL1" tabIndex="0">

    <xsl:attribute name="value">

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

    </xsl:attribute>

        <xsl:choose>

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

              <option />

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

              <xsl:if test="not(xdXDocument:GetDOM(&quot;MyList&quot;)/xml/rs:data/z:row[@ows_calcDate=$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(&quot;MyList&quot;)/xml/rs:data/z:row">

              <option>

                  <xsl:attribute name="value">

                        <xsl:value-of select="@ows_calcDate" />

                        </xsl:attribute>

                        <xsl:if test="$val=@ows_calcDate">

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

                        </xsl:if>

                        <xsl:value-of select="@ows_calcDate" />

    </option>

    </xsl:for-each>

             </xsl:when>

    <xsl:otherwise>

              <option>

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

    </option>

    </xsl:otherwise>

    </xsl:choose>

    </select>

    6) There are 4 places in this snippet where you need to implement the "substring-after" function on the calculated field. In short what this function will do is return everything "after" the "string;#" part of the returned column:

    <select class="xdComboBox xdBehavior_Select" title="" style="WIDTH: 130px" size="1" xd:binding="my:field1" xd:boundProp="value" value="" xd:xctname="dropdown" xd:CtrlId="CTRL1" tabIndex="0">

    <xsl:attribute name="value">

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

    </xsl:attribute>

        <xsl:choose>

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

              <option />

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

              <xsl:if test="not(xdXDocument:GetDOM(&quot;MyList&quot;)/xml/rs:data/z:row[substring-after(@ows_calcDate, &quot;#&quot;)=$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(&quot;MyList&quot;)/xml/rs:data/z:row">

              <option>

                  <xsl:attribute name="value">

                        <xsl:value-of select="substring-after(@ows_calcDate, &quot;#&quot;)" />

                        </xsl:attribute>

                        <xsl:if test="$val=substring-after(@ows_calcDate, &quot;#&quot;)">

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

                        </xsl:if>

                        <xsl:value-of select="substring-after(@ows_calcDate, &quot;#&quot;)" />

    </option>

    </xsl:for-each>

             </xsl:when>

    <xsl:otherwise>

              <option>

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

    </option>

    </xsl:otherwise>

    </xsl:choose>

    </select>

    7) Once you have made the changes described above, save and close your Notepad file.

    8) Right-click on manifest.xsf in the folder and choose Design

    9) From the File menu choose Save As and re-save this as your Test.xsn

    10) Preview the form - result: your calculated field should only show the actual values!

    As you can imagine, any errors in the changes you make in the XSL could cause your form to no longer open - hence my guidance on having a backup copy! :)

    I hope this helps!

    Scott

  • Hi Scott,

    thank's for your quick responde. I'll try.

    João

  • Hi Scott,

    many thanks. it's works very well.

    regards,

    João

  • Hi.

    I'm relatively new to Infopath and Sharepoint and the example described above is exactly what I am trying to achieve.

    I can filter the fields and return the xml to a drop down list ok. From the filtered fields I am populating a repeating table.

    My problem is the text being returned contains html tags, <div> text </div> or 1,#Name.

    I also don't really understand your statement in the example:

    "Note: be sure to remove the “ows_” from the beginning of the FilterField ID." or how to achieve this.

    Any help would be welcome. Thankyou

    GrahamP

  • This is a great post! I can't find the "Modify settings and columns" area. Does this apply to WSS?

  • Grahamp - what are the data types of those columns in SharePoint?

    doubleH - "Modify Settings and Columns" was the terminolgy used with SharePoint 2003. In 2007, simply navigate to your list and from the "Settings" menu choose "List Settings."

    Scott

  • Thanks Scott. Here is the URL I am trying to use...

    http://lynx/_vit_bin/owssvr.dll?/?Cmd=Display&List={%7B609688A9%2D3FBA%2D4FA6%2DAF27%2D695DF7422F40%7D}&XMLDATA=TRUE

    I am getting an error...

    The following file(s) have been blocked by the administrator: /_vit_bin/owssvr.dll

    Any ideas on how to correct ?

  • Hi doubleH,

    Hmmm...I have never seen this error before when using this sample. You may want to post this to a SharePoint blog to see if they can offer any suggestions as to where to look to identify the cause of the error.

    Scott

  • Hi Scott

    I'm using rich text and lookup data types. I've changed the rich text to plain text and that seems to work fine, however I need to use the lookup value in sharepoint and can't change this.

    Is there a way around using the lookup and returning the value which is a name in this case without the 1,# prefixing the name.

    Thanks

    Grahamp

Page 2 of 7 (105 items) 12345»