Filter within merge

Question first:
In MailMerge is it possible to filter a table-start / table-end region to not print records which do not fit a criteria?

Supporting Information:

Suppose this is the data that I have now

User - Grade
John - 4
Tim - 3
Scott - 2
Jane - 4
John - 1
Tara - 3

I need to see the data like this:

*Grade 5

Grade 4
John
Jane

Grade 3
Tim
Tara

Grade 2
Scott

Grade 1
John*

Since I can not add easily add any code behind the scenes for this merge I have thought of filtering the tables and having:

Grade 5
Grade 4
Grade 3

etc
These grades are always going to be in a finite list such as {1, 2, 3, 4, 5}

Hi Joseph,
Thanks for your inquiry.
Most likely to achieve this you will have to implement a little bit of extra code somewhere. If you are using a DataSet/DataTable then you can most likely achieve this by using DataView as shown in this sample here. You could create multiple DataView classes and then set the Sort property to “Grade = 5” etc.
Otherwise you will need to use the FieldMergingCallback property and handle the fields individually in there.
If you are looking to define which sort you are doing through the merge fields in your template than remember that you can define custom tags in the Field Result of the merge field (writing on the merge field without Field Codes toggled). This will not affect the field code and it can be parsed by your application through code.
If you have any further queries please feel free to ask.
Thanks,

Adam,

Is there any event or similar that I could handle between the ExecuteWithRegions and the Aspose.Words.Reporting.IMailMergeDataSource, so that I could capture the actual <<TableStart: Students>> tag and then add additional non-standard mail merge tags in there and filter the IMailMergeDataSource in code?

Ideally I’m looking for something similar to MailMerge.MergeField event with the ability to filter the result set that is selected.

Hi Joseph,
It sounds like what you need to use is the FieldMergingCallback property. The handler passed to this property will fire each time a field is encountered in the document.
To handle the filter through code you can use the FieldMergingCallback like so:

doc.MailMerge.FieldMergingCallback = new HandleMergeField(5);
doc.MailMerge.ExecuteWithRegions(customersDataSource);
public class HandleMergeField: IFieldMergingCallback
{
    int mGradeClass;
    public HandleMergeField(int gradeClass)
    {
        mGradeClass = gradeClass;
    }
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs args)
    {
        if (args.TableName == "Student")
        {
            // Check if this field contains the grade number, then if the value is different to the one we are filtering by don't merge the result
            if (args.FieldName.Equals("GradeNumber") && ((int) args.FieldValue) != mGradeClass)
            {
                args.Text = "";
            }
        }
    }
    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs args)
    {
        // Do Nothing
    }
}

Unfortunately there is no call for when a TableStart field is encountered, if you are looking to define the value to filter by in the Document you can however insert it straight into the document in the FieldResult. This will not affect the actual name of the field and can be used inside the FieldMerging handler in a similar way to the code above.

// Field.Result Will return the FieldResult of the field, i.e <>
if (args.Field.Result.Contains("(Sort:"))
{
    // Parse number and use ite to check whether this field should be merged with this data or not.
}

If these suggestions do not help could you please post an example document here and maybe some pseudocode of what you are looking to achieve and I will provide some further suggestions.
Thanks,

Hi Adam,

I will try and make a working example to work towards trying to resolve this.

The train of thought I am thinking of is Merging in the row and then deleting it afterwards. So instead of filtering is there a way of deleting rows after the merge… one thing I was initially trying on this train of thought was something like:

Dim regexStr As String = "DelStart.*DelEnd"
Dim regex As New System.Text.RegularExpressions.Regex(regexStr)
srcDoc.Range.Replace(regex, String.Empty)

with two mail merge fields at the TableStart/TableEnd with a value like IF(grade <> 5 then DelStart) etc.

However wildcards aren’t supported by the Range.Replace, is there a way I could select the entire range within a TableStart area (or the area between two MergeFields) and then call Range.Delete().

Maybe I need to use a DocumentVisitor or similar to achieve this if the above idea isn’t feasible.

Also, I found an old post from 2007 saying that Aspose is looking at supporting conditional statements such as “IF” and “SKIPIF” however I can’t find anywhere that SKIPIF is mentioned since, is this function supported or is there a plan to support it in an upcoming release?

The main concern with your posted solution is that my reporting system is made to be generic and run for a couple dozen different reports, so adding code for a single specific report is something that I am trying to avoid (as other similar report templates are likely to occur over time).

Hi Joseph,
Thanks for this additional information.
I believe I understand what you are trying to achieve. You can implement the method that you described above, but I believe it would be extra work, as the idea that I suggested above would be a lot more simplier to implement.
The method I described in my last post is more efficient as it checks a value from the template while merging and then does not merge the data row at all if the filter is matched to this value. There is no requirement to then go back and delete fields after merging.
Just in case this description is not clear, you would for example:

  1. Include a flag in the field value of the field you want to filter such as “=5” or you include a whole merge field at the start of the region as you suggested above.
  2. Implement the FieldMerging method which will parse any flags/special merge fields and compare with the value about to be merged. It will then skip all of the fields for this record if the filter matches.

Regarding support of the conditional fields, the IF field is fully supported in recent versions of Aspose.Words. However if you are using regions then you would have to include the IF field inside the region markers. I’m not sure if this would work as expected if you are merging a lot of fields inside the region.
The SKIPIF field is currently not supported, at least not within regions. However you could easily implement the functionality of this field inside the FieldMerging handler by intercepting this field and then skipping that record. If you do implement this then you’ll find that it is almost identical to the process I suggested above.
If you have any further queries please feel free to ask.
Thanks,

Hi Adam,

The issue with your suggested solution is that my merge region is embedded within a table such that when the merge occurs I end up with rows with several empty rows.

Attached is a rough version of my template that I am trying to get working so you can see the layout of what I am trying to achieve.

Hi Joseph,
Thanks for attaching your template for me to look at. I have created some sample code for you below, it is the implementation of my idea above. I have also attached the edited template. This template includes an extra field which defines the filter in the field result (e.g = 5). The code will handle this field while merging and will not merge a particular row if the value does not match this filter. You can also set if you want the filter field merged result to be removed from the document during merging.
If there are any other requirements that you need to define n your template then we can take a further look into them. I also think you are correct when you suggest there should be some sort of way to easily skip a record in template when merging. I have linked a new feature request to include a member in the FieldMergingArgs which should give you the option to skip the merging of a current record. This would make implementing this sort of filtering much easier. We will keep you informed of any developments.

// Pass an boolean option which defines whether to remove the field containing the filter or not
doc.MailMerge.FieldMergingCallback = new HandleMergeField(true);
// Set up the handler any node changing during merging.
doc.NodeChangingCallback = new HandleNodeChangingMerge();
doc.MailMerge.ExecuteWithRegions(dataset);
public class HandleMergeField: IFieldMergingCallback
{
    bool mDeleteField;
    public HandleMergeField(bool deleteFieldWithFilter)
    {
        mDeleteField = deleteFieldWithFilter;
    }
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs args)
    {
        // Check if this field contains a filter defined by the equals sign in the field code.
        // If it does have a filter then evaluate it and if the value is differen to the filter, mark
        // the row it appears in to be removed
        if (args.Field.Result.Contains("="))
        {
            // Get the filter value between the "=" symbol and the end of the field result symbol
            string result = args.Field.Result;
            int index = result.LastIndexOf("=");
            string filterText = result.Substring(index + 1, result.IndexOf("╗", index + 1) - index - 1).Trim();
            // If the value is different mark it for removal
            if (!args.FieldValue.Equals(filterText))
                args.Text = "%RowRemove%";
            // The value is the same, if the filter field is set to be removed the set the value to merge to empty
            else if (mDeleteField)
                args.Text = "";
        }
    }
    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs args)
    {
        // Do Nothing
    }
}

public class HandleNodeChangingMerge: INodeChangingCallback
{
    void INodeChangingCallback.NodeInserted(NodeChangingArgs args)
    {
        Node node = args.Node;
        if (node.NodeType == NodeType.Run)
        {
            Run run = (Run) node;
            // Remove the row marked with the %RowRemove% tag
            if (run.Range.Text.Contains("%RowRemove%"))
            {
                Row row = (Row) run.GetAncestor(NodeType.Row);
                if (row != null)
                    row.Remove();
            }
        }
    }
    void INodeChangingCallback.NodeInserting(NodeChangingArgs args)
    {
        // Do Nothing
    }
    void INodeChangingCallback.NodeRemoved(NodeChangingArgs args)
    {
        // Do Nothing
    }
    void INodeChangingCallback.NodeRemoving(NodeChangingArgs args)
    {
        // Do Nothing
    }
}

If you have any further queries, please feel free to ask.
Thanks,

Hi Adam,

That code looks exactly like what I need, I have merged it in with my other existing FieldMerging code however it seems you did not post the attachment you reference in your latest reply.

Hi Joseph,
Thanks for your inquiry.
Please find the template attached to this post, I forgot to attach it last time.
Thanks,

This works fine for tables. However sometimes we do not use a table. The region contains some paragraphs only. How would we handle filtering in that case?

Are you planing to enhance the mailmerging with some more events so that such workarounds like you have described are not required anymore?

Hi
Thanks for your request. Yes, we plan to support sorting and filtering within regions by adding additional syntax. Your request has been linked to the appropriate issue. We will let you know once this feature is available.
By the way, why do not you use DataView approach suggested by Adam? I think this approach perfectly fit your needs.
Best regards,

Hi,

I guess to use a DataView I have to prefilter the data before I do merging. However I want the customer to specify the filter criteria via the document… like the thread starter has needed it. It is the same example. And the solution posted here is nearly perfect.

I needed this for a template where persons are categorized by some property. So I have created a table with say 2 columns. In each column I added a table which contains the persons of a specific category. So I needed some way to say “here starts the table of persons of category A” and “here starts the table of persons of category B”.
A quick solution would be to filter the persons by that category in code and perform a mailmerge with a list named “personsofcategorya” and a list named “personsofcategoryb”.
That would work and we have done it in the past this way. But now imagine you have n categories managed by the customer… I could create a list for each category and do a mailmerge in a loop or I could find some way to make the filter dynamic and picked up through the document.

I also do not use a DataTable as source I use List containing business objects. So creating a DataView is an additional step to implement.

Thanks for your help.

Kind regards,
TK

P.S. I am not sure if I should start a new thread or not I noticed if I have the same region twice in the document I have to run mailmerge twice… has this changed or is this behaviour up to date?

Hi
Thank you for additional information. As mentioned earlier, we plan to add additional syntax in one of future versions. This will allow sorting an filtering data in regions. We will let you know once this feature is available.
Regarding filtering business object, I think you can use LINQ query to filter and sort them.
https://docs.aspose.com/words/java/mail-merge-with-xml-data-source/
Regarding filling multiple regions with the same name, only the first region will be filled when you execute mail merge with regions. To fill the second region, you have to execute mail merge second time.
Best regards,

Hi,

I had the same issue and I have used to code shown above to implement filtering in our application and I would like to thank you very much for solving this problem for us.

There is however a small bug in the code in the comparrision being made.
The following line does not work if the fieldvalue and the filtervalue are not of the same type (for example number vs. string)

if (!args.FieldValue.Equals(filterText))

This problem is easily fixed by using:

if (!args.FieldValue.ToString().Equals(filterText))

Cheers, Bas.

Hi Bas,

Thanks for the correction. This problem occurs because args.FieldValue returns an object. Please let us know any time you have any further queries. We are always glad to help you.

Best Regards,

The issues you have found earlier (filed as WORDSNET-50) have been fixed in this .NET update and this Java update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

The issues you have found earlier (filed as WORDSNET-3797) have been fixed in this .NET update and this Java update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.