10 Replies Latest reply on May 4, 2017 8:47 AM by swetzel

    New to Filemaker, How do I do set a field to a calculated result?

    swetzel

      I am trying to set a field (call it "Result") to a complex calculation.  I want to do something like this:

       

      If DatabaseField:Type = "A" {

           many lines of calculations giving Answer
      } else if DatabaseField:Type = "B" {

           many lines of calculations giving Answer

      } else if DatabaseField:Type = "C" {

           many lines of calculations giving Answer

      } else if DatabaseField:Type = "D" {

           many lines of calculations giving Answer

      Put Answer into Database:Result

       

      The question is, how do I do this in Filemaker?  I would like to use constants and variables in my calculations but I cannot figure out how to do it? Should I use a script?  Should I use the calculation window for a field?  Can someone help me get started with the right syntax?

        • 1. Re: New to Filemaker, How do I do set a field to a calculated result?
          philmodjunk

          You can use a script step such as Set Field to assign the results of a calculation to your field.

          You can define your Result field as a calculation field with your expression as its calculation.

          You can define result as a data field (Date, text, number..) of a type appropriate to your result and specify the calculation as an auto-enter calculation.

           

          Your expression in all cases would be structured like this:

           

          Case ( DatabaseField:Type = "A" ; Result calculation goes here for Type A ;

                      DatabaseField:Type = "B" ; Result calculation for Type B ;

                      DatabaseField:Type = "C" ; Result calculation for Type C ;

                      DatabaseField:Type = "D" ; Result calculation for Type D ;

                      Result calculation if none of the above are true goes here (optional)

                    )

          1 of 1 people found this helpful
          • 2. Re: New to Filemaker, How do I do set a field to a calculated result?
            jdevans

            FileMaker Pro 15 Help

             

            Case(test1;result1{;test2;result2;...;defaultResult}) (remove the curly braces for subsequent tests)

             

            Here's how I like to format mine for readability:

            Case(

            test1;result1;

             

            test2;result2;

             

            test3;result3;

             

            /*more tests here using same as above */

             

            defaultResult

            )

            1 of 1 people found this helpful
            • 3. Re: New to Filemaker, How do I do set a field to a calculated result?
              swetzel

              Thank you but how do I use variables?  Can I do something like

              X=1

              Y=2

              Z=3

              A = Z-Y

              B = A * Y

              Result = A + B + Z

               

              ?

              • 4. Re: New to Filemaker, How do I do set a field to a calculated result?
                philmodjunk

                Yes you can use variables and you can use fields.

                 

                Variables come in three forms: Let variables, script variables and global variables.

                 

                Let ( A = 3 ; A * A - 5 )  // Let variable

                Set Variable [ $A ; value: 3 ] script variable

                Set Variable [ $$A ; Value: 3 ] global variable

                 

                Let variables only exist inside the Let function that assigned a value.

                $Script variables only exist within the script that creates them and assigns a value. When the script ends, the variable does not persist.

                $$Global variables persist until the file is closed

                 

                But in many cases, you will find that you need a field and not a variable.

                1 of 1 people found this helpful
                • 5. Re: New to Filemaker, How do I do set a field to a calculated result?
                  jdevans

                  I suppose it is worth mentioning that you maybe should describe for us when or how you want this calculation to run. If it's via a button click, then first, create the script as described above by philmodjunk, and then assign the script to the button.

                  In Layout mode, right click the button, select Button Action, select Perform Script from the dropdown, and then select the script you created that does the calculation. The result of the variables can be assigned to the field you specify by way of a "Set Field" script step.

                  • 6. Re: New to Filemaker, How do I do set a field to a calculated result?
                    keywords

                    Your best method with a complex calculation is to use the Let ( ) function. This allows you to declare whatever variables you need in the top part of the function (and variables can use previously declared variable) and the expression you need to use to deliver in the field as the second part of the function. So your example would look like this (structured the way I personally prefer):

                    Let (

                    [

                           X = 1

                         ; Y = 2

                         ; Z = 3

                         ; A = Z - Y

                         ; B = A * Y

                         ; result = A + B + Z

                    ] ;

                    result

                    )

                     

                    NOTE: the variables can call on values within the database, with one of the biggest advantages of this being that you can make just one call. For example, in you opening example, you could include at the head of your list of variables type = DatabaseField:Type. Then subsequently you need only refer to this variable rather than out to the field itself (Case ( type = "A" ; do this ; type = "B" ; do that ; etc)

                    1 of 1 people found this helpful
                    • 7. Re: New to Filemaker, How do I do set a field to a calculated result?
                      swetzel

                      Thanks everyone.  I want to automatically calculate the value for a field.  I guess I can do that in the calculation window.   I am trying to create a constant in the calculation window but I get an error.  What Am I doing wrong?

                       

                      Using part of my example above I try:

                      Let ( X = 1 ; X )

                      Let ( Y = 1 ; Y )

                      Let ( Z = 1 ; Z )

                       

                      Let ( A = Z-Y; A)

                       

                      Case (

                        Type = "A" ; A

                      )

                       

                      I think  I am a missing something, sorry I am such a noob at this.  I know how to program, I just don't know how to do anything like this in Filemaker.

                      • 8. Re: New to Filemaker, How do I do set a field to a calculated result?
                        swetzel

                        I think that helps Keyword, thanks to all of you for the help.

                         

                        I think I need to do something like:

                         

                        Case(

                        Type = "SH" ;

                         

                             Let (

                             [

                                    X = 1

                                  ; Y = 2

                                  ; Z = 3

                                  ; A = Z - Y

                                  ; B = A * Y

                                  ; result = A + B + Z

                             ] ;

                             result

                             );

                         

                        test2;result2;

                         

                        test3;result3;

                         

                        /*more tests here using same as above */

                         

                        defaultResult

                        )

                         

                        Correct?  I will give it a go after dinner!

                        • 9. Re: New to Filemaker, How do I do set a field to a calculated result?
                          philmodjunk

                          That depends. Will the variables have different values for each type?  If so then you are correct, but if the variables have the same value for every type, you should start with Let type and put Case were you put result.

                          1 of 1 people found this helpful
                          • 10. Re: New to Filemaker, How do I do set a field to a calculated result?
                            swetzel

                            Thank you everyone.  With your help, I was able to get started on my complex calculation.  Once I understood the basic syntax its pretty easy.