1 Reply Latest reply on Jun 14, 2011 10:18 AM by philmodjunk

    Relational Databases - FMP 6



      Relational Databases - FMP 6


      I am using FileMaker Pro version 6 and need help on how i can use relational databases.

      I am creating a database on employee information e.g. vacation time, sick days, benefits, insurance, salary etc. etc.

      I have all my layouts created with information and they all work fine, except sick days.

      I need to track the following:

      - the date they went out of the office

      - the time they went out of the office

      - the hours they were off 'sick' that day

      - the total # of hours they were out sick

      - the total # of days they were out sick

      - the remaining # of sick days allowed (10 - the total # of days out)

      I have calculations to figure out all of that information but am not sure what to put into what database.  I want to be able to use the master database and see all the calculations.  I was originally using repeating fields so I could enter more than one entry per person but that created issues with the calculations.

      Does anyone have any helpful advice on what to put into the relational database and what fields I should be 'matching up'?  Sorry for any unclear points I may have made, I am still learning about this program.

        • 1. Re: Relational Databases - FMP 6

          Define a serial number field, EmployeeID in your original file if you have not done so already. For your existing records, use Replace Field Contents with the Serial Numbers option to give each Employee and ID number and also update the serial number settings at the same time.

          In your new file define a number field, EmployeeID so that you can define a relationship between the two files that link your records by EmployeeID. (It might be tempting to link employees by name but names are not unique and people change their names from time to time.)

          If you intend to use this new file only to log when an Employee is not present, you can define the following fields:

          TimeStamp fields would be ideal for this table, but don't know when they were added. They aren't an option in version 5.5 files, so they may not be an option for you with version 6.

          If you have timestamp fields, you can record the date and time an employee left and returned in just two fields. If you don't have that option, you'll need four and more complex calculations to compute the elapsed time they were out:

          DateLeft, DateReturned (Date fields)
          TimeLeft, TimeReturned (Time fields )
          Reason For absence (Sick, Vacation, UnpaidLeave, etc.)
          cElapsedTime,  A calculation field that computes elapsed time. With timestamp fields, your calculation can be as simple as subtracting the two timestamp fields. If that is not an option, you'll need a more complext calculation using all four of the left, Returned fields.
          sTotalTime, a summary field computing the total of Elapsed Time.

          With version 6, you'll likely need to define the same relationship linking these two files in both files. In the new file, you can set up a summary report that breaks down each employee's time out of the office by Reason with subtotals for each. You can include fields from the related employee record in the original file to fill in details such as the employee's name.

          From the Employees file, you can add a portal to your new file and use it to display and log each time an employee was absent.