9 Replies Latest reply on Nov 27, 2012 10:08 AM by philmodjunk

    Date range as relationship key?

    BobSchwenkler

      Title

      Date range as relationship key?

      Post

           I'm creating a DB to track a manufacturing process. I've got crocks (for sauer kraut) that are used over and over again. They'll be in use for certain date ranges. When planning future production batches I want to be able to see which crocks are available for the date range I'm about to be planning on.

           Pretend crock A is scheduled for use during 10/1-10/8 and 10/20-10/30. If I want to schedule aonother batch for 10/9-10/19 that'd be fine, but if it were for 10/8-10/20 that wouldn't be.

           I've been going over the different ways to do this in my head and haven't figured anything that feels clean yet. I'd like to be able to use a relationship to dynamically determine crock availability but if needed I can just script something to manually determine this stuff.

           Any ideas?

        • 1. Re: Date range as relationship key?
          philmodjunk

               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----<UsageSchedule

               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

          • 2. Re: Date range as relationship key?
            BobSchwenkler

                 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.

            • 3. Re: Date range as relationship key?
              philmodjunk

                   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.

              • 4. Re: Date range as relationship key?
                BobSchwenkler

                     Thanks!

                     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.

                • 5. Re: Date range as relationship key?
                  philmodjunk

                       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.

                  • 6. Re: Date range as relationship key?
                    BobSchwenkler

                         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.

                    • 7. Re: Date range as relationship key?
                      philmodjunk

                           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.

                      • 8. Re: Date range as relationship key?
                        BobSchwenkler

                             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...

                        • 9. Re: Date range as relationship key?
                          philmodjunk

                               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.