12 Replies Latest reply on May 20, 2015 4:23 PM by gremlin9297

    Sum for a specific year

    gremlin9297

      Title

      Sum for a specific year

      Post

      Hello again my friends,

      I have a portal that represents the number of days taken throughout employment, so it spans several years. My goal is to sum the sick days of just the current year excluding any previous years count. The attached is the current calculation im using... problem is that it either looks at one one records date instead of all of them and the calculation fails or it sums everything instead of the specific date. Thanks in advance for any help. 

      Sick_Total.png

        • 1. Re: Sum for a specific year
          philmodjunk

          Your calculation is doing exactly what it was designed to do. It just won't do what you want it to do. wink

          Any time you set up a reference to a field from a related table such as your portal in a calculation, this reference only accesses the value of the specified field for the first related record. That would be the first record in your portal if it is unsorted and unfiltered.

          To get such a subtotal from a set of related records can be done in a number of different ways:

          You can set up a new relationship to a different occurrence of your portal table such that you match by year as well as by the primary key (employeeID?) used in the current relationship. This can be a global field such that you specify the year in that field and then Sum (NewTableOccurrence::Sick) will give you the total for each employee.

          You can setup an ExecuteSQL calculation where the WHERE clause specifies the year.

          You can set up a one row filtered portal to your current portal's table occurrence where the portal filter filters by year. You can then put a summary field defined in Vacation_Sick that totals the Sick field in this one row portal to show the desired total. (This last option is display only, it's not a good choice if you then want to use this total in a script or calculation.)

          • 2. Re: Sum for a specific year
            gremlin9297

            Unfortunately the totals are used in other calculations, the other bit was that it would be an automatic calculation.  What i'm aiming to do is have the total be a calculation that automatically matches the dates(specifically the year) of the sick time taken to the current year and then shows a total of sick days taken that match only the current year. So option 3 wouldn't work and option one would be a last resort since it would require entering a year each time to get the total. The attached should give you an idea of what i'm trying to accomplish. The total is just an object not a portal. Thanks again for any help

            • 3. Re: Sum for a specific year
              philmodjunk

              Option 1 cn still be used by setting up calculation fields with the year function to generate the needed match values in the two tables.

              And executeSQL also can be used if you are up for using SQL and have FileMaker 12 or newer.

              • 4. Re: Sum for a specific year
                gremlin9297

                I have the requirements to run the executeSQL... only thing is i haven't used it before and not familiar with setting up a calculation that uses it. 

                • 5. Re: Sum for a specific year
                  philmodjunk

                  Thus, you might find using calculation fields that compute the year in option 1 an easier option to implement.

                  • 6. Re: Sum for a specific year
                    gremlin9297

                    hmmm.. after looking up SQL looks like it may have future benefits form learning it. I tried a calculation or string for SQL but its not working and not sure where the error is.

                    ExecuteSQL ("SELECT sum(vs.sick) FROM Vacation_Sick vs WHERE year(vs.Date) = year(current_Date) " ;"" ; "" )

                    I am so new at this that any and all help would be great. thanks.

                    • 7. Re: Sum for a specific year
                      philmodjunk

                      How is it not working? Do you get the wrong result or a question mark?

                      I would think that you'd write it this way:

                      ExecuteSQL ("SELECT sum(vs.sick) FROM Vacation_Sick vs WHERE year(vs.\"Date\") = ? " ;"" ; "" ; Year ( Get ( CurrentDate ) ) )

                      Note that it is often necessary to enclose field and table names in double quotes by using \"Name\". The backslash is needed to insert the double quote as a character in the string instead of the character that marks the end of the quoted string.

                      • 8. Re: Sum for a specific year
                        gremlin9297

                        Perfect! That worked! Seems I needed to add the quotation marks and move what is was being compared to the end of the sequence. Now I just need it to calculate the current record viewed instead of all records... what do I need to do to constrain the calculation to the record being viewed?


                        Thanks again for all the help! Saving me hours!

                        • 9. Re: Sum for a specific year
                          philmodjunk

                          What specific data in your current record needs to be part of this query?

                          I'd guess that you need to use AND in your WHERE clause and compare a field in your query to a second question mark. You then add a field from your current table as one more parameter at the end of the ExecuteSQL function call.

                          Useful SQL resources:

                          FileMaker Inc's PDF on SQL: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
                          SeedCode's SQLExplorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                          • 10. Re: Sum for a specific year
                            gremlin9297

                            not entirely sure. I need it to get the total which it currently does, but of just the record currently being viewed. Unfortunately since i am extremely new to SQL im not sure what information to add to the sequence so that the query reflects only the current record and not every record. 

                            • 11. Re: Sum for a specific year
                              philmodjunk

                              What tables and relationships are invovled? What uniquely identifies the current record and then is used to match to the correct records to get this sum? Do you have an employee ID field in an employee table that is the basis for your current record? Is there a corresponding employee ID field in the vacation sick table?

                              This is what I think that you need:

                              ExecuteSQL ("
                              SELECT sum(vs.sick) FROM Vacation_Sick vs
                              WHERE year(vs.\"Date\") = ? AND vs.\"_fkEmployeeID\" = ? "
                              ;"" ; "" ; Year ( Get ( CurrentDate ) ) ; __pkEmployeeID )

                              But your field names may be different than my example

                              • 12. Re: Sum for a specific year
                                gremlin9297

                                Perfect! Your a life saver! Thank you! Once again for you've helped me out!