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);
Terms & ConditionsAccessibility statement