6 Replies Latest reply on Dec 5, 2008 2:02 PM by TSGal

    New to FileMaker 9.0 (used Filemaker 6.0 in the past)



      New to FileMaker 9.0 (used Filemaker 6.0 in the past)


      I am looking to put together a multi-purpose Filemaker database serving a specialized unit in a municipal police agency.  This is a task force which include members of local, state and federal law enforcement agencies.  This database will only service one agency. There are 135 members assigned to this unit.

      What does this entail?

      Personnel Records- usual pedigree information, duty status, emergency contact information, education and training, firearms qualifications, former commands (work locations within the department) and teams assigned within this unit and dates assigned.  A photo directory will be generated on a quarterly basis listing pertinent information and call back numbers.


      Equipment Issued.  A listing of equipment assigned to the Unit from the agency.  The personnel the equipment was issued to (or if it is still in house, operational, inoperable or returned to agency).  Also equipment from other agencies are assigned to us.  This must also be noted.   Equipment may be assigned and reassigned at will.  New equipment must also be recorded.


      Overtime.  Overtime listing dates and tours scheduled to work and time actually worked.  The difference will be overtime recorded.  There is a dollar figure that the government reimburses the local agency for overtime incurred by each member.  A separate report must be generated for that on a monthly basis.  Report must be generated listing all personnel and overtime incurred. (Even if the overtime amount is zero.)  A report must be generated by teams assigned.  Finally a year to date overtime list must be generated listing overtime for each person month by month with a yearly final.


      Vehicle Assignments:      Car information (make, license plate number etc,) personnel assigned. Vehicle return, accident listing.  Report generated regarding accidents, vehicle assignment and return sheet.


      So far that’s it.  I know that this will be an arduous task but I have to get it done.  I have used filemaker 6.0 in the past to develop a separate database for personnel and overtime records.

      What I heard from that:  Filemaker 6.0 has a limit on adding hours and minutes.  Yearly overtime figures easily reach 60,000 hours.  6.0 could not handle this so I  had to convert the time in whole numbers.  ½ hour was equal to .50, 15 minutes was .25, etc.  IS that the same in 9.0

      I am an average Joe computer user.  My one ability is that I don’t quit.  I will keep banging away until I achieve my goal. 

      I will be relying heavily on userboards to get past the rough patches.  The police department will not pay for the application to be developed but will purchase the program.  The Filemaker 6.0 version was a success however I need to step up my game to include all of the other elements listed here.


      Here is what I am thinking so far:

      Startup screen – password protected to give various levels of access to different people.

      After the start up screen, have an introduction page with various modules and subs.

      For example:  Personnel (complete personnel access)

      Sub –new training


      Equipment  etc


      I know that I have not provided nearly enough information.   This is my springboard.  I would appreciate any and comments, tips and help.  I was seriously thinking about purchasing some templates from Scriptology just so that I can get a layout down.  Any feedback?

        • 1. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)

          I have read about other police depts. using FileMaker. Google "police FileMaker" and you'll find some links. If any of those people would be willing to share with you it would save a ton of time.


          I'm not familiar with any "limit on adding hours and minutes" -- FileMaker 9 has no problem with this that I've heard of. 


          I would suggest that you read up on FileMaker 9's security implementation before you get too far into it. 


          I would also suggest fmforums.com as another helpful developer community. 

          • 2. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)



            Thank you for your post.


            Everything you said is doable.  Let's go over each point....


            Personnel Records

            This is fairly straight-forward, and you should not run into any problems here.  Photo directory is fine.  If there are any specific concerns about this, let me know.


            Equipment Issued

            Again, straight-forward.  You will need some kind of key field (equipmentID?) to link to the appropriate individual.  If equipment is assigned and reassigned at will, you will need to see a screen that shows the current owner and then a way to assign it to someone else.  This should not be difficult.



            This is a very subjective topic.  You need to determine what constitutes overtime, what is the rate, and these could be different for each group.  A lot of thought and planning will need to go into this.  You can then break it down by week, month, year, whatever you determine.  Prepare to spend a great deal of time in this area.  From personal experience, whenever I got this working properly, the rules changed.  :-)


            Vehicle Assignments

            This can also be treated like "Equipment Issued" above.  However, vehicles are always being serviced; especially in a law enforcement environment, so prepare to also attach a maintenance table (to show history of service for a vehicle), and also if assigned a temporary vehicle while the main vehicle is being repaired/serviced.


            With FileMaker Pro 6.0, I never ran into any limits on time.  What limits did you encounter?  I just tested this out, and I formatted my time fields to display 24 hour time and discard seconds.  That is hh:ss.  I added two time fields together, entering 14:40 + 14:40, and the result is 29:20.  I then added 111114:40 + 111114:40 and the result is 222229:20.  So this should definitely handle your concerns.


            Startup screen with password can also be accomplished.


            This should get you pointed in the right direction.  I would recommend downloading a free 30-day trial of FileMaker Pro 9 from our web site (www.filemaker.com) and try some of these topics for yourself.


            Let me know if you have any questions or want clarification for any of the topics above.



            FileMaker, Inc. 

            • 3. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)

              Sorry TSGal- I did have an entire explaination for how I did overtime in 6.0 but I deleted it somehow.  I will try again later. 

              But anyway, about the time limits.  I narrowed it down.  It is the Trailing Grand Summary amount.

              After entering about 12,000 overtime slips- between Jan and July- I get a total overtime amount for each person and then a grand total for the office.  The Summary fields have a limit of 32766 hours. 


              Also  how do you feel about Scriptology for templates

              • 4. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)



                I have a Trailing Grand Summary with Time values and numeric values.  I just compiled 1000 entries, and my Time field shows:




                and my Number field summary shows:




                I cannot find a limit for hours in FileMaker Pro 9.  Can you be a little more specific.


                I have not used Scriptology for templates, but the author, John Mark Osborne, used to work in Technical Support here at FileMaker many years ago, and he is well respected in the FileMaker community.



                FileMaker, Inc. 

                • 5. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)


                  I am still using 6.0. The time limitations in the Trailing Summary is in Filemaker 6.0.

                  Unfortunately I really haven't began working in 9.0 yet.

                  In 6.0, I made a few near fatal mistakes so I am trying to put it all on paper first (the right way) and then commit it to program.  All of this while continuing to work a full case load.  Needless to say, it is going extremely slow.


                  Thank you for all of your help.

                  Just brainstorming:

                  I would like to begin:

                  click on the icon and program asks for a password.

                  The program then opens to a main screen with icons for

                  A.  Personnel Records

                     1. Enter new personnel file

                     2. Edit personnel file

                     3. Search personnel information

                     4. Personnel Reports

                  B. Overtime

                     1. Enter new overtime

                     2. Search overtime records

                     3. Overtime Reports

                        a. Individual

                        b. Team

                        c. Office


                  *Is it possible to create a one page fill in sheet with salaries of individual ranks.

                  This amount changes every year but I do not want to create a new file.

                  What I am looking for is when I create a report for overtime, I can put in the rate of overtime on a separate page and depending on the rank of the individual, it will calculate total amount of overtime at that rank.


                  Thanks again.

                  I am looking forward to working on creating this new database in 9.0 with a little more consistancy in time of development.


                  • 6. Re: New to FileMaker 9.0 (used Filemaker 6.0 in the past)



                    I did all my tests with FileMaker Pro 6.0, and I did not have the limitation, so I cannot determine why it isn't working properly for you.


                    Yes, you can create a password for the file, so when you click on the icon, the program will load and ask you for a user name and password.  This can be found under the File menu -> Access Privileges -> Passwords.


                    You can also have a start-up script that takes you to a specific screen.  This can be found under FileMaker Pro menu -> Preferences -> Document.


                    Your Personnel Records layout can have all the information you want.  The Overtime layout can also be set up.


                    Yes, you can have a layout that displays salaries of individual ranks.  You can even set up a password that only permits you to view this data (leaving others locked out of the fields and/or layout).


                    Yes, you can create a report that is based upon an overtime rate.  If you have your links into the other tables set up properly, the rate can change depending on the rank of the individual.


                    If you have any other questions or need clarification, please continue to contact me.



                    FileMaker, Inc.