Use one table where each record represents a Crock. Relate it to a table where each record represents one of these usage time periods:
Crocks::__pkCrockID = UsageSchedule::_fkCrockID
A pair of date fields in UsageSchedule can record the date range for that usage event.
See this link if the above notation is unfamiliar: Common Forum Relationship and Field Notations Explained
I've got all this built already. I've been trying to figure out the best way to create a calculation here that'll indicate the availability of every crock over a selected date range. That's where I'm a little stuck.
There are several ways to match records by date range:
1) Define this type of relationship:
Table1::Date1 < Table2::Date AND
Table1::Date2 > Table2::Date
2) Define this type of relationship:
Table1::DateList = Table2::Date
DateList would be a text field that lists all the dates from date 1 to date 2 in a list of return separated values. There are very simple custom functions that can generate such a list and it can be done with a script if you do not have FileMaker Advanced to install the custom function. The list based date range method is supposed to evaluate more quickly than one using the inequality operators.
3) Use a filtered portal where you use either of the above two methods in a portal filter expression.
for a list of dates, use the FilterValues function to see if a date is a member of that list.
This last method simplifies your relationship graph but is limited to displaying results. They can't be used in calculations nor exported.This method requires using FileMaker 11 or newer.
4) Use an SQL query in an ExecuteSQL function call to check on a date range. This will also use inequality operators and requires FileMaker 12.
I remembered that my main problem was actually getting keys to calculate but have them indexed. I'd liked to have done it without scripting but I don't think it was possible with how I've got things set up here.
It can be done with a stored indexed calculation also. If there is a fixed interval to your date ranges, you don't even need a custom function to do so.
How can I get a stored calc when the calc is pulling data through a relationship? FM appears to not be able to do this. All my dates are being calculated in Usage Schedule from yet another table.
Why are the dates in a different table than Usage Schedule?
The dates, if not subject to change can be copied into Usage Schedule via an auto-enter setting on the date field--either calculation or Looked up Value can do this. If the dates are subject to change in that related table, use a script trigger on that date field to update related records in usage schedule each time the date is changed.
There's a lot more going on than just tracking dates in this DB. The dates are being pulled from an accounting table into the production batches (here I'm calling this Usage Schedule) table.
Dates in Usage Schedule (or Batches) are subject to change as an allotted batch with estimated start (processed) and end (jarred) dates is processed and subsequently jarred. So script it is, I'd love to be able to do it all with calcs though...
Seems to me that the accounting table should pull dates from the schedule table rather than the opposite like you have here, but that's an opinion based on very limited info.