Populating form data from SharePoint List Views


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


Comments (112)

  1. wmhogg007 says:

    Hi,

    It may be just my browser, but it appears that the URL examples seem to be truncated.  Could you re-post the full URLs in the comments section?

    Thanks,

    William H

  2. infopath says:

    Sure:

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

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

  3. raymedia says:

    Hi there. Thank you so much for the tips. It worked for me really well..until I convert the view page to XSLT Data View in FP.

    I had to convert it so I could use the grouping filter in FP.

    ie) a and (b or c).

    When I open the *.iqv file, it launched the excel but then give a alert saying "Unable to open"

    I thought it may caused by typo but when I tired again..before and after convert the webpart, it clearly does not like that.

    Could you please advise me on this matter?

  4. sudhir.kesharwani says:

    Hi,

    Superb article,  it gave me answers for most of the doubts that i had in my mind ๐Ÿ™‚

    I just want to know if we can dynamically alter URL for secondary data source.

    Say i want to create filter based on the values selected in another field of my infopath form…

    is it possible ….

    Regards,

    Sudhir Kesharwani

  5. smckeon says:

    Thatโ€™s great!  And you can get a preview of the filter just by popping it into a URL and check it out. Except.. How do you handle special Characters? I have a SP list with a column that looks up into another list. Thatโ€™s the field I want to filter. So my filter string looks like this:

    &FilterField1=Customer&FilterValue1=3;#New England Bio Labs

    This above filter doesnโ€™t work. I suspect that itโ€™s because of the ID and delimiter at the beginning ie: โ€œ3;#โ€.  

    Any suggestions?

  6. dtr18c says:

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

    How might I apply an XSL style transformation on the returned xml?

  7. sumedha says:

    Sorry for asking such basic question but I need exactly what has been mentioned in this article. I have created the url and the data connection but I dont know how to show it on the form. Could anyone please ellaborate on this.

    thanks!!

  8. c10 says:

    Is there a limit on the number of elements a share point list can have?

    I have a list with around 300 items (rows) and 4 columns but when i try to use this list to bind to drop down column i get only about 100 returned.

    Is there a way to oversome this?

  9. infopath says:

    Hi c10,

    Is there a limit on the number of elements in a SharePoint list? There may be – but you may want to post that question to a SharePoint blog.

    However, the behavior you are seeing in InfoPath is expected. The items that are returned in a data connection are based on the default View in SharePoint. Each view in SharePoint, by defauly, limits the number of items to display to 100 – hence you only get 100 items returned.

    To workaround this:

    – Navigate to your SharePoint list

    – From the View dropdown choose Modify this view

    – Scroll to the bottom and expand the "Item Limit" section

    – Change 100 to something larger than what you would expect this list to store (i.e. 500, 1000, etc.)

    I hope this helps!

    Scott

  10. wsmallwood says:

    Hi, I am very new with Infopath, so I apologize for the simplicity of the question.  I am using this code to essentially create a sharepoint/infopath based mail merge.  It is working GREAT, except I can’t move to the next record.  Any suggestions?

    Thanks in advance!

    Wendy

  11. mattmw24 says:

    Can I put a variable in the URL – i.e. a field in the InfoPath form?  That way the filtered value in the URL is populated by a choice the user makes on the form?  I’m trying to populate a repeating table from a SP list that only includes the items in the list for what they select.  So if the list contains detail for all projects, when they choose a project on the form, the repeating table is populated with only the detail for the project chosen.  I’ve seen examples using custom code, but I don’t have VS tools for Office and I thought this would be possible out of the box.  Am I wrong?

    Thanks,

    Matt

  12. kristina.fitzgerald says:

    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

  13. jhill@ppc.com says:

    Hello – looks like it’s been a little while since others have posted, but thought I would ask anyway.  We’re trying to include a multi-line column from a list into an Infopath form.  I successfully generated the url as shown above, and the url does return xml data in a browser.  The url looks something like this:  http://oursite.com/ocio/cis/gitmeet/_vti_bin/owssvr.dll?Cmd=Display&List={7B30642C-A7E2-44AF-95C2-4AFF5E13461B}&XMLDATA=TRUE

    However, when trying to create a Receive data connection in Infopath, the wizard will not accept the url — "unable to connect to the sharepoint site."

    Thanks.

  14. infopath says:

    Hi jhillppc,

    Are you able to connect to a list (on the same site) that does not contain a multi-line column? I don’t believe this is an issue with that column type as I can connect in InfoPath and display the multi-line data without issue so something else may be occurring.

    Are you familiar with the network tracing tool Fiddler? You can get it here (http://www.fiddler2.com/fiddler2/version.asp – click the Install Fiddler2 link) and then you can start this while trying to create the connection. This is a network tracing tool but it only traces HTTP traffic. Maybe what you see in this trace will point you in the direction of the issue.

    Scott

  15. jhill@ppc.com says:

    Hi, Scott – thanks for the reply.  I will try Fiddl’ing.

    Jan

  16. jhill@ppc.com says:

    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

  17. infopath says:

    Hi Jan,

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

    Scott

  18. jhill@ppc.com says:

    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

  19. MiniSvlad says:

    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.  

  20. jccarvalho says:

    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

  21. infopath says:

    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

  22. jccarvalho says:

    Hi Scott,

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

    João

  23. jccarvalho says:

    Hi Scott,

    many thanks. it’s works very well.

    regards,

    João

  24. gp28 says:

    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

  25. doubleH says:

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

  26. infopath says:

    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

  27. doubleH says:

    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 ?

  28. infopath says:

    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

  29. gp28 says:

    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

  30. infopath says:

    Hi Grahamp,

    Yes – you will need to hand-modify the View (the XSL file) that contains the control (i.e. the dropdown) that is showing the values.

    Take a look at the steps I provided to jccarvalho on Wednesday, April 15, 2009 11:27 – these are the same steps you will need to complete.

    Scott

  31. adayuen says:

    How to connect to a XML file or SharePoint list sized at around 5500 records in browser-enabled Infopath form 2007

    Dear All,

    I have a data file which contains around 5500 records of user data. I created two data connections -one to very huge XML file and another to very huge SharePoint list. However both can’t work in Browser form due to server timeout or what ever make the server can’t response. Once I cut it 1/4 in size of records then it wokrs hence I guess this is due to too many records.

    I am wondering if there is anyway to connect to a single database file size @5500 records in browser-enabled InfoPath form? I don’t have the SQL server.

    Please help!

    Regards,

    Ada

  32. gp28 says:

    Hi Scott

    My previous posts are all working fine. Thanks a lot for the help.

    Would you know how I can populate a drop down box from a sharepoint list, or even a built in list, to return "active" hyperlinks. Or use a graphic, like a GO button once the item is selected from the drop down list.

    I only have 2 or 3 links per item which are intended to link to a procedure, form or word document which are all sitting in sharepoint library. Thanks

    Grahamp

  33. infopath says:

    Hi Grahamp,

    Here is something I just tested that, I think, is close to what you are asking:

    – Created  a list that contains a column of type "Hyperlink"

    – Added some items to the list where the hyperlink column points to different documents on my site

    – Added a Receive data connection from my InfoPath Form Template to this list and I am able to get the hyperlink column

    – Added a Dropdown Listbox control to the template, set the source to this data connection and then set the "Value" property for the dropdown to the hyperlink column and set the "Display" property to the Title column

    Now, when I select an item from the dropdown listbox what is being stored is the hyperlink to the document. What you cannot do "out of the box" is once an item is selected, automatically navigate to the selected link. You could use code for this or you could simply add a "hyperlink" control next to the dropdown listbox so once an item is selected, the hyerplink control will show the address and allow the user to click on the link.

    In testing this I found that the data connection retrieves both the "address" and "description" properties for the hyperlink. In my case, I left both of these as the URL so in the hyperlink control on the form template, instead of just pointing it to my field (i.e. field1) I needed to use the following expression so it would only display one URL:

    substring-before(my:field1, ",")

    I hope this helps!

    Scott

  34. gp28 says:

    Thanks Scott

    The receive data connection works fine and the title of the document is displayed in the drop down box.

    I added the hyperlink control and edited it to select the data source (my:field1). With or without the substring the value returned is always, http://address/ and will not activate the hyperlink.

    Any ideas?

    Grahamp

  35. gp28 says:

    Scott

    Update to my last post

    All works fine if I simply insert a hyperlink control using text. The problem occurs if I insert a graphic and then apply the hyperlink to read the field1 data source. The link simply reverts back to read the address field which is not populated. Thanks

    Grahamp

  36. infopath says:

    Hi Grahamp,

    What do you mean by "insert a graphic?" Can you provide me the steps you are following?

    Thanks,

    Scott

  37. gp28 says:

    Hi Scott

    Sorry, I wasn’t very clear.

    I’m using an image of a "GO" button in a cell next to the drop down list. The image is simply pasted into the cell and then I apply a hyperlink to the image that reads the value in the drop down list.

    Users select from the list and then the GO image to get the document.

    Hope this explains it better. Thanks

    Grahamp

  38. infopath says:

    Hi Grahamp,

    Hmmm…this is working for me. Here is what I have:

    – Data connection to my SharePoint list where I have a "hyperlink" field defined where each one points to documents on my site

    – Dropdown box that uses this data connection as its source. I have the "Value" property set to my hyperlink field and the "Display" property to the Title field

    – I copied and pasted an image to my form template and set the hyperlink as follows:

        – Link to: Datasouce: substring-before(my:field1, ",")

        – Display: This was auto-set to "<< Selection in document>>"

    Now, what may be key here is what is being returned from your list. Drag the node that the dropdown box is bound to your View again but this time as a text box. Preview the form and make a selection – is the correct URL being returned? Again, in my case I was getting the URL returned twice as: URL, URL – this is why I needed to use the "substring" function.

    Scott

  39. gp28 says:

    Scott

    I checked the links and all are being returned correctly. Exactly as you described above I pasted an image and set the hyperlink to the Datasource.

    However before previewing I rechecked the hyperlink and find the "Link to: value has changed and the Adress is now selected displaying "http://address&quot; with the Datasource greyed out.

    Totally confused?

    Grahamp

  40. infopath says:

    Hi Grahamp,

    Well on the positive side…at least we know why the link does not work! ๐Ÿ™‚

    Let’s look at something else: are you using the method described in this post to retrieve the fields from your list? If so, I was not – I was just using a standard SharePoint list data connection so I may need to try this method to see if there is any difference in the result. Also – how did you create the hyperlink column in SharePoint? Was it just adding a hyperlink column to your library or was it a site column? Can you provide me with a sample URL that you have in one of those fields?

    Scott

  41. gp28 says:

    Scott

    Again like you I am using a standard Sharepoint list data connection, but will be using the method described in this post to filter the data when all is running.

    In the Drop down list box properties I have tried all 3 methods of linking the data source in the "List box entries" section all with the same result. The drop down works well and the correct data is returned. If I insert a hyperlink anywhere on the form view using the "Data source" as my:field1 and the "Display" as text all works perfectly well.

    Using an image and adding a hyperlink with a data source is where it falls apart.

    I have created a Links library in Sharepoint to handle the data required to be filtered and returned to various types of forms.

    Would you please look at entering the links manually in the drop down box, then paste and hyperlink an image to the drop down to see if you get the same result. Somehow I can’t believe this is a Sharepoint issue.

    There’s a 12 hour time difference between us so I’ll follow with a sample url later if still required.

    Many thanks Scott

    Grahamp

  42. infopath says:

    Hi Grahamp,

    I read the post you found and have tried hard-coding the URL’s in the dropdown as well as first hard-coding the hyperlink on the image and then switching back to binding the hyperlink to "field1" and in all cases, this is just working for me.

    I am still concerned though over the way you are phrasing what you asked me to do: "Would you please look at entering the links manually in the drop down box, then paste and hyperlink an image to the drop down to see if you get the same result".

    For this request, here is what I did:

    – Changed my dropdown box to "Enter list box entries manually"

    – I then entered two URL’s to two different documents on my server. I set the "value" to the URL and the "Display" to some dummy text (here are my URL’s: http://myservername/FileAttachments/SomeTextFile.txt and http://myservername/FileAttachments/SomeWordDoc.docx)

    – I had already pasted an image to my form in the following manner:

        – Navigated to C:WINDOWSWeb

        – Right-clicked on the "exclam.gif" file and chose Copy

        – Right-clicked in my layout table cell and chose Paste (I have a layout table on my View with 1 row and 2 columns. The first column has the dropdown and then second column contains this pasted image)

    – I right-clicked on the image and chose Hyperlink

    – In the "Link To" option I chose Data Source and selected my "field1" and clicked OK

    I then Preview the form, select either option from the dropdown, click my image and the appropriate document opens.

    Is there anything here that is different than what you are doing?

    Scott

  43. gp28 says:

    Hi Scott

    Aaaha it works but don’t ask me why!

    The only thing that we are doing differently is that I am selecting Insert > Picture> From File and you are copying and pasting.

    Infopath will not let me paste a copied image from the clipboard onto the view, all options to cut, copy and paste are greyed out.

    When I copy an image already in my view and paste it into the required cell it works fine.

    So the operative word here is "paste" Any ideas why?

    Grahamp

  44. infopath says:

    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

  45. gp28 says:

    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  

  46. infopath says:

    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

  47. Louie1 says:

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

    thanks, Lou

  48. jboling says:

    @ 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

  49. RandySt says:

    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?

  50. wizardntexas says:

    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?

  51. lluke says:

    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>

  52. infopath says:

    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

  53. lluke says:

    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

  54. infopath says:

    That is great to hear!

    Scott

  55. lluke says:

    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

  56. Stone 13 says:

    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?

  57. lluke says:

    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

  58. infopath says:

    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

  59. filipbruins@hotmail.com says:

    Thanks! Using the url trick i was able to solve a sorting problem with my drop down content bound to an SP List.

    Drop downs in Info Path web forms were sorted in creation date order, instead of alphabetical order. Binding to the returned xml file instead of directly to a list solved my problem ๐Ÿ™‚

  60. carvis says:

    Hello to all… this is a great articvle and i can almost use it.

    I have a sharepoint list and a form that queries that list using a data connection. The list is already sorted but when the drop menues get populated in the web-form they are not sorted.

    When you say "Go to modify settings and coloumns", where do you mean? My list URL shows this:

    http://collab.safeway.com/it/Contentmgmt/Lists/Consumer%20Protection%20Multi/Allitemsg.aspx

    I dont see anything containing a GUID or "_vti_bin…".

    Where do i get the GUID?

    Thanks a bunch and any help is appreciated.

  61. rbuchanan says:

    This probably isn’t the right post for this but maybe somebody can point me in the right direction.

    I’m using infopath 2003 with sharepoint 2007.  

    I have a form with a drop down list that uses a column from a sharepoint list as a data source.  

    The column has numerous duplicates but I’m trying to retrive a unique list.  

    I’ve been trying trying to stumble my way through trying to create a filter but haven’t had much luck.

    Any help or suggestions would be great I haven’t had much with the posts I’ve found so far.

    Thanks

  62. jrc1 says:

    Is there a way to specify the row from a list that data in the columns are used to populate the infopath form.  In other words, I have 8000 employees and want to populate a portion of a form with info from a SP list for a specified employee.  How can I tell the infopath form what employee I want to pull from?

  63. shelly255 says:

    Hi –

    We have used your "View" method to bind only a view of a list to a control many times and it works beautifully.  However, I recently did the same thing on a list that is a content type, and the results were not as expected.  I created a view of the list, selecting only the columns I wanted to choose from on my InfoPath form, but when I brought up the form in Infopath and tried to bind to a control, other columns were there (from other views) and some of the columns I wanted were not.  Does this method not work with a list that is created using a content type?

    Thanks,

    Shelly

  64. sharepoint99 says:

    Hey Scott,

    How to use ur technique for Accessing Subfolders via secondary data connection …i have folders in my Form Library and when i make a data connection from infopath to the library it works only at the root level and does not work inside the folder ie it does not get value when the form is inside a folder..any idea how can i solve this issue

  65. infopath says:

    Hi sharepoint99,

    I don’t know of any way to get this process to work with subfolders in a library.

    Scott

  66. Spegg says:

    The list I am accessing has a lookup column in it. It is this column I am interested in.  When the data is displayed in my browser infopath form, the lookup ID and Value are displayed.  I.E. 23;#BOM.  How do I just retrieve the Value? (BOM)

  67. MarsdeNation says:

    This is great and it works, but when I update data in the list, the data that is in the form is not updated, is still contains the old data, even data that has been deleted from the list.  I was hoping it would be dynamic but it seems to be static.

  68. slheim says:

    Hi MarsdeNation,

    When you setup the data connection you want to make sure you choose the option "Access the data from the specified location" – this is not the default option. As such, if you left the default option (Incluse the data as a resource file…) then the data will not automatically refresh.

    Scott

  69. joel says:

    hi there, thanks for this useful post, i'm able to see the data when previewing in infopath as well as when pasting the URL to the browser's address bar but once i'm loading it inside an infopath web part, i'm getting this error….

    An error occurred querying a data source.

    Click OK to resume filling out the form. You may want to check your form data for errors.

    Hide error details

    XML data that is required for this form to function correctly cannot be found.

    An entry has been added to the Windows event log of the server.

    Log ID:5566

    Correlation ID:6558c56a-e6e5-4c6d-80fc-95af77785a5f

  70. Gertjan says:

    Joel, do you still have this XML Data problem?

    I too have this problem. The form and code work fine when opened in infopath (on the sharepoint server) and displays/filters the correct values, but when published to my sharepoint site, it opens and shows all values, except when I select one from the first column, it gives me that error. Cannot find anything in the event logs on the server.

  71. Paul_Mc_87 says:

    Ello ello ello

    I have an SP list I am querying using this XML method (i could use another but i quiote like this way) problem i am having is the drop down being populated is coming from a multi line field in SP. The reason for the SP being multi line is that when the user makes a selection i want a number of drop downs to populate based on the selection, therefore tis better i have once row per initial selection rather than a new row (or even table) for every possible answer / question respectively.

    Anyway the problem i am having is that my results when displayed in the combo box are all displayed on one line and i would like to seperate them, i have tried turning the field into rich text rather than plain text but this just displays the HTML . I did think of looping through the data and inserting a new line every time it find </br> but i think this is inefficient and also my coding skills are really not up to scratch for something like that.

    Any suggestions??

  72. Durbs-99 says:

    Thanks for the article, I realise you wrote it many moons ago.

    I am trying to implement and have read most of the posts but can't get the final bit.

    I have data pulling into SharePoint from an InfoPath Form.

    The default view only shows 100 items and that's the way the client wants it.

    However in the form I want to see all list items in the drop down.

    I have created a dedicated view and copied the GUID in the manner you mentioned.

    My issue is that it still appears to show the 100 items when testing in the browser:

    xxxxxx.sp.xxxxxxx.net/…/owssvr.dll{AAB70B32-4E77-4A79-8212-7D42CE598181}&view={8557385C-3578-4675-A45C-805A5E6CAAB5}&XMLDATA=TRUE&noredirect=true

    Am I screwing up the syntax here ?

    the List and View Guids are different but if I remove the 'view={8557385C-3578-4675-A45C-805A5E6CAAB5}' section I basically get the same result despite the View showing all 200 list items.

    Help Appreciated

  73. Scott Heim says:

    Hi Durbs-99,

    I don't believe I am able to reproduce what you are seeing – here is what I did for a test:

    – Have a custom SharePoint 2007 list with 20 items

    – My default view for the list shows the items in groups of 5

    – I created a new SharePoint view that shows all items

    – In my XSN, I added two data connections:

    LimitedItems:

    http://servername/_vti_bin/owssvr.dll?Cmd=Display&List={4A837605-81CD-4F27-9C3B-4A6EEF62B379}&XMLDATA=TRUE&noredirect=true

    AllItems:

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

    – Added 2 dropdown list boxes to my InfoPath Form Template

    – Bound the above data connections to the dropdown list boxes

    – Published the XSN to my server

    – Opened it in the browser – result: one showed 5 and the other showed all 20.

    Out of curiosity what happens if you just drag the data connection to your view as a "Repeating Table" – this makes it easier to see how many items are truly being returned.

    Scott

  74. Bharath I says:

    Hi

    i am working with this SharePoint URL protocol, its quite good. But my question is can i get list item versions using this URL protocol concept and also can i apply filter operator, for example

    Example: http://contoso/sites/_vti_bin/owssvr.dll?Cmd=Display&List={115BC7B7-0A82-403E-9327-F3C73E6D37F3}&XMLDATA=TRUE&FilterField1=ID&FilterValue1=2&FilterOp1=Geq

    Is there a way to use filter operator in URL protocol.

    thnxs

  75. April says:

    I got a question I'm hoping someone can answer.  I've used this URL protocol successfully many time in my SharePoint 2007 environment, however, when I try to replicate in my SharePoint 2010 Dev Environment I get a file not found error when I run my form with the code.  I believe the issue could be tied to the fact that when I type the url and put in the browser, it prompts me to save rather than just displaying in the browser.  Does anyone have a solution for this?

  76. April says:

    Update: I figured out what the issue was with my SP 2010 error.  For My Filter Field Value, I was using a field name with a space in it.  To get it to work, I had to click on the field and get the actual field name and place it in my string and it worked.

  77. PB_Infopath says:

    Hi,

    I tried modifying the XSL for string;# issue in the dropdownlist items, but if i add substring-after() and Preview the form, dropdown look ok, but when i try to publish and upload the .xsn on my sharepoint server it gives me – 'Unknown errror' while trying to upload the form. Any idea what's causing that?

    – PB

  78. andrew says:

    I need to have cascading dropdowns in my infopath form.   My question is how to I select from a dropdown and filter the data in the second list using c# code? I would like to use the changed event to drive the retrieval of the second list and then bind back to the dropdown. This is a very customized form. I have attempted this using the filters on the dropdowns but it does not work in the basic template for an unknown reason.

  79. Hi,

      Good article. Now i am working on infopath forms in sharepoint. I want to print the infopath forms stored in the sharepoint as .xml files using C# and aspx pages of wsp project programatically. Can you have any idea about it? Waiting for the reply …….  Thank you….

  80. Colleen says:

    I saw a reference to multi-line SharePoint List columnns below.  I'm trying to pull data from a SharePoint Custom List into my InfoPath form.  For some reason, it won't allow me to Add the multi-line columns that are in the list.  The other types of fields came in (Single Line of Text, Choice), but not the Multiple Lines of Text Column.  I re-established the Data Connection, but still nothing.  Thoughts?

  81. Michael Friedenberg says:

    Does this work in Infopath 2007.  I thought I could set up a secondary data connection.  It allows me to set it up initialy but when I try to modify it or use in the form. It tells me that I can't connect. But I have the SharePoint list open in my browser so I'm obviously able to connect.

  82. Luis says:

    I have a travel request form, and I created a list in sharepoint with profiles containing preferences of the member who will fill the form. I want to retrieve these preferences and fill them in the correct fields depending on the user.

    How can I do that?

    Please help

  83. Scott Heim says:

    Hi Luis,

    Here is an overview of the steps you will need to take assuming you are using InfoPath and SharePoint 2010:

    – Create a "Receive" data connection to your SharePoint list but be sure to uncheck the option: "Automatically retrieve data when the form loads" on the last screen of the Data Connection Wizard. We don't want the form to download all the data right now.

    – On the Data tab of the Ribbon, click the Form Load button – this will add a Rule to the Form Load event

    – Add the following actions:

        – Set a field's value

             – Field: The query field from your list connection that uniquely identifies the member (not sure if you know this when the form opens)

             – Value: the member name (or ID, etc.)

        – Query for Data

             – Data Connection: select your SharePoint list connection

    So at this point, what we have done is query for the one member record that matches the member you entered above. Again, if you don't know this at the time the form loads then you can use a Rule on, say, a button to do the same thing. Now, for the rest of the Rule actions:

        – Set a field's value:

             – Field: One of the fields in your main data source that you want to fill with data from your list

             – Value: The corresponding field from the "data fields" of your SharePoint list data connection

        – Set a field's value – repeat the above step for each of the fields you want to fill

    A few things to consider:

    – If you don't know the member ID, name, etc. when the form is opened (in other words, someone would be entering that information) then you will need to use a Rule on a button or the text box where they enter the member name, ID, etc.

    – If there is no way to uniquely identify the member in your list (i.e. member name or member number, something like that) and you are going to be returning multiple rows of data then the only way to populate that data will be using code.

    Scott

  84. Juli says:

    I am having an interesting issue with this.  I am setting up this connection for an Infopath 2010 form.   As long as I put the guid in without the {}'s it allows me to set it up.  

    As soon as I put the guid between the {} it tells me the internet address is not valid.

    Any suggestions?   I suspect that not having it in the {} is causing another issue.

    Thanks

  85. Vinod says:

    Dropdown changed event woking fine in Infopath preview, when I published in Sharepoint server 2010 the event is not firing

  86. Scott Heim says:

    Hi Vinod,

    As a quick test, try this:

    – Open the XSN in Design View

    – Right-click on the dropdown box and choose Properties

    – Select the Browser tab

    – Change the Postback setting to: Always

    – Save the changes, re-publish and test

    Any change in behavior?

    Scott

  87. Amanda says:

    Hi

    I am trying to achieve an alphabetically sorted dropdown list box in Infopath 2007 (with SP2007). My form needs to be browser-enabled.

    I have folloed Ed Essey's original guidance but have run into the same problem that Scott, Gertjan and Joel have experienced, i.e, my dropdown list is working in Infopath/Preview, but does not work when I publish to website. When I try to load the form in the browser it shows an iInfopath Forms Services' error – "There has been an error while processing the form" and the options are to Start Over (with a new form) or Continue (with existing form). If I continue the dropdown is empty whereas rest of form is fine as before. The error details is "An error occurred accessing a data source. An entry has been added to the Windows event log of the server.  Log ID:5566"

    Did anyone find a solution to this error?

    Thanks, Amanda

  88. Amanda says:

    In response to my own query above (dated 16th Feb). I searched for the 5566 error and found the article support.microsoft.com/…/896861.

    Implementing Method 1 fixed the problem for me and now my list box is sorted alphabetically ๐Ÿ™‚

  89. Keith says:

    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??

  90. Scott Heim says:

    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

  91. Mohamad Rafi says:

    Hi,

      Really this thread help me alot. Thank you.

  92. Hans Anker says:

    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?

  93. Cubix says:

    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

  94. Scott Heim says:

    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

  95. TC says:

    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.

  96. TC says:

    —-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?

  97. Joni says:

    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

  98. Jesse B says:

    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????

  99. Anil Bingu says:

    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

  100. CharlesD says:

    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

  101. SD says:

    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.

  102. Nullz1011 says:

    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.

  103. correct filtering says:

    i checked.. filtering works like this:

    &FilterField1=Project_x0020_ID&FilterValue1=2007650B75

  104. Zed says:

    the problem is that the database does not refresh…an idea

  105. Gregory says:

    Hello,

    hope anyone will see that comment. I am using InfoPath 2013 and try to add Data Connection to: collaboration.kraft.com/…/owssvr.dll{4E06076A-FABD-4677-B840-AE3A63A13DEE}&XMLDATA=TRUE

    But the only thing I get is : InfoPath can't open XML document. The file is not a valid XML

    It seems to blank

    What am I doing wrong?

  106. Gregory says:

    Something wrong with the link view:

    "https:// collaboration.kraft.com/sites/ <site name> /_vti_bin/owssvr.dll? Cmd=Display& List={4E06076A-FABD-4677-B840-AE3A63A13DEE}&XMLDATA=TRUE

  107. Scott Heim says:

    Hi Gregory,

    The first thing you will want to do is make sure the URL you are creating is valid and that it works in Internet Explorer. If you don't get data back in IE, then it will not work in InfoPath.

    – Launch IE

    – Copy/paste this URL (after fixing the site placeholder) and see if you get data in IE:

    https:// collaboration.kraft.com/sites/ <site name> /_vti_bin/owssvr.dll? Cmd=Display& List={4E06076A-FABD-4677-B840-AE3A63A13DEE}&XMLDATA=TRUE&noredirect=true

    You should see the list data being returned in the browser. If not, then make sure the list exists at the level of your URL. As an example, if the list is actually at the root site but you are specifying a subsite, then it will fail.

    Scott

  108. Ben says:

    Hi, I have the connection string working, I want to filter it to data that is contained within a field on the inforpath form I am using to connect to the list.

    Is there anyway to enter the field into the filter part of the connection string?

    I have tried using the field xpath data and field name after FilterValue1 without any luck.

    To test I have manually provided some demo data of this field in the value and viewed the filtered xml within the browser and it worked, but I need that dynamic data from the field in the actual form.

  109. AzuDaioh says:

    Hello. Thanks for the post. I have a question about filtering the secondary data connection. I was following the steps outlined on the post dated Oct 12, 2011:

    I added a button with the following actions:

    Set a field's value:@ows_ID=field6

    Query using data connection:owssvr

    Set a field's value:field1=@ows_field1

    But I am getting an error message "some rules were not applied, the paramter is incorrect." Any help is appreciated.

    My secondary data source contains over 5000 items so I'm hoping to be able to only connect to the one record I need on my form.  Thanks so much.

  110. Grig says:

    It is too hi level. I can not populate a combo box with what you provide.

    It is too general. You've missing a lot of steps!