February 2009 - Posts - Mel Balsamo
in

InfoPath Dev

This Blog

Syndication

Mel Balsamo

February 2009 - Posts

  • Comparing Items in a List against an Updated Data Source

    Data Validation using Code
     

    Data validation using rules doesn’t work for all scenarios. For example, suppose you have a form with a repeating table that is populated from a secondary data source. You save the form and open it a month later and the list is out of date because some of those values in the secondary data source were updated. In this situation, data validation must happen to all nodes in the list, but InfoPath rules don’t easily allow iterating over lists (you can use nested evals but it’s tricky). Even if you could do it, the performance would be horrible, since each item would have to validate itself against the entire secondary data source. What you want is a way to load the new list and compare items to that by first sorting the two lists and then walking them side-by-side. For this kind of data validation, you’re better off just writing some code. This blog shows you how to do data validation with code. 
     

    Our sample form has a repeating table with a “Value” field which looks up values from an external data source. 
     

    Figure1

     

    The typical form will have a data connection to a SharePoint list or a SQL database. To keep things simple for our sample, we’ll use an XML file as our external data source (the same technique can be used with secondary data sources stored as a SharePoint list or as a table in a SQL database). Our secondary data source is a resource file (XML) named “Numbers”, and contains the following data:
     

    <?xml version="1.0" encoding="utf-8"?>

    <Numbers>

         <Number value="1" name="One"/>

         <Number value="2" name="Two"/>

         <Number value="3" name="Three"/>

         <Number value="4" name="Four"/>

         <Number value="5" name="Five"/>

         <Number value="6" name="Six"/>

         <Number value="7" name="Seven"/>

         <Number value="8" name="Eight"/>

         <Number value="9" name="Nine"/>

         <Number value="10" name="Ten"/>

    </Numbers>


     

    Figure2

     

    The Value dropdown in our form looks up the values from the XML external data source: value is the “@value” attribute and display name is the “@name” attribute. Double click on the dropdown and the properties look like this:

     

    Figure3


     

    Previewing the dropdown:


    Figure4

     

    Now, our validation scenario is as follows:

    a)      We open the form, select one or more values and save it as an XML file.

    b)      Later, the external data source changes and one or more values may be removed.

    c)       When opening the old form after this change, wouldn’t it be nice to see some validation telling us that those old values are no longer current? 
     

    Note: You may think that this can be done with InfoPath’s “Open Rules”, but Open Rules run before querying of external data sources, so data validation fails because there are no values loaded from the external data source and all values will show invalid as a result. 
     

    Our solution uses code to perform the validations. The code queries the external data source when the form loads and then walks through the main data sources using an XPath index to see if the main data source value is still in the external data source. If not, an error is added to the InfoPath error board. Later, if a change occurs to the list, we remove the error (since we assume a change must replace the value with a valid value). 
     

    Note: the XPath is not the fastest way to do this. If both lists are sorted, a better solution would be to walk them side-by-side, but that’s more complicated.

     

    using Microsoft.Office.InfoPath;

    using System;

    using System.Windows.Forms;

    using System.Xml;

    using System.Xml.XPath;

    using mshtml;

     

    namespace ValidationInCode

    {

          public partial class FormCode

          {

                private const string VALIDATION_ERROR_NAME = "Invalid";

                private const string VALIDATION_ERROR_MESSAGE = "This value is no longer a valid choice. Please choose a new value.";

     

                public void InternalStartup()

                {

                      EventManager.FormEvents.Loading += new LoadingEventHandler(FormEvents_Loading);

                      EventManager.XmlEvents["/my:myFields/my:Table/my:Value"].Changed += new XmlChangedEventHandler(Value_Changed);

                }

     

                public void FormEvents_Loading(object sender, LoadingEventArgs e)

                {

                      QueryDataSources();

                      PerformValidation();

                }

     

                private void QueryDataSources()

                {

                      // Manually query secondary data source.

                      DataConnection dc = DataConnections["Numbers"];

                      dc.Execute();

                }

     

                public void Value_Changed(object sender, XmlEventArgs e)

                {

                      foreach (FormError error in Errors.GetErrors(VALIDATION_ERROR_NAME))

                      {

                            if (error.Site.IsSamePosition(e.Site))

                            {

                                  Errors.Delete(error);

                                  return;

                            }

                      }

                }

     

                private void PerformValidation()

                {

                      // Get the secondary data source needed for validation.

                      XPathNavigator ds = DataSources["Numbers"].CreateNavigator();

     

                      // Verify all nodes have valid values.

                XPathNodeIterator nodes = MainDataSource.CreateNavigator().Select("/my:myFields/my:Table[my:Value != '']", NamespaceManager);

                      foreach (XPathNavigator node in nodes)

                      {

                             //Get the value node

                     XPathNavigator valueNode = node.SelectSingleNode("my:Value", NamespaceManager);

     

                    // Validate existing selections against a potentially changed data source.

                            XPathNavigator exists = ds.SelectSingleNode("/Numbers/Number/@value[. = '" + valueNode.Value + "']");

                            if (exists == null)

                            {

                                  Errors.Add(valueNode, VALIDATION_ERROR_NAME, VALIDATION_ERROR_MESSAGE);

                            }

                      }

                }

     

          }

    }

     


    At Qdabra, we submit our forms to SQL using our Database Accelerator Web Service Suite (DBXL) which acts as a general suite of Web Services for any form (i.e. Web Service works for any form regardless of the data source). You can also set up Submit to SharePoint and open your form from SharePoint. 
     


    Let’s try out the form.  To simulate what would happen when the external data source is updated, we can fill out the form and type in a bogus value (that we know is not in the valid list of values). Let’s enter a value of “Zero”, insert more items, some exist in the list of values, some don’t, and then submit the form. 

     

    Figure5

      

    When the form opens, we see that the fields, whose values are not in the valid list, are marked invalid. Yay!

     

    Figure6

     

    And after selecting new values, the validation errors are removed. Yippee!

     

    Figure7

     

    Of course, validation is only happening on form load so if the external data source changes while you are editing the form, you could still end up submitting bogus data. So, you’ll want to add a rule to your submit command that calls validation again and interrupts submit if it finds validation issues. We’ll leave it as an exercise to the reader to do this but please send me e-mail if you can’t figure it out after 30 minutes of trying. Hint: Google xdExtension

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