Hi.
I have a post workflow from my booking (parent) that creates a new subbooking (subtable of booking) entry. I had to program it myself because I do not want to open up a new sheet which the built in Javascript helper does.
The booking has some formulas also attached to the subbooking so I need to update formulas the booking record when the new subbooking has been added. I can not get this to work…
This is how the booking sheet looks after the post workflow has been run.
Anyone who has the solution?
The code looks like follows:
* Key Field: 1000350 // Subbooking 1000384
* Subbooking nr subtable key: 1000384
* Invoicenr subtable key: 1000462
* Ref subtable key: 1000717
* Field Name Field Id
* - - - - - - - - - - - --------
* Booking nr 1000350: 1000278
* Short Name 1000386: 1000670
* Next subbooking nr 1000392: 1000395
* Traffic ID 1000389: 1000286
* Subbooking created 1000804
* From 1000374: 1000677
* Address ID 1000397: 1000683
* Comments 1000398: 1000684
* Street 1 1000375: 1000679
* Street 2 1000399: 1000792
* Summary text 1000400: 1000685
* Countrycode 1000401: 1000789
* Postcode 1000376: 1000686
* City 1000377: 1000688
* Loading from 1000430: 1000698
* Loading to 1000431: 1000699
* Loadref 1000434: 1000700
* To 1000403: 1000678
* Address ID 1000404: 1000689
* Comments 1000405: 1000690
* Street 1 1000406: 1000691
* Street 2 1000407: 1000692
* Summary text 1000408: 1000693
* Countrycode 1000409: 1000790
* Postcode 1000410: 1000695
* City 1000412: 1000694
* Delivery from 1000432: 1000701
* Delivery to 1000433: 1000702
* Deliveryref 1000435: 1000703
* Ref subtable key: 1000717 // subbooking : 1000427
* Ref 1000414: 1000704
* Cll 1000415: 1000705
* Type 1000416: 1000706
* Description 1000417: 1000707
* LDM 1000418: 1000708
* Net Weight 1000437: 1000709
* Gross Weight 1000420: 1000710
* Temperature 1000428: 1000712
* ADR 1000422: 1000713
* ADR Description 1000423: 1000714
*/
var rootNodeId = param.getNewNodeId(1000350);
var query = db.getAPIQuery("/booking/2");
var entry = query.getAPIEntry(rootNodeId);
var createNewSubbooking = param.getNewValue(1000804);
if (createNewSubbooking === "Yes") {
entry.setFieldValue(1000804, "No")
entry.save(); // save before run function to avoid loop
var bookingMain = {
"Booking nr": param.getNewValue(1000278),
"Short Name": param.getNewValue(1000670),
"Next subbooking nr": param.getNewValue(1000395),
"Traffic ID": param.getNewValue(1000286),
"From": param.getNewValue(1000677),
"Address ID From": param.getNewValue(1000683),
"Comments From": param.getNewValue(1000684),
"Street 1 From": param.getNewValue(1000679),
"Street 2 From": param.getNewValue(1000792),
"Summary text From": param.getNewValue(1000685),
"Countrycode From": param.getNewValue(1000789),
"Postcode From": param.getNewValue(1000686),
"City From": param.getNewValue(1000688),
"Loading from": param.getNewValue(1000698),
"Loading to": param.getNewValue(1000699),
"Loadref": param.getNewValue(1000700),
"To": param.getNewValue(1000678),
"Address ID To": param.getNewValue(1000689),
"Comments To": param.getNewValue(1000690),
"Street 1 To": param.getNewValue(1000691),
"Street 2 To": param.getNewValue(1000692),
"Summary text To": param.getNewValue(1000693),
"Countrycode To": param.getNewValue(1000790),
"Postcode To": param.getNewValue(1000695),
"City To": param.getNewValue(1000694),
"Delivery from": param.getNewValue(1000701),
"Delivery to": param.getNewValue(1000702),
"Deliveryref": param.getNewValue(1000703)
};
//Construct array for senting to addSubBooking function
var bookingLineItems = [];
var lineItemsSize = entry.getSubtableSize(1000717);
for (i = 0; i < lineItemsSize; i++) {
var row = {
"Ref": entry.getSubtableFieldValue(1000717, i, 1000704), // Ref: 1000704
"Cll": entry.getSubtableFieldValue(1000717, i, 1000705), // Cll: 1000705
"Type": entry.getSubtableFieldValue(1000717, i, 1000706), // Type: 1000706
"Description": entry.getSubtableFieldValue(1000717, i, 1000707), // Description: 1000707
"LDM": entry.getSubtableFieldValue(1000717, i, 1000708), // LDM: 1000708
"Net Weight": entry.getSubtableFieldValue(1000717, i, 1000709), // Net Weight: 1000709
"Gross Weight": entry.getSubtableFieldValue(1000717, i, 1000710), // Gross Weight: 1000710
"Temperature": entry.getSubtableFieldValue(1000717, i, 1000712), // Temperature: 1000712
"ADR": entry.getSubtableFieldValue(1000717, i, 1000713), // ADR: 1000713
"ADR Description": entry.getSubtableFieldValue(1000717, i, 1000714) // ADR Description: 1000714
};
bookingLineItems.push(row); // Add the row object to the array
};
// Send details to addSubBooking function
addSubBooking(bookingMain, bookingLineItems);
}
entry.recalculateAllFormulas();
entry.save();
//sleep function to get new nr of subBookings
function sleep(milliseconds) {
var start = new Date().getTime();
while (new Date().getTime() - start < milliseconds) {
// Busy-wait loop
}
}
// Add subbooking
function addSubBooking(bookingMain, bookingLineItems) {
var query = db.getAPIQuery("/booking/4");
var entry = query.insertAPIEntry();
entry.setFieldValue(1000350, bookingMain["Booking nr"]);
entry.setFieldValue(1000386, bookingMain["Short Name"]);
entry.setFieldValue(1000392, bookingMain["Next subbooking nr"]);
entry.setFieldValue(1000389, bookingMain["Traffic ID"]);
entry.setFieldValue(1000374, bookingMain["From"]);
entry.setFieldValue(1000397, bookingMain["Address ID From"]);
entry.setFieldValue(1000398, bookingMain["Comments From"]);
entry.setFieldValue(1000375, bookingMain["Street 1 From"]);
entry.setFieldValue(1000399, bookingMain["Street 2 From"]);
entry.setFieldValue(1000400, bookingMain["Summary text From"]);
entry.setFieldValue(1000401, bookingMain["Countrycode From"]);
entry.setFieldValue(1000376, bookingMain["Postcode From"]);
entry.setFieldValue(1000377, bookingMain["City From"]);
entry.setFieldValue(1000430, bookingMain["Loading from"]);
entry.setFieldValue(1000431, bookingMain["Loading to"]);
entry.setFieldValue(1000434, bookingMain["Loadref"]);
entry.setFieldValue(1000403, bookingMain["To"]);
entry.setFieldValue(1000404, bookingMain["Address ID To"]);
entry.setFieldValue(1000405, bookingMain["Comments To"]);
entry.setFieldValue(1000406, bookingMain["Street 1 To"]);
entry.setFieldValue(1000407, bookingMain["Street 2 To"]);
entry.setFieldValue(1000408, bookingMain["Summary text To"]);
entry.setFieldValue(1000409, bookingMain["Countrycode To"]);
entry.setFieldValue(1000410, bookingMain["Postcode To"]);
entry.setFieldValue(1000412, bookingMain["City To"]);
entry.setFieldValue(1000432, bookingMain["Delivery from"]);
entry.setFieldValue(1000433, bookingMain["Delivery to"]);
entry.setFieldValue(1000435, bookingMain["Deliveryref"]);
entry.setFieldValue(1000385, "Draft");
// Loop through bookingLineItems to add subtable rows
for (var i = 0; i < bookingLineItems.length; i++) {
var lineItem = bookingLineItems[i];
var subtableRootNodeId = (-i-1);
// Add subtable field values using the correct field IDs
entry.setSubtableFieldValue(1000414, subtableRootNodeId, lineItem["Ref"]); // Ref (1000414)
entry.setSubtableFieldValue(1000415, subtableRootNodeId, lineItem["Cll"]); // Cll (1000415)
entry.setSubtableFieldValue(1000416, subtableRootNodeId, lineItem["Type"]); // Type (1000416)
entry.setSubtableFieldValue(1000417, subtableRootNodeId, lineItem["Description"]); // Description (1000417)
entry.setSubtableFieldValue(1000418, subtableRootNodeId, lineItem["LDM"]); // LDM (1000418)
entry.setSubtableFieldValue(1000419, subtableRootNodeId, lineItem["Net Weight"]); // Net Weight (1000419)
entry.setSubtableFieldValue(1000420, subtableRootNodeId, lineItem["Gross Weight"]); // Gross Weight (1000420)
entry.setSubtableFieldValue(1000428, subtableRootNodeId, lineItem["Temperature"]); // Temperature (1000428)
entry.setSubtableFieldValue(1000422, subtableRootNodeId, lineItem["ADR"]); // ADR (1000422)
entry.setSubtableFieldValue(1000423, subtableRootNodeId, lineItem["ADR Description"]); // ADR Description (1000423)
}
entry.loadAllLinkAndLoad(); // Have to do this here otherwise it doesnt fetched linked values
entry.save();
}