1 Reply Latest reply on May 29, 2012 7:33 PM by philmodjunk

    Calculation field from value in different table (and filtered from yet another field in second...

    JakeGarrison

      Title

      Calculation field from value in different table (and filtered from yet another field in second table)

      Post

      Wow, ok. Let me start off by saying this is making me feel so totally stupid. I'm sorry if I am reposting a question that might already be answered... but I'm having a hard time even wrapping my head around the question that I am trying to ask. 

      So, here goes.

      Basicly I have two tables (that are important to this question at least).

      The first is called: Time Punches

      • Punch Id
      • Employee Id
      • Client Id
      • Project Id
      • Phase Id
      • Start
      • End
      • Total (End - Start and converted to hours) //already got this working
      • Rate
      • Cost (Total * rate) //got this working as well

       

      and the second: Phases

      • Phase Id
      • Project Id
      • Client Id
      • Phase Name
      • Labor Budget Hours //the amount of labor hours allowed. Calculated during estimate 
      • Labor Spent Hours
      • Labor Budget Actual //the amount of money set aside for labor. Calculated during estimate 
      • Labor Spent Actual

      Ok... this is where im having trouble....

      For the table "Phases" the field "Labor Spent Hours" needs to be a total of all "Time Punches::total" cells... but only in rows that have the matching "Phase Id"

      I want to do the same with "labor Spent Actual" as well. 

      Does this make sense?

      My goal is to create a time clock system that will automaticly help me keep track of my labor costs and staying within budget. 

      Thanks for your help. I'm really looking forward to using filemaker! :)

        • 1. Re: Calculation field from value in different table (and filtered from yet another field in second...
          philmodjunk

          How does Phase id in Phases get a value? Is it an auto-entered serial number?

          If so, that one field unique identifies each record in Phases. IF not, you should define an additional field, set up as an auto-entered serial number, as the primary key for this table. Assuming Phase id is a unique identifier for now (you can use the new serial number field if it's not), set up this relationship:

          Phases::Phase id = Time Punches::Phase id

          Client id and Project id should be removed from the Time Punches table as they are not needed.

          Then you can define a calculation field as Sum ( TIme Punches::Total ) to compute the total hours and Sum ( Time Punches::Cost ) as the total cost. In each case, the total will be of all the time punches records linked to the current Phases record by Phase ID.

          Instead of calculation fields defined in phases, you can define two summary fields in Time Punches, one as the total of Total and one as the Total of Cost. Placing these summary fields on a Phases layout or referring to them  in a calculation defined in Phases will also return the total hours and cost for that phase.