July 2012 - Posts - Hilary Stoupa

InfoPath Dev

Hilary Stoupa

July 2012 - Posts

  • qRules RefreshSharePointListItems - Walkthrough for v4.2

    In version 4.2 of qRules, we made a number of changes to simplify the SubmitToSharePointList command. Those changes also impact the RefreshSharePointListItems command - in a good way, I'm glad to say. Like SubmitToSharePointList, you can now run the refresh command for multiple lists with a single rule action, and fewer parameters are required.

    In case you've not seen them, here are my blog posts on these changes thus far:

    In this post, I'll be starting with the form we left off with on the Working With Existing Data post - here's a copy you can use. Keep in mind that my form & mapping is pointed to sites in my environment, so you'll need to modify that, and it is using the current 4.2 trial version of qRules - which expires 9/15/2012. So, you'll want to re-inject with a new trial if you are following this at a later date.

    The Basics

    First off, in case you aren't familiar with the refresh command - let's talk about what it does and how it works. This command is always used with forms that are set up to use the SubmitToSharePointList command where a form field is set to maintain the SharePoint list item ID. The command should generally be run with a rule on QdabraRules data source finshedLoading attribute - a condition should be used so that the rule only runs when that attribute is equal to true.

     When you submit your list items, SharePoint returns the newly created list item ID to the field you have specified via your mapping:

    If you look at your mapping.xml file in a text editor, you'll see the IsId attribute is set to "true":

    On a successful submit, qRules is going to update the indicated field with the value of the associated list item ID - all items in a SharePoint list have a unique integer value in the ID column. By saving that value in the XML for your form, the next time the SubmitToSharePointList command runs, qRules can perform an update on the existing item rather than creating a duplicate.

    The RefreshSharePointListItems also leverages this field - it adds to your form the ability to update all your SharePoint list connected items with the latest data from the list, so your users can update either the XML or the list - they'll stay in sync when your XML is next opened. It also adds the ability to propagate deletes - both from the XML to the list and vice versa.

    This command has two modes - Report and Update. Because the command has the potential to delete or change data, the default mode is Report. That means you have to explicitly state that you want the list data to overwrite your form data.

    A new attribute has to be added to your fields where you are keeping your list item IDs - qRulesLastModified. This attribute stores the date and time the form data was last saved to the list. When the refresh command runs, qRules compares the XML date & time values with the Last Modified column for the list item. If the list item was updated more recently, qRules overwrites the form values, using the mapping.xml to know which column writes to which field.

    If there is an item in the XML that has a list item ID, but can't be found in the list (and the command is run in Update mode), qRules deletes the data from the XML.

    When the RefreshSharePointListItems command first runs, it also creates a list of the items with SharePoint list item IDs currently in the form. When SubmitToSharePointList is next run, if any of those items have been deleted from the XML, they will also be deleted from the SharePoint list.

    Back To Work

    Hopefully that explanation didn't bore you to death or baffle you. But we'll walk through it, and the background above might make what we are going to do seem a little less obscure, okay?

    Since we are picking up where we left off last time, our form already has fields for the list item IDs, and we have our submit commands set up. So the first thing we'll do is add the qRulesLastModified attribute to those.

    When I created my schema, I referenced the ShPId node so that I could have same name in a number of locations:

    Referencing a node is simple - add the node in one location you want it. Then right click the node and select Reference:

    In the next dialog, select the group you'd like to add the field to - simple as that.

    Now the cool part - I just have to add the qRulesLastModified attribute to one instance of my ShPId field, and they all get it:

    Form Changes

    We first need to add an additional data source connection. qRules uses an owssvr.dll data connection to return each item for refreshing in preview or in Filler. If deployed as a browser form, it uses the SharePoint object model - but since we are going to want to be able to preview and test our work, we are going to add an owssvr.dll data connection.

    The data connection is to an XML file, and the URL looks like this:

    Your URL needs to be valid so that you can create the data connection, but qRules will use the server URL from your mapping and the list GUID from your mapping (since, after all, that will be the server and list you submitted to).

    So, in my case, the URL I'll use looks like this:

    The GUID is to my Customers list - again, during the refresh process, qRules will ignore the GUID in the URL and use the one from the mapping file instead, so all that matters is that the URL is valid. You can test your URL in the browser - depending on your browser, it will either return XML in the browser or give you an option to save the returned XML.

    We add a new XML data connection to the form:

    Use your owssvr.dll URL for the location:

    Be sure to select the "Access the data from the specified location" radio button:

    And de-select the checkbox that queries the data on load:

    You'll notice I've named my data connection "Refresh" - name yours whatever you like, but as usual, we need the exact name, including the correct casing, for our command parameters.

    Adding the Command

    Now that we've added our qRulesLastModified attribute and our data connection, we can add the command. We are going to put the rule for the command on the QdabraRules finishedLoading attribute, with the condition that the attribute is equal to true (see *Note* at end for some additional information on this):

    We set the Command node of the QdabraRules data source to the RefreshListItems command:
    RefreshSharePointListItems /dsname=Refresh

    Did you name your data source something else? If so, you'd need to use whatever your data connection's name is for the /dsname parameter. If your mapping data connection is named anything other than "mapping", you'll need to include the mapping parameter as well:
    RefreshSharePointListItems /dsname=Refresh /mapping=MyVeryFancyMappingFile

    Here's a copy of my work in progress, in case you need it.

    As I mentioned earlier, the default mode for this command is Report - this means any changed items will be reported to the QdabraRules Results node. Put that node on your form so you can see the outcome.

    Testing in Report Mode

    Guess what? We are already ready to test! Once you have the form set up to submit list items, adding refresh functionality isn't much work. To test, we'll preview and submit some items to our lists. We'll save our XML that we created in preview, change some of our list items, and then preview again, using our existing XML so we can see what the report result is.

    So, preview your form, create or select a customer, and add some orders and order details:

    Submit your list items, using the submit button, and then use File --> Save As to save the completed XML. Close your form.

    You can open the XML in a text editor, and you'll see that your list items have a SharePoint list item ID along with a value for the qRulesLastModified attribute:

    We need to change some of our new list items from SharePoint - here's my original details:

    And their modifications:

    Set your form template up to preview with existing data, using the XML you saved in your earlier preview (how-to can be found here):

    Remember, we have not included the /mode parameter at this point, so by default our refresh command will only report on changes. Preview your form again, and take a look at the Results field - you'll find text like this:

    Testing in Update Mode 

    Keeping your template set up to preview with your existing data, add the /mode parameter to your command:

    Preview again - this time, you'll have the same report, but your items that you changed in SharePoint should be refreshed to match:

    Euw. Big ugly validation errors - what happened there? Well, my form has a data type of whole number for the Quantity field, but SharePoint is returning the data with decimals - that's okay, I can take care of that with a rule on the Quantity field:

    Preview again, and:


    Leaving the preview open, where the refresh command has already run, try deleting existing items and adding some new ones. Remember, the deletes will occur when the SubmitToSharePointList command is executed, so be sure to click your submit button to run the command. Be sure you remove the path to the XML you've been previewing with before you close your template - otherwise, you will do what I always do - delete the XML and be completely confused when your form refuses to preview.


    That's it! Here's my final form if you want it. The best part of the qRules 4.2 changes, I think, is that if I need to submit data from my form to another list (I'm sure people in your organization never change requirements on you... ) I can export the mapping from my form, import it into the mapping tool, and add the list mapping. I just need to replace the mapping in my form with the new one, and with no other changes, my current submit and refresh commands will now include the new list. That makes changes easier and faster for the form developer, so hooray for that.



    *NOTE*: As you may already know, in InfoPath, rules run before code. qRules is code, of course, but code that is accessed via rules. We want to make sure the qRules code has fully loaded prior to executing any commands. So, if you have a command you would like to run on a form load rule, you should instead run it on the finishedLoading attribute. The qRules code sets that attribute to true when it is fully loaded.


Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.