1 Reply Latest reply on Jan 30, 2013 8:59 AM by philmodjunk

    Yearly report for month schedules



      Yearly report for month schedules



           I initialy had a monthly report demand.

           We have two fields for job dates: startdate  and  enddate; and we needed to filter these dates based on a given month. The solution to this problem was given by the following post: http://forums.filemaker.com/posts/40612f7c41.

           However, based on this solution, now I need to create a report for the whole year, showing all jobs that were performed each month. If a job lasts more than one month   or   starts in the middle of the month and extends to the next month, we need to show this records twice in the year report, and so on. How can I create a report like this?

           I thought about creating another table to show this, but I don't know how.

           Thank you,


           I'm using FM Pro 12 based on Mac OSX 10.8.2



        • 1. Re: Yearly report for month schedules

               A record in the Layout's found set cannot be listed twice, so I think your related table is the way to go. Once your related table is properly populated with records, you can base your report layout on this new table and then a given job can be listed in more than one place on your report.

               I will assume that your table is named Jobs and that it as an auto-entered serial number (or Get ( UUID ) ) field named __pkJobID. We can thus set up this relationship to the new table:


               Jobs::__pkJobID = JobDates::_fkJobID

               See this thread if my notation is not familiar to you: Common Forum Relationship and Field Notations Explained

               You need just one more field in JobDates: JobDate, a date field for storing a single date.

               You'll need to use a script that loops from Jobs::StartDate to Jobs::EndDate, creating one record in JobDates for each date in that range. For new Jobs, you can set up script triggers to perform this script whenever Jobs::EndDate gets a new value or each time a Jobs record is committed. For existing records, the same script can be performed from inside a looping script that loops through all your jobs records to do a "one time fix".

               #Perform this script from a layout based on Jobs
               Freeze Window
               SetVariable [$Start ; value: jobs::Startdate ]
               SetVariable [$End ; value: Jobs::EndDate ]
               SetVariable [$ID ; value: Jobs::__pkJobID]
               IF [ IsEmpty ( Jobdates::_fkJobID ) ]
                   Go to Layout [ "JobDates" ( jobdates ) ]
                   Go To Related Record [Show only related records; From table: jobdates ; Using layout: "jobdates" (jobdates) ]
                   Delete All Records [No dialog]
               End IF
                  Exit Loop If [$Start > $End ]
                  New Record/Request
                  Set Field [JobDates::_fkJobID ; $ID ]
                  Set Field [JobDates::JobDate ; $Start ]
                  Set Variable [$Start ; Value: $Start + 1 ]
               End Loop
               Go To Layout [Original Layout]