6 Replies Latest reply on Sep 4, 2012 12:05 PM by philmodjunk

# Calculations on new records

### Title

Calculations on new records

### Post

I'm very new to Filemaker Pro on the mac and therefore this may be a simple query.

I have created a DB where some of the fields perform calcualtions based on entries elseqhere.  THese all work correctly until I add a new record.

Although I input the information the DB fails to make the calcuations for the new record?

thanks

• ###### 1. Re: Calculations on new records

Do you have an example of a calculation that fails?

• ###### 2. Re: Calculations on new records

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

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.

• ###### 4. Re: Calculations on new records

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 field--it becomes a data entry task instead.

• ###### 5. Re: Calculations on new records

Phil

Thanks for this - I used the Case Function and it is brilliant.  I like the idea of the related table but can't really find a tutorial to help me set it up.  Told you I was a beginner  but I totally get the Case Function so I will use this for the time being!

CHEERS!

• ###### 6. Re: Calculations on new records

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.