You should look up the sum function in Help.
Sum ( FIeldA )
will just return the value of FIeldA from the same record.
Sum ( Field A ; Field B ; Field C ) will return the sum of those three fields from the same record as the record in which you defined this calculation field.
Sum ( Repeating Field ) will sum the repetitions of the repeating field
Sum ( Related Table::Field A ) will sum the value of Field A across all the records in the related table that are related to the current record of the table in which you defined this sum calculation.
Either define the Sum Calculation in a field in the Results table instead of expectations or use a summary field in Expectations in place of this calculation field.
That's what I thought, None of them work though. I have done the following:
- created a calc field in the results table with : Sum ( expectations::gross_income ) (this stays empty when put on the layout).
- put a summary field of the expectations table summary -> gross_income (this stays empty when put on the layout).
Portals are not working either so something is wrong with my relationships but I have no clue what it is.
creating a single condition relationship trough a text field should work right?
Se my first post for the description.
1 of 1 people found this helpful
can you take a screenshot of the relationship between these two tables and perhaps a screenshot of the results (in layout mode)?
the results table:
the expectations table:
the layout based on the results table: (the gray field left is the 'expect:income' field, the the results table. As you can see nothing is displayed, just the placeholder text)
That's it, let me know if you need more info.
1 of 1 people found this helpful
From the connector line, the fund field in expectations is a global or otherwise un-indexed field. This is what is keeping this calculation from working. The relationship from expectations to fund_results is a "one way" relationship from expectations to Fund_results, it will not work from Fund_Results to expectations.
This is it! I made them indexable and now they work.
does this mean:
- I cannot use a field that has a calculation which gets the fund from the related table's record?
- I can use an auto enter text field with this calc, but when somebody changes the fund in one record, the related record does not get updated with the new fund assignment?
- I need to write a script to avoid this?
Well it depends. Note that a relationship where one ( not both ) match field is unindexed does work, but only in "one direction". So as in the case of all FileMaker relationships, context is key (pun intentional).
Two methods are commonly used to get around this limitation where you need to calculate a value from related data, but need it to be indexed:
Make it an auto-enter calculation
Use a script to update the value everytime a value in the related table is changed/added/deleted.
An auto-enter calculation does not re-evaluate when data from a related or global source is modified so you have to script the updates. Thus, both methods are often combined. Script triggers and custom menus are usually needed to implement those needed scripted updates.
OnObjectSave on a field in the child record, for example, could update a parent record value whenever the value in that field changes. A custom menu can replace the standard New Record and Delete Record (and Delete Found Set) options with scripts that both update the needed values and then do the needed record deletes.