10 Replies Latest reply on Aug 28, 2016 9:12 AM by philmodjunk

    Calculation field


      Hi Everyone,

      I want to create calculation field to count the following record as below:



      Record Date               Number of day

      12-jan-2015                         3

      13-jan -2015                        1

      3-jan-2016                           5

      15-8-2016                            2

                               Total number of day base on current year : 7


      I want to create calculation field to count record base current year only.


      Hope you all help me with this.




        • 1. Re: Calculation field

          So you want to get a total number of days but only from those records where the date is from the current year.


          ExecuteSQL ("

          SELECT Sum ( NumberOfDay ) FROM YourTableOccurrenceNameHere


                      Year ( RecordDate ) = ?" ;

                "" ; "" ; Year ( Get ( CurrentDate ) ) )

          • 2. Re: Calculation field

            Some additions,


            The calculation returns same value in all records, so the calculation field may be better defined as global or unstored or in another table or use script to calculate, not calculation field.


            If there are spaces in field name, you need to quote it like \"Number of day\"


            Using range in WHERE may be better for performance.

            RecordDate >=? AND RecordDate <=?

            • 3. Re: Calculation field

              Hello, I tries follow what you said, but it showing question in the result.

              • 4. Re: Calculation field

                ExecuteSQL is very picky about field and table occurrence names.


                Please post exactly what you have attempted to set up and then list the exact field and table occurrence names. A table occurrence is a "box" in your relationship graph that, in turn, refers to one of your tables.

                • 5. Re: Calculation field

                  Hello again and thanks for your help.
                  for example, in staff profile form , it will about the permission record like below

                  Permision Date     Type                    # of day

                  1-Jan 2015            Annual Leave         3

                  2-Jan 2015          Sick Leave               3

                  15- Jan-2016      Annual Leave           4

                  23-Feb 2016      Annual Leave           2

                  4 - Mar 2016     Maternity Leave         14


                  Let say I want to create calculation field to calculate how many day of this staff used Annual leave in the current year(2016)



                  • 6. Re: Calculation field

                    If you still need help, please post the expression that produced a question mark result.

                    • 7. Re: Calculation field

                      Hello, sorry that late.

                      In the result it shows  [?]

                      • 8. Re: Calculation field

                        Whats your calculation's data type? If its text, change it to "Number"(Ref: Below Image) & check-out.




                        • 9. Re: Calculation field

                          I would recommend you use an external tool, like RazorSQL, to get your queries working. FileMaker's brain-dead data viewer (a "tool" needing, IMHO, a total rewrite) is really almost no help at all. The data viewer doesn't help you write your queries with pop-up assist, it doesn't give error messages (? is it's best advice), and it has other limitations. Yuck!

                          The data viewer is "OK" for really simple stuff, but it fails miserably for any serious SQL work, that is, unless you have lots of time and plenty of frustration handling ready.


                          Using an external tool is a joy in comparison. I find I get queries working much faster and with MUCH less frustration!


                          Once you get your queries working, then move them to FileMaker and add replaceable parameters and the like.


                          NOTE: The external tool, like RazorSQL, IS CONNECTING TO YOUR ACTUAL FILEMAKER DATABASE IN REAL TIME.


                          HOPE THIS HELPS.

                          1 of 1 people found this helpful
                          • 10. Re: Calculation field

                            Several posts ago, I asked you to post exactly what you tried. I was asking for the actual calculation as you had it set up as a call to ExecuteSQL. Had you done that we might have been able to spot what was producing the ? result--which typically indicates a syntax error in the SQL query.