6 Replies Latest reply on Oct 22, 2008 1:10 AM by ceebee

    monthly reports

    msbehavinmaiden

      Title

      monthly reports

      Post

      I work at a company where I keep track of all the jobs that we do, and at the end of the month I will print out a report of all the jobs that we did in that month.  I currently have my database w/ access but am working on creating a filemaker database.  I am having a problem creating a report that will show only the jobs that were done between the dates given.

       

      I have fields BeginningDate and EndDate to enter when we started and completed the job.  Currently with access, when I open my report I have a filter box that pops up and I enter the BeginningDate and EndDate that I want to sort within (such as 9/1/2008 and 9/30/2008).  How do I filter a filemaker report to do the same?

       

      Here's to complicate matters more (maybe).  I need the report to show jobs that may have a beginning date less than the one I entered (such as 8/23/2008) if it has and end date within that range (such as 9/13/2008).  I also need it to show any jobs that were started within the range but end after the end date (such as start 9/15/2008 and end 10/3/2008).  As well as displaying any jobs that have a beginning date that begins before the filter date (such as 8/10/2008) and ends after the filter end date (such as 10/5/2008).  This will allow me to show any jobs that were worked on during the month.

       

      I hope someone will be able to understand this.... I am finding that access filters and such are very different from filemaker, which is why I am running into this problem.

       

      Any help offered is greatly appreciated.

        • 1. Re: monthly reports
          ceebee
            

          I'm not sure about access but this is how I would handle it in FM.

          Create two global fields and enter your start and finish dates in each. Then create a calculated field and use a calc which will set a flag dependant on whether the start or finish date falls within your range. Than merely select those records with the set flag.

           

          Hope this helps.

          • 2. Re: monthly reports
            msbehavinmaiden
               I have already created 2 global fields to enter begin and end date.  How do I work with calculations?  Can you give me an example of how to set it up?
            • 3. Re: monthly reports
              ceebee
                

              If you call your calc field 'CalcFlag' the calculation will go something like this:-

               

              Case (jobstartdate >= gDate1 and jobstartdate <= gDate2; 1;

              jobendate >= gDate1 and Jobenddate <= gDate2; 1 ; 0 )

               

              This should mark all jobs in your range with a '1' in the calculated field.

               

              HTH

               

              • 4. Re: monthly reports
                TSGal

                ceebee:

                 

                Thank you for your post.

                 

                This definitely satisfies the first two options, but I don't think it handles the third option. That is, the job occurs during the month but the jobstartdate occurs before the beginning of the month and the jobenddate occurs after the end of the month.  Therefore, I would add one more case to your calculation:

                 

                 

                Case (jobstartdate >= gDate1 and jobstartdate <= gDate2; 1;

                   jobendate >= gDate1 and Jobenddate <= gDate2; 1 ;

                   jobstartdate <= gDate1 and jobenddate >= gDate2; 1; 0 )

                 
                 
                TSGal
                FileMaker, Inc. 

                 

                • 5. Re: monthly reports
                  msbehavinmaiden
                    

                  Thank you for your help.  I have solved that problem.  Now I have another one I wonder if you can help me with...

                   

                  I want the name of the month to appear at the top of the the report that I am printing it for.  If I enter a date range say 9/1/2008 - 9/30/2008 I want to appear at the top "Job List - September 2008".  How can I get the month and year at the top of the page?  I have been trying to use the MonthName command and am not having much luck.

                   

                  Any ideas?

                  • 6. Re: monthly reports
                    ceebee
                      

                    This is an easy one you can use either of the global start or end fields to put at the top of the report. In layout mode, right click on the field and select date format from the drop down menu. Use the custom settings to show only the month and year. Job done.

                     

                    HTH

                    Best regards from the UK