Do you have an example of a calculation that fails?
If ( FFT AS 1="A" ; 60; If ( FFT AS 1="B" ; 50 ; If ( FFT AS 1="C" ; 40 ; If ( FFT AS 1="D " ; 30 ; If ( FFT AS 1="E" ; 20 ; "")))))
Like I said, it works for the exisiting records but when I add a new record it fails to calculate??
I think you should check out 'Case Statement' in Help, you'd find it is very much easier to write that kind of expression,and much easier to de-bug.
Are you sure it never evaluates, or is it just with records where FFT AS 1 = D? Your test has "D[space]" in it, in case that was not intentional.
Case ( FFT AS 1="A" ; 60;
FFT AS 1="B" ; 50 ;
FFT AS 1="C" ; 40 ;
FFT AS 1="D" ; 30 ;
FFT AS 1="E" ; 20
And Here is an alternative to Case:
If ( PatternCount ( "ABCDE" ; FFT AS 1 ) ; 60 - ( Code ( Upper ( FFT AS 1 ) ) - Code ( "A" ) ) * 10 )
A third alternative is to not use a calculation at all for this but instead to set up a related table of values where the value in FFT AS 1 is used to match to a record in this "look up table". This last approach allows the user to modify what values are returned without having to redefine your calculation field--it becomes a data entry task instead.
Create a new table with 2 fields and five records:
FFT | Value
A | 60
B | 50
C | 40
D | 30
E | 20
We'll call this table FFTValues
Then open Manage | Database | Relationships and create this relationship
YourOriginalTable::FFT As 1 = FFTValues::FFT
You then have two options for how you use this relationship
The simplest is to simply use the field tool to put the FFTValues::Value field on your table based on your original table and when you enter or select a letter A through E, the correspoinding value is displayed on your layout. If you then decide that the Value A should return 65 instead of 60 to go to your layout for the FFTValues table, find the record where FFT = "A" and change the 60 to 65 and all records where FFT As 1 = "A" will automatically update to show 65 instead of 60.
Sometimes, you want to see such a change only affect new records and currently existing records should not show a new value if a value in the FFTValue table is edited. In those cases, you can set up an auto enter option (looked up value) for a field defined in your original table that "looks up" and copies the current value into this field.