Field calculation from related table

[dloose]

New to FM11.

I have a pretty simple invoicing database that uses two related tables "EquipmentBilling" and "Invoices"

"Invoices" has a calculation field Discount that determines a discount based on what department

a user is from:

If ( EquipmentBilling::Department <> "25380000" ; 0;

If (Equipment_Used="Imaging Core Computers"; 0;

If (Equipment_Used="Fortessa Flow Cytometer"; 0;

If (Equipment_Used="Training"; 0 ;Cost * .75))))

The problem is that processing of the first line of this calculation, I guess passing the value of the department from the related

EquipmentBilling to Invoices appears slow. So a database user enters a record but no discount is applied even if the department is

"25380000" as entered into EquipmentBilling. Deleting an entry from Invoices corrects the issue and the Discount field is subsequently evaluated correctly; but this is very kludgy.

Any advice greatly appreciated.

[David Jondreau]

1) A change in a related table will only update an unstored calculation. Sounds like you're using an auto-enter calculation.

2) Case() is your friend here.

[comment]

"Invoices" has a calculation field Discount that determines a discount based on what department

a user is from

The actual calculation doesn't look like that's what it does. In any case, you should not be hard-coding data inside a calculation formula. For example, to determine a discount based on the selected department, you should assign a discount rate to each department in the Departments table and look it up from there. This will enable you to modify a department's discount rate without affecting existing invoices.