windowsforms-16995-controls-and-libraries-spreadsheet-examples-mail-merge-how-to-filter-data.md
In most real-world scenarios, a data source used for mail merge contains more data rows than the number of rows required in a resulting document. In this case, data must be filtered according to certain criteria, which can be built upon a single or multiple data fields.
This tutorial illustrates how to filter data when performing a mail merge with the SpreadsheetControl.
To get started with this tutorial, run the application created in the How to: Create a Master-Detail Report example. A master-detail template is automatically loaded into the SpreadsheetControl. This template contains a two-level detail range (click the Mail Merge Design View button in the Ribbon to show template ranges):
You can filter data individually in each detail range of a template. In this example, filter category products (the DetailLevel0 range) by the unit price and the quantity of units in stock.
Select any cell within the DetailLevel0 range and click the Edit Filter button on the Mail Merge tab.
In the invoked dialog, you can visually construct an expression in which the UnitPrice data field is compared with required values (to merge only products whose unit price is greater than or equal to 10 and less than or equal to 45 ).
Click OK.
In a mail merge template, each filter criterion is stored as a “FILTERFIELD(n)” defined name, where (n) is the zero-based index of the filter criterion in the entire template. The DefinedName.RefersTo property value is a filter expression (filter conditions combined by logical operators) preceded by the data member name.
For example, after you have added the filter criteria as described above, the following defined name is added to the Worksheet.DefinedNames collection of the template worksheet.
| Name | RefersTo |
|---|---|
| FILTERFIELD0 | “CategoriesProducts;[UnitPrice] Between (10,45) And [UnitsInStock] >= 30” |
Click the Mail Merge Preview button to review the result.
See Also