Back to all How-tos

Complement record using Metadata

This How-To shows you how to add information to your record using external sources, in the Automation tool – Workflow. For this purpose we will create empty fields which we will populate using the Metadata Tools.

Plus and Minus
The advantage of this method over others, such as grabbing external data in the DataMapper, is that you have the ability to open a single connection to your external data source, instead of establishing this connection for each record.

The disadvantage, is that you can only complement record-level data, not detail tables, because the detail tables are not available in the metadata in the current versions.

Step 1: Creating fields in DataMapper

In order for the record to be complemented, we first need to add each field inside the record. We do this in the Data Mapping configuration. For each field, follow these steps:

  1. Open your existing data mapping configuration or base yourself upon this example.
  2. Right-click on the record header and click Add a field
  3. Enter a name for the field, for example myfield
  4. Right-click on the new field and choose Set type then the type you want to use.
  5. Right-click again on the field and choose Default Value. Put in a default value that would correspond for what you want for this field.

Metadata only has String values, but they are automatically converted to the appropriate format when creating Content Items.

Step 2: Adding values to the fields in Workflow

 In order to test this method, we will need a template. You can use the following print template (can be used with the datamapper given above) open it in Connect Designer and click on the Synchronize Fields and Structure button in the Data Model tab. It would also be good to drag and drop the field (created in the datamapper in the section above) somewhere inside of the template in order to verify the success of our operation.

Now is the time to create a Workflow process containing at least 2 tasks:

  • Executed Data Mapping (you can use the datamapper given above with the added field)
  • Create [some] Content (Web, Email, Print) .

We’ll need to add some metadata management tasks right between the datamapping and content creation tasks.

Single field value for all records

For basic, global field additions, a single task is required: the Metadata Fields Management task.

For each field:

  1. In the Metadata Fields Management Properties dialog, click on the (+) sign under the list.
  2. Under Action, select Add
  3. Under Field Information: select the Document level. The Field name should correspond to the name of your field, preceded by the string _vger_fld_. For example, _vger_fld_myfield. The Field value should, obviously, be the value you want.

One last important thing: because we modified the metadata in the process, we need to make sure Connect takes these changes into account. Double-click the Create X Contents task and add a checkmark to the Update Records from Metadata.

Finally, if using the resources provided, you can add a Create Job task and a Create Output task (choose the default resources to create a PDF).

You can run a test using this process and you should notice that the field (ie. myfield) has been successfully modified.

This is what the process should look like:

A different value for each record

For field values that change on each record, we have to use a few more tasks into the mix:

  1. Add a branch after the Execute Data Mapping.
  2. Add a Metadata Sequencer task. Sequencing is based on 1 occurrence of the level ‘Document’.With this task, we will be able to process each record individually and to add the data to that record only.
  3. The field can be modified using a Metadata Fields Management task, the same way it is used in the above example.
  4. Now, we need to find a way to recuperate the metadata outside of the branch because the metadata changes made in the branch are not maintained outside of the branch (you can observe this by trying in debug mode). To do this, we need to add the following tasks:
    1. Right before the branch (after the Execute Data Mapping task) use a Set Job Infos and Variables task that stores the ‘recordset id’ in a local variable.
    2. In the branch, after the Metadata Fields Management task, add an Update Data Records task, which updates the Metadata source in order to keep the changes made for each record.
    3. Finally, add a Retrieve items task right directly after the branch. This task should retrieve a Record Set by a Record Set ID. The Record Set ID is defined by the local variable used in the Set Job Infos and Variables task to save the ‘recordset id’ before the branch.

But where does the data come from?

Complementing each record means that the data for each record has to come from somewhere. In the example screenshot above, there is no indication of how to obtain this data.

Here are a couple of ways to obtain this data that I can think of:

Microsoft® Excel lookup

The Lookup in Microsoft® Excel Documents task can complement one metadata field by using the data in another field, making it a great method to do this quickly. For example, an Excel spreadsheet could retrieve a client’s email from a Client ID that is present in the record, from the original data source.

enter image description here

This single task actually replaces both the Sequencer and Fields Management tasks above. It can thus be simply added between the Execute Data Mapping task and Create X Contents task (without the branch). For more information about this task, check out its task properties in the documentation.

A Script writing to a local variable

Obviously, using a script to complement data is a little more complex, but it does provide with a whole lot more freedom in where that data comes from. The following script does not need to be put in a branch. It loads the metadata in memory, opens a connection to the database, and then loops through each document in the metadata. For each document, it queries the database to get the Email from the CustomerID, and at the end saves the metadata back to Workflow.

var dbloc = "C:/lookup/lookup.mdb";

var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var src = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbloc + ";";
conn.Open(src);

var myMeta = new ActiveXObject("MetaDataLib.MetaFile");

myMeta.LoadFromFile(Watch.GetMetadataFileName());

Watch.Log(myMeta.job().group(0).count, 2);


for(var i = 0; i <= myMeta.job().group(0).count; i++) {
  var thisDoc = myMeta.job().group(0).document(i);
  var customerID = thisDoc.fieldByName("_vger_fld_CustomerID");
  var query = "SELECT `email` FROM [info] WHERE `customer_id` = '" + customerID + "'";
  rs.Open(query,conn,1,1);
  rs.MoveFirst();
  thisDoc.fields.add2("_vger_fld_Email", rs.fields("email"), 2);
}

myMeta.saveToFile(Watch.getMetadataFileName());

Conclusion

Obviously there is always more than one way to deal with changing metadata. These examples will certainly either give you the solution, or at least put you on the right track!

Leave a Reply

Your email address will not be published. Required fields are marked *