7 Replies Latest reply on Apr 11, 2013 9:45 PM by hsu912174

    First and last record of the day?



      First and last record of the day?



           Being new to filemaker, and without much background in software development, I figured I might have done some things rather unorthodoxly. Recently I am trying to construct a database to track my weight training. It has a table tracking the exercises I did (and the specific details such as weights, reps, etc), and another "timesheet" that records the time when I sign in and out of the Gym. I have constructed it in such a way that I wolud go to the gym and hit the hgue "BRING IT" button on the sign in page, which will log the time into "sign in" field and take me to the exercise  tracking table. When I am done I would hit a "sign out" button to take me to another layout based on the timesheet table. The one thing I have been trying to achievie, but have been hitting dead ends for days, is that I want it to also display the number of exercises I did today.

           I have set up the table to record each set of exercises, and, as we all know, one would do several sets of the same exercise. In order to have the list grouped by kinds of exercise (i.e. a header in the list grouping sets of the same exercise together) sorted by chrological order I also divised a field "GroupID" which wolud update only when I have moved on to another exrecise. Therefore what I am trying to achieve is to obtain the "GroupID" from the last record of the day and subtract it by the first record of the day. Though I have been stared at the list of script steps and functions pretty hard, I can't really find a function that may help me do that yet. Can anybody shed some light on this? 


        • 1. Re: First and last record of the day?


               In both "Exercises" and "Timesheet" tables create one "Creation date" field having Creation Date cheked in the Auto Enter option of the field setting to store creation date of each record. Create four fields i.e. "Creation Time", "SigninFlag" and "SignoutFlag" in "Excercises" table and a calculation field as "Constant1" in "Timesheet" table by setting calculation =1.

               Establish this relationships: Exercises_Exercises__CurrentDayRecord (self join to determine all the records of a day) and in the relationship give sort order "Creation Time" field by descending order.

                  Excersises               Excersises

                       Creation Date =     Creation Date

               After signin process when a new record is being created in "Exercises" table just check

               if  Count (Exercises_Exercises__CurrentDayRecord) = 1

                 Set Field SigninFlag =1

               End If

               After signout process do follwing thing

               Go to Layout Exercises

               Go To Related Record of Exercises_Exercises__CurrentDayRecord

               Set Field SignoutFlag =1

               Establish below relationship to determine first record of the day from Timesheet context



                  Timesheets               Excersises

                       Creation Date  =     Creation Date

                       Constant1        =      SigninFlag


               Establish below relationship to determine last record of the day from Timesheet context



                  Timesheets               Excersises

                       Creation Date  =     Creation Date

                       Constant1        =      SignoutFlag

               Although this process is a lengthier one but i hope it will work for you. Any more clarification in thsi, you can ask freely.




          • 2. Re: First and last record of the day?

                 There are summary fields that can return the maximum and minimum values for a field in your current found set. So if you can perform a find for the set of records that you want, these two summary fields can return the first and last values from that set of records.

            • 3. Re: First and last record of the day?

                   Thanks for the suggestion, though I just realized that the entire GroupID contraption wouldn't work because of some issues. The basis of my if statement to decide whether to increment the GroupID number is based upon the comparison between the field "Exercise_name" and the calculation field "Exercise_name_prev" based on "getNthRecord ( Exercise_name ; get (recordNumber) - 1)." The problem I ran into is that this get(recordNumber) isn't always sequntial  - after several failde attempt I created a calculation field of "get(recordNumber)" just for the hack of it, and observed - in horror - how the numbers would jump around haphazerdly. Does anyone know of an alternative to cercumvent this issue, or is there a way I can bring the RecordNumber under control?

                   Thanks for the great suggestions above, but I guess I have to fix this before I can move on.

              • 4. Re: First and last record of the day?

                     Get ( RecordNumber ) returns the position of a record in the current found set. Change the sort order, omit or find records and the value returned for a given record can change.

                     I can't go into specifics because it is not clear how you have set up your data and your tables. I'd have a related record where there is only one record for each group of records in your current file. Then a serial ID number in this added table can be used to identify all records for that group.

                • 5. Re: First and last record of the day?

                       My database conissits of two tables: Timesheet and Exrecise_log. So far the affectde field in my table are Exercise_name, Exercise_name_prev, GroupID, GroupID_prev in the Exrecise_log. I tried several ways to fill in the _prev fields but all seem to fail because of how unreliable get(RecordNumber) is. So far I am resorting to the qick and dirty way of go to previous record -> copy from field -> go to next record -> past into _prev field solution. It works great, but I am hoping to find a more elegant and robust solution.

                  • 6. Re: First and last record of the day?


                         Thanks for the insight. It's working out.

                    • 7. Re: First and last record of the day?


                           In my project flagging seems to be a bit overkill, but I will definitely keep yours in mind when I move onto bigger projects.