How to loop M3 activity node in PFI

Here is a solution to iterate thru the result set of an M3 activity node in ProcessFlow Integrator (PFI).

Background

M3 API transactions of type Get return one record. For example, CRS610MI.GetBasicData returns information about the specified customer number.

M3 API transactions of type List typically return multiple records. For example, CRS610MI.LstByNumber typically returns thousands of records.

Problem

Unfortunately, the M3 activity node in PF Designer does not include an iterator:

As a consequence, we cannot iterate individually thru the records of the M3 output, nor can we use other activity nodes inside each iteration. For example, there are valid scenarios where I would like to do something at each iteration: send an email, assign a value to a variable, execute SQL, call a Web Service, or do something else, even though there could be thousands of iterations. It’s not possible by default.

Other nodes

There are other activity nodes that appropriately have an iterator. For example, the SQL Query activity node includes an iterator, so we can call other activity nodes at each iteration to do something with each record of the SQL result set:

Goal

The goal is to find a workaround to iterate thru the result set of an M3 activity node, as illustrated in this fake screenshot:

Workaround 1

One workaround is to iterate thru the M3_output.row array by using JavaScript code in an Assign activity node.

for (var i = 0; i < M3_output.row.length(); i++) {
    var row = M3_output.row[i];
    // do something
    row.fieldName1;
    row.fieldName2;
    row.fieldName3;
}

Where fieldName is the output field of the result set, for example: CONO, CUNO, CUNM, etc. Alternatively, we can use the other syntax for accessing JavaScript Object properties: row[“fieldName“];

But there are several drawbacks to this workaround. First, it requires programming, which counters the graphical paradigm of PF Designer. Second, we cannot call other activity nodes from that JavaScript code. Third, PF Designer throws the false positive error message that we can ignore: “ReferenceError: M3_output is not defined”.

Workaround 2

Another workaround is to use an Assign activity node to serialize the JavaScript object into a String with delimiters (for example a semi-colon), and then two DataIterator activity nodes to parse that String by row and by column. It’s a bit cumbersome but it works.

for (var i = 0; i < M3_output.row.length(); i++) {
    var row = M3_output.row[i];
    csv += row.fieldName1 + ';' + row.fieldName2 + ... + ';' + row.fieldNameN + '\n';
}

The drawback – in addition to being disconcerting for non JavaScript programmers – is that it will take a lot of CPU and memory to process a big result set because appending a String to a String is exponentially slower.

Workaround 3

The third workaround is to create a for loop using a Branch activity node. Indeed, a for loop can be transformed equivalently into a recursive function with an if statement.

Yes branch:

i < M3_output.row.length() - 1

No branch:

i >= M3_output.row.length() - 1

This is my recommended solution.

LPA

Note that this is not a problem anymore with the newest Lawson Process Automation (LPA) and Lawson Process Designer as the M3 transaction node now correctly includes an iterator:

That’s it!