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

# Calculation Help

### Title

Calculation Help

### Post

FM PRO 11

OSX 10.6

Hello!

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..

Ricardo

Total.

• ###### 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 ; "" )

Total:
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?

Ricardo

• ###### 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.