4 Replies Latest reply on Oct 14, 2008 3:19 PM by davidanders

    FMP 5.5  TimeCard and JobTime Sum of Summary



      FMP 5.5  TimeCard and JobTime Sum of Summary


      FileMaker Pro version 5.5  and  Filemaker Server ver 5.5 on Macintosh.
      A TimeCard database is duplicated for each employee (contractors).
      It contains a number of repeating fields
      User. . . . .  . . . . . . . . . . . . . . . . . . . . . . . . DayName (date)  (five repetitions)
      Date  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DayStart  (time)  (five repetitions)
      UserDateCalc. . . . . . . . . . . . . . . . . . . . . .DayEnd  (time)  (five repetitions)
      Job1Name. . Job1TimeSum. . . . . . . .  Job1Time (time)  (five repetitions)
      Job2Name. . Job2TimeSum. . . . . . . .  Job2Time (time)  (five repetitions)
      Job3Name. . Job3TimeSum. . . . . . . .  Job3Time (time)  (five repetitions)
      Job4Name. . Job4TimeSum. . . . . . . .  Job4Time (time)  (five repetitions)
      Job5Name. . Job5TimeSum. . . . . . . .  Job5Time (time)  (five repetitions)

      The Job#TimeSum sums the time entries for that user in that payperiod on that line.

      A new record is created for each payperiod (two weeks in the real DB)
      JobNames will not be constant, it will change from one job to another on some payperiods

      A TimeCardSum DB is related to the TimeCard by a script that creates a new record
      and pastes the UserDateCalc for relating the two records.  The script then creates four
      additional new records for Job2, Job3, Job4, and Job5 (if theyy are not blank)

      The individual TimeCard JobTimeSums need to be summed for the Total JobTime for all Users.
      This TotalTime for a Job would be related to a Jobs database for billing purposes (QuickBooks
      manually entered)


      What would be the best approach?  Another Database for JobsTime Total?
      I hope this is clear enough for you to offer suggestions.
      Thank you.

        • 1. Re: FMP 5.5  TimeCard and JobTime Sum of Summary



          Thank you for your post.


          You can use the Sum() function in a calculation field to add up the repetitions.  For example, Sum(Job1Time) will add the 5 repetitions for the week and give you a total.  Then, you can use a Summary field to total for the entire file, or include the Summary field in each sub-summary section of a report.


          It sounds like you have everything already set up.  What additional information do you need?  Maybe I should ask, "What is not occurring that you want to occur?"



          FileMaker, Inc. 

          • 2. Re: FMP 5.5  TimeCard and JobTime Sum of Summary

            UserOne may have Job1234 in Job1Line on his Timesheet.

            UserTwo may have Job1234 in Job3Line oh her Timesheet.

            Summing the five individual days time is easy.

            Now, the Job1234 time from UserTwo on Job3Line needs to go into a record on the JobTimeSum database.

            And, the Job1234 time from UserOne on Job1Line needs to go into a record in the JobTimeSum database.

            I have that working. 

            Those two entries (sum of five days, UserOne) and (sum of five days, UserTwo) need to be summed for a TotalJobTime and needs to be available (related) to a Jobs database.

            I would wave my arms and make pictures, if possible.

            Thank you for your time. 

            • 3. Re: FMP 5.5  TimeCard and JobTime Sum of Summary



              Maybe waving your arms and making pictures would help.  :-)


              Seriously, it sounds like you are able to sum the five individual days easily.  I'm assuming that information is contained in a calculation field.  Let's call it "Total Time".


              In your Jobs database, I would assume you have created a link into the Timesheet database, where the key field would be the Job Number.  In this case, Job1234.  You could then pull the "Total Time" for each record, and once again, add the records in the portal.  This would give you a total time for that job.  Is this what you are trying to accomplish?  If not, let me know.


              I have sent you a private message (top of this page - right side - X Messages) with an email address, just in case you want to send me screenshots and don't have the ability to display them on a web site.



              FileMaker, Inc. 

              • 4. Re: FMP 5.5  TimeCard and JobTime Sum of Summary

                Thank you.

                Just asking a question, sometimes, even if the listener nods knowingly, can lead to a rethinking of your process.

                (my explanation was not as clear at it should have been, given the text only format) 

                Exporting the TimeCard Job1 thru Job5 with their time sums to another database, where the Jobs and Time were in individual records, allowed a Related JobName Sum to get the correct info.

                Thank you again for your time.