You can have a Summary field (in the child table) which is the total of this field.
If you place that field inside a single row portal (same relationship), then you will get the total of all rows. You can then Filter the portal.
Make the "filter" be something like:
relationshipTO::Include = "Y"
and then ONLY those will be 'summed'.
Sum ( Field1 ; Expression ) is another way of writing:
Field1 + Expression
Sections::chk_Include = "Y"
Is an expression that evaluates to produce only two possible results: 1 (TRUE) or 0 (FALSE)
When an expression in FileMaker has this format:
There might be a million related records that link to your current record, but the expression only refers to the value of the "first" related record. In an unsorted relationship, the first related record is the oldest related record (The first one to be created).
Thus you are getting 60 + True or 60 + 1 which is 61.
Sum has no "conditional parameter" capability. It simply either sums the listed fields, or if used in this format:
Sum ( RelatedTable::Field )
Sums up all the values of Field of those related. It can't "pick and choose" which related values to sum.
You have several alternative approaches possible:
Set up a relationship different from that used for your portal that only matches to included records. Then sum (RelatedTable::Field ) will work.
Define a summary field in the related table that totals this field. Put this summary field inside a one row filtered portal where the portal filter expression excludes all records where the check box is not selected.
Use ExecuteSQL with the Sum function and a WHERE clause that only selects the fields that are included and that appear in this portal.
Thank you both beverly and philmodjunk,
For helping to understand the problem. Since I came from the world of SQL before starting Filemaker, I found it to be the easiest solution for. me.
I did try some of the other solutions but I couldn't figure out which field to include or not to include, etc. Everytime I tried I either got my original answer, the total answer (105) or nothing appeared.
did you use ExecuteSQL() then? please post your final solution.
Yes, I did use ExecuteSQL and here it is:
ExecuteSQL("SELECT sum(NoOfDays) FROM Section WHERE chk_Include = 'Y' " ; "" ; "" )
Unless your original portal shows all related records, you need another term in your WHERE clause.
Add the same equality statement as in your relationship (for the portal). ExecuteSQL() does not know context, so will not just sum the related records unless you tell it.
" SELECT SUM ( Section.NoOfDays )
WHERE Section.foreignKey = ?
AND Section.chk_Include = ? "
; "" ; "" // default delimiters
; currentrecord::primaryKey // you need to match for the portal on each record
; "Y" // use parameter to pass data to search
Your fields, of course, would replace the 'foreignKey' and 'primaryKey'.
excellent example of a calculated field (sum) in the parent based on a calculated field in the child!
Thank you BruceRobertson,
Now I see where I was going wrong in FM. I understand things so much better with an actual example.