Update test data table rows

The Update/setTestData extension updates one or more rows in a Virtuoso test data table from inside a journey.

This is useful when your test needs to refresh, change, or reuse test data while the journey is running.

Parameters:

env required, the Virtuoso environment to use: PRODUCTION, UK, US, or APP2;
token required, the Virtuoso API token. Store this as an environment variable where possible;
table required, the name of the test data table to update;
data required, the JSON data to write into the table;
row required, the row number or row numbers to update;
projectId required, the Virtuoso project ID where the data table exists.

Note: Row numbers start from 0. For example, the first row is 0, the second row is 1, and so on. The data value must be valid JSON, and the JSON keys must match the column names in the test data table.

NLP usage

Use the extension in a journey by calling Update/setTestData with the execute command. Pass each value to the matching extension input using as inputName.

Note: Script inputs are passed as text in Virtuoso. Because of this, pass the data value as a JSON string and pass multiple row numbers as a JSON array string, for example "[0,1,2]".

To update a single row in a test data table:

execute "Update/setTestData" using "PRODUCTION" as env, "$setTestDataToken" as token, "CustomerData" as table, '{"CustomerName":"John Smith","Status":"Active"}' as data, "0" as row, "$projectId" as projectId

To update multiple rows with the same values:

execute "Update/setTestData" using "PRODUCTION" as env, "$setTestDataToken" as token, "CustomerData" as table, '{"Status":"Inactive"}' as data, "[0,1,2]" as row, "$projectId" as projectId

You can also use Virtuoso variables to make the same step reusable across different environments, tables, rows, or projects:

execute "Update/setTestData" using "$environment" as env, "$setTestDataToken" as token, "$tableName" as table, "$jsonData" as data, "$rowIndex" as row, "$projectId" as projectId

Example setup using variables before calling the extension:

store value "CustomerData" in $tableName
store value '{"CustomerName":"John Smith","Status":"Active"}' in $jsonData
store value "0" in $rowIndex
execute "Update/setTestData" using "$environment" as env, "$setTestDataToken" as token, "$tableName" as table, "$jsonData" as data, "$rowIndex" as row, "$projectId" as projectId

For example, if your table has columns called CustomerName and Status, the JSON should use the same names:

{"CustomerName":"John Smith","Status":"Active"}

This extension requires the following resource:

https://cdnjs.cloudflare.com/ajax/libs/axios/0.21.1/axios.min.js

The extension should be configured as:

Run asynchronously: Yes
Scope: Global

Limitation: This extension updates rows by row number. Make sure the row exists before running the journey. If a row number does not exist, or if the JSON contains a column name that is not present in the table, the update may fail.

Add the extension to your Virtuoso instance

Select the region that matches your Virtuoso account.

Add extension to Virtuoso →	Add extension to Virtuoso Free Trial →
Add extension to Virtuoso US →	Add extension to Virtuoso UK →

View source

Last updated: 15/09/2023

Resources:

https://cdnjs.cloudflare.com/ajax/libs/axios/0.21.1/axios.min.js


  
const endpoints = {
  PRODUCTION: "https://api.virtuoso.qa/api",
  US: "https://api-US.virtuoso.qa/api",
  UK: "https://api-UK.virtuoso.qa/api",
  APP2: "https://api-app2.virtuoso.qa/api"
};

const BASE_URL = endpoints[env];

if (!BASE_URL) {
    doneError("Invalid environment " + env + ". Please use PRODUCTION, US, UK, or APP2");
}

async function getTable() {
    const endpoint = `${BASE_URL}/projects/${projectId}/dataTables?envelope=false&token=${token}`;
    const dataTables = await axios.get(endpoint)
        .catch(error => {
            doneError('Error fetching data table id: ' + error);
        });
    const dataTable = Object.values(dataTables.data).find(value => value.name === table);
    if (!dataTable) return doneError(`Table ${table} not found`);
    return dataTable;
}

function getColumnId(table, columnName) {
    const column = Object.values(table.attributes).find(attribute => attribute.name === columnName);
    if (!column) {
        doneError(`Could not find column ${columnName} in table`);
    }
    return column.id;
}

function getColumnName(table, columnId) {
    const column = Object.values(table.attributes).find(attribute => attribute.id === columnId);
    if (!column) {
        doneError(`Could not find column with ID ${columnId} in table`);
    }
    return column.name;
}

async function getTableValues(tableId) {
    const endpoint = `${BASE_URL}/testdata/tables/${tableId}/values?envelope=false&token=${token}`;
    const { data: tableValues } = await axios.get(endpoint)
        .catch(error => {
            doneError('Error fetching data table values: ' + error);
        });
    return tableValues;
}

async function updateTable(tableId, newValues, modifiedDate) {
    const endpointUrl = `${BASE_URL}/testdata/tables/${tableId}/values?envelope=false&token=${token}`;
    const requestData = {
        modifiedDate: modifiedDate,
        newTestDataValues: newValues
    };
    await axios.put(endpointUrl, requestData)
        .catch(error => {
            doneError('Error updating table');
        });
}

function updateTableValues(table, rows, oldValues, data) {
    let dataObj = {};
    try {
        dataObj = JSON.parse(data);
    } catch (e) {
        doneError("Could not parse data, make sure that it's correctly formatted in JSON");
    }
    for (const [key, value] of Object.entries(dataObj)) {
        const columnId = getColumnId(table, key);
        try {
            rows.forEach(row => {
                if (!oldValues[row]) oldValues[row] = {};
                oldValues[row][columnId] = value;
            });
        } catch (e) {
            doneError(`Error updating value ${key} in row ${row} of table`);
        }
    }
    return oldValues;
}

async function setTableData(data, rows) {
    const tableObj = await getTable();
    const oldValues = await getTableValues(tableObj.id);

    // Convert single row input (string or number) to an array with one element
    const rowInput = typeof rows === 'string' || typeof rows === 'number' ? [rows] : rows;

    const newValues = updateTableValues(tableObj, rowInput, oldValues, data);
    await updateTable(tableObj.id, newValues, tableObj.modifiedDate);
    done("Table Updated");
}

const rowInput = typeof row === 'string' ? JSON.parse(row) : [row];
setTableData(data, rowInput);

Update/setTestData Extension

Update test data table rows

NLP usage

Comments