6 Replies Latest reply on Dec 29, 2008 11:08 PM by Skids

    Displaying a Calendar type report (Layout)



      Displaying a Calendar type report (Layout)


      Hi,  I am new to Filemaker and I am thinking of transferring data from a RealBAsic Database application into Filemaker.  I need help with the creation of a timesheet report which will display a calendar view of a whole month's worth of timebookings grouped into weeks.  The data table records the hours worked on each day except those where no work was completed. Using October 2008 as an example the first week of the report would display the following:

      Date                     days 

      Monday 29 Sept 08   0

      Tuesday 30 Sep 08   0

      Wednesday 1 Oct 08 1 

      Thursday 2nd Oct 08 0.5

      Friday 3rd Oct 08     1

      Saturday 4th Oct 08  0

      Sunday 5th Oct 08    0


      and so on through the month.  At present I am able to produce a list of time bookings but I don't see how I place days that have no time booked onto the layout as they have no records. 


      I have a couple of ideas:  first I could pad my time bookings table with days that have zero time worked entries which seems a little clumsy or perhaps I could build the weekly portions as text strings from within a script and paste the text to a global text field if the script system allows it.


      At present I only have access to an old copy of FM Pro version 5.5.  If I can work out the general concepts I will purchase a newer version which I guess is more OS X ish. 


      Any ideas as to the best way to solve this problem?


      best wishes


        • 1. Re: Displaying a Calendar type report (Layout)
          Jens Teich
             You need two table. The first one with the dates does obviously exist already.

          But you need a second table for the calendar. Each record one day. A relationship connects your table::date with calendar::date. You always look at lists or portals from calendar table. The data of your date table is shown as related field. Mutliple dates on one day can be handled via the list()-function.

          As time proceeds you can create new records in the calendar table. A more sophisticated approach would be to create only as many records as you want to see simultaniously. If you want to see one month you create 31 records, a global _calendar_start_date, an ID field and you get the date as _calendar_start_date + ID.

          This tricky setup allows to change the viewed month by simply changing _g_calendar_start.

          • 2. Re: Displaying a Calendar type report (Layout)

            Alternately, your second table can have just one record in it, but with either 7 (if you only want a week view) or 42 (if you want a month view) calculated globals that calculate the Monday Of (a global date field), the Tuesday Of (same global date field), Wednesday of (same global date field), etc.


            Then create 7 or 42 relationships from this table to your data table.


            Your layout will be done on the new table with portals to the 7 or 42 related dates.


            You'll have to do some fun clock arithmetic to get the calculated fields, but that's the fun part.



            • 3. Re: Displaying a Calendar type report (Layout)

              Thanks jensteich for the help.  If I understand correctly the new Calendar table is used to drive the layout (report) as it has a record for each day of the month.  First I would find the first day of the month, followed by the previous Monday (in my example I display the last two days of September when displaying October) then use the found record list from Calendar to select the data from my data table TimeBookings.  


              At first this seems complex and it is quite different to how I have implemented it in SQLite and RealBasic as the FileMaker method seems to require that the stored data do the work rather than the code behind the data.  




              • 4. Re: Displaying a Calendar type report (Layout)

                jsalzer, thank you for posting your ideas.  Again if I have understood your solution has the month of interest created whenever it is required, then this month of records is used to select records from the table TimeBookings.




                • 5. Re: Displaying a Calendar type report (Layout)

                  I think that you have it.


                  If you're setting up your calendar with Monday starting the week (as it sounds like you're doing), keep in mind that you need to take into account that everything shifts if the first day of the month is a Sunday.  To compensate, your calculated globals would be (for example):


                  One of these:

                  gDate (A global date field in which you can enter any date to get to that corresponding month)


                  Six of these:

                  gDay1OfMonth = Date(MiniCalMonth; 1; MiniCalYear)

                  gDay8OfMonth = Date(MiniCalMonth; 8; MiniCalYear)



                  gDay22Plus7OfMonth = Date(MiniCalMonth; 22; MiniCalYear) + 7

                  gDay22Plus14OfMonth = Date(MiniCalMonth; 22; MiniCalYear) + 14 


                  And 42 of these:


                  gMonday1 = Case ( DayName ( MiniCalDay01) = "Sunday"; GetAsDate(Int(MiniCalDay01 / 7) * 7- 6); GetAsDate(Int(MiniCalDay01 / 7) * 7+1 )) 


                  Then set up your 42 relationships, and you're good to go.


                  If you start your week on your calendar with Sundays, you no longer need to do the "case" to offset as shown in the 42.


                  Hope that helps!


                  • 6. Re: Displaying a Calendar type report (Layout)

                    Thank you again.  I have just downloaded the trial version of FMP V9 and have just started to create my application.  It could be an interesting few days!