Sorting and Filtering

Data rarely arrives in the order and number that a document needs: line items should be sorted by price, and a stock report should list only products that are running low. With TX Text Control, sorting and filter instructions belong to a merge block in the template, and MailMerge applies them during the merge, without any change to the data or the merge code. You define the instructions, combine them, set a condition for a whole block and store them with the template.

Merge Blocks in Getting Started adds a first filter and sort instruction to an existing block. Besides the invoice data with its LineItems array in data.json from that page, the programs use the nested orders data of Nested Blocks.

How Sorting and Filtering Works

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

An empty list or null means no filtering, no sorting or no condition. There are three ways to get the instructions into a template:

• In the Document Editor, users set filters and sorting in the Filter and Sort dialog, as described in Define Rules in the Document Editor.
• When you insert a block, set the lists on the MergeBlockInfo that you pass to DataSourceManager.InsertMergeBlock.
• For an existing block, create a MergeBlockInfo from the block's subtext part, change the lists and call Apply, which stores them in the block. Without Apply, the block doesn't change.

Filter Instructions

A FilterInstruction compares a column of each row with a value. The 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 contains numbers, the values are compared as numbers; otherwise both are compared as strings. Culture sets the culture for parsing numbers; the default is the invariant culture.
• Combined instructions. The LogicalOperator And or Or connects an instruction with the one before it. The constructor without it uses And.
• Blank values. For IsBlank and IsNotBlank, use the constructor with only the column name and the operator.

A filter keeps the rows that match. A filter on the quantity column with IsNotBlank, for example, leaves out a last row that contains only a total, as the blog article MailMerge: Using Filters to Remove Unwanted Rows shows.

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, the rows are sorted in ascending order. A block can have several sorting instructions, for example by price and then by description. Like FilterInstruction, it has a Culture for comparing numbers.

Set Instructions When Inserting a Block

The following program inserts a LineItems block that merges only rows with a price of at least 50 and a description, sorted by price and then by description:

using TXTextControl;
using TXTextControl.DocumentServer;
using TXTextControl.DocumentServer.DataShaping;
using TXTextControl.DocumentServer.DataSources;

string jsonData = File.ReadAllText("data.json");

using (ServerTextControl tx = new ServerTextControl())
{
    tx.Create();

    MergeBlockInfo lineItems = new MergeBlockInfo("LineItems")
    {
        ColumnNames = new List<string> { "Description", "Quantity", "Price" },

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

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

    DataSourceManager dataSourceManager = new DataSourceManager();
    dataSourceManager.LoadJson(jsonData);
    dataSourceManager.InsertMergeBlock(tx, lineItems,
        new MergeBlockSettings(BlockTemplateType.TableRow, true));

    // The instructions are stored with the block
    tx.Save("line-items.tx", StreamType.InternalUnicodeFormat);

    using (MailMerge mailMerge = new MailMerge { TextComponent = tx })
    {
        mailMerge.MergeJsonData(jsonData);
    }

    tx.Save("line-items.pdf", StreamType.AdobePDF);
}

Both line items of the sample data match the filters, and "Priority Support - 12 Months", the cheaper item, comes first. To connect a filter with Or, pass the logical operator: new FilterInstruction("Quantity", RelationalOperator.GreaterThan, 1, LogicalOperator.Or).

Change the Instructions of an Existing Block

The following program prints the instructions of all blocks of line-items.tx and replaces the sorting of the LineItems block:

using TXTextControl;
using TXTextControl.DocumentServer.DataShaping;
using TXTextControl.DocumentServer.DataSources;

using (ServerTextControl tx = new ServerTextControl())
{
    tx.Create();
    tx.Load("line-items.tx", StreamType.InternalUnicodeFormat);

    foreach (SubTextPart subTextPart in tx.SubTextParts)
    {
        if (!DataSourceManager.IsMergeBlock(subTextPart))
        {
            continue;
        }

        MergeBlockInfo mergeBlockInfo = new MergeBlockInfo(subTextPart);
        Console.WriteLine($"Block {mergeBlockInfo.TableName}");

        foreach (FilterInstruction filter in mergeBlockInfo.Filters ?? new List<FilterInstruction>())
        {
            Console.WriteLine($"  Filter: {filter.LogicalOperator} {filter.ColumnName} {filter.ComparisonOperator} {filter.CompareTo}");
        }

        foreach (SortingInstruction sorting in mergeBlockInfo.SortingInstructions ?? new List<SortingInstruction>())
        {
            Console.WriteLine($"  Sort: {sorting.OrderBy} {sorting.SortOrder}");
        }

        if (mergeBlockInfo.TableName == "LineItems")
        {
            // Replace the sorting instructions: the highest price first
            mergeBlockInfo.SortingInstructions = new List<SortingInstruction>
            {
                new SortingInstruction("Price", SortOrder.Descending)
            };

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

    tx.Save("line-items.tx", StreamType.InternalUnicodeFormat);
}

The code works as follows:

• The MergeBlockInfo constructor with a subtext part reads the block with its instructions. The lists can be null, so the loops use an empty list instead.
• ColumnName, ComparisonOperator and CompareTo are the parts of a filter; OrderBy and SortOrder the parts of a sorting instruction.
• Assigning a new list replaces the instructions, and Apply stores them. Apply doesn't change nested blocks; call it for each block you change.

The blog article Filtering and Sorting Repeating Blocks in MailMerge using C# builds inventory reports that list, for example, only products with a low stock. Its template filters the inventory block for an inventory count of less than 100, so only matching products of the 100 records reach the table, followed by a summary calculated with formulas:

Merge a Block Only for Matching Parent Rows

BlockMergingCondition works one level up: it's checked against the parent record of a nested block, and when the parent record doesn't match, the block isn't merged at all. The following program changes the template of Nested Blocks so that articles are listed only for the order with the ID 123:

using TXTextControl;
using TXTextControl.DocumentServer;
using TXTextControl.DocumentServer.DataShaping;
using TXTextControl.DocumentServer.DataSources;

string jsonData = File.ReadAllText("orders.json");

using (ServerTextControl tx = new ServerTextControl())
{
    tx.Create();
    tx.Load("orders-template.tx", StreamType.InternalUnicodeFormat);

    SubTextPart articlesPart = tx.SubTextParts.GetItem(MailMerge.MergeBlockNamePrefix + "articles");

    if (articlesPart == null)
    {
        Console.WriteLine("The template has no articles block.");
        return;
    }

    MergeBlockInfo articles = new MergeBlockInfo(articlesPart);

    // The condition is checked against the parent row, the current order
    articles.BlockMergingCondition = new List<FilterInstruction>
    {
        new FilterInstruction("id", RelationalOperator.Equals, 123)
    };

    articles.Apply();

    using (MailMerge mailMerge = new MailMerge { TextComponent = tx })
    {
        mailMerge.MergeJsonData(jsonData);
    }

    tx.Save("orders.pdf", StreamType.AdobePDF);
}

SubTextPartCollection.GetItem with a name returns the subtext part of the block, or null. In orders.pdf, order 123 lists its article, and order 321 appears without articles. The blog article MailMerge: Rendering Conditional Table Rows uses a condition to show a note only for products that aren't available: the nested outofstock block is merged only for products whose available value is false.

Where the Instructions Are Stored

Apply and InsertMergeBlock store the instructions in the subtext part that represents the block, as a JSON string in its Data property. A template saved in the TX Text Control format keeps this data with its merge blocks: the Mail Merge Template Assistant, for example, downloads its templates as .tx files that keep their sorting, filter and merging rules. Save templates with sorting and filter instructions in the TX Text Control format.

Define Rules in the Document Editor

The Reporting tab of the Document Editor lets users filter and sort the rows of a block without code:

• For a new block, the Insert Merge Block dialog, shown in Create a Merge Block in the Document Editor, has a Filter & Sort... button. It opens the Filter and Sort dialog, and the rules are stored with the block when it is inserted.
• For an existing block, the Edit Merge Block button of the ribbon opens the Edit Merge Blocks dialog. Users select a block and click Filter & sort... to change its rules; the dialog also renames, deletes and goes to blocks.

On the Filter tab of the Filter and Sort dialog, each line compares a Field with a value in Compare to using a Comparison, such as Greater than or is blank, the operators of the table in Filter Instructions. And or Or connects a line with the one above it. On the Sort tab, Sort by and Then by list the fields to sort by, each Ascending or Descending. The dialog stores the same Filters and SortingInstructions that your code sets with MergeBlockInfo.

The dialog has no setting for a block merging condition, and the JavaScript API of the Document Editor has no methods for merge block instructions. Two online demos show how an application lets users define all rules:

• The Mail Merge Template Assistant sends the template to the server, where MergeBlockInfo stores the sorting, filter and merging rules; Template Assistant describes it.
• The Conditional Merge Blocks demo writes the rules into the data of the block's subtext part from JavaScript, as the blog article Mail Merge: Conditional Data Shaping in Merge Blocks explains. The format of this data isn't part of the reference.

Next Steps

• Conditional Content: include or leave out whole sections depending on the data.
• Nested Blocks: build the master-detail templates that merging conditions work with.
• Template Design: store templates with their rules.