Point Deep

Mundeep's Tech Blog

Archive for the ‘Office’ Category

InfoPath 2007 Custom Field Validation (Programmatic)

Posted by mundeep on May 20, 2009

Adding custom (programmatic) field validation to a form in InfoPath 2007 is quite easy. Firstly make sure you have set your programming language of choice by going to the Tools menu then “Form Options” -> “Programming”, and then selecting your preferred language under “Form template code language”. For this example i am going to be using C#.

Back in your form design window right-click on the field you wish to validate, and select “Programming” -> “Validating Event”. This will open a “Visual Studio Tools for Applications” (VSTA) window, and generate the following code snippet:

        public void myFieldName_Validating(object sender, XmlValidatingEventArgs e)
        {
            // Write your code here.
        }

Obviously this code is not doing any validation yet, we just need to insert our validation code in place of the “your code here” comment. Let us try a simple validation of ensure the data entered in the field is at least 3 characters long with the following code.

public void myFieldName_Validating(object sender, XmlValidatingEventArgs e) {
//Check the Length of the NewValue of the field
if (e.NewValue.Length < 3) { //Report the validation error back to the client on the form e.ReportError(e.Site, false, "Please Enter a valid MyField"); } } [/sourcecode] As mentioned in the comments e.NewValue retrieves the 'New' value that is being entered/inserted into the field before it has been set and e.ReportError sends any validation errors back to the form for the client (read the MSDN article for details on the parameters of ReportError).

Unfortunately there is a minor issue with this basic piece of code and that is due to the fact that the validating event also gets fired during the loading of the form and as shown in this kb article we need to check the XmlOperation that is being performed when the validation is fired. This means updating our code to the following.

public void myFieldName_Validating(object sender, XmlValidatingEventArgs e) {
//Ensure validation is only done when inserting or changing a value
if (e.Operation == XmlOperation.Insert || e.Operation == XmlOperation.ValueChange) {
//Check the Length of the NewValue of the field
if (e.NewValue.Length < 3) { //Report the validation error back to the client on the form e.ReportError(e.Site, false, "Please Enter a valid MyField"); } } } [/sourcecode] Save your code changes and while still in VSTA build the project to ensure you don't have any coding errors (Ctrl+Shift+B, or from the menu "Build" -> "Build 'MyFormName'"). If you get a "Build succeeded" message in the status bar you can close the VSTA window and then preview your form to see your validation in action.

Advertisements

Posted in .NET, Office | Tagged: , , , , | 1 Comment »

Retrieving unique values from a column or list in Excel 2007

Posted by mundeep on February 10, 2009

To generate a list of unique values from a column/list in excel with repeating values it is possible to use the “Advanced Filtering” functionality. To demonstrate let us look at an example where you have some de-normalised data such as a list of countries and colours on their flag you can retrieve both a list of the countries and a list of unique colours used.

Here is a list of our initial data:

Sample Denormalised Data

Figure 1) Sample Denormalised Data

From this we want to return a list of (a) unique countries & (b) unique colours used.

1) In the menu click on Data and then in the Ribbon click on “Advanced” in the filter section.

Figure 2) Advanced Filter Button

Figure 2) Advanced Filter Button

2) If you get a warning about not being able to determine which row contains column labels click on OK (this will assume the first row in your data selection is the column header).

Figure 3) Column Label Warning

Figure 3) Column Label Warning

3) In the advanced filter dialog:

  • Select “Copy to another location”.
  • In the ‘List Range’ select a column of data (eg. the country column).
  • Leave criteria range blank.
  • In the ‘Copy To’ select a single cell in the same sheet to copy the data to.
  • Ensure the “Unique records only” checkbox is ticked.
  • Click OK
Figure 4) Advanced Filter Dialog

Figure 4) Advanced Filter Dialog

4) Repeat for the ‘Colour’ column.

Figure 5) Filter Colour Column

Figure 5) Filter Colour Column

5) You should now have a unique list of countries & colours as follows (note the column headers for the unique lists was changed).

Figure 6) Final Result

Figure 6) Final Result

Posted in Office | Tagged: , , , | 10 Comments »