Script to update inventory current stock when incoming purchase status set to 'received'

[achamess]

I run a research lab. I'm setting up Airtable to manage purchase requests and inventory. 

I have two tables:

1. Purchase Catalog, with fields 'Item' and 'Current Stock'

2. Order Line Items, with relevant fields fields "Quantity' and 'Status'. 

'Order Line Items' is a table that represent specific instances of wanting to puchase something. Purchase Catalog represents different items one would purchase. The two tables are linked. 

When we received an item, I will change the status in 'Order Line Items' to 'Received', at which point, I want the field 'Quantity' (from Order Line Items) to add to the value in field 'Current Stock' (from Purchase Catalog). 

I asked ChatGPT to make me a script and it looks like it would work but it's not doing anything to change 'Current Stock'. Could someone help me figure out how to do what I'm seeking?

// Define input variables
let tableLineItems = base.getTable("Order Line Items");
let tableCatalog = base.getTable("Purchase Catalog");

// Get the record ID from the automation trigger
let recordId = input.config().recordId;

// Field names
let statusField = "Status";
let quantityField = "Quantity";
let catalogLinkField = "Purchase Catalog";
let currentStockField = "Current Stock";

// Fetch the specific record from the Order Line Items table
let record = await tableLineItems.selectRecordAsync(recordId);

// Log the record details for debugging
console.log(`Record ID: ${recordId}`);
console.log(`Status: ${record.getCellValue(statusField)}`);
console.log(`Quantity: ${record.getCellValue(quantityField)}`);
console.log(`Linked Catalog Record: ${JSON.stringify(record.getCellValue(catalogLinkField))}`);

// Ensure the record exists and the status is "Received"
if (record && record.getCellValue(statusField) === "Received") {
    let quantity = record.getCellValue(quantityField);
    let catalogRecord = record.getCellValue(catalogLinkField);

    // Check if there is a linked record in Purchase Catalog and a valid quantity
    if (catalogRecord && quantity) {
        let catalogRecordId = catalogRecord[0].id;

        // Fetch the linked record from Purchase Catalog
        let catalogRecordData = await tableCatalog.selectRecordAsync(catalogRecordId);

        // Log the current stock before updating
       console.log(`Current Stock before update: ${catalogRecordData.getCellValue(currentStockField)}`);

        if (catalogRecordData) {
            let currentStock = catalogRecordData.getCellValue(currentStockField) || 0;

            // Update the Current Stock in Purchase Catalog
            await tableCatalog.updateRecordAsync(catalogRecordId, {
                [currentStockField]: currentStock + quantity
            });

            // Log the updated stock value
            console.log(`Updated Stock: ${currentStock + quantity}`);
        }
    }
}

[TheTimeSavingCo]

Hmm, I'm curious why a rollup field with "SUM(values)" doesn't work here?  It'd do the exact same thing as what you want the script to do, except without the script

If you'd still like to troubleshoot the script, could you provide a read-only invite link to a duplicated copy of your base with some example data?  https://support.airtable.com/docs/adding-a-base-collaborator#adding-a-base-collaborator-in-airtable

Troubleshooting stuff like this is a lot easier when one can test the code directly, view the console log outputs etc

[achamess]

I'm open to alternatives. Can you propose how to do that? Because the value I want to roll up is not in the same table. I don't use Roll ups a ton, but maybe I should.

But this brings up another matter. The current stock of an item will change over time, not just by new items being received, but also current items being used up. I'm not sure how to create a solution such that I can also tell Airtable when I've used an item, such that it changes the 'Current Stock' to reflect my usage. Any ideas?

[TheTimeSavingCo]

Yeah rollups would be really useful here.  With reference to your question about the usage of an item, I usually solve this with a "Movements" table, where each record represents either incoming or outgoing quantities for that product:

Link to base