2 Replies Latest reply on Aug 16, 2013 7:38 AM by JohnSherwin

    Copy Value from a field to use in a calculation

    JohnSherwin

      Title

      Copy Value from a field to use in a calculation

      Post

           Hi,

           I have a time sheet database with several tables: Users, Rates, Hours, Joblist, Invoice, Quotation.

           In the Rates table I have 3 records showing fields: Rate ID, Rate Description and Rate Letter as follows:

           1

           B

           50.00

            

           2

           A

           100.00

            

           3

           C

           0.00

            

           In the hours table I have fields and calculations giving me the total hours by a staff member, at a particular rate and a particular activity, this total hours by those 3 parameters are presented in a summary field. 

           I want to add a further calculation field that will multiply that summary result x the value associated with rate A, B or C in the rates table.

           As I said, I am a newbie and cannot figure out how to get a calculation to extract the correct rate from the correct record in the rates table. I want 1 calculation to copy the rate for A, another to copy the rate for B and another to copy the rate for C.

           I have been trying lookup, get, case but it is a bit trial and error, mostly error.

           Any help would be great.

        • 1. Re: Copy Value from a field to use in a calculation
          philmodjunk

               Regardless of whether you set up a looked up value setting on a Rate field in the Hours table, an auto-entered calculation that multiplies the Rate times the total hours or an unstored calculation that multiplies the Rate times the hours, you first need a valid relationship between the two tables:

               Hours>-------Rates

               Hours::RateLetter = Rates::RateLetter

               With that relationship, you can set a rate field in Hours with a looked up value setting that will copy over the Rate from the Rates table each time you select or change the value of the Hours::RateLetter field.

          • 2. Re: Copy Value from a field to use in a calculation
            JohnSherwin

                 Thanks PhilModJunk.

                 I have a relationship of Rates::RatesDesription to Users::RatesDescription and then

                 Users::RatesDesription to Hours::RatesDescription and then

                 Does that work? I cannot make a direct link without causing a duplication of the Hours table as it becomes circular, I think I need the realtionship between the User table and the Hours table for other reasons.

                 I attempted to take the relationship aspect out of it just in order to get the calculation right by attempting to get the value for  field on the same table. In other words on the Rates table adding a field that displays only the A rate, which cannot seem to do. I felt if I could do that without a relationship involved, then I could look to do it across tables. 

                 Does that make sense?