Query for Items in SharePoint Form Library Using GetListItems Web Method - Agni Reddy
in

InfoPath Dev

Agni Reddy

Query for Items in SharePoint Form Library Using GetListItems Web Method

Generally to get data from SharePoint form library a receive data connection to a SharePoint list is taken. But there a couple of drawbacks of this data connection:

  1. It only gives the data of the items in the default view. So if the item limit is 100 then the only the first 100 items data is received.
  2. There is no way to query so as to get the information on a certain number of items in the SharePoint form library.

To solve these two issues we can take a data connection to the GetListItems method in the Lists SharePoint web service. One thing to note is that any data that you want to query on needs to be present in the view.

A direct data connection to the GetListItems method cannot be taken and this HowTo shows how the data connection can be taken and how to use it to query items. We will create a solution with one field and then query for items with a certain value in that field.

Create InfoPath solution:

  1. Open Visual Studio and create a new InfoPath project.
  2. Create a new field named SearchValue and drag drop it into the UI.
  3. Create another field named QueryValue and drag drop it into the UI.
  4. Now add a button named Search in the view.
  5. Go to "Tools->Form Options" and Click on Form Library Columns Tab.
  6. Click on Add, select field SearchValue.

  1. Click "OK" two times to save changes.

Publish InfoPath form to Share Point:

Our next step is to publish the form to Share Point.

  1. Go "File->Publish", publish dialogue box is seen.
  2. Click "Next" button.
  3. Select "To a Share Point Library" and click on Next button.
  4. Select "Create a new Form Library" and click on Next button.
  5. Enter Share Point site URL where you want to publish the form and click on Next button.
  6. Enter the form library name and Click on Next button.
  7. Verify if the "Search" field is listed in the promoted properties and Click on Publish button to publish the form.
  8. Now the form is published, click on close button to close the publish dialog.

Create GetListItemsConfig.xml for use in dataconnection:

Next step is to create an XML file to use in querying the database.

  1. Copy the following xml into a text editor and save it as GetListItemsConfig.xml:

<?xml version="1.0" encoding="utf-8" ?>
<GetListItemsConfig>
    <LibraryName>GetListItems</LibraryName>
    <ViewGuid>FD71288E-8BFC-4D66-8886-9DB767D528F7</ViewGuid>
    <QueryXmlString>
        <Query>
            <Where>
                <Eq>
                    <FieldRef Name='xd__x007b_14A5521A_x002d_CE31_x002d_4DBC_x002d_924F_x002d_937E7A4288B8_x007d_' />
                    <Value Type='Text'></Value>
                </Eq>
            </Where>
        </Query>
    </QueryXmlString>
    <RowLimit>1000000</RowLimit> 
</GetListItemsConfig>

All of the items in red need to be replaced as per instructions below. Please note that <LibraryName> should be same as the form library name given in step 6 above.

To obtain View GUID follow these steps:

  1. Open the form library to which you have published the InfoPath form.
  2. Click on "Modified" column in the share point promoted lists.
  3. Copy the URL in the address bar into a note pad.

 

For eg: http://system2/sites/GetListItems/Forms/AllItems.aspx?SortField=Last%5fx0020%5fModified&SortDir=Asc&View=%7bFD71288E%2d8BFC%2d4D66%2d8886%2d9DB767D528F7%7d

  1. Search for &View, take the number/GUID after "=" in the URL and remove %7b at starting and %7d at ending, replace %2d with hyphen "-".

To obtain <FieldRef> "Name" value follow these steps:

  1. Open the manifest.xsf of the solution in a text editor (be careful that you do not make any changes to the manifest.xsf file).
  2. Look for the list properties node:

<xsf:listProperties>

    <xsf:fields>

        <xsf:field name="SearchValue"  columnName="{14A5521A-CE31-4DBC-924F-937E7A4288B8}" node="/my:myFields/my:SearchValue" type="xsd:string">

        </xsf:field>
    </xsf:fields>
</xsf:listProperties>

  1. Copy the GUID of the promoted field in the brackets "{}".


Add the GetListItemsConfig.xml a secondary data connection:

  1. Choose Data Connections from the Tools menu, and then click Add.
  2. In the Data Connection Wizard, select Receive Data, and then click Next.
  3. Select XML Document, and then click Next.
  4. Click Browse, locate and select the GetListItemsConfig.xml file, click Open, and then click Next.
  5. Click Finish, click Yes, and then click Close.


Take connection to GetListItems webmethod:

Now follow the steps below to add the GetListItems web method as a data connection:

  1. Go to Tools->DataConnections  and Click on Add button.
  2. Select Receive Data and click on Next button.
  3. Select WebService Radio Button and Click on Next button.
  4. Enter the Webservice address:
    for eg: http://<sharepoint server name>/<subsite name>/_vti_bin/lists.asmx?WSDL"
  5. Select Getlist and Click on Next.
  6. Set Sample value as form library name that you just created and click on next.
  7. Uncheck the Check Box Automatically retrieve data when a form is opened.
  8. Enter the name as GetListItems and click on Finish.
  9. Close the InfoPath form and open the GetlistItems.xml file.
  10. Replace the existing XML data with the following XML data and save the xml:

<dfs:myFields
        xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
        xmlns:ns5="http://schemas.microsoft.com/sharepoint/soap/"
        xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-05-12T09:05:35"
        xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <dfs:queryFields>
        <ns5:GetListItems>
            <ns5:listName></ns5:listName>
            <ns5:viewName></ns5:viewName>
            <ns5:query></ns5:query>
            <ns5:rowLimit></ns5:rowLimit>
        </ns5:GetListItems>
    </dfs:queryFields>
    <dfs:dataFields>
        <ns5:GetListItemsResponse>

            <ns5:GetListItemsResult></ns5:GetListItemsResult>
        </ns5:GetListItemsResponse>
    </dfs:dataFields>
</dfs:myFields>

  1. Open the manifest.xsf in text editor and search for <xsf:dataObject name="GetListItems.
  2. You will see the following data:

<xsf:operation name="GetList"
<xsf:operation name="GetList" soapAction="
http://schemas.microsoft.com/sharepoint/soap/GetList" serviceUrl="http://system2/sites/_vti_bin/lists.asmx">   

  1. Change "GetList" shown as in red to "GetListItems" and save the manifest.xsf. Be careful that you do not make any other changes.
  2. With this you now have a data connection to the GetListItems web method.

Add code to query from SharePoint form library created above:

  1. Double Click on the Search button the view.
  2. Click on Edit Form Code.
  3. In the new function that gets created add the following code:

string queryValue = thisXDocument.DOM.selectSingleNode("/my:myFields/my:QueryValue").text;
IXMLDOMNode getListItemResult = getListItems(queryValue);

  1. Copy the following code into FormCode.cs to get the Getlistitems:

private IXMLDOMNode getListItems(string queryValue)
{
    // get the DOM and set Property to be able to access the GetListItems DOM correctly
    IXMLDOMDocument2 domGetListItems = (IXMLDOMDocument2)thisXDocument.GetDOM("GetListItems");
    domGetListItems.setProperty("SelectionNamespaces", "xmlns:dfs='http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' xmlns:ns5='http://schemas.microsoft.com/sharepoint/soap/' xmlns:my='http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-03-29T21:51:38' xmlns:ns7='http://schemas.xmlsoap.org/soap/envelope/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'");

    // Set the Library name
    thisXDocument.GetDOM("GetListItems").selectSingleNode("/dfs:myFields/dfs:queryFields/ns5:GetListItems/ns5:listName").text = thisXDocument.GetDOM("GetListItemsConfig").selectSingleNode("/GetListItemsConfig/LibraryName").text;

    //Set the view Guid
    thisXDocument.GetDOM("GetListItems").selectSingleNode("/dfs:myFields/dfs:queryFields/ns5:GetListItems/ns5:viewName").text = thisXDocument.GetDOM("GetListItemsConfig").selectSingleNode("/GetListItemsConfig/ViewGuid").text;

    // Set the Row limit
    thisXDocument.GetDOM("GetListItems").selectSingleNode("/dfs:myFields/dfs:queryFields/ns5:GetListItems/ns5:rowLimit").text = thisXDocument.GetDOM("GetListItemsConfig").selectSingleNode("/GetListItemsConfig/RowLimit").text;

    // Get the Query Node from GetListItemsConfig
    IXMLDOMNode fileQueryNode = thisXDocument.GetDOM("GetListItemsConfig").selectSingleNode("/GetListItemsConfig/QueryXmlString/Query ");

    // Set the Value from the text box in the value node
    fileQueryNode.selectSingleNode ("Where/Eq/Value").text = queryValue;

    //Get the  query node from the GetListItems query parameter nodes
    IXMLDOMNode oQuery = thisXDocument.GetDOM("GetListItems").selectSingleNode("/dfs:myFields/dfs:queryFields/ns5:GetListItems/ns5:query");

    // Append the query node
    oQuery.appendChild(fileQueryNode);

    // Query sharepoint using GetListItems dataconnection
    try
    {
        WebServiceAdapter2 getListItemsWebserviceAdapter = (WebServiceAdapter2) thisXDocument.DataAdapters["GetListItems"];
        getListItemsWebserviceAdapter.Query();
    }
    catch(Exception ex)
    {
        thisXDocument.UI.Alert(ex.Message);
        return null;
    }

    // Return the data given  Sharepoint querying
    IXMLDOMNode getListItemsResult = thisXDocument.GetDOM("GetListItems").selectSingleNode("/dfs:myFields/dfs:dataFields/ns5:GetListItemsResponse/ns5:GetListItemsResult");
    return getListItemsResult;
}

  1. Compile the code.
  2. Republish the InfoPath form on to the existing form library created above

To verify that it is working correctly:

  1. Create multiple forms and save to the SharePoint form library.
  2. Click on "Fill out a form".
  3. Open the visual studio project.
  4. Go to Tools-> DebugProcesses.
  5. Select "infopath.exe", click on "Attach".
  6. Select "Common Language Runtime" and Click on "Ok" and "Close" buttons.
  7. Now go to "return getListItemsResult;" line of code, right click and select Insert breakpoint.
  8. Enter text you want to query on in the Query Value textbox in InfoPath.
  9. Now click on search button.
  10. When walking through the code you will see that the getListItemsResult node will have the correct data from the SharePoint site.
Published May 15 2006, 12:40 PM by Agni Reddy
Filed under:

Comments

No Comments

About Agni Reddy

Agni is a Microsoft MVP. He began his InfoPath career in 2004 testing solutions developed at Qdabra™ Software and quickly transitioned to an InfoPath developer. He now has expertise developing complex InfoPath solutions. Agni holds a Master's degree in Information Systems.
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.