Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

1651 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Chattwo
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
e4a1e67364dd2383abd61e66619e3f2ef30a3f57.png

1 Reply 1
Leslie_Burke
7 - App Architect
7 - App Architect

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);