Sorting and Filtering Your Data for Mail or Catalog Merges in Microsoft Office Publisher 2003

  • Article

 

Andrew May
Microsoft Corporation

January 2004

Applies to:
    Microsoft® Publisher 2002
    Microsoft Office Publisher 2003

Summary: Sort or filter records from a data source programmatically before performing a mail or catalog merge operation in Microsoft Office Publisher 2003. (6 printed pages)

Contents

Introduction
Sorting Data Records
Filtering Data Records
Conclusion

Introduction

The mail or catalog merge functionality in Microsoft® Office Publisher 2003 is a powerful and flexible way to create a wide range of publications that are automatically customized and personalized for the audiences they target. You can also use sorting and filtering to even further customize the results of a mail or catalog merge, so your business communications are tailored to the people who receive them.

Merge only the records you want

For mail and catalog merge, Publisher allows you to connect to a number of different types of data sources, such as Microsoft Office Excel 2003 spreadsheets, Microsoft Office Word 2003 tables, Microsoft Office Access 2003 databases, or ASCII text files. However, these data sources may not be structured exactly as you need them. This is especially true for data sources not under your direct administration. The data source may contain records you don't want in your merge, or the records may not be arranged in the same manner as you want your merge publication to be.

Use the sorting and filtering functionality of Publisher to order and select the data records that appear in your merge, before the merge takes place. Select only the records you want included in the merge, and specify the order in which records are merged. In both sorting and filtering, you specify columns in your data source for Publisher to use in evaluating each data record.

Sorting enables you to specify the order in which you want records merged. It does not affect which records are merged, but rather the order in which they are merged. Sorting is based on the values in up to three specific columns of your data source. For example:

  • Suppose you were doing a nationwide mailing of a brochure. You might want all of the brochures addressed to a specific postal code to be printed consecutively. Your data source has a column for postal codes. You could sort based on postal code.
  • You want to create a catalog of items your company offers; you want all items of a certain type to appear together in the catalog. Using the "Item Type" column in your data source, you sort the records to be merged according to item category.

Filtering enables you to specify criteria by which each record is evaluated for inclusion in your mail or catalog merge. Those records that meet the criteria you have specified are included in the merge. For example:

  • Suppose you were doing a mass mailing brochure. If you only wanted to mail your brochure to selected postal codes, you could set up filter criteria that only included records where the postal code was equal to the one(s) you specified.
  • You want to send a post card with a special discount offer to only those customers who haven't ordered from your company in the past three months. Your data source includes a column listing the date of each customer's last order. You could create a filter criterion that only included those customer records were the last order date was greater than three months ago.

Once you connect the data source to your merge template publication, you can sort or filter data source records at any time up until you actually execute the merge operation. Sorting or filtering data source records in Publisher has no effect on the original data source.

Using the object model to create sort and filter criteria enables you to create and save complex criteria and reuse them. For example, if you have several complex filter criteria you need to use repeatedly with the same data source and merge publication, setting those criteria through automation saves rework and minimizes errors. Or, you could create a single filter criterion for use with several data sources.

For more information about programmatically performing catalog merge operations, see Creating Catalogs with Microsoft Office Publisher 2003.

Sorting Data Records

Use the SetSortOrder method of the MailMergeDataSource object to specify the order in which your records are included in the merge. This method enables you to perform up to three nested sort operations on your data source records.

The following example sorts data source records twice; first, in descending order based on zip code, and then, within those results, in ascending order based on last name. This example assumes a data source is connected to the active document.

ActiveDocument.MailMerge.DataSource.SetSortOrder _
    SortField1:="ZIPCode", SortAscending1:=False, SortField2:="LastName"

Note   You do not need to insert the data source column(s) you specify as sorting criteria as a data merge field in the merge publication. In this example, neither ZIP code nor Last Name must appear anywhere in the merge publication in order for Publisher to sort the records base on their values.

Filtering Data Records

Filtering enables you to define a set of criteria, and receive only those data source records that match the criteria included in your merge operation. Publisher assembles a filter comprised of the criteria you define, and uses that filter to determine whether or not to include a given data source record in the merge.

The Filters collection belongs to the publication's MailMergeDataSource object, and contains any filter criteria you want to apply to your data source records. Each MailMergeFilterCriterion object represents a single criterion with which to filter your data. Additionally, you can conjoin individual MailMergeFilterCriterion objects to create a single, complex filter criterion.

Creating Filter Criteria

Use the Add method of the MailMergeFilters collection to add a criterion to the filter Publisher uses to determine data source record inclusion. The Add method lets you specify:

  • The column in the data source table to compare against.
  • The column value to compare against.
  • The type of comparison you want to perform (for example, equals, contains, or is blank).
  • Whether to apply the filter criterion immediately upon addition, or apply all filter criteria later.

**Note   **To specify this, set the DeferUpdate parameter of the Add method to False. When set to False, Publisher does not immediately apply the added filter criterion to the data source records; rather, it queues the criteria and only applies them when the ApplyFilter method of the MailMergeDataSource object is called. This enables you to conjoin multiple filter criteria and then apply them as a single, complex criterion.

  • The conjunction with which to join the next filter criteria, if any. (This is discussed in the next section.)

Conjoining Filter Criteria

To ensure you get the filtering results you desire, you should be aware of how Publisher assembles the filter used to evaluate your records for inclusion.

Publisher interprets conjunctions in the following manner:

  • If the conjunction is and, the filter criterion immediately following it is joined to the filter immediately preceding it and they operate as a single criterion. Multiple filter criteria can be conjoined and used as a single, complex criterion in this way.
  • If the conjunction is or, the filter criterion immediately following it is treated as a separate, independent criterion from the one immediately preceding it. Filter criterion cannot be joined into a single criterion using this conjunction.

For example, suppose you were working with a data source comprised of real estate listings, and you wanted to filter the records for the merge to contain only those listings for houses located in two neighborhoods (Ballard and Fremont) priced at or below a certain list price ($400,000).

You would not specify the criteria in the following order:

Neighborhood = Ballard or Neighborhood = Fremont and List Price = < 400000

Publisher would interpret this as:

(Neighborhood = Ballard) or (Neighborhood = Fremont and List Price = < 400000)

Not as you had intended it:

(Neighborhood = Ballard or Neighborhood = Fremont) and (List Price = < 400000)

Instead, construct your filter to join the criterion you want treated as a single criterion using and, and separate the filter criterion you want treated independently using or:

(Neighborhood = Ballard and List Price = < 400000) or (Neighborhood = Fremont and List Price = < 400000)

Below is the code for such a filter. This example assumes a data source is connected to the active document.

Sub SetDataFilters()     
        'filter data to include only houses in Ballard or Fremont
        'that have a list price lower than $400,000
    With ThisDocument.MailMerge.DataSource
        With .Filters
            'add two criterion that act as a single criterion
            .Add "Neighborhood", msoFilterComparisonEqual, _
                msoFilterConjunctionAnd, "Ballard", True
            .Add "List Price", msoFilterComparisonLessThanEqual, _
                msoFilterConjunctionOr, "400000", True
            'add two criterion that act as a single criterion
            .Add "Neighborhood", msoFilterComparisonEqual, _
                msoFilterConjunctionAnd, "Fremont", True
            .Add "List Price", msoFilterComparisonLessThanEqual, _
                msoFilterConjunctionOr, "400000", True
        End With
        .ApplyFilter
    End With    
End Sub

**Note   **In adding each criterion, the Conjunction parameter of the Add method actually applies to the criterion that follows the one being added. For example, in the previous example, msoFilterConjunctionOr is specified when the MailMergeFilterCriterion object for the second filter criterion (List Price =< 400000) is added, because the next filter (Neighborhood = Fremont) is treated as the start of the next independent criteria.

Altering Existing Criteria

You can alter criteria you add to a filter by changing the properties of the MailMergeFilterCriterion object(s) that represent the criterion. Use the Column, CompareTo, Comparision, and Conjunction properties to alter the specifics of a criterion.

As with the Conjunction parameter of the Add method, the Conjunction property of the MailMergeFilterCriterion object specifies the conjunction placed between the specified criterion and the one that follows it. To change the conjunction joining a criterion to the filter, you must change the Conjunction property of the criterion immediately preceding it. The conjunction specified for the last MailMergeFilterCriterion object in a Filters collection has no effect when Publisher assembles the filter.

The next example shows how to add criteria to the filter, and set the proper conjunction to ensure the desired results. This example adds another neighborhood criterion to our earlier example involving house listings. First, the conjunction on the final criterion is set to or. Then, two criteria (one specifying neighborhood, the other list price) joined by the conjunction and are then added to the collection. This example assumes a data source has been connected to the active document.

Sub AddCriteriaToFilter()
    With ThisDocument.MailMerge.DataSource
        With .Filters
            'Sets the conjunction on last criterion to Or
            .Item(.Count).Conjunction = msoFilterConjunctionOr
            'Adds two joined criteria that act as a single criterion
            .Add "Neighborhood", msoFilterComparisonEqual, _
                msoFilterConjunctionAnd, "SeaTac", True
            .Add "List Price", msoFilterComparisonLessThanEqual, _
                msoFilterConjunctionAnd, "400000", True
        End With
        'Applies all criteria in Filters collection to data source records
        .ApplyFilter
    End With
End Sub

Merge Data Source Sort and Filter object model hierarchy

The figure below illustrates how the section of the Publisher object model involved with sorting and filtering data records is structured.

Click here to see larger image

Figure 1. Part one of structure of the Publisher data source object mode (click picture to see larger image)

Click here to see larger image

Figure 2. Part two of structure of the Publisher data source object mode (click picture to see larger image)

Conclusion

The mail and catalog merge functionality in Publisher is a powerful tool for quickly creating customized and personalized catalogs and mailings, and the sort and filter functionality included in it makes it even more flexible. Rather then having to set up your data source exactly as you want the merge to execute, you can use the sort and filter functionality to choose just the data records you want to include, and then set the order in which they are merged. For mail merge, this enables you to control the order in which merge publications are created for the selected records. For catalog merge publication, this enables you to control the order in which the data records you select appear in the merged catalog publication.