Efficiently Filtering Contact Items in a Contact Folder in Outlook 2010

Summary:  Learn how to efficiently get and filter information for contact items in Microsoft Outlook 2010. Specifically, this article describes how to use the Table object and how to use the GetTable(Object, Object) method of the Folder object.

Applies to: Office 2010 | Outlook 2010 | Visual Studio

Published:  October 2010

Provided by:  Jonathan Fingold, SDK Bridge, LLC

The Table object represents a read-only dynamic rowset of data in a Folder or Search object, and you can use the GetTable(Object, Object) method of the Folder object to get a Table object that represents items in a folder. The Table object allows for for fast enumeration and filtering of items in the folder. Each row of a Table represents an item in the folder, and each column represents a property of an item. The initial Table object contains a default subset of the available properties. This article uses a C# Microsoft Outlook 2010 add-in project to show how to use this object. The sample provided with this Visual How To uses a C# Outlook 2010 add-in project written in Microsoft Visual Studio 2010. To use this sample, you should already be familiar with C# and creating custom forms and add-ins for Outlook. This sample also builds upon functionality presented in the Visual How To Efficiently Getting and Setting Custom Properties in a Contact Folder in Outlook 2010. Files The add-in contains five noteworthy classes: ThisAddin—Initializes the add-in, provides access to the Opportunites contacts folder, and creates the Opportunites folder if it does not already exist. OpportunitiesRibbon—Provides an add-in ribbon that contains a Get Report button that displays the report form and two buttons for importing and exporting data between Outlook and specified data files. Form1—Gets a Table object for the items in the Opportunities contacts folder and displays the information in a DataGrid. Constants—Defines property references for the built-in and custom properties that this add-in imports and exports, and contains other constants, such as the display names and data types of the properties. ContactItemHelper—Handles the file operations associated with importing and exporting the property data. In addition to the Visual Studio project files, the code sample .zip file also contains two additional files: Sales Opportunity.oft and ContactData.csv. You will have to publish the Sales Opportunity form to your Personal Forms Library with the name "Sales Opportunity". Additionally, to populate the Opportunities contacts folder, you must do the following: Save the ContactData.csv file. In the OpportunitiesRibbon.cs file, update the dataImportFile class field to contain the path for the ContactData.csv file. Compile and run the add-in, which starts Outlook. On the Add-In ribbon, click Import Data. Start-up and the Opportunities Folder The ThisAddin class defines an OpportunitiesFolder property, for getting the Folder object for the Opportunities folder, and a CreateOpportunitiesFolder method, for creating the folder if it does not already exist. When creating the Opportunities folder, the add-in uses the UserDefinedProperties object to define five user properties for the five custom properties that correspond to the fields on the Sales Opportunity form. Getting the Default Table Class When the form loads, and when the Reset button is clicked, the form gets the default Table object from the Opportunities contacts folder and then displays that information in a report (as shown in the following code). In addition, the ShowContactReport method of the form adds a new report by calling NewDataGrid, or adds an error message by calling NewErrorMessage. The default Table contains a row for each contact in the folder, and includes the following eight contact properties: EntryID, Subject, CreationTime, LastModificationTime, MessageClass, FirstName, LastName, and CompanyName. public Form1() { InitializeComponent(); ShowDefaultReport(); } /// <summary>Handles the Click event for the form's Reset button.</summary> private void resetButton_Click(object sender, EventArgs e) { ShowDefaultReport(); } /// <summary>Displays the contact data with the default columns and /// does not filter the rows.</summary> private void ShowDefaultReport() { Outlook.Folder opportunities = Globals.ThisAddIn.OpportunitiesFolder; if (opportunities != null) { Outlook.Table contacts = opportunities.GetTable(); ShowContactReport(contacts); } else { ShowContactReport(null); } } /// <summary>Displays the data from the specified Table /// object.</summary> /// <param name="table">The Table containing the contact data /// to display. </param> private void ShowContactReport(Outlook.Table table) { this.SuspendLayout(); // Free memory for any old controls. foreach (Control c in this.reportPanel.Controls) { if (c != null) { c.Dispose(); } } this.reportPanel.Controls.Clear(); if (table != null) { // Add the new control with updated contact information. this.reportPanel.Controls.Add(NewDataGrid(table)); } else { this.reportPanel.Controls.Add(ErrorMessage()); } this.ResumeLayout(); this.Refresh(); } Accessing Table Data The table provides information about the properties it contains in its Columns property, which contains a collection of Column objects. Note The Outlook Columns collection uses one-based indexing. When a custom property is added to a Table object, that property becomes a column in the table. The value of the Name property of the Column object contains the property name referenced by namespace, instead of the property's display name. The form uses the GetDisplayName method of the Constants class to return the desired display name for the property to display on the report, as shown in the following code. In addition, the form formats the Purchase Estimate column as Currency. To enumerate the rows contained in the Table object, use the MoveToStart() and GetNextRow() methods, and the EndOfTable property. To access the properties contained in each row, use the GetValues() method of the Row object. The values are returned in an object array, and the ordering of the values matches the ordering of the columns in the table’s Columns collection. /// <summary>Creates a DataGridView control that displays the contact /// information contained in the specified Table object.</summary> /// <param name="table">The Table containing the contact data to /// display. </param> /// <returns>The new DataGridView.</returns> private DataGridView NewDataGrid(Outlook.Table table) { DataGridView dataGrid = new DataGridView(); // For each column in the table, add a column to the control. Note // that the Table column collection uses 1-based indexing; whereas, // the DataGridView column collection uses 0-based indexing. dataGrid.ColumnCount = table.Columns.Count; for (int i = 1; i <= table.Columns.Count; i++) { Outlook.Column tableColumn = table.Columns[i]; DataGridViewColumn dataColumn = dataGrid.Columns[i - 1]; dataColumn.Name = tableColumn.Name; dataColumn.HeaderText = Constants.GetDisplayName(tableColumn.Name); dataColumn.ValueType = Constants.GetDataType(tableColumn.Name); dataColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // Format the Purchase Estimate property data as currency. if (dataColumn.HeaderText == Constants.purchaseEstimateDisplayName) { dataColumn.DefaultCellStyle.Format = "C"; } } // For each row in the table, add the contact data to the control. table.MoveToStart(); while (!table.EndOfTable) { Outlook.Row contact = table.GetNextRow(); object[] contactData = contact.GetValues(); // The ordering of the contact property values returned by the // Table's GetValues method matches the ordering of the column // information returned by the Table's Columns property. dataGrid.Rows.Add(contactData); } // Modify the control's display and behavior properties. dataGrid.AutoSize = true; dataGrid.Dock = DockStyle.Fill; dataGrid.BorderStyle = BorderStyle.FixedSingle; dataGrid.ReadOnly = true; return dataGrid; } Filtering a Table Object on Built-in Properties You can filter items either when getting the table from the folder, by using the GetTable(Object, Object) method with specified filtering criteria, or after the table is created, by using the Restrict(String) method. In either case, the method returns a new Table object. In the following code, the folder's GetTable(Object, Object) method filters the items by using a Jet query string to get only information for items with a CompanyName value of "Adventure Works". The GetTable(Object, Object) and Restrict(String) methods support both Jet and DAV Searching and Locating (DASL) query strings. /// <summary>Handles the Click event for the form's Filter /// button.</summary> /// <remarks>Displays the contact data with the default columns /// and filters the rows based on the CompanyName property. /// </remarks> private void filterButton_Click(object sender, EventArgs e) { Outlook.Folder opportunities = Globals.ThisAddIn.OpportunitiesFolder; if (opportunities != null) { string criteria = "[CompanyName] = 'Adventure Works'"; Outlook.Table contacts = opportunities.GetTable(criteria); ShowContactReport(contacts); } else { ShowContactReport(null); } } Modifying the Columns in a Table Object You can add or remove columns from an existing table by calling the Add(String) or Remove(Object) method on the Columns collection for the Table object. The Name parameter can contain an explicit built-in property name, or a property name referenced by namespace. However, it must be referenced as the property name in the English locale. To add a custom property to a Table, you have to reference the property by its namespace and explicitly append the type of the property to the end of the property reference, as shown in the following code. /// <summary>Handles the Click event for the form's Customize /// Columns button.</summary> /// <remarks>Displays the contact data with the addition of the /// CustomerID built-in property and the five Sales Opportunity /// custom properties. Does not filter the rows.</remarks> private void customizeColumnsButton_Click( object sender, EventArgs e) { Outlook.Folder opportunities = Globals.ThisAddIn.OpportunitiesFolder; if (opportunities != null) { Outlook.Table contacts = opportunities.GetTable(); AddCustomColumns(contacts); ShowContactReport(contacts); } else { ShowContactReport(null); } } /// <summary>For a Table object, removes the CreationTime and /// LastModificationTime properties and adds the CustomerID /// built-in property and the five Sales Opportunity custom /// properties.</summary> private void AddCustomColumns(Outlook.Table contacts) { for (int i = contacts.Columns.Count; i > 0; i--) { if (contacts.Columns[i].Name == Constants.creationTimeDisplayName || contacts.Columns[i].Name == Constants.lastModificationTimeDisplayName) { contacts.Columns.Remove(i); } } contacts.Columns.Add(Constants.customerIDProperRef); contacts.Columns.Add(Constants.encounterDatePropTag); contacts.Columns.Add(Constants.purchaseEstimatePropTag); contacts.Columns.Add(Constants.salesRepPropTag); contacts.Columns.Add(Constants.salesValuePropTag); contacts.Columns.Add(Constants.tradeShowPropTag); } The Constants class includes reference strings for the Sales Order custom properties that specify the property type, as shown in the following code. // References to custom properties by their namespaces, including data // type. public static readonly string encounterDatePropTag = encounterDatePropRef + "/0x00000040"; public static readonly string purchaseEstimatePropTag = purchaseEstimatePropRef + "/0x00000006"; public static readonly string salesRepPropTag = salesRepPropRef + "/0x0000001f"; public static readonly string salesValuePropTag = salesValuePropRef + "/0x00000005 "; public static readonly string tradeShowPropTag = tradeShowPropRef + "/0x0000000b"; Filtering a Table Object on Custom Properties You can filter items by most of the available properties. However, custom properties must be defined in the folder where you are applying the filter. The call will fail if you filter on a custom property that is defined only in the item. In the following code, the folder's GetTable(Object, Object) method filters the items and uses a Jet query string to get information only for items with a Sales Rep value of "Karen Berg". Then, the Columns collection for the Table is modified, and the resulting table is displayed in the report. /// <summary>Handles the Click event for the form's Customize /// Columns button.</summary> /// <remarks>Displays the contact data with the addition of the /// CustomerID built-in property and the five Sales Opportunity /// custom properties. Filters the rows based on the custom /// Sales Rep property.</remarks> private void filterCustomColumnsButton_Click( object sender, EventArgs e) { Outlook.Folder opportunities = Globals.ThisAddIn.OpportunitiesFolder; if (opportunities != null) { string criteria = string.Format("[{0}] = 'Karen Berg'", Constants.salesRepDisplayName); Outlook.Table contacts = opportunities.GetTable(criteria); AddCustomColumns(contacts); ShowContactReport(contacts); } else { ShowContactReport(null); } } The article Efficiently Getting and Setting Custom Properties in a Contact Folder in Outlook 2010 demonstrated how to load data from a data file to populate contact items in the Opportunities folder. The contacts in this folder include custom sales information, which can be accessed through a custom Sales Opportunity form. If you want to get a report of the existing data, you should consider using the Table object, because it provides an efficient way to access and filter property data. The Table object represents a read-only dynamic rowset of data in a Folder or Search object, and you can use the GetTable(Object, Object) method of the Folder to get a Table that represents items in a folder. To obtain items in the folder, use the GetTable(Object, Object) method on the Opportunities folder. Table Object The Table object allows for fast enumeration and filtering of items in the folder. Each row of a Table represents an item in the folder, and each column represents a property of an item. The initial Table object contains a default subset of the available properties: EntryID, Subject, CreationTime, LastModificationTime, MessageClass, FirstName, LastName, and CompanyName. Using the Table Object To demonstrate some capabilities of the Table object, the add-in contains a report that displays the table data in a form. The form provides four buttons for accessing the table data. When the form loads, it gets the default table for the Opportunities folder and displays the default information that was provided. The Reset button also gets the same table and displays the same information. The Filter button specifies filter criteria to get a specific set of contacts in the Opportunities folder—in this case the criteria selects only the items that have a CompanyName value of "Adventure Works"—and then displays the information available in the table. The Customize Columns button gets the default table for the Opportunities folder, removes the CreationTime and LastModificationTime columns from the table, adds the Sales Opportunity custom properties to the table, and then displays the resulting information. Finally, the Filter Custom Column button specifies filter criteria to get a specific set of contacts in the Opportunities folder (this time, the criteria selects only the items that have a custom Sales Rep property value of "Karen Berg"), modifies the columns contained in the table, and then displays the resulting information. Adding Custom Properties to a Table The custom properties in this example span several of the possible data types: Encounter Date contains a date value, which is DateTime in C#. Purchase Estimate contains a currency value, which is double in C#. Sales Rep contains a string value. Sales Value contains a 32-bit signed integer value, and represents the rank of the contact. Trade Show contains a Boolean value, which is bool in C#. To add a custom property to a Table, you have to reference the property by its namespace and explicitly append the type of the property to the end of the property reference. For more information about how to add custom properties to a table, see the following MSDN topics: Adding Columns to a Table Object Unsupported Properties in a Table Object or Table Filter MAPI Property Tags Property Types Filtering by Using a Table Object You can filter items by most of the available properties. However, custom properties must be defined in the folder where you are applying the filter. The call will fail if you filter on a custom property that is defined only in the item. The GetTable(Object, Object) and Restrict(String) methods support both Jet and DAV Searching and Locating (DASL) query strings. Considerations There are several things to consider when you use the GetProperties(Object) and SetProperties(Object, Object) methods: Although additions and deletions of the underlying folder are reflected by the rows in the Table object, the Table does not support any events or methods to update the underlying folder. If you must have a writable object from the Table, obtain the Entry ID for that row from the default EntryID column in the Table, and then use the GetItemFromID(String, Object) method of the NameSpace object to obtain a full item. You can only use the Restrict(String) method of the Table object to apply another filter to that Table if the parent object of the Table is a Folder. If the parent object is a Search object, the Restrict(String) method will return an error. Certain properties, such as binary properties, computed properties, and HTML or RTF body content, cannot be added to a Table by using the Add(String) method of the Columns collection. Although the SetColumns(String) method of the Items collection can be used to facilitate caching certain properties for fast access to those properties, some properties are restricted from SetColumns(String). Because these restrictions do not apply to the Add(String) method of the Columns collection, the Table object is less restrictive than the Items object. See the Explore It section for a list of articles that cover these issues and other information.

Watch the video > [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/93f20a0f-b2a4-4bc5-9f91-ace425cbf333] Length: 09:21 Grab the Code Outlook Developer Center Properties Overview Table Interface GetTable Method Columns Interface Column Interface Adding Columns to a Table Object Unsupported Properties in a Table Object or Table Filter Referencing Properties by Namespace MAPI Property Tags Property Types