Hello community,
I am trying to automate the following problem:
I have a stock table "Stock" and a catalogue of products "Catalogue". Not all products from the Catalogue are listed in the Stock and the Stock changes on a regular basis. The availability of products from the catalogue is linked via "Link to another record" field to the stock the value used for this is the "articlenumber" So when the stock changes (e.g. we have a new product) I need to manually select the article number in the linked field for the new product.
So I created an automation that runs daily at 8:00 am and runs a script that is supposed to copy values from field "Articlenumber" to field "Articlenumber-linked" in the catalogue if a record with this article number is found in the stock. This is my script:
// Get the current table
const currentTable = base.getTable('Catalogue');
const linkedTable = base.getTable('Stock');
// Load all records in the current table
const query = await currentTable.selectRecordsAsync();
// Iterate through each record
for (const record of query.records) {
// Get the value from Column A
const valueFromColumnA = record.getCellValue('Articlenumber');
// Find the linked record in the Lagerbestand table based on the value from Column A
const linkedRecord = await linkedTable.selectRecordsAsync({
filterByFormula: `{Field in Stock} = '${valueFromColumnA}'`
});
// If the linked record is found, update the link in Column B
if (linkedRecord.records.length > 0) {
await currentTable.updateRecordAsync(record, {
'Articlenumber-linked': [{ id: linkedRecord.records[0].id }]
});
} else {
// If no linked record is found, you can choose to clear the value in Column B
// Uncomment the next line if you want to clear the value in Column B when no match is found
// await currentTable.updateRecordAsync(record, {'Column B': null});
}
}
My issue is that the field "Articlenumber-linked" is populated with a random articlenumber from the stock that doesn't match the product and when I try to change it to the value from ColumnA I get an error.
Can someone help me update this script so that "articlenumber-linked" equals "articlenumber" in catalogue if articlenumber is found in stock.
Let me know if I can assist with further clarification.
Thank you very much for your help!
Kind regards
Nik
