1 Reply Latest reply on Dec 7, 2009 10:40 AM by philmodjunk

    Time card relationship diagram



      Time card relationship diagram


      Hi guys,


      I need some advice on how to integrate a time card table to my current relationship graph.


      My filemaker solution has 7 main tables: CUSTOMERS, JOBS, INVOICES, PAYMENTS, EMPLOYEES, TIMECARD and EXPENSES


      The JOBS table tracks all service orders placed by a customer

      The INVOICES table assigns an invoice to a job so a a payment can be received later once the job is completed

      The PAYMENTS table records payments made by the customer once the job is completed. Information contained in this table includes the duration of the job.

      The TIMECARD table logs each employee's work hours for a given job. The hours worked by an employee is equal to the duration of the related job

      The EXPENSES table keeps track of all business expenses including payroll activities. 


      Here's an overview of my current relationship graph:


      CUSTOMERS -< JOBS (1-MANY|There's a customerID foreign key in the JOBS table) 

      JOBS -< INVOICES (1-MANY|There's a jobsID foreign key in the INVOICE table) 

      INVOICES -< PAYMENTS (1-MANY|There's an InvoiceID foreign key in the PAYMENTS table)  

      EMPLOYEES -< JOBS (1-MANY|There's an EmployeeID foreign key in the JOBS table)  


      The trouble: 

      I know that I need a 1-to-many relationship between the EMPLOYEES table and the TIMECARD table.


      But how do I automatically report any payment made to an employee as an expense when applying a payment to a customer's account.


      P.S.: An employee payment (not to be mistaken with a customer payment) is calculated by multiplying the hours worked (JOB  duration) by the rate (determined in the EMPLOYEE table).


      Thanks for any help 



        • 1. Re: Time card relationship diagram

          I'm assuming that "automatically report any payment made to an employee as an expense when applying a payment to a customer's account" means you want to use this information to either create a new invoice record or update an existing invoice record.


          What do you need to see in your invoice record?


          A single amount billed for a single Job or....?


          (Many invoices use a related table to itemize different charges.)