7 Replies Latest reply on Nov 30, 2009 9:41 AM by philmodjunk

    report / summary total of time and datefields



      report / summary total of time and datefields


      Hallo, I'am new to filemaker. I'am making a database for a handicapt person who need nusing and help 24 hours a day.

      I managed oke untill this problem, I hope you can help!

      I make month views (worksheets) with fields month_begin(date field), month_current(calculation), day1 - day31 (calculation fields), time_start(time field and repetition 31), time_end(time and repetition 31), workhours(calculation and repetition 31)


      I have no problems making a layout with the worksheets from one user. But... I need to view (I think it is a report) a worksheet from a specific month with a total of the beginning- and endtimes and a total of the workhours from each day and all users!


      Its important for me tot create this month overview because I need to see if every day is completely scheduled. Everyday must scheduled with 24 hours, if not I can take action. 


      Hope you can help! 


        • 1. Re: report / summary total of time and datefields

          Without completely redesigning your database...


          A calculation field can be created that uses Sum(Workhours) to compute the total hours recorded in the WorkHours repeating field.

          • 2. Re: report / summary total of time and datefields

            Thanks for your answer. Sorry I'am pretty new to this stuff. Two questions:

            where do I place the SUM function? (I double click on the field and the options I get is to make the field a button and then I have the option to add text and functions to the field)


            But the first problem is that I can't create a list report where I see only one worksheet of a month. It displays a worksheet for every month!


            Thanking you in advance


            • 3. Re: report / summary total of time and datefields
                 I mean the list report shows a wordsheet for every record. But I need a worksheet for one month with the total of records from al users.
              • 4. Re: report / summary total of time and datefields

                To define a new field, you have to select Manage | Database from the file menu and then click the fields tab.


                I'm assuming that you have a table with one record for each user that records their time for the entire month. Is that correct?


                It sounds like you can do what you need using a summary field set to compute the "total of" the calculation field that I've already suggested.

                Then create a summary report with a Subsummary part that part "sorted by" your month_begin date field. 


                Before you get overwhelmed by what I just suggested, try getting the original calculation field defined and read up on Summary Reports in the help file. Then you can reply back here to take on the next step.

                • 5. Re: report / summary total of time and datefields

                  Thanks for your answer Phil. I created a report with summary field and managed to get it sorted by month.

                  But I face a new problem 


                  My report display my whole database, so I want to make a (pop-up) window where I can select a month, before I go to my report.  The pop-up window isn’t the problem but don’t know how to put the month (the value I choose from the pop-up window) in my report. 


                  Do I have to make a script? or put something with my button?


                  Hope you can help me with this one  


                  I have made an example that possibly clearafy my question. How can I attach this example?? 


                  • 6. Re: report / summary total of time and datefields

                    I forgot to mention that the dropdown list I created from the valuelist where I named the moth is not working. I can see the values but when I click I see a dialog 'sorry this field is not modifiable' 

                    Is this because my Month field is a calculation: Month(Date_Begin) ??

                    I guess I have to make new fields with month but how can I relate this to my datefield. 

                    • 7. Re: report / summary total of time and datefields

                      You are correct about the calculation field being the source of your error message.


                      I have a date field in one of my DB's where I always want the first of the month entered no matter what day is entered by the user. I use the Auto-enter field option to modify the user's input after they have entered a valid date.


                      Define the date field in Manage | Database | Fields

                      Either double click the new field in this dialog or click the options button while the new field is still selected. Click the Auto-enter tab.


                      Enter this calculation:

                      If ( Not IsEmpty(Self); Date(Month(Self); 1; Year(Self)) ; "" )


                      After you click "OK", clear the "Do not replace existing value..." check box.


                      Now use Format | Date... to format the date to just show the month and year.


                      Now the user can either type in a date or you can format the field with a drop down calendar, then the user can select any date in the month, but is actually entering a date with the same month and year, but with 1 as the day.


                      If you make this field a global field, (Select this on the Storage tab in field options), it'll be easier to use in a scripted find.