Subtract single value from each number in a field where cells contain multiple values

[Matthew_Chattwo]

I have an inventory tracking challenge that I’m having difficulty with

In my line of work clients order resources. Each resource contains a number of items from my stock room. I want to track inventory of items automatically based on resource orders that clients make. I think I need a formula that says "hey! a resource has been ordered.That resource contains items A,B,C…Let’s subtract “quantity items used” from “item inventory level.”

Note: The quantity of items used will vary based on the number of participant the client wants to use the resource for.

I haven’t gotten very far with this but the image below gives you the idea. I want to subtract 40 from the stock levels contained within records 114, 110, 107, 105, 104 and 112. I’ve learned a lookup isn’t the way to go but now I’m stuck.

[Leslie_Burke]

Adding this back here for future people looking for a script to deduct a quantity sold from an On Hand Inventory level. Script I'm using....adjust your fields & table names as necessary:

// Define input variables
let inputConfig = input.config();
let orderLineItemId = inputConfig.orderLineItemId;
let soldQty = inputConfig.soldQty;

// Initialize the base and tables
let orderTable = base.getTable("Order Line Items");
let productsTable = base.getTable("Products");

async function updateInventory() {
    // Fetch the order line item record
    let orderLineItemRecord = await orderTable.selectRecordsAsync({
        recordIds: [orderLineItemId]
    });

    // Get the linked product record ID
    let productRecordId = orderLineItemRecord.records[0].getCellValue("Product")[0].id;

    // Fetch the linked product record
    let productRecord = await productsTable.selectRecordsAsync({
        recordIds: [productRecordId]
    });

    // Calculate the new inventory
    let currentInventory = productRecord.records[0].getCellValue("On Hand US Inventory");
    let newInventory = currentInventory - soldQty;

    // Update the product inventory
    await productsTable.updateRecordAsync(productRecordId, {
        "On Hand US Inventory": newInventory
    });
}

// Run the function to update inventory
updateInventory().catch(console.error);