Table relations: ordering system with running stock count based on ordering date

[nikmagnus]

First a description of the database, then my question.

 

I have an ordering system for fruit trees

- as the trees are seasonal, I have a fixed stock and sell out. Each tree has a stock amount

- the orders are accepted from Feb-June and are sent by mail and email. The orders are entered into filemaker weekly an often not entered by the date the order has been sent, eg some get delayed, others lost and entered later. Each order has a date received field.

- We send out the orders in a first -in - first serverd, so sort the orders by the date_received field

- The database is an orders table, a portal into a order line by line table, a tree table for stock numbers, a portal into a transaction line by line table

 

What I'm trying to do is have a running stock count so I can see which trees

are out of stock. The simple way it works at the moment is that I have a formula which counts all the tree orders and subtracts it from the stock, and

the result is +ve or -ve. When it is -ve I've run out of stock.

 

I'd like a system where if I look at an early order it will have tree numbers

ordered up to that date, rather than everyone's order. I feel like I need to have a relationship between the order table and the tree stock table that is sorted chronologically in the order_received date. I could do it with a formula that finds only that tree in all the orders, sorts the date_received and then checks that the order is less or greater than the stock number in the list. I'd prefer to have something in the background that keeps a running tally that shows up then I'm putting in a new order that lets me know if a tree is out of stock or not depending on the date of the order.

 

My gut feeling is that I can do this with a relationship but the idea is too complex for my brain to juggle at once.

 

Could you give me any tips / guidance?

 

Thanks, Nik