I have a form with ID and status, and would like to save status history in a subtable on the form each time the status is changed.
I have the following code in Pre-workflow, but no record is added to the subtable. I’ve tested the logic by creating records in other sheets - the code inside the if block is executed.
What am I doing wrong?
/**
* Key Field: 1000143
* Status subtable key: 1000146
* Field Name Field Id
* - - - - - - - - - - - --------
* ID : 1000139
* Status : 1000140
* Status : 1000141 (in subtable)
* Timestamp : 1000145 (in subtable)
*/
// constants
var FORM_KEY = 1000143;
var FORM_STATUS = 1000140;
var SUBTABLE_KEY = 1000146;
var SUBTABLE_STATUS = 1000141;
var SUBTABLE_TIMESTAMP = 1000145;
var query = db.getAPIQuery("/lookuplink/6");
var entry = query.getAPIEntry(param.getNewNodeId(FORM_KEY));
var oldStatus = param.getOldValue(FORM_STATUS);
var newStatus = param.getNewValue(FORM_STATUS);
if (oldStatus != newStatus) {
var newRow = entry.getSubtableRootNodeId(SUBTABLE_KEY, -100);
entry.setSubtableFieldValue(SUBTABLE_STATUS, newRow, newStatus);
};
First of all, every time when you finish the entry works, remember to use entry.save() to make sure the data is really saved. Not pretty sure how you want to add data in subtable. If creating a new entry in subtable, you can use setSubtableFieldValue directly with second parameter negative number. If modifying existed entries, using getSubtableRootNodeId is correct but the second parameter should be subtableRowIndex, instead of -100 or any negative number. You can use a for loop to get subtableRowIndex and execute getSubtableRootNodeId afterwards, setSubtableFieldValue, and save finally.
Thanks James. Those were the last pieces I needed to understand.
I’ve added the below script to Post-workflow for the sheet - it now has a status history subtable that I can use for data analysis/metrics
/**
* Key Field: 1000143
* Status subtable key: 1000146
*
* Field Name Field Id
* - - - - - - - - - - - --------
* ID : 1000139
* Status : 1000140
* Status : 1000141 (subtable)
* Timestamp : 1000145 (subtable)
*/
// constants
var FORM_PATH = "/lookuplink/6";
var FORM_KEY = 1000143;
var FORM_STATUS = 1000140;
var SUBTABLE_KEY = 1000146;
var SUBTABLE_STATUS = 1000141;
var SUBTABLE_TIMESTAMP = 1000145;
// get old and new status values to test if status changed
var oldStatus = param.getOldValue(FORM_STATUS);
var newStatus = param.getNewValue(FORM_STATUS);
if (oldStatus != newStatus) {
// setup ready for update
var query = db.getAPIQuery(FORM_PATH);
var entry = query.getAPIEntry(param.getNewNodeId(FORM_KEY));
// create formatted
var today = new Date(new Date().getTime() + account.getTimeZoneOffset());
function pad2(n) { return n < 10 ? '0' + n : n }
var fmtTimestamp = today.getFullYear() + "/" +
pad2(today.getMonth() + 1) + "/" +
pad2(today.getDate()) + " " +
pad2(today.getHours()) + ":" +
pad2(today.getMinutes()) + ":" +
pad2(today.getSeconds());
entry.setSubtableFieldValue(SUBTABLE_STATUS, -100, newStatus);
entry.setSubtableFieldValue(SUBTABLE_TIMESTAMP, -100, fmtTimestamp);
entry.save();
};
