AboutJamey Key Expertise NEW and INTERMEDIATE USERS. WHAT CAN FILEMAKER PRO DO FOR ME? I have been using FileMaker products for over 10 years and have basic knowledge in all areas of FileMaker database set up and use. I`m not prepared to answer questions about complex situations or calculations but can help beginners and intermediate users set up and use FileMaker databases.
Experience Have been using FileMaker since 1989 in our business. We have 10 users on the network who use these databases every day. Our largest databases have had over 30,000 records at one time. In 1995 one of my files was chosen as "Most Unique Database" in a contest sponsored by Claris, maker of FileMaker. Recently I have developed a set of files for new computer system installed at our church.
Expert: Jamey Key Date: 5/26/2008 Subject: Table relations: ordering system updating stocks depending on date of order
Question 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
when 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?
Thanks, Nk
Answer Nik;
Yes this is too complex for my brain too. I want you to email me directly and if possible send a copy of your file, or a small sample copy and include a layout with some markings on it of what you would like the end result to be. Lets experiment with it till we get it like you want it.
