Skip to main content

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.

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

Reply


Terms & Conditions