Hello!
I have three tables: Products, Bill of Materials & Pack Cards. Products & Bill of Materials are linked together, with up to 10 rows linked from Bill of Materials to each record in Products. These are manually created and do not change. The Pack Cards table is entered 2-3 times daily & is linked to both Products & Bill of Materials and contains entries of Products divided into up to 4 records based on the type of label indicated in a field (we ship plants and they require different stickers based on destination). These records look like duplicates if you are only looking at the product ID. Once I am done processing a round of Pack Cards with Page Designer, I delete the entries and re-import later, so the links are deleted. I have a script set up to run at the click of a button, but what it currently does is only links only one out of the four possible records with records in the Bill of Materials. My goal is to have all four linked up, but I’m not sure how. The end result would be that each of the pack cards records has at least two but usually 5/6 links to the Bill of Materials table, and the corresponding records in Bill of Materials would have at least 0 but up to 4 records linked. Not every record in Bill of Materials needs a link, but every record in Pack Cards needs a link, and both are connected with the same variable in Products. Also, I specified the view because there are over 4000 records in the Bill of Materials and only 500 are marked as required to be linked to the pack cards, so I’m trying to avoid going through all of the unnecessary records and having the script take too long. Also, the reason I’m not just doing a lookup from the link in Products for the Bill of Materials in Pack Cards is that I want to be able to create a table of info from the Bill of Materials in Page Designer and I can’t do that without a direct link.
I apologize for being wordy, I’m just not sure how to explain this otherwise.
// link button with script
let table = base.getTable(‘Pack Cards’);
let field = table.getField(‘Link To BOM’);
// When run from a button field, the script skips the prompt
// and automatically uses the button’s record.
let record = await input.recordAsync(‘Choose a record’, table);
let cellValue = record.getCellValue(field);
output.markdown(# You have selected ${record}.);
// set the table
let dashboardTbl = base.getTable(“Bill of Materials”);
let view = dashboardTbl.getView(“Inventory on Pack Cards - marked T”);
// get the table records
let dashboarddate = await view.selectRecordsAsync();
// loop through the records
for (let record of dashboarddate.records) {
// get info from field value "Products"
let dashboarddate = record.getCellValue("Product ID");
// only run on records where linked "Pack Cards" field is empty
if (record.getCellValue('Pack Cards') === null) {
//message output - fields that will be updated
output.text(`Linking pack cards to BOM for ${record.name} ${dashboarddate}`);
//Linked table
let otherTable = base.getTable("Pack Cards");
let otherTableQuery = await otherTable.selectRecordsAsync();
//Loop through linked table
for (let otherRecord of otherTableQuery.records) {
//match linked cell value to string specified
if (otherRecord.getCellValue("Product ID") === dashboarddate ) {
//get ID of linked field
let idYouNeed = otherRecord.id;
//Update original table with id value of linked field (must be an array)
dashboardTbl.updateRecordAsync(record, {
"Pack Cards": [{id: idYouNeed}]
});
}
}}}
I tried doing this script below, but it didn’t link anything at all.
// link button with script
let table = base.getTable(‘Pack Cards’);
let field = table.getField(‘Link To BOM’);
// When run from a button field, the script skips the prompt
// and automatically uses the button’s record.
let record = await input.recordAsync(‘Choose a record’, table);
let cellValue = record.getCellValue(field);
output.markdown(# You have selected ${record}.);
// set the table
let dashboardTbl = base.getTable(“Pack Cards”);
let view = dashboardTbl.getView(“Today ALL”);
// get the table records
let dashboarddate = await view.selectRecordsAsync();
// loop through the records
for (let record of dashboarddate.records) {
// get info from field value "Product ID"
let dashboarddate = record.getCellValue("Product ID");
//message output - fields that will be updated
output.text(`Linking pack cards to BOM for ${record.name} ${dashboarddate}`);
//Linked table
let otherTable = base.getTable("Bill of Materials");
let otherView = otherTable.getView('Inventory on Pack Cards - marked T')
let otherTableQuery = await otherView.selectRecordsAsync();
//Loop through linked table
for (let otherRecord of otherTableQuery.records) {
//match linked cell value to string specified
if (otherRecord.getCellValue("Products") === dashboarddate ) {
//get ID of linked field
let idYouNeed = otherRecord.id;
//Update original table with id value of linked field (must be an array)
dashboardTbl.updateRecordAsync(record, {
"Bill of Materials": [{id: idYouNeed}]
});
}
}}
