Future Inventory & Ship Out Dates

[4pine]

Hello! We operate in an industry dealing with perishable goods, where the timing of receiving products (INBOUND) and the customer's requested shipping date (OUTBOUND) are crucial factors. We are trying to create a base structure/formula so we can keep track of inventory.

Our primary challenge is to accurately determine the current inventory of raw materials on any given day. This inventory depends on the items we currently have on hand and any anticipated future deliveries of raw materials (INBOUND) for the upcoming week, provided they arrive before the customer's designated ship-out date (OUTBOUND Ship Date). Furthermore, our system must consider existing customer orders placed for the week and determine which incoming delivery of raw materials these orders will draw from.

For example, suppose we receive an INBOUND delivery of 200 units of raw material on Monday, with another 400 units arriving on Thursday. If a customer places an order on a Sunday evening and requests 200 units to be shipped on Friday, we'll allocate 200 units from Thursday's INBOUND delivery to the order scheduled for the Friday OUTBOUND ship date and we should still have 200 units available (from the Monday delivery) to fulfill any additional orders for that week plus then 200 left over from Thursday's inbound delivery to fill any shipping out on Thursday or Friday.

Now, if a customer selects a ship-out date for Friday and requests 600 units, we should be able to fulfill that order by using the 400 units from Thursday's delivery and the 200 units from Monday's delivery. 

We need a solution that can confirm that when a customer chooses a specific ship-out date (e.g., Friday), our inventory for that date is accurately calculated. This calculation should consider future INBOUND product deliveries and account for any sales already scheduled for those future dates.

Current Base Structure:

• PO Table - Includes RECEIVE date
• Items (Raw Materials) Table - Items we hold in inventory
• PO-ITEM Table (joining table) - Items and quantities in each PO from our vendors
• Product Table - Products we sell
• Product-Item Table (joining table) - How many of each Item/Raw material is in each Product we sell
• Customer Order Table - Includes an order date and a ship date (they get to choose when we should ship/deliver the product)
• Order-Product Table (joining table) - What products and their quantities are in each order
• Accounts - Customers & Vendors

[Dimitris_Goudis]

Hey @4pine, 

Having already build inventory tracking systems and ERPs using Airtable I would recommend the followings: 

- Build a process to submit each purchase in a table {Purchases}. This table has to be linked with Items
- The purchased quantity has to update the Available quantity in items table using automation
- Then build the similar process for the sales and using a second automation you will update the field of available quantity

On Items table you gonna need to have the following tracking fields:
- Available quantity 
- Commitment quantity -> sold but not delivered yet, those items are still in your warehouse before your picker collects them. Ideally you don't want to sale those pics
- Ordered quantity (optional)-> quantity you purchased but not arrived

For any kind of question please do not hesitate to text back.

Yours sincerely,
Dimitris Goudis