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.)
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
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.
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.
Thus, you might find using calculation fields that compute the year in option 1 an easier option to implement.
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.
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.
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!
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
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.
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:
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
Perfect! Your a life saver! Thank you! Once again for you've helped me out!