Jump to content
Salesforce and other SMB Solutions are coming soon. ×

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


Recommended Posts

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

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use