10 Replies Latest reply on Jun 3, 2012 4:44 PM by robwoof

    Help in adding a calculation field

    kwrigh

      I want to use a field to perform a calculation using several other fields. I'm having a little trouble figuring out how to do that.

       

      My calculation is:

       

      If (Field 3 = "Text1", Field 4, Field 6) Endif (Field 3 is text and Fields 4 & 6 are numbers.

       

      The numerical result would be stored in Field 10.

       

      Can someone help me figure out how to write a script to do this?

       

      Thanks in advance,

       

      K

        • 1. Re: Help in adding a calculation field
          erolst

          Can you describe what exactly you're trying to achieve? You mention a script in the last sentence, but it sounds

          as if you really need a calculation field.

           

          For a calculation field, re-define or create Field 10 as calculation; in the Specify Calculation dialog, write

           

          If ( Field 3 = "Text1" ; Field 4 operator Field 6 )

           

          You didn't write which calculation you want to perform, so substitute operator with the appropriate one.

          Check that result type is set to Number; it's the default for a new calculation field, but check anyway.

           

          PS: If these are your actual field names, I suggest using more meaningful ones.

          • 2. Re: Help in adding a calculation field
            kwrigh

            You're right.  I was mistaken in calling it a calculation - Let me try this again.

             

            What I want to do is to check the value in Field 3 - If that value is "text 1", then I want to copy the numerical value in Field 4 to Field 10, else I want to copy the numerical value in Field 6 to Field 10.  Does that make sense?

             

            I also would like to sum Field 10.

             

            K

             

            PS.  Not real field names, just using them for this example. The real field names are a little long.

            • 3. Re: Help in adding a calculation field
              brsamuel

              Set Field [ Field 10 ; If ( Field 3 = "text 1" ; Field 4 ; Field 10 )]

               

               

               

              Set Variable [ $totl ; Value:Sum ( Field 10 )]

              • 4. Re: Help in adding a calculation field
                comment

                kwrigh wrote:

                 

                I also would like to sum Field 10.

                 

                Use a summary field?

                • 5. Re: Help in adding a calculation field
                  comment

                  brsamuel wrote:

                   

                    

                  Set Variable [ $totl ; Value:Sum ( Field 10 )]

                   

                  I don't think that's going to work.

                  • 6. Re: Help in adding a calculation field
                    brsamuel

                    You are absolutely correct.  I was wearing "relationship-colored glasses" when I wrote that.

                    • 7. Re: Help in adding a calculation field
                      kwrigh

                      Thanks brsamuel.

                       

                      The Set Field command worked, however, it only entered values for 1 record in that field. I need to loop through a list of about 700 records in that field and then I need to do the same for 4 additional fields.  I have seen a couple of ideas on the forum, but none that seemed exactly what i need. 

                       

                      I also need to total the records for each of the field (1 grand total for each of the 5 fields containing 700 records each.)

                       

                       

                      Field 1          Field2      . . .

                      2504

                      1507

                      7709

                      10000

                      .

                      .

                      .                   

                       

                      Any ideas?

                       

                      K

                      • 8. Re: Help in adding a calculation field
                        robwoof

                        Hi K,

                         

                        If you have multpile records, you can either put the Set Field inside a Loop, or you can use the "Replace Field Contents" (select the Calculation option). You can then use exactly the same calculation from the Set Field, but Replace will calculate the result for each record and place it in each record.

                         

                        For a Loop:

                        Go To Record [First]

                        Loop

                        Set Field [ Field 1 ; <calculation 1> ]

                        Set Field [ Field 2 ; <calculation 2> ]

                        ...

                        Set Field [ Field n ; <calculation n> ]

                        Go To Record [Next, Exit After Last]

                        End Loop

                         

                        Using Replace Field Contents:

                        Replace Field Contents (Calculation) [ Field 1 ; <calculation 1> ]

                        Replace Field Contents (Calculation) [ Field 2 ; <calculation 2> ]

                        ...

                        Replace Field Contents (Calculation) [ Field n ; <calculation n> ]

                         

                         

                        As for the Grand Total, create a "Summary" field for each field you need totalled. When you place the summary field in the footer or a Grand Summary (leading or trailing) layout part, it will show you the grand total of the found set of records.

                         

                        Does that make sense?

                         

                        Rob

                        • 9. Re: Help in adding a calculation field
                          kwrigh

                          Thanks Rob.  The Replace Field was exactly what I needed. I tried the summary field and it works but I was thinking more of something on the lines of one total, this gave me the entire field, filled with the same total value.  I need to total about 20 of the 100 fields in the report. Clearly, I am not used to working in databases. I am used to excel (gasp).

                          • 10. Re: Help in adding a calculation field
                            robwoof

                            OK, if you want a field which calculates the total of several fields together, perhaps you might try the GetSummary calculation (see http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref3.33.43.html#1029667). If you "summarise" a field by itself, you just get a grand total. If you want to aggregate the grand total of multiple fields into a single field, you might try a Calculation field instead of a Summary field. The calculation could be something like:

                             

                            GetSummary ( FieldA ; FieldA ) + GetSummary ( FieldB ; FieldB ) + GetSummary ( FieldC ; FieldC ) + ....

                             

                            Does that help?

                             

                            Rob