If not sure if this is even possible in Filemaker Pro 12, but I would like to have a cumulative sum from some data columns and show it in a layout form view.
For example if I have a table with five fields
ID Type Lymph Neutro Eosin Mono
1 A 10 5 3 2
2 A 8 7 2 3
3 B 5 1 0 0
Now I can add a column where I calculate the sum of lymph, neutro, eosin, and mono for each row. However, I would like a column where I have the sum of these columns for each type. Ideally, I would have the cumulative sum on a layout so that as I enter data I can see the total number of cells that I've counted.
ID Type Lymph Neutro Eosin Mono RowSum CumSum
1 A 10 5 3 2 20 20
2 A 8 7 2 3 20 40
3 B 5 1 0 0 6 6
The general idea is something like this in a generic(fictional) computer language:
CumSum = if(Type[ID] == Type[ID-1], RowSum[ID] + CumSum[ID-1], else(RowSum[ID]))
Is this even possible? I am completely new to Filemaker Pro, so any help would be appreciated.
You are looking for Summary Fields. Essentially, to create a Summary Field, you go into
File>Manage>Database...
and create a new field with the type set to Summary. when you press Create, a box will pop up allowing you to choose what you want to summarize.
Now the important thing to understand is that the field will determine what records to add up based on where it is placed in a report.
What you want to do is create a report layout, and have a section that is a Subsummary When Sorted By Type. You then place your summary field in that section of the report, and sort the report by Type.
Also, I usually create my own Summary Fields, but Filemaker may be able to create them for you automatically when you create a report layout.
There is more information on Summary Fields here
And there is more information on creating layouts here