Selecting and Updating Process Data in SI

Storing values into process data can be really useful.  However, when you “update” process data, it is an update TO process data.  It is not an update OF process data.  If you have multiple updates, it will create multiple entries.  Multiple updates will NOT overwrite the value in process data.

Doing a “select” will return the first value from process data.  Selecting a value, manipulating the value, then updating process data with the new value will create multiple entries.  Every time you do a select from process data, it will return the initial value.

This is fine if you’re only using two values.  If you have more than two values, then you need to use the last function within your xpath.

Here’s an example of concatenating a list of values separated by commas:
string[100] test_string;
test_string = "";
select xpathresult into test_string from processdata where xpath = "//test[last()]";

if len(test_string) > 0 then
  begin
    test_string = test_string + ", " + #FIELD;
  end
else
  begin
    test_string = #FIELD;
  end

update processdata set xpathresult = test_string where xpath = "test";

This rule will do a select from the last <test> node in process data.

It then checks the length of the value returned.  If the length is greater than 0, it means that it found a value already in process data.

It will then take that value, add a comma, a space, and the value in the field.

If the length is 0, that means there was not a value in process data.  Since it’s the first value to be updated, it will not add a comma and just set the variable to the field.

It then updates process data with new value in the string variable.

The data for #FIELD in this example has the values of “one”, “two”, “three” and “four”.

Process data gets updated and looks like:

  <test>one</test>
  <test>one, two</test>
  <test>one, two, three</test>
  <test>one, two, three, four</test>

You can see how it continues to add nodes to process data, with each new node containing the next value.

Here’s an example of updating process data with a running counter:
string[100] next_number_string;
integer next_number_integer;
next_number_string = "";
next_number_integer = 0;

select xpathresult into next_number_string from processdata where xpath ="//next_num[last()]";
If len(next_number_string) > 0 then
  Begin
    next_number_integer = atoi(next_number_string);
    next_number_integer = next_number_integer + 1;
  End
Else
  begin
    next_number_integer = 1;
  end

ntoa(next_number_integer,next_number_string);
update processdata set xpathresult = next_number_string where xpasth = "next_num";

This rule does a select to get the current value.  If the length is greater than 0, it means there already was a value.  Since the select returns a string, we need to convert it to an integer first with atoi.

We then increment the value that was returned.

If the length is 0, there was no value in process data yet.  We assign a value of 1, to the integer variable instead.

We then convert the value back to a string, and update process data with the new value.

Process data gets updated and looks like this:

  <next_num>1</next_num>
  <next_num>2</next_num>
  <next_num>3</next_num>
  <next_num>4</next_num>

You can see how it just increments the counter for each update to process data.

That’s really all there is to it.  From the little testing I’ve done, you can use full paths in your xpath, or the //<node name>, just as long as you add [last()] to the end, it returns the last.  I’ve seen some rather complex xpath statements, so I’m not sure how well it works with the more complex ones, or what tweaks you would need.  If you come across a statement that doesn’t seem to work, let us know in the comments.

In both of these examples, we’re just updating right back to process data and that’s it.  In real world scenarios, you’re going to probably use the variables elsewhere in your extended rules or replace the variable with a #FIELD to store the value directly into a field.

The next time you need to keep a running variable in process data, you can simply use the [last()] function and it’ll make your mapping life that much easier.

Click below to find out more what Appleyard’s EDI Services can do for you

Leave a Reply

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

*