1 Reply Latest reply on Jul 9, 2012 1:13 PM by philmodjunk

    Serial or Date field for relating???

    FMNewbie

      Title

      Serial or Date field for relating???

      Post

      Hi there... I know this is probably simple but I'm out of practice at this.  I have a new database that has two tables:  SHIFT REPORT and CASES.  SHIFT REPORT would contain all the information on who was working on a given day etc.  CASES wil contain information on events that happened throughout the shift.   I'd like to relate them by date so that on a certain date you could search the individual cases of the day, or the shift report for the whole day.   Presumably, the best way to relate them is by date... or should I create an auto-creation serial number for both tables??  

      I'm a little lost.... 

        • 1. Re: Serial or Date field for relating???
          philmodjunk

          99% of the time, it will make no difference as long as your interface design is solid.

          If you base the relationship on an auto-entered date field defined in Shift Report, should you ever discover that the date in Shift Report is incorrect for some reason, fixing the date breaks it's connection to any related records in Cases. This is an inherent risk to using any field other than a serial number or Get (UUID) (new in FileMaker 12) as the primary key for records in a table.

          Using the serial number field for your relationship, you can still produce reports based on Cases that show the dates from Shift Report and you can also use the date for sorting and searching on such a layout.

          The other tradeoff to balance here is that if use the serial number field, searches and sorts on a Cases layout that specify a date will perform a bit slower than using a date field defined in Cases.

          So you are balancing two negatives that both look fairly minor to me: A bit more solid design for the relationship if you use serial numbers vs. slightly better performance in a few cases if you do not.