The whole setup for making Bill Of Materials (BOM) for different products.
I have 4 Tables...
- Table1: Main BOM
- Table2: Materials lines
- Table3: Material list (Synchronised from another base)
- Table4: Product list (Synchronised from another base)
- Table1 is linked to Table4 in a one to one relationship. Basically selecting a product to create a BOM.
- Table1 is also linked to Table2 in a one to many relationship. The materials in the BOM with a quantity and material comments etc.
- Table2 is linked to Table3 - selecting a materials from a standardised list of materials which is then given a quantity and a comment etc.
That is in broad strokes what is happening.
The problem is often Products is very similar so it's tempting to duplicate each BOM, however to avoid confusion I want every material line to only link to one BOM. So instead of creating a new BOM from scratch we have a button linked up to a script. It takes the line where the button is pressed create another line underneath push in the the link to Table4, but then it goes to Table 2 make duplicates of all the line items of all the material lines in the BOM thats being duplicated, but instead of linking to the old BOM it'll link to the newly created BOM. I don't know if that makes sense? And is enough to go on?
Even weirder when I do it it seems when the system is "warmed" up it dosen't throw the error as frequently. But the problem comes when the error happens half way through the execution then there is a lot of clean up do to as there will be random Line Items and a newly created BOM with only bits of material lines created.
This is my script:
// Table and field names for Table 1
let tableA = base.getTable("BOM");
let linkedMaterialItemsField = "Add Materials";
let linkedGarmentField = "Select Product";
let expectedDeliveryField = "Expected Delivery";
let launchDateField = "Launch Date";
let processCommentsField = "Process comments";
// Table and field names for Table2
let tableB = base.getTable("LAUNCH SHEET MATERIALS");
let typeField = "Type";
let chooseMaterialField = "Choose a material";
let freeTextField = "Free Text";
let positionUseField = "Position/Use";
let selectFreeColorField = "Select Free Color";
let amountField = "Amount";
// Start the script by informing the user
output.markdown("# Script Started: Duplicating Records");
try {
// Get the record in Table A where the button was pressed
output.text("Please select a record from Table A...");
let record = await input.recordAsync('Pick a launchsheet you want to dublicate', tableA);
// Check if the record is valid
if (record) {
//output.text(`Record selected: ${record.name}`);
output.text(`Creating a copy Launch sheet... ${record.name}`);
// Copy the specified fields from the original record to the new record in Table A
let newRecordId = await tableA.createRecordAsync({
[linkedGarmentField]: record.getCellValue(linkedGarmentField),
[expectedDeliveryField]: record.getCellValue(expectedDeliveryField),
[launchDateField]: record.getCellValue(launchDateField),
[processCommentsField]: record.getCellValue(processCommentsField)
});
output.text(`New launchsheet created...`);
// Get the linked records from Table B using the correct field name
let linkedRecords = record.getCellValue(linkedMaterialItemsField);
if (linkedRecords && linkedRecords.length > 0) {
output.markdown(`### ${linkedRecords.length} launch sheet materials will be copied:`);
// For each linked record in Table B, create a copy and link to the new record in Table A
let newLinkedRecordIds = [];
for (let i = 0; i < linkedRecords.length; i++) {
let linkedRecord = linkedRecords[i];
let recordInB = await tableB.selectRecordAsync(linkedRecord.id);
if (recordInB) {
output.text(`Copying linked materials ${i + 1} of ${linkedRecords.length}`);
let newLinkedRecordId = await tableB.createRecordAsync({
[typeField]: recordInB.getCellValue(typeField),
[chooseMaterialField]: recordInB.getCellValue(chooseMaterialField),
[freeTextField]: recordInB.getCellValue(freeTextField),
[positionUseField]: recordInB.getCellValue(positionUseField),
[selectFreeColorField]: recordInB.getCellValue(selectFreeColorField),
[amountField]: recordInB.getCellValue(amountField)
});
//output.text(`New linked record created in Table B with ID: ${newLinkedRecordId}`);
newLinkedRecordIds.push({id: newLinkedRecordId});
}
}
// Update the newly created record in Table A to link to the new records in Table B
await tableA.updateRecordAsync(newRecordId, {
[linkedMaterialItemsField]: newLinkedRecordIds // Use the variable as the key
});
//output.text(`Linked materials has successfully been copied and linked to new launchsheet.`);
} else {
output.text("It seems there are no linked materials to copy.");
}
output.markdown("# Launchsheet successfully copied!");
} else {
output.text('No launchsheet selected.');
}
} catch (error) {
output.markdown(`## Error: ${error.message}`);
output.text("An error occurred during the script execution. Please try again if the problem persist please call Kim");
}
