by John Mark Osborne, <jmo@best.com>, http://www.best.com/~jmo
Time Card
Automated time and service billing
BONUS: TIMECARD.FP3
PLATFORM: Mac/Win
This article started out as a question on our forum. I gave a quick description of the solution but the folks on the thread wanted more. With this article and the accompanying bonus file, you should be able to easily setup your own system to automatically track the time spent on projects.
All magazine subscribers have access to the ISO FileMaker forum. Just logon to our web site with your password and a new button called forum will show at the bottom of the button bar on the left.
The concept behind this solution is to use a startup and close script to track the time spent on projects. Each time you launch a solution, the date and time will be placed into a log file. When you close the file, the ending date and time will be stored on that same record. Each launch of the file creates a new record in the log file so you can produce a report for your client.
There are a few limitations to this solution. Startup scripts do not run when a file is opened via a relationship. This can easily be avoided by simply making sure files don't get opened by a relationship. Another problem is accidentally leaving a file open while you are not working on it. When you finally close that file, a greater amount of time than you spent will be recorded. These are minor issues considering the time this solution can save.
Getting Started
The first step is to create the TIMECARD.FP3 file. This file will serve as the log for all projects. It will need eight fields. The first field is a number field, Serial Match. This field has the option to auto enter a serial number. The reason for the auto enter option will be discussed later in this article.
You will also need two date fields and two time fields. These fields will store the starting date, ending date, starting time and ending time. The Project text field will store the name of the project you are working on. The Total Time calculation field will return the total time per record. This calculation deserves some explanation:
Case(IsEmpty(Date Start) or IsEmpty(Date End) or IsEmpty(Time Start) or IsEmpty(Time End), 0, ((Date End - Date Start) * 86400) + Time End - Time Start)
The formula tests to see if any of the date or time fields are empty. If an empty value were figured into the formula then a much larger or smaller amount of time would be returned thus throwing off the report to your client (e.g. the file crashes and never returns the end date and time). If none of the fields are empty then the Date End and Date Start fields are subtracted. This accounts for any periods of time that cross multiple days (e.g. you work from 10:00 p.m. till 1:00 a.m.). The result is then multiplied by the number of seconds in a day (86400). If the dates are the same then a zero is returned and just the starting and ending time are subtracted.
You will also need a summary field so you can total all the records for a particular project. The bonus file that accompanies this article demonstrates how a subsummary report could be used to produce an invoice for your customers.
Setting Up Your Solutions
Each project you work on will need one field, one relationship and two short scripts. I recommend creating a template file from which you create all files. This template would contain common fields like creation date and standard interface elements like 3D boxes as well as the field, relationship and scripts included in this article. This will save a couple of minutes when you produce each new file and prevent mistakes implementing the solution.
The field you need to add is a number field, Match. This field will be used to create a relationship from your project file to the log file. Make sure to turn on the option for allowing the creation of related records. The field that Match will relate to is the Serial Match field. The reason the Serial Match field is set to auto enter a serial number is so you don't need a calculation to create a unique key from each field. This could easily be done by concatenating the Project field and a serial number field in the project file. However, the technique demonstrated here uses a little known feature and will remove the need for the calculation.
There is a little known feature that was added in FileMaker Pro 3.0v2. You can read about it in the Read Me file for the Updater. It allows you to create a related record without populating the match field in the master file. Normally when you try to enter data into a related field without populating the match field, an error message will occur saying, "this field cannot be modified until 'match field' is given a value". If the match field in the related file is set to auto enter a serial number then this error message will not occur. This feature enables this solution to create the match value in the TIMECARD.FP3 log file and avoid the issue of creating unique values across many files.
Each project file will also need a startup and close script. These scripts just set the date and time values to the log file using Set Field. Here is the Set Field step for the start date:
Set Field ["TIMECARD::Date Start", "Status(CurrentDate)"]
Once these scripts are created, they need to be set to run on startup and close via Document Preferences.
The startup script includes an additional two Set Field steps. The first Set Field initializes the Match field. Each time the file is launched, a new record needs to be used to store the current work session. The second Set Field sets the name of the project to the log file. If you use a template file as described above then this is the only script step you will need to modify for each new file.
When starting on a new project, remember to edit the Project Set Field script step in the startup script immediately. Otherwise, your time won't be recorded against the correct project. To prevent yourself from erroneously billing the wrong party, be sure to always create new projects from a template file. Cripple the startup and close script in the template file by putting an If statement around it that can never be satisfied. Be sure to remove this If statement as part of your new project setup process.
## END ##