Have spent whole afternoon now trying to figure this out.
I have a booking sheet where I have a subtable with invoice(s).
I want to update link and load on all subtable items when I save.
I know there is a button for this, but I want to learn the recipe for other use cases I know I will have later on
By the way - the 500 record limit if you keep values in sync, what does that actually mean? Can the sheet contain max 500 records or is it max 500 fields that can be synced?
The code I have put together:
/**
* This comment is auto generated on 2024/09/05 13:52:43, and it reflects the field definition at this given time.
* If you need the current field definitions for your database, please go to "Start" => "Account Setup" => "DB Maintenance" => "Download Data Dictionary"
* AP_Name:wentro
* Key Field: 1000350
* Subbooking nr subtable key: 1000384
* Invoicenr subtable key: 1000462
* Field Name Field Id
* - - - - - - - - - - - --------
* Booking nr : 1000278
* Status : 1000279
* Temperature : 1000372
* Customer name : 1000281
* Customer ID : 1000282
* ADR : 1000370
* Booking date : 1000285
* Traffic ID : 1000286
* Traffic name : 1000287
* Cll : 1000284
* Billing Address : 1000291
* Summary invoice : 1000518
* LDM : 1000289
* Invoice reference : 1000293
* Net weight : 1000292
* Next Sequence : 1000396
* Oil surcharge code : 1000452
* POD : 1000294
* Gross weight : 1000296
* Currency : 1000521
* Oil surcharge name : 1000453
* Attachments : 1000295
* Chargeable weight : 1000297
* Credit invoice : 1000519
* Current surcharge : 1000454
* Contribution : 1000522
* Subbookings : 1000394
* Next subbooking nr : 1000395
* Subbooking nr : 1000392
* Loading from : 1000430
* Loading to : 1000431
* Loading Country + Postcode : 1000465
* Delivery from : 1000432
* Delivery to : 1000433
* Delivery Country + Postcode : 1000466
* Cll : 1000436
* LDM : 1000380
* Net weight : 1000437
* Gross Weight : 1000381
* Chargeable weight : 1000429
* Temperature range : 1000382
* ADR : 1000383
* Sequence : 1000373
* Summary : 1000463
* POD : 1000391
* Contribution : 1000523
* Invoice : 1000455
* Invoicenr : 1000456
* Currency : 1000469
* Contribution : 1000459
* Status : 1000460
* Invoicetype : 1000461
* Invoice summary : 1000464
* [b]1[/b] Sender : 1000326
* [b]2[/b] Consignee : 1000327
* [b]5[/b] Annexed documents : 1000328
* [b]12[/b] Instructions : 1000330
* [b]13[/b] Incoterms : 1000329
* Created By : 1000346
* Creation Time : 1000347
* Last Modified By : 1000348
* Last Modified Time : 1000349
*/
// Get entry
var rootNodeId = param.getNewNodeId("1000350");
var query = db.getAPIQuery("/booking/2");
var entry = query.getAPIEntry(rootNodeId);
// Get invoice nrs from subtable
var subTableLength = entry.getSubtableSize(1000462);
var invoices = [];
for (var i= 0; i < subTableLength; i++) {
var a = entry.getSubtableFieldValue(1000462, i, 1000456);
invoices.push(a);
}
// Send invoices array to findInvoiceRootNode function and receive root note id's
var recordArray = findInvoiceRootNode(invoices);
// Update link and load for all invoices
for (i = 0; i < recordArray.length; i++) {
var recordId = recordArray[i];
var query2 = db.getAPIQuery("/ekonomi/1");
var entry2 = query2.getAPIEntry(recordId);
entry2.loadAllLinkAndLoad();
entry2.save();
}
function findInvoiceRootNode(invoiceNr) {
var response = [];
var keyField = 1000456; // Keyfield of Invoice sheet
var invoiceArrayLength = invoiceNr.length;
for (i = 0; i < invoiceArrayLength; i++) {
var query = db.getAPIQuery("/ekonomi/1");
query.addFilter(keyField, '=', invoiceNr);
var result = query.getAPIResultsFull();
var entry = result.next();
while(entry) { //There should be only one entry but anyway...
response.push(entry.getRootNodeId());
entry = result.next();
}
}
return response;
}