AnsweredAssumed Answered

Time Tracking

Question asked by kip on Jan 2, 2019
Latest reply on Jan 6, 2019 by onefish

I am trying to enhance our team database by adding a time tracking module for our team members. Works fine so far (team members can log in and enter the time (date, starting time, ending time and short description) for each project they are currently working on.


Right now I am stuck with the calculation of overtime per person per day / week / month / year and would appreciate a hint how I could solve this problem.


So far, I got these tables:



- ID

- Name

- Password

- Status (active team member / inactive team member for people who left the office, but where we still have to track our time for the projects)

- state ( A, B or C to determine the holidays -> Table: days )



- ID

- PersonID —> PERSON::ID

- Mon

- Tue

- Wed

- Thu

- Fri

- Hours (Mon+Tue+Wed… to calculate the sum of working hours in a week)

- Date (date from which this working time model should start)

I set up this table so we could use it for different working time models - some colleagues work 8 hours a day, some only Mon, Tue and Fri for 4 hours, other team members 6 hours a day, but only 4 on Fridays, etc. // the date field should indicate the date, when the current working time model starts for the referred person)


TIME TRACKING ( Person <-> Project )

- ID

- PersonID —> PERSON::ID

- ProjectID —> PROJECTS::ID

- Date ( date field )

- Start ( starting time of work for a certain project )

- End  ( ending time of work for a certain project )

- Description



- ID

- Project title



- ID

- Date

- holiday in state A ( 0/1 )

- holiday in state B ( 0/1 )

- holiday in state C ( 0/1 )

( this table contains a single entry for each day in the year // in some states our holidays differ a bit, plus there are special days off for excursions etc. )





- How can I compare the hours that Person A should work in a week with the hours he or she worked in that week on all projects ?


- How can I determine the hours that Person B (Table: Person ) in state A ( Table: Person ) should work in a week, when there’s a holiday on wednesday ( would be in Table: days ) and this person would usually work Mon-Thu 4 hours ( would be in Table: Time Tracking )?



I am wondering whether this requires a damn difficult calculation to combine the different working time models (which of course may also change during the year) or if I need an other table where a script could gather the information from DAYS and HOURS, then calculate the hours a person should work and enter it in the table.



( FileMaker 17 on MacOS )