SharePoint Integration

Re: Query Sharepoint List - From Infopath 2007

seanpanter — Wed, 26 May 2010 18:54:17 GMT

I've been working on a simlar problem using InfoPath 2007 (Windows client solution) and SharePoint 2007 list data. I don't want to receive all of the list items in the InfoPath form.

The article below describes how you can use the OWSSVR.dll to receive pre-filtered SharePoint list data in XML form.

http://www.synergyonline.com/blog/blog-moss/Lists/Posts/Post.aspx?ID=24

I've connected to the OWSSRV.dll data from InfoPath to create a pre-filered SharePoint data source.

Query the SharePoint list data:

You can query a SharePoint list view using querystring parameters (see the article)

You can pass querystring values to filter the XML list data (http://[servername]/[sitename]/_vti_bin/owssvr.dll?Cmd=Display&List={GUID}&XMLDATA=TRUE&FilterField1=[ColumnName]&FilterValue1=[FilterValue])

Create an InfoPath Data Source:

Create a new InfoPath data source to Receive Data
Choose the XML Document option
Paste in your URL with querystring when prompted for the XML document

When creating a datasource in your InfoPath forms with OWSSVR.dll, if any of the columns are empty on all list items, this column will not be represented in the InfoPath datasouce. It may be good practice to seed your list with at least one item that has values in all columns before creating the datasource. I found this while migrating my form into a new environment and reconnecting it's datasources.

This should get you started with the OWSSRV.dll

For Consideration

The XML data returns additional values for multi-select SharePoint list columns ie// 2;#Request Received

You may want to look into using a Rule and the xPath function substring-after to clean these up

I'm not sure how well this would work in an InfoPath Forms Server solution (?)
Microsoft recently published this article on using the OWSSVR.dll: http://msdn.microsoft.com/en-us/library/ms478653.aspx

Re: Query Sharepoint List - From Infopath 2007

LISpeedyG — Sun, 02 Mar 2008 18:53:22 GMT

Hi Paul,

I too am very new to InfoPath and Sharepoint. I am also trying to query a SP list in InfoPath, since the overhead becomes too burdensome once all the items in the lists are retrieved (1600). And the form becomes unmanegeable.

If you would not mind explainign how to set up your routine on the server I would greatly appreciate it. And, also how you can pass a value from within the InfoPath form to have the SP List queried based on that value.

Additionally, I was wondering in anyone else has come up with a solution that enables the form data (once returned from SP) to be dissociated from the SP DB. This is needed since the form is emailied and we don't want the person receiving the form t ohave access to the DB. However, in the current configuration, the recipient is presented with the UserName and Password request and if they do not have one the form is cancelled. In our scenario we fill out one portion of the form with the recipients information and then the recipient is required to fill in the rest at their facility.

If anyone can help with some (all) of these questions I would truly appreciate it.

Thank You,

Gus

Re: Query Sharepoint List - From Infopath 2007

PNeate — Wed, 13 Feb 2008 08:53:47 GMT

Sure,

To be honest I based my function on the following code http://msdn2.microsoft.com/en-us/library/ms429658.aspx

But here is my function :-

Public Shared Function GetListItem(ByVal ListGUID As String, _

ByVal ListItemID As String, _

ByVal ListSiteURL As String, _

ByVal ReturnFieldList As ArrayList) As XmlNode

'Declare and initialize a variable for the Lists Web service.

Dim listService As New SharepointServer.Lists()

Dim sFieldList As New StringBuilder

'Authenticate the current user by passing their default

'credentials to the Web service from the system credential cache.

listService.Credentials = System.Net.CredentialCache.DefaultCredentials

'Set the Url property of the service for the path to a subsite.

listService.Url = ListSiteURL

'Instantiate an XmlDocument object.

Dim xmlDoc As New System.Xml.XmlDocument()

'Assign values to the string parameters of the GetListItems method, 'using GUIDs for the listName and viewName variables. For listName, 'using the list display name will also work, but using the list GUID

'is recommended. For viewName, only the view GUID can be used.

'Using an empty string for viewName causes the default view

'to be used.

Dim listName As String = ListGUID ' "{2D0B4CF8-0A1F-4FAC-83BA-C9278EB9C711}"

Dim viewName As String = String.Empty

Dim rowLimit As String = "150"

'Use the CreateElement method of the document object to create 'elements for the parameters that use XML.

Dim query As System.Xml.XmlElement = xmlDoc.CreateElement("Query")

Dim viewFields As System.Xml.XmlElement = xmlDoc.CreateElement("ViewFields")

Dim queryOptions As System.Xml.XmlElement = xmlDoc.CreateElement("QueryOptions")

'To specify values for the parameter elements (optional), assign CAML fragments to the InnerXml property of each element.

query.InnerXml = "<Where><Eq><FieldRef Name=""Title"" />" _

& "<Value Type=""Text"">" + ListItemID + "</Value></Eq></Where>"

For x As Integer = 0 To ReturnFieldList.Count - 1

sFieldList.Append("<FieldRef Name=""" & ReturnFieldList.Item(x) & """ />")

viewFields.InnerXml = sFieldList.ToString

queryOptions.InnerXml = ""

Try

'Declare an XmlNode object and initialize it with the XML response 'from the GetListItems method. The last parameter specifies

'the GUID of the Web site containing the list. Setting it to

'Nothing causes the Web site specified by the Url property

'to be used.

Dim nodeListItems As System.Xml.XmlNode = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, Nothing)

Return nodeListItems

Catch ex As System.Web.Services.Protocols.SoapException

Return Nothing

End Try

Return Nothing

End Function

Once this is on the web server you can add the web service as a secondary data source.

Hope this helps and let me know if you need anything else and I try to help.

Paul

Re: Query Sharepoint List - From Infopath 2007

boverton — Tue, 12 Feb 2008 18:40:28 GMT

I'm doing something similar and would love to see your code for this solution.

Thanks,

Brad

Re: Query Sharepoint List - From Infopath 2007

PNeate — Thu, 07 Feb 2008 13:46:57 GMT

Thanks for the quick response. After doing some more digging around I found that I could wrapper the Sharepoint GetListItem web service with one tailored to what I was trying to pull though then simply referencing this as a secondary data source. Was a little fiddle but it works fine now.

Cheers

Re: Query Sharepoint List - From Infopath 2007

Clay Fox — Wed, 30 Jan 2008 23:07:06 GMT

The issue comes down to dynamic queries.

In InfoPath you can not have a dynamic query on secondary data connections except with web services. SharePoint uses web services but it does not provide the ability to pass a parameter. You have to load the whole list and then use filters to only display the correct record.

With a database table and a web service you can accomplish what you need but I do not think it is possible with a SharePoint list.

Another possibility is keeping the help as an XML file and loading it with the form but if it is extensive than you are right performance would be better with a dynamic query. Also after about 2K items your performance will suffer in SharePoint.

Query Sharepoint List - From Infopath 2007

PNeate — Wed, 30 Jan 2008 17:30:49 GMT

I am very new to InfoPath so if this is a simple thing to do just point me in the right direction, and I'll be on my way. :o)

I am trying to query a Sharepoint list so that instead of receiving loads of data I only receive the one entry that matches an given ID.

To explain further, I'm plan to have a help text list within Sharepoint which particular users will have access to so they can write their own help notes within a multi line text box. Within my InfoPath form I have rich text boxes placed at strategic locations, each one has an ID value which corresponds to an entry within the help text Sharepoint list.

As a user moves though the InfoPath form I would like to dynamically query the help text list with the help ID and pull pack the corresponding help text.

I am using InfoPath 2007 with MOSS 2007.

Thank you for your help.