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

    Calculations on new records



      Calculations on new records


      Please help!


      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?


      Please can you advise what I'm doing wrong



        • 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??



            • 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 Wink):

                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



                  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 Sealed but I totally get the Case Function so I will use this for the time being!



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