It would sometimes be useful to have the ability to extract data only from a specific range of records instead of having to process an entire data stream. In this post, we look at an efficient and generic way of specifying a range of records in the OL Connect DataMapper.

Why record ranges are useful

Imagine you have a data stream that contains, say, 1 million records. Yep, it happens. But what if you don’t need to extract every single record? Or what if you want to process those records in batches of 10K records, for instance?

You could obviously run the data mapping operation once, then store the result (or keep it in the database) so that you can access individual records later, but that seems like a bit of an overkill if, in the end, you will only ever be using a few tens or hundreds of records or if you want generate your documents over a long period of time.

It would also be handy to be able to access individual records without having to store 1 million of them in the database.

To handle those various use cases, we need a way to specify that we only want to extract from a certain record up to another record, while ignoring all other records. We’ll be using runtime parameters to do that:

A basic record range

The easiest way to target a record range is by specifying a start and end index for the data stream. For instance, if our fromRecord runtime parameter has a value of 5 and our toRecord has a value of 7, then the DataMapper should only extract records 5, 6 and 7. If our toRecord parameter doesn’t have a value (i.e. its value remains set to 0), then it means there is no end record in the range, and the DataMapper should run starting at record 5, until the end. And if the fromRecord is set to 0, then there is no specific starting record in the range, so the DataMapper extracts data starting at record 1.

For efficiency’s sake, the data mapping process should look at the record range immediately, before any other step is performed, so it can determine quickly if it should skip over the current record (when the fromRecord value has not yet been reached) or stop the process entirely (when the current record index is larger than the toRecord value).

This is handled with a multi-case condition:

The left operand in that condition is simply to the value 1. We will code each case to return either 1 or 0 to signal whether or not the condition has been met.

The first case (eg branch) checks if the record index is smaller than the fromRecord value:

If the condition evaluates to true, then the rest of the branch is executed: the Skip to next record action step immediately jumps to the following record. If not, then the second case is evaluated, to check if the current record index has now gone past the toRecord value:

The code checks if a value was specified in toRecord and if so, whether that value is now smaller than the current record index. When that happens, the Stop data mapping action step is executed, thereby completely ignoring the rest of the data stream.

The Default case doesn’t do anything. The rest of the data mapping steps could be embedded in that branch, but I chose to implement them after the multi-case condition, to keep things simple and separate.

In Workflow, all you need to do is specify the proper values in the runtime parameters, as in this example, where we’re only extracting record # 45:

It’s not hard to imagine the efficiency gain of this process if, for instance, you just want to extract records 10,001 to 11,000 out of a total of 100,000 records! And even better, this technique can be used inside any data mapping process, regardless of the original data format.

A slightly more elaborate range

Now this technique is very useful to extract a specific record or to process records in batches inside a loop. But it relies on the record index, which may not necessarily be the information on which you want the range to be based. What if you want to process records based on a range of dates?

Let’s assume your records all have a date field and you only want to extract records when that field’s value falls between two specific dates. Let’s also assume that your data stream is sorted on that date field. Then, the logic remains exactly the same: the fromRecord and toRecord parameters will now contain start and end dates to define the range, and the conditions will need to be adjusted to compare dates:

Note that when comparing dates, make sure you use a format that allows simple conversion from string to date (yyyy-mm-dd is the ISO standard and it works perfectly).

But what if your data stream isn’t ordered by date?

Well in that case, you no longer need a range, but rather a filter. It’s almost the same logic, except you can never cut short the data mapping process because you still have to examine every single record to determine if it falls between the specified range of dates. Filters are less efficient than ranges, but they can be very useful as well. You would implement the filter using a standard JavaScript condition that checks if the record date falls outside the specified range, in which case the DataMapper would immediately skip to the next record:

let recDate = new Date(data.extract('./CUSTOMER[1]/INVOICE[1]/Date[1]'));
let fromDate = new Date(automation.parameters.fromRecord);
let toDate = new Date(automation.parameters.toRecord);
((recDate < fromDate) || (recDate > toDate))

How about multiple ranges?

There are use cases for being able to specify various ranges, just like you can when printing a Word document to a printer, for instance. You can specify things like:

1,5,7-9,15-25,42

Of course, this can be handled in the DataMapper as well, but it will require some changes in the logic and code that I have described above. I will leave it up to you to implement your own multi-range handling process. Feel free to share your techniques and comments below if you have found a different or better way of handling ranges and filters.

Leave a Reply