What Version of FileMaker are you using?
What you have in place looks like a good starting point as you have set up two "many to many" relationships with join tables. This allows a given "document" to be linked to multiple events and for an event to link to many documents. The same is true for your items in inventory. An inventory item can be linked to multiple events and an event can link to multiple inventory items.
But managing the check out process so that you can't "double book" an item in inventory will be tricky.
There are two basic issues:
- The dates an item is reserverved are a range of days and doublebooking is possible for two events if even one day in that range conincides for both events.
- Judging by the presence of a QTY field, each record in inventory represents multiple instances of that item. To determine availability, you thus have to check the quantity available over each day from start date to finish date against the quantity needed for that event.
Step one is that you'll need to Modify the Calendar table to add two more fields:
ResDate: the date of one day in the Start Date to Finish Date range of dates for the event.
Qty: The number of this item needed on that day.
When you select an Item from inventory for your event and reserve it, you create one record in Calendar for each day that it is reserved and record the quantity required. These records can be generated by a script so that you do not have to enter them manually. This does allow you to make an item of inventory unavailable or only a portion of the days in an event and you can also vary the number of items needed for different days. It will be up to you to decide if such flexibility is useful or not to your business.
With such a structure to the calendar table, you can use a new Tutorial: What are Table Occurrences? of Calendar with this relationship as a way to check on availability for a given item required for a given event:
Events::gItem_ID = Calendar|AvailCheck::Item_ID_FK AND
Events::Start Date < Calendar|AvailCheck::ResDate AND
Events::Finish Date > Calendar|AvailCheck::ResDate
This might be the expression that you use to check avilability with that relationship:
Max ( Calendar|AvailCheck::Qty ) < Inventories::Qty
On the other hand, you may want to check availability on a day by day basis for each item if you can release an item from one event for use with another before the Finish date. (Maybe 500 chairs are needed on day 1, but not Days 2, 3 and 4...)
Thanks for the deatailed answer PhinModJunk,
I am Using Filemaker 12 advanced.
I have attached the new Relationship chart, please let me know if I understood corectly.
Also I have a few more questions.
How am I using Events::gItem_ID ?
I am confused on how I would query the Inventories table in order to add items to a show, for example, I think what I need to do is:
- start with a script that searches all records in the calandar table in the date range of the event
- search inventories and somehow filer all the Item Id's that were returned in the previous search
What I would like to do is still be able to see all the items but somhow show they are in use and by what... maybe I am getting ahead of myself here though. I still don't have the basic framework.
How am I using Events::gItem_ID ?
gItem_ID would be a field with global storage. (in my naming convention, I start global fields with a lower case g to help identify the global fields.)
If you select an Item ID in this field, you can then check the related records in Calendar AvailCHeck to see if that item is available in the quantities that you will need for the current event record in Events. (You make this check from the context of the events table.)
This only allows you to check one item at a time so it may not be the best option for your final design of your database, but it's a good way to start for making sure that all the other parts of the system are working correctly. If desirable, you could even use this relationship to pull up a summary report with of records from the related table with one row of data for each date where items are reserved (can be reserved from any number of events) with a total for the number reserved for use on that day.
Once you can check availability one item at a time, you may find it more useful to be able to set up an additional table of related records where you build the list of items and quantities that you need for an event in the planning stages and then you can use a more sophisticated version of this approach to check availablity for each item in that list either all at the same time or item by item as you add them to the list. Once you have finished building the list and can confirm that you have all the needed items available for the dates that you need them, you can use a script to generate all needed records in the Calendar AvailCheck table to actually reserve them.
Please note that you do not need scripts that search the table in order to check availability. That's what this relationship is for. It allows you to select an item and see immediately if sufficient quantity are available for the dates during which it will be needed for your event.