1 2 Previous Next 16 Replies Latest reply on Apr 27, 2011 12:50 PM by WesleySantiago

    Multi-activity time/date recording



      Multi-activity time/date recording


      What’s the best way to handle time/date driven information in filemaker? This would be a personal health database of various exercises and weight/fat logs.

      Should I stack everything into one table, where any one record can show multiple activities (even if only one activity is recorded)? Or should each activity be it’s own table, presented through portals on a master “date” record (in another table)?

      The end use would be personal use only where moving averages may come into effect on distance running activity.

        • 1. Re: Multi-activity time/date recording

          OK, the following is based on twenty years of using FMP to track my own marathon training. So, YMMV, but here is how I do it.

          Main table uses the date for it's primary key field, pretty much everything else is related via the date. The main table holds basic data, weight, weather, locations, notes, etc. I have child tables for: Runs, each mile is a separate record with fields for date, lap number, time and distance. Plans, would have the date, distance, and a note field for races for that date. Another table for non-running exercises, again linked by date, with fields for type, and reps, etc. I reuse these fields as I don't do much tracking of these items, but you can always do summary reports. The biggies is runs, put a portal on you main layout and set it to allow creating new records and you can sum the days run(s) etc. If you do more than one run a day, put an extra field in the run table for distinguishing which lap records go with which run, etc. 

          I have over the past few months recreated this is FM Go, with the initial intention of syncing the data. But my iPhone is always with me and Go is instant on so now I just make back ups. 

          Note: this breaks the cardinal rule of relational databases, your primary key is always supposed to me a meaningless piece of data, but rules were meant to be broken. Just don't allow the Main table DateID to ever be modified. I usually will auto create a year's worth of records in the main table, and just let the others be populated as needed. 


          • 2. Re: Multi-activity time/date recording

            >rules were meant to be broken.


            • 3. Re: Multi-activity time/date recording

              The reason that rule exists is that if there is "meaning" to the key, users may want/need to change that meaning and doing so "breaks" the link to the related records until you make identical changes to the related records.

              In this case, should you discover that you got the date wrong, you can't correct the error without first making a matching date change to the related recordsm If you used a serial number field instead of the date field, you would not have any problems changing the date. If you need to show the date on any layouts based on the related tables, you can still add the date field from the main table to show the date.

              • 4. Re: Multi-activity time/date recording

                I won't say that in twenty years I haven't logged run on the wrong day, 'cause I have, but by using the date as the foreign key I can quickly go to a list view of the run records and replace the wrong date with the correct one and Voiia all is well. In this case having meaningful data as a key makes life much easier and corrections quicker. And this is not a db for sowing in the wild. Were I creating this for others I might go the traditional route, but in my case I having the actual date handy is, well, very handy.

                As always, YMMV

                • 5. Re: Multi-activity time/date recording

                  I often "break the rules" myself when it's a not for the public database. It's a decision that has also turned around and bitten me in a very sensitive place also when I found that I saved design work on the front end only to lose the savings and much more handling issues less cavalier design choices would have avoided. Wink

                  Don't feel you have to continue the discusion if you don't want to but I'd like to better understand this statement:

                  "In this case having meaningful data as a key makes life much easier and corrections quicker."

                  The only corrections affected by doing it your way would seem to be the date itself and correcting the date is slower, not quicker due to the need to perform Replace Field Contents operations on each and every related table that uses the date key.

                  • 6. Re: Multi-activity time/date recording

                    Phil I agree with everything you said, but...

                    My belief (and experience) is that for a runner, keeping a personal log, that the data is all based on the date. How far did you run today, when is the next race, etc. Portals, summaries, everything is date driven. So to attempt to tie everything else together is any other way than by the date is just going to make life difficult down the line. I understand that I could create serial numbers or UUIDs for every main record, but I would still have to have a date field that could not be modified, so do to it the "correct" way (to me) is redundant. In my case all of the main table records and their respective dateIDs are created in advance, usually a year's worth at the time (every day of the year get a record), and once done never change, all of the other tables use that date as their foreign key. My db started in FMP 3, it has been converted for every upgrade of FMP since then, blessedly much to the credit of FileMaker, using a date as the key field all the while has never been a problem.

                    As far as corrections being made they are never more that 27 records at a time. Errors are always operator caused, the file opens to today's date, if I wait a couple of days to enter the data AND don't pay attention to what I am doing I will enter the splits for the wrong date. But all I have to do is go to the run table do a find on the wrong date, do a replace for the correct date and when I go back to the main layout everything is well again. The correct way I would "only" have to ascertain the matching foreign SerialID do a find on it and correct the date. The question is why and is it really necessary. In my case maybe not, for others maybe yes. I have given my file to a few friends along the way, and some are still using it, but no one ever complained about have any problems based of the schema.

                    So again, I agree with the premise, just not all of time.

                    • 7. Re: Multi-activity time/date recording

                      I think you are missing my point. You can have your date field on all your layouts for both the parent record and all related tables. Just define it in the parent table and add that field to all your layouts where you need your date. You can sort by it. You can perform finds for it. All without making it the primary key in your database.

                      Changing less than 30 records with a replace field contents operation may be simple and quick, but it's a "fix" you won't need to make if you base it on a serial number field instead. And you won't risk a follow on mistake with the Replace field contents where you could conceivably compound your first error by changing the date to yet another wrong date, but now it's a date you have in your system so that your updated child records with the wrong date are now intermixed with child records with the same but correct date. Surprised I realize that's highly unlikely, but unlikely things happen and they can be completely avoided in this case by using a serial number in place of the date.

                      • 8. Re: Multi-activity time/date recording


                        If I understand you correctly, you're arguing that one dedicated "Date" table should be driven by serial numbers, with external "join branches" for activity categories (in serparate tables), for that singular day-record?

                        • 9. Re: Multi-activity time/date recording

                          While I cannot speak for Phil, I do believe that is what he is advocating. You would have a Master/Parent/Interface table that would have records for each day of the year. Those records would have a field for meaningless ID for relational purposes. This table would then have a field for other value(s) including one for the date. When you go to create a new record in a related table, such as for logging daily runs, that meaningless ID would be used to populate the foreign ID field for each record that is to be related to the Master table and that the value of the date field would be available for viewing and use in the Child tables. This is a requirement of the first normal form for relational databases. FileMaker does not enforce this form, but does accommodate it through whatever means the developer chooses. The simplest being use of the auto-enter serial number.

                          It is my belief and experience that in this instance, this is not necessary and unnecessarily complicates the user's experience. In my database I have set it to upon opening to automatically go to Todays date on the Main Interface layout, so if I forget this little 20-year old fact and begin to enter splits for a run, which I do through a portal, they will be logged under the wrong date (which was automatically entered into the Child/Log table record via FMP allow creation of new records). To correct my error I simply go to a list view of the Run Log table find the wrong dates and change them. As an aside ALL records should always have what are often called developer's fields: Creator. Creation Time and Date (now a Timestamp) and similar Modified fields. They can be very helpful in sorting out data entry problems. In addition, a date in FMP is stored as a number that is then displayed as a date, so unique and not is empty validations will still work. Also keep in mind that these master records are created en masse for a year or more at a time. The dates are unique and sequential. The child records are created on an as needed basis.

                          Phil disagrees. This is a classic discussion amongst database developers. The best that Phil and I can do in this case is to agree to disagree. Not uncommon amongst FM developers, it is often said that if you ask ten FM developers how to do something you will get fifteen different answers. FileMaker is a very flexible program. The two hosts of the FileMaker Success Tips podcast have discussed this very topic more than once over the course of their shows. One, Andy Cohen takes the same position as Phil, while Lee Strong takes a more "git 'er done" approach. 

                          So there it is, from the standpoint of Normal Forms, Phil is correct. From my position use of the date as a primary key fits all of the criteria for the first normal form except being meaningless, but for me it works, and works quite well and has for a long time. You, armed with more than enough information, can set it up as it fits your needs. But YOU still have to do the workouts!

                          Next up Tupelo, "Trample the weak. Hurdle the dead."

                          • 10. Re: Multi-activity time/date recording

                            In my experience, there is never a need for a Date table.  Each record has a date when the activity happened.  You can then use a global date field to display the related (by date) records any time you wish.  The date table isn't a real problem except ... all information becomes related and thus slows down searches and any calculation placed in the date table which pulls data from the related records cannot be indexed.

                            With the ability to self-join table occurrences and join only on a global date (or even simply filter portals now with vs. 11), a date table is redundant.  Even with a calendar solution, a date for each day isn't required.

                            Phil is ABSOLUTELY right in that every 'true' table should have a unique serial and it should be meaningless.  I do not consider a date table a true table; it is simply an auxiliary table used for selection and which could be handled more easily using other methods.  My opinion only ...  Smile

                            • 11. Re: Multi-activity time/date recording

                              LaRetta and I are in agreement here. (Thanks!)

                              I can't and won't speak for her here, but for myself, I am speaking from the experiences I've had both in fixing my own poor designs when I broke this rule and also in reworking client designed solutions that broke this rule.

                              • 12. Re: Multi-activity time/date recording

                                @LaRetta, Phil,

                                I appreciate insight into the more formal approach.

                                However, I'm stumped on one point. LaRetta mentioned a global date field. I've heard of global variables. I didn't get much insight with a google search.

                                If I have one table for running, and another for swimming, wouldn't I need a thrid table to act as a 'hub' for the other two, to show their data in a two portals?  I don't quite grasp how self-joining would be useful with multiple tables dedicated to multiple activities.

                                • 13. Re: Multi-activity time/date recording

                                  Global fields are fields with global storage enabled in Field options. You can look them up in FileMaker help.

                                  I'm thinking in terms of this basic structure:


                                  You define a an EventID and EventDate field in events, plus any other fields that are appropriate. Link your detail table(s) by Event ID. Events is not just a "date table". Any time you are looking at data an an event detail layout, you can just add the Event Date field from Events to your layout. That way if you record a bunch of stuff for a 4K run and then realize it was on Sunday instead of Saturday, you pull up the Event Record and change the date for that event. All the details records will show the same newly updated date. (And you can edit the date from a detail record if you choose to make the date field from Events and editable field.)

                                  • 14. Re: Multi-activity time/date recording


                                    I'm very new at this. Related time/events/dates is difficult for me to understand.

                                    (Useful side info: My needs are to transfer existing spreadsheets for previous running   distances, swimming distances, and weight readings into one database.)

                                    I read the help pages on global storage.

                                    I'm casually assuming each of my spreadsheets (containing date and 1 variable data) would command 3 tables beings built in FM. Furthermore, I understand your technique would require a fourth table, Events. The fourth table would contain 1 record(using global storage??) and I could see 3 portals showing the matches for that one date. (My understanding grows very fuzzy at this point.) In order to see multiple dates (potentially in List View), I would have to create multiple "date" records in what you're calling an "Event" table. (????)

                                    While seeing one date and related EventDetails is fine, I'm very stuck on a few points:

                                    -After I import 3 spreadsheets, will I have to manually link day Z across 3 tables to day Z in 1 record of the Events table? I'd like to have the ability to see multiple dates (and related activities) in one layout.

                                    -How I could build "time slices" of my efforts. How would it be possible to select week X through week Y and export three tables of related information to be graphed? If one global storage record drives one date, I don't see how this is possible.

                                    Thanks so far for the insights.

                                    1 2 Previous Next