Dwindling Value Lists based on Dates

[jbray86]

Hello to all. This will be my first post on the forum, so I apologize if this isn't in the right place.

Over the past few months I have been slowly building a database which will eventually track pieces of equipment as they go out into the field. The main purpose of the database is to keep us from "double booking" equipment for different projects which happen at the same time.

Comment has helped me through to this point with some older explanations/examples. I am not sure that they are exactly what I need, but have seemed like steps in the right direction.

- To prevent items from being used on two different jobs at the same time, I set up a self join relationship as outlined in Topic #198229. Specifically this post: http://fmforums.com/forum/showpost.php?post/303839/

- Ultimately, I didn't want the users to even have the option of seeing the booked equipment, so I used the example in Topic #185835 on Dwindling Value Lists. Specifically this post: http://fmforums.com/forum/showpost.php?post/246150/

Now I am at the crossroads. After recreating these examples as to really understand what is going on, I need to merge the ideas. Perhaps this is not the best way to achieve my goal, so I am hoping that someone here may have some advice on the matter.

I thank you for any tidbit of information you might have, and I really thank Comment for his examples as they have taken me this far.

[jbray86]

Alright, I am going to post up some more information about my project, because I am sure that there is a way this can be done, but my personal knowledge of filemaker is not quite extensive enough for it to be obvious.

Here is a run down of what I am trying to do:

The company I work for is trying to keep track of different pieces of equipment and when they are in use. The task I have is to build a database that allows a piece of inventory to be assigned to a project. This is a relatively simple task. The difficulty comes in setting up parameters and calculations that prohibit that piece of equipment from being assigned to another project which occurs during the same range of dates.

The theory of this database looks like this:

There are Projects with specific dates and pieces of Inventory. They each have a table. The inventory needs to be broken down into two types, Affiliated Inventory and Available Inventory. When a piece of gear is assigned to a project, it needs to become unavailable to any other projects during that date range.

In short:

1) I need to create a list of affiliated equipment from all records in a particular date range.

2) I then need to build a dwindling value list that excludes these values when the new project falls within that specific date range.

Part 1 looks like this: if (Project::DateOut ≤ Affiliated::DateIn) and (Project::DateIn ≥ Affiliated::DateOut); if true then add to list of affiliated inventory; or if false, don't add to list.

Part 2 will need to be a value list of items which ≠ affiliated inventory. The result is the remaining unaffiliated inventory which can be selected in the drop down list.

I understand how to build a DVL of inventory that is affiliated and available when it falls within one record. The real problem I have is in restricting the selection of inventory items from multiple records based upon the date range.

If anyone can offer any advice, I would appreciate it.

Thanks.