Skip to content

Updating a Table System Variable from a Script

A mapping script can read a Table system variable, change one row, and save the table back. QIE has no per-row update, so the script loads the whole table as a CSV message, edits it with node paths, and writes the whole table back with qie.setVariable.

Before you start

  • Check Allow variable to be updated from scripts on the variable. Without it, qie.setVariable throws an error. See Programmatic Updates.
  • Use this for tables that change occasionally. Concurrent writes are not serialized: if two messages update the same table at the same time, the last write wins and the other change is lost. For a table that changes on every message, use an external database table or the Shared Cache instead.

The script

This example keeps the Providers table (columns PVID, NPI, LastName, FirstName) in step with an MFN^M02 staff master file feed. An add (MAD) or update (MUP) record either changes the provider's row or adds one. Other record-level event codes leave the table alone. The script assumes one MFE/STF record per message.

var TABLE_NAME = 'Providers';
var eventCode  = message.getNode('MFE-1');
var providerId = message.getNode('MFE-4.1');

if (StringUtils.equals(eventCode, 'MAD') || StringUtils.equals(eventCode, 'MUP')) {
   // 1. Load a copy of the table as a CSV message; the first row holds the column names
   var table = qie.parseCSVString(qie.getVariable(TABLE_NAME) + '', true, '"', ',', true);

   // 2. Find the row for this provider
   var targetRow = 0;
   for (var row = 1; row <= table.getRowCount(); row++) {
      if (StringUtils.equals(table.getNode('PVID', row), providerId)) {
         targetRow = row;
         break;
      }
   }

   // 3. Update the row, or add a new row when the provider is not in the table yet
   if (targetRow === 0) {
      targetRow = table.getRowCount() + 1;
      table.setNode('PVID', providerId, targetRow);
   }
   table.setNode('LastName', message.getNode('STF-3.1'), targetRow);
   table.setNode('FirstName', message.getNode('STF-3.2'), targetRow);

   // 4. Save the whole table back
   qie.setVariable(TABLE_NAME, table.toString());
}

How it works

  • qie.getVariable(TABLE_NAME) + '' turns the table into CSV text with the column names on the first line. Parsing that text gives the script its own copy to edit. Change the table only through this copy, never through the object qie.getVariable returns.
  • setNode(column, value, row) writes one cell. A row number one past the last row adds a new row. Columns the script doesn't set, such as NPI here, keep their current value, or stay blank on a new row.
  • Spell column names exactly as they appear in the table. setNode with an unknown column name adds a new column instead of failing.
  • qie.setVariable saves the table immediately. Other channels see the new value, including through qie.doTableLookup, the next time they read the variable.