
1. Re: Calculations on new records
Sorbsbuster Aug 31, 2012 4:18 AM (in response to SiaranMorganLloyd)Do you have an example of a calculation that fails?

2. Re: Calculations on new records
SiaranMorganLloyd Aug 31, 2012 4:38 AM (in response to SiaranMorganLloyd)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??
thank

3. Re: Calculations on new records
Sorbsbuster Aug 31, 2012 5:18 AM (in response to SiaranMorganLloyd)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 debug.
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.

4. Re: Calculations on new records
philmodjunk Aug 31, 2012 9:01 AM (in response to SiaranMorganLloyd)rewriting it into a Case function (and I removed the extra space ):
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 fieldit becomes a data entry task instead.

5. Re: Calculations on new records
SiaranMorganLloyd Sep 1, 2012 1:55 PM (in response to SiaranMorganLloyd) 
6. Re: Calculations on new records
philmodjunk Sep 4, 2012 12:05 PM (in response to SiaranMorganLloyd)Create a new table with 2 fields and five records:
FFT  Value
A  60
B  50
C  40
D  30
E  20We'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.