Sorting and Filtering

Line items sorted by price, a stock list with only the products that are running low, an order table without the empty total row of the data: the data rarely arrives in the order and number a document needs. Sorting and filter instructions belong to a merge block in the template, and MailMerge applies them while it merges, with no change to the data or to your merge code. Users set them in a dialog box of the Reporting tab; your code sets them when it inserts a block, changes them on existing blocks and adds conditions that decide whether a nested block is merged at all.

Merge Blocks describes blocks and the Reporting tab's block tools. The code below uses the invoice data invoice.json with its Items array, loaded into the Reporting tab ribbonReportingTab1, and, for merging conditions, the orders template of Nested Blocks and Master-Detail Reports.

How Sorting and Filtering Works

A MergeBlockInfo describes a block, including three lists of instructions:

An empty list or null means no filter, no sorting or no condition. The instructions get into a block in three ways: users set them in the Filter and Sort dialog box, your code passes them with a new block to InsertMergeBlock, or your code changes an existing block and stores the change with Apply.

Filter and Sort in the Reporting Tab

The Insert Merge Block dialog box of the Reporting tab has a Filter & Sort button, which opens the Filter and Sort dialog box for the new block. On its Filter tab, each line compares a field with a value, and And or Or connects a line with the line above it; the Sort tab lists the fields to sort by. This filter keeps only the rows whose LineTotal is greater than 1000:

Merged without the filter, the block lists every row of the order:

With the filter, only the rows above 1000 remain:

Filter Instructions

A FilterInstruction compares a column of each row with a value. Its constructor takes the column name, a relational operator, the value and, optionally, a logical operator:

• Numbers or text. When the value can be parsed as a number and the column holds numbers, they are compared as numbers; otherwise both are compared as strings. Culture is the culture for parsing numbers; the default is the invariant culture.
• Several instructions. LogicalOperator, And or Or, connects an instruction with the one before it. The constructors without it use And.
• Blank values. For IsBlank and IsNotBlank, use the constructor with only the column name and the operator.

The blog article MailMerge: Using Filters to Remove Unwanted Rows removes a total row of the data with IsNotBlank.

Sorting Instructions

A SortingInstruction sorts the rows by a column. Its constructor takes the column name and SortOrder.Ascending or SortOrder.Descending; without a sort order, rows are sorted in ascending order. Several instructions sort by several columns, the first one first.

In Form1.cs, SortOrder is also the name of an enumeration of System.Windows.Forms. The examples of this page need the following using directives, and the alias among them makes SortOrder mean the TX Text Control type:

using System.Collections.Generic;
using TXTextControl;
using TXTextControl.DocumentServer;
using TXTextControl.DocumentServer.DataShaping;
using TXTextControl.DocumentServer.DataSources;
using TXTextControl.DocumentServer.Windows.Forms;
using SortOrder = TXTextControl.DocumentServer.DataShaping.SortOrder;

Set Instructions When Inserting a Block

The following handler inserts an Items block that merges only items with a price of at least 200 and a description, the highest price first:

private void insertFilteredItemsButton_Click(object sender, EventArgs e)
{
    MergeBlockInfo itemsBlock = new MergeBlockInfo("Items")
    {
        ColumnNames = new List<string> { "Description", "Quantity", "Price" },

        // Only items with a price of at least 200 and a description
        Filters = new List<FilterInstruction>
        {
            new FilterInstruction("Price", RelationalOperator.GreaterThanOrEqualTo, 200),
            new FilterInstruction("Description", RelationalOperator.IsNotBlank)
        },

        // The highest price first, then by description
        SortingInstructions = new List<SortingInstruction>
        {
            new SortingInstruction("Price", SortOrder.Descending),
            new SortingInstruction("Description")
        }
    };

    ribbonReportingTab1.DataSourceManager.InsertMergeBlock(textControl1, itemsBlock,
        new MergeBlockSettings(BlockTemplateType.TableRow, true));
}

Merged with invoice.json, the table has one row: Training at 480.00. Consulting at 150.00 doesn't pass the price filter. To connect a filter with Or, pass the logical operator: new FilterInstruction("Quantity", RelationalOperator.GreaterThan, 1, LogicalOperator.Or).

Change the Block at the Input Position

Instructions of an existing block are changed through a MergeBlockInfo created from its subtext part. The following handler sorts the block that contains the input position by price:

private void sortCurrentBlockButton_Click(object sender, EventArgs e)
{
    SubTextPart subTextPart = textControl1.SubTextParts.GetItem();

    if (subTextPart == null || !DataSourceManager.IsMergeBlock(subTextPart))
    {
        MessageBox.Show(this, "Place the input position in a merge block.");
        return;
    }

    MergeBlockInfo mergeBlockInfo = new MergeBlockInfo(subTextPart)
    {
        // Replace the sorting instructions: the lowest price first
        SortingInstructions = new List<SortingInstruction>
        {
            new SortingInstruction("Price", SortOrder.Ascending)
        }
    };

    // Store the change in the block
    mergeBlockInfo.Apply();
}

• SubTextParts.GetItem returns the innermost subtext part at the input position, and DataSourceManager.IsMergeBlock tells whether it is a merge block.
• The MergeBlockInfo constructor with a subtext part reads the block with its instructions. The lists can be null when a block has none.
• Changing the lists doesn't change the block. Apply stores filters, sorting instructions and the merging condition in the subtext part. It doesn't touch nested blocks; call it for each block you change.

The parts of existing instructions are in ColumnName, ComparisonOperator and CompareTo of a filter, and in OrderBy and SortOrder of a sorting instruction.

Open the Filter and Sort Dialog Box in Code

Your application can offer the dialog box for any existing block, for example from a context menu. FilterAndSortDialog shows the columns of a table and returns the instructions the user defines:

private void filterAndSortButton_Click(object sender, EventArgs e)
{
    SubTextPart subTextPart = textControl1.SubTextParts.GetItem();

    if (subTextPart == null || !DataSourceManager.IsMergeBlock(subTextPart))
    {
        MessageBox.Show(this, "Place the input position in a merge block.");
        return;
    }

    MergeBlockInfo mergeBlockInfo = new MergeBlockInfo(subTextPart);

    // The columns of the block's table fill the drop-downs of the dialog
    DataTableInfo table = ribbonReportingTab1.DataSourceManager.DataTables[mergeBlockInfo.TableName];

    if (table == null)
    {
        MessageBox.Show(this, "The loaded data source has no table " + mergeBlockInfo.TableName + ".");
        return;
    }

    using (FilterAndSortDialog dialog = new FilterAndSortDialog(table))
    {
        // Show the instructions the block already has
        dialog.Filters = mergeBlockInfo.Filters?.ToArray();
        dialog.SortingInstructions = mergeBlockInfo.SortingInstructions?.ToArray();

        // System.Windows.Forms.DialogResult: TX Text Control has a DialogResult type too
        if (dialog.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
        {
            // Write the instructions into the block
            dialog.StoreBlockMetaData(subTextPart);
        }
    }
}

The code works as follows:

• The FilterAndSortDialog constructor takes the DataTableInfo of the block's data, which DataTables returns by name; another overload takes the column names as a string array.
• Filters and SortingInstructions are arrays; set before the dialog opens, they fill it, and afterward they hold what the user defined.
• StoreBlockMetaData writes the instructions into the block. It throws an exception for a subtext part that isn't a merge block.
• TX Text Control has a DialogResult enumeration of its own in TXTextControl.DocumentServer.Fields, a namespace that Form1.cs often imports for merge fields, so the comparison names System.Windows.Forms.DialogResult in full.

Merge a Block Only for Matching Parent Rows

BlockMergingCondition works one level up. It is checked against the parent record of a nested block, and when the parent record doesn't match, the block isn't merged at all. With the orders template of Nested Blocks loaded in textControl1, the following handler lets the articles block appear only for the order with the ID 123:

private void articlesConditionButton_Click(object sender, EventArgs e)
{
    SubTextPart articlesPart = textControl1.SubTextParts.GetItem(MailMerge.MergeBlockNamePrefix + "articles");

    if (articlesPart == null)
    {
        MessageBox.Show(this, "The template has no articles block.");
        return;
    }

    MergeBlockInfo articles = new MergeBlockInfo(articlesPart)
    {
        // Checked against the parent row, the current order
        BlockMergingCondition = new List<FilterInstruction>
        {
            new FilterInstruction("id", RelationalOperator.Equals, 123)
        }
    };

    articles.Apply();
}

GetItem with a name returns the subtext part of that name, or null; the name is the prefix txmb_ from MergeBlockNamePrefix followed by the block name. Merged with orders.json, order 123 lists its article, and order 321 appears without articles. The blog article MailMerge: Conditional Rendering of Merge Blocks compares conditions with blocks that disappear for missing data.

Where the Instructions Are Stored

Apply, InsertMergeBlock and the dialog box store the instructions in the subtext part of the block, as a JSON string in its Data property. They travel with the template: save templates with sorting and filter instructions in the TX Text Control format, which keeps this data with the blocks. The blog article Mail Merge: Conditional Data Shaping in Merge Blocks shows the JSON of a block with a merging condition.

Next Steps

• Conditional Content: include or leave out whole sections depending on the data.
• Nested Blocks and Master-Detail Reports: the master-detail templates that merging conditions work with.
• Reporting Ribbon and Dialog Boxes: the other dialog boxes of the Reporting tab.
• The blog article Filtering and Sorting Repeating Blocks in MailMerge using C#: inventory reports built with filters and sorting.