External sorting in Job Creation now provides a document sheet count field to an external sorting application. This is useful when using third party postal sorting applications from within Job Creation, because postal sorting typically requires the mailpiece size to be known. This sneak peek explains that feature, but also gives a short howto on using external sort in Job Creation.

Many postal services offer discounts when organizations send sorted mail, and a sizable number of postal services even require bulk mail to be sorted. Sometimes this is simply a sort by postal code, but more often there is a specific sorting algorithm for which the postal service or a third party offers a program or a web service.

Job Creation in OL Connect can sort documents by itself, but it can also run an external program to sort a job. This allows running a mail sorting program from within Job Creation. As long as that external program reads a CSV file, and produces a CSV file as output, it can be used in Job Creation.

While this functionality has been available for a long time, there was an important detail missing: mail sorting almost always needs the size of the mailpiece, i.e., the number of sheets in an envelope, but it was not possible to pass the document size as a field in the CSV for an external sorting program.

In version 2023.1, we have added an item <Document Sheet Count> to the list of fields that can be selected for the CSV input file used by an external program. It’s a small addition, but it makes life a lot easier for those who have to deal with postal sorting.

So, where do you find this, and how to use it? That’s the next part of this article. If you are already familiar with the external sort feature, you may just want to go have a play with it now.

To have a look at external sorting, create a new job preset, or open an existing one (this has changed too, but that’s explained elsewhere). Make sure you have a data mapper config at hand, because that’s needed. In the job preset wizard, check the option Apply filtering and sorting to record selection. Press Next until you are on the Sorting Options page, and choose Use external sort.

The lower left of this page has the list of fields Fields to Export. Press the Add button, and the field list that is shown now has an item <Document Sheet Count>.

To play around with this, you’ll need an external sorting program. Turns out that sorting CSV’s is a one-liner with PowerShell, so we’ll use that. To make life even easier, I have created a PowerShell script that wraps this one-liner with easy to use parameters and that does numeric sorting (see mpower-shells on GitHub for the sort_csv.ps1 PowerShell script).

Running a PowerShell script from OL Connect’s external sorting requires specifying the PowerShell executable as the Command, and provide the script through the -File parameter:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "C:\OL Connect\MyProject\SortCSV\sort_csv.ps1"

The script needs an input file and a output filename. The ${input} and ${output} variables can be used for this; OL Connect will substitute these for filenames, so it also knows the output filename. With those parameters the command looks like this (the script expects the -In and -Out parameters):

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "C:\OL Connect\MyProject\SortCSV\sort_csv.ps1" -In "${input}" -Out "${output}"

Tip: if your external sort program requires specific names or locations for the input or output file, wrap it in a script to handle renaming and moving to the filenames that OL Connect passes.

Finally, you can specify numeric sorting instead of alphabetical by passing the -N parameter as well, which the script understands. If your sort field is not called Size, use -SortField to pass another field name.

This script also needs a header row in the CSV file, so make sure to check First row of sort data has field names, in your job preset.

Next, you can specify the fields in the output CSV in the lower left of the external sort wizard page in your job preset. This script returns all the columns, so you can just add all the columns you had for the input. Note that the first column must be the field that was mentioned as Record ID Field at the input side. It defaults to RecID.

If a mail sorting program also does address cleansing, you can check Include in meta data for the columns with the cleansed address information, which makes them available to fix address in your output during Output Creation. Use additional content to add the cleansed addresses on the address carriers. If an address is already there, cover it with a white image, which can also be done with Output Creation’s additional content.

Obviously, apart from the way to run a PowerShell script as an external sort program, and the use of ${input} and ${output}, all of the parameters and the input and output files, are specific to how this script works. An actual mail sorting program will come with its own parameters and input and output requirements. So make sure to follow its instructions on how to run it.

Final notes on working with external sort: when trying to make a new program work with it, make sure you can run it on a command prompt or in a PowerShell shell, before trying to run it from within Job Creation. Also, wrapping such a program in a command file or PowerShell script usually makes life easier. To get test data for such an external program, it can be convenient to run a script in Job Creation’s external sort, and have it copy its input CSV somewhere. The sort_csv.ps1 script featured here can easily be adapted for this.