Some types of data are more difficult to handle than others, in particular when each data value is of variable length or isn’t in a fixed location. But even mainstream data types can sometimes be challenging to data map using the standard steps. See how version 2021.1 of the DataMapper greatly improved its scripting capabilities to allow you to handle these types of data files more efficiently.

Note: all examples shown use resources that are attached at the bottom of this article.

The story

Let’s start by using a sample data file that would normally require a bit of gymnastics to extract from:

01|INV1867748|411350|CU63047838|2021-02-24
02|Twitterlist|188 Drewry Street|RaymondVille|AB|Canada|L5A 5D2
03|Dionysus|Moiser|1(555)873-0623|dionysus.moiser@example.com
04|53674|Thule Railing M450|199.95|3|3|0|599.85
04|364678|Fischer Ski Boots|111.2|3|3|0|333.6
04|389316|Hestra Mens Racing Gloves XL|119.77|4|4|0|479.08

These types of data files are fairly common. Each line has an identifier (the first two characters) that determines what the line contains. In our example, 01 identifies a new invoice, with some general information about the invoice. 02 is the company information, 03 is the contact information and 04 indicates a line item (there can be multiple line items, obviously).

Note how each line has a different number of fields, which prevents us from treating this file like a CSV, and how each repeating field doesn’t necessarily start on the same column, which makes it challenging in standard Text mode.

Here’s the datamodel we want to fill with our data mapping config:

Previously on OL Connect…

With previous versions of OL Connect, processing this data file would require something similar to this:

For each line, the process checks the first two characters, and depending on their value, it branches out accordingly and extracts the proper fields. This requires us to have already created record properties to store the entire content of the line being read and to convert it to an array. The Extraction task in each branch then simply extracts each array value into its corresponding field.

Not overly complex, but then again our data is not that complex either. Still, it should illustrate the difference with the new feature quite nicely.

With OL Connect 2021.1…

Here’s how you could do the same thing starting with OL Connect 2021.1:

Yep, that’s all there is to it! Now of course, you probably think that the Extract everything task is a complex script. Well… you be the judge:

I pasted an image to avoid line wrapping, which looks messy, but you can get a better look at the script in this article’s companion resources. But even without studying the code in detail, you can probably tell that it’s extremely basic. Let’s take a look at what the code does.

The whole magic occurs through the new record.set() and record.tables.tablename.addrow() methods. Both of them allow you to pass an object that can be a subset of your data model. All properties present in the object are extracted. Note that the object’s properties must already exist as fields in the data model, since adding fields dynamically would break the uniform data model paradigm.

So for instance, you could create a script like this one to extract two values to your data model:

var myObject = {
   ContactFirstName: "John",
   ContactLastName: "Doe"
}
record.set(myObject);

and that would extract two fields (ContactFirstName, ContactLastName), while leaving any already extracted field untouched.

Similarly, you can add line items to detail tables:

var myItem = {
   id: "00001",
   description: "ACME Tool",
   price: 6.99,
   qty: 3,
   total: 20.97
}
var index = record.tables.items.addRow(myItem);

And before you ask, yes you can also change existing detail lines since each detail line is itself a record, and therefore supports the set() method. So for instance, if we want to change the description of the product we just added, we could use the following code:

record.tables.items[index].set({description:"OL Tool"});

These features give you much more flexibility in the way you process your data and in some cases, they will give the DataMapper a serious boost in performance since everything can be accomplished inside a single task.

But wait, there’s more!

We could have stopped there and this would already have been a great feature, but we wanted to address one additional item.

The DataMapper already allows you to specify how many copies of each record you want to extract. This is often required in order to simplify template design when different copies of the same document have to go to different recipients (e.g. Customer, Administration, Archiving).

However, there is no way for the Designer to know with which copy of the record it is currently working because each copy is an exact clone of the original record. So the Designer cannot personalize each document according to its intended recipient.

Starting with version 2021.1, the DataMapper allows you to make as many changes as you like to each copy of the record. For instance, the following code shows how you can personalize each record:

record.copies = 3;
var oneRec = {CopyFor:'',Note:''};

for(var i=0;i<record.copies;i++){
  switch (i) {
    case 0: 
      oneRec.CopyFor = "Customer";
      oneRec.Note = "Payment due in 30 days";
      break;
    case 1: 
      oneRec.CopyFor = "Administration";
      oneRec.Note = "Customer always late, due date set to 30 days";
      break;
    case 2: 
      oneRec.CopyFor = "Archive";
  }
  record.setCopy(i, oneRec); 
}

Here, the magic occurs with the record.setCopy() method. Just like the record.set() method we mentioned before, the setCopy() method takes an object that contains a subset of the datamodel and applies the values in that object to the specified copy of the record (where copy 0 is the original record). This code can be inserted anywhere in your config; regardless of where you insert it, it will only get executed at the very end of the DM process for each record.

Notice how, in the example above, the Note field is set to different values on the Customer and Administration copies. This allows the Designer to display different content from the same record.

Note that in order to change values in the record copies, the fields you modify must either have a default value or have an extraction task earlier in the process that actually assigns a value to them.

Conclusion

Starting with version 2021.1, the DataMapper’s scripting environment gives you full control over the content of the fields and records you extract.

Have fun experimenting!

Resources