Formula Help for managing a reduction in original order quantity

Platform discussions Special workflows & use cases
,
1

Hi Folks -

Does anyone know what the following formula would need to be in Monday for us to keep track of recurring orders?

We want to be able to do something that looks like the following:

(original order amount)-(shipment qty)-(shipment qty), etc…until we hit zero and at that point or shortly before then, a trigger is pulled notifying us that the PO has run out.

Thanks

1 Like
2

Hey @brianwbates :wave:

Would this information be kept track of all within the same item? If so, then you can absolutely create a formula that will continue to subtract different columns from an original “Inventory” column to show the remaining number left:

image
image2536×572 67.4 KB

However, this would require you to only use those original number columns or to add any newly added number columns to the formula as necessary.

Additionally, you may find some helpful information in one of our formula support articles:

If you are looking to create a formula that calculates orders across several items, then a formula will not be possible as the column only works with one item at a time.

However, you can keep an “Inventory” Item that shows the original amount of inventory you have as a negative number. From there, every additional order will show how many of that item you have left within the column summary:

image
image1140×542 68.2 KB

You can also choose to keep the inventory item as positive and have each order show the numbers as negative but in either case, you will need to use the opposite options of + and - numbers between the original inventory item and the order items to make sure the column summary is showing the accurate number of inventory left.

We also have this helpful resources for keeping track of your inventory and orders that you can refer to at any time!:

I hope this helps! Remember to mark as solved if this answers your question :slight_smile:

Cheers,
Jenna

3

Thank you very much, but this still does not help us:

I’m trying to find a way to track orders.

For example, we get an order for 50 widgets.

Then, over the course of many months, we ship multiple orders against the original 50 (eg 10 widgets one week, 5 widgets the next week, 8 widgets, etc…), until we reach ZERO.

We need to be able to track the total shipped each time against the original order (50), so we know when to stop shipping against the original order.

I don’t think we need a board to do this unless there is an intuitive way to go about it. Alternatively, a formula column would work, wherein column, there would be a formula that we could manually update, each time we ship against the original order (50) and the formula would then calculate the remaining widgets from the original order that is left to ship.

Any suggestion on what that formula would look like in Monday? Thanks

4

Monday.com doesn’t have a built-in formula that directly subtracts quantities over time, but you can achieve a similar outcome using a combination of columns and automations. Here’s a possible approach:

Columns:

  1. Original Order Amount: This column holds the initial total quantity of the order. (Number)
  2. Remaining Quantity: This column will dynamically track the remaining quantity after each shipment. (Number)
  3. Shipment Qty: This is where you record the quantity shipped for each instance. (Number)
  4. Shipment Date (Optional): If you want to track shipment dates, you can add this as a Date column.

Automations:

  1. When a Shipment Qty is added:
  • Trigger: When a value is added to the “Shipment Qty” column.
  • Action 1: Update “Remaining Quantity” with a formula:=([Original Order Amount] - {Shipment Qty}) This formula subtracts the newly added “Shipment Qty” from the “Original Order Amount” and stores the result in “Remaining Quantity.”
  1. When Remaining Quantity reaches a threshold (Optional):
  • Trigger: When the value in the “Remaining Quantity” column falls below a specific threshold (e.g., 10).
  • Action 1: Send Notification:
    • This action will send an alert (email, SMS, etc.) notifying you that the PO is running low. You can customize the notification message.

Additional Tips:

  • You can create a Status column that displays messages like “Active” when the remaining quantity is positive and “Fulfilled” when it reaches zero. Use a formula based on the “Remaining Quantity” to update the Status automatically.
  • Consider using Integrations with accounting software if you need more complex inventory management features.

Limitations:

  • This approach requires manual entry of shipment quantities.
  • Monday.com doesn’t offer negative numbers, so reaching zero in “Remaining Quantity” indicates the order is complete.

This is a basic example, and you can customize it further based on your specific needs. Remember to test your automations thoroughly before relying on them for critical notifications.