3 Replies Latest reply on Jul 26, 2010 1:44 PM by philmodjunk

    Calculation Help



      Calculation Help


      FM PRO 11

      OSX 10.6


      I have three fields "number of jobs", "Fee per Job" &  "Total".  I would like to make a calculation that will solve the empty field if 2 are filled in. So if i have "Total" & "number of jobs" it will figure out "fee per jobs"... If i have "number of jobs" & "Fee per job" it will figure out the total  and so on..

      Thanks for your time.



        • 1. Re: Calculation Help

          I'm assuming Number of Jobs * fee per job = Total

          Define all three fields as number fields

          Double click them in Manage | Database | Fields, select the auto-enter tab in the dialog that pops up.

          Select the calculation option and enter the following expressions for each field:

          Number of Jobs:
          If ( fee per job ≠ 0 ; Total / fee per job ; "" )

          fee per job:
          If ( number of jobs ≠ 0 ; Total / Number of  Jobs ; "" )

          Number of Jobs * fee per job

          For all three fields, clear the "Do not replace existing value..." check box after you've entered the calculation.

          • 2. Re: Calculation Help

            I set it up this way and for some reason the fields clear whatever info they have in them once i tab to the next one.  

            They are all Number fields I have triple checked all my calculations and unchecked the "Do not replace existing value" on all the fileds is there something i missed?

            Thanks for all your help!!


            • 3. Re: Calculation Help

              Sorry, I should have tested that. I've been trying several variations of the above settings and so far, every combination I've tried hasn't worked either. Either the values entered get overwritten by the wrong value or the other fields fail to update....

              I can get this to work:

              Define three data fields:

              Number of Jobs, FeePerJob, Total as number fields

              You could then write an OnRecordCommit script like this:

              If [ not IsEmpty ( YourTable::NumberOfJobs ) and not IsEmpty ( YourTable::Total ) ] 
                 Set Field [ YourTable::FeePerJob; YourTable::Total / YourTable::NumberOfJobs ] 
              Else If [ not IsEmpty ( YourTable::Total ) and not IsEmpty ( YourTable::FeePerJob ) ] 
                 Set Field [ YourTable::NumberOfJobs; YourTable::Total / YourTable::FeePerJob ]
              Else If [ not IsEmpty ( YourTable::NumberOfJobs ) and not IsEmpty ( YourTable::FeePerJob ) ] 
                 Set Field [ YourTable::Total; YourTable::NumberOfJobs * YourTable::FeePerJob ]
              End If

              This is a very crude script. You can build more checks into it to avoid replacing existing values and such if you wish.