Allow Offline Use of a SharePoint List by Caching Locally - Matt Faus

InfoPath Dev

Matt Faus

Allow Offline Use of a SharePoint List by Caching Locally

Populating drop-down lists and other components of an InfoPath form from data stored in a SharePoint list is a great technique that allows centralized storage of data and ease of maintenance. The only downside of storing data in a SharePoint list is that the user of an InfoPath form must have network access to the SharePoint site in order for the form to work properly. However, there is a rather simple way to create an InfoPath form that uses a local version of the list by default and provides the ability to update the list when a connection to the SharePoint site is available. Using this technique allows the SharePoint list to be updated as often as necessary but new versions of the InfoPath form (with the latest local version of the list) need only be deployed as desired.

In this task we will create an InfoPath form that has data connections to a custom SharePoint list and an XML Document that we will include in the form template that represents the local version of the list. Once these connections are made we will use code to make a button which updates the local version of the list with the data from SharePoint. Let’s start by creating the SharePoint list.

Create the SharePoint list:

  1. Browse to the SharePoint site that you will be using for your form, and then click the Create link at the top of the page.
  2. Scroll down to the Custom Lists section, and then click the link to create a new Custom List.
  3. Name the list Products, change the name of the Title column to Name, insert a new column, Description, and insert five rows of data. Refer to Figure 1.

Figure 1. Creating the SharePoint List.

Create the XML local list:

  1. Open a text editor, copy the following text, and then paste it into the text editor.
  2. Save the file as Local Products.xml.

<?xml version="1.0" encoding="UTF-8"?>
        <Product ID="1" Name="Race Car" Description="A remote controlled race car. Speeds up to 100mph"/>
        <Product ID="2" Name="Airplane" Description="Flies very high!"/>
        <Product ID="3" Name="Boat" Description="Guaranteed to never sink."/>
        <Product ID="4" Name="Truck" Description="Same style and colors as the US Army."/>
        <Product ID="5" Name="Hovercraft" Description="Over land or sea, this beast of a machine floats on a bubble of air!"/>

Create the form and add the SharePoint List data connections:

  1. Open InfoPath and choose to Design A New Blank Form.
  2. From the Tools menu, select Data Connections.
  3. Click Add, select Receive Data, and then click Next.
  4. Select SharePoint Library Or List, and then click Next.
  5. Type the URL to your SharePoint site into the field, and then click Next.
  6. Select Products from the list of available Lists, and then click Next.
  7. Select the ID, Name, and Description fields, unselect everything else, and then click Next.
  8. Change the name of the data connection to Products Online, uncheck the box labeled Automatically Retrieve Data When Form Is Opened, and then click Finish.

Add the offline version of the list as a data connection:

  1. In the Data Connections dialog box, click Add.
  2. Select Receive Data, and then click Next twice.
  3. Click Browse, browse to the Local Products.xml file, click Open, and then click Next.
  4. Name the data connection Products Offline, click Finish, click Yes on the box that pops up, and then click OK to close the Data Connections dialog box.

Design the Form:

  1. Type Products Viewer into the view, and then type Enter a few times to insert some whitespace.
  2. Open the Layout task pane and insert a custom table with two columns and two rows.
  3. Resize the table to match figure 2 and type Products and Description into the columns of the first row.
  4. Open the Controls task pane and insert a List Box control inside the first column of the second row.
  5. Double-click the list box, change the Field Name to Product, and then select Look up values in a data connection.
  6. Select Products Offline from the Data Connection drop-down list, select /CachedList/Products/Product for the Entries, select @ID for the Value, select @Name for the Display Name, and then click OK.
  7. Open the Controls task pane and insert an expression box into the second column of the second row.
  8. Click the Formula button in the box that pops up, click Insert Field or Group, select Products Offline (Secondary) from the Data Source drop-down list, select Products/Product/Description, and then click Filter Data.
  9. Click Add, select ID from the first drop-down list, select Is Equal To from the second drop-down list, and then select Select a Field or Group from the third drop-down list.
  10. Select Main from the Data Source drop-down list, select myFields/Product, and then click OK until all dialog boxes are closed. Refer to Figure 2.

Figure 2. The form design.


Figure 3. Populating the Products list from the local version of the data source.

Add the Refresh Products button and code:

  1. Click to the right of the Products Viewer text to set the insertion point. Type Space a few times to insert some whitespace.
  2. Open the Controls task pane, and then insert a button.
  3. Double-click the button that was just inserted, change the label to Refresh Products, change the ID to butRefreshProducts, click Apply, and then click Edit Form Code to open the Microsoft Script Editor.
  4. Replace the contents of the OnClick event handler with the following code:

    // Get the updated version of the List
    // If the user is offline, the code will skip to the "catch" block below
    XDocument.DataObjects["Products Online"].Query();

    // Get the ShareList DOM
    var oSharePointDom = XDocument.GetDOM( "Products Online" );
    // Setup the Namespaces
        'xmlns:dfs="" ' );

    // Get the local DOM - to does not use namespaces
    var oLocalDom = XDocument.GetDOM( "Products Offline" );

    // Get a sample cached list node that we will need to clone later
    var nSampleProductNode = oLocalDom.selectSingleNode( "/CachedList/Products/Product" );

    // Remove all of the items from the local copy of the list
    oLocalDom.selectNodes( "/CachedList/Products/Product" ).removeAll();

    // Loop through all of the items in the SharePoint List
    var nlSharedItems = oSharePointDom.selectNodes( "/dfs:myFields/dfs:dataFields/dfs:Products" );

    while( (nSharedItem = nlSharedItems.nextNode() ) != null )
        // Create a new row from the sample
        var nNewLocalCacheRow = nSampleProductNode.cloneNode(true);

        // Set the values
        nNewLocalCacheRow.selectSingleNode( "@ID" ).text = nSharedItem.selectSingleNode( "@ID" ).text;
        nNewLocalCacheRow.selectSingleNode( "@Name" ).text = nSharedItem.selectSingleNode( "@Name" ).text;
        nNewLocalCacheRow.selectSingleNode( "@Description" ).text = nSharedItem.selectSingleNode( "@Description" ).text;

        // Append to to our local cached list
        oLocalDom.selectSingleNode( "/CachedList/Products" ).appendChild( nNewLocalCacheRow );
catch( e )
    // Uncomment this line for more debug information
    //XDocument.UI.Alert( e.description );

    // Tell the user that they must be connected to the Internet to use this feature.
    XDocument.UI.Alert( "A connection to the SharePoint List must be available to use this feature." );

Try it:

  1. Open a Web Browser and browse to the SharePoint list that our form references.
  2. In the InfoPath designer click Preview Form, and select one of the Products from the list box. Notice that the description is what was stored in the XML document.
  3. Switch to your web browser and edit the Products list. Try inserting a new row as well as editing the name and description values for existing rows.
  4. Switch back to the previewed form, and click Refresh Products. Notice that the list now contains any extra rows that you added in SharePoint and the descriptions and names are also updated.
  5. Take your computer offline. This can be done by either physically disconnecting the network cable in the back of your computer, or by disabling the connection from the Network Connections folder in the Control Panel.
  6. After you have gone offline, notice that clicking the Refresh Products button emits an error message telling the user that they must be online to use the feature.
  7. While still offline, close the preview and open another preview.
  8. Click the Refresh Products button and notice that the error message is emitted and the local version of the list is used.

Figure 4. Previewing the form after refreshing the local version from the SharePoint list.

Customizing for your solution:

One potential downside of putting the Refresh Products code behind a button is that the user will not always click the button and could therefore be filling out the form with an old version of the list. A way to remedy this situation is to put the code provided above within the OnLoad event handler. This way every time the user opens the form the newest version of the list will be obtained if a connection is available.



Lalato said:

This is great.  Thanks!


April 11, 2008 12:04 PM

Lalato said:

Matt, when I try this it works great.  However, the new data does not seem to stay.  When I open the form again, the old data is there and I have to hit refresh again.

Is that the way it's supposed to be?

April 17, 2008 9:08 AM

Matt Faus said:

Hi Lalato,

Sorry for the late reply.  One catch with this tutorial is that data stored in a secondary data connection is not saved across sessions.  So, each time you open InfoPath, the data in the 2DS is reverted to how it was when the template was originally created.

There is no way to edit the template to include the new data at runtime, so the only way you could refresh the data in the 2DS is to design the template, update the 2DS, and then publish the new version.

This tutorial is best for lists that do not change much, or when offline is not a terribly common scenario.  However, if you are using InfoPath 2007, there are some built-in mechanisms that allow offline functionality without all the heavy work described above!

April 26, 2008 9:58 AM

About Matt Faus

Matt holds a BS in Computer Science and Software Engineering from the University of Texas at Dallas. During his studies, he helped Qdabra Software develop DBXL and InfoPath solutions. During that time he lived in Dallas, Seattle, and Valencia, Spain. He now works at Microsoft, developing for Hotmail.
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.