Using calculation to mimic summary field

[FMquestions]

Hi,

I was wondering if it is possible to mimic the summary field type by a calculation so that I can minimize the fields in a table. I have a people, object and measurement table that stores different type of measurement.

Let's say a person has two objects (i.e. cat and dog) and the object's height and weight have been measured three times. For the person, I am interested in getting the sum of each type of measurement (height and weight) for the first measurement and then sum of all the subsequent measurements. What I am currently doing is defining two additional fields in the measurement table: height_baseline (calculation to determine if this is the first or follow-up measurement) and height_baseline_sum (summary field type of height_baseline). To get the follow-up sum, I use two more fields. I am also interested in getting the sum of the measurement if the object is marked as "targeted" in the object table. If I was to follow my method, I would be creating another two fields.

This method works but I feel like a calculation that can determine if it is the first or follow-up measurement and also sum the measurement would be more efficient. I've tried using the sum function but it would only give me the summary for an object but I need the summary of all the object of that particular measurement for a person. Any ideas? Please let me know if I need to clarify anything else. Thanks a million in advance.

[comment]

I am not quite clear of what you want to get at the end. Why don't you continue with the example you started:

Let's say a person has two objects (i.e. cat and dog) and the object's height and weight have been measured three times.

So that would be 12 records in the Measurements table (2 objects x 2 parameters x 3 measurements), right? And now what?

[FMquestions]

It will actually be 6 records in the measurement table because the 2 parameters are in different fields. The assumption is that every time a measurement is made, both the height and weight will be measured.

I want to get the baseline (defined as 1st measurement) and the follow-up (defined as the 2nd and 3rd measurement) sum of both the cat and the dog for each person. For each type of measurement (height and weight), I have 2 fields (height_baseline and height_follow-up), and then 2 more summary fields (height_baseline_sum, height_follow-up_sum). As you can see, that is 4 additional field for each type of measurement, which is extremely inefficient.

The additional summary I need is a summary of the height of all objects that is marked as "targeted", this is another field in the measurement table.

[comment]

I am still not sure I follow this fully. I think that for each object you can calculate =

Sum ( Measurements::Height ) - Measurements::Height

to get the "followup" (assuming the first related record is the baseline).

Of course, the more fields you have in the Measurements table, the more summary/calculation fields you will need to summarize them. That's why it may be preferable to structure Measurements as I suggested at first - i.e. each measurement is a record, with the parameter measured being one of the fields. This would allow you to build a report sorted by Object and by Parameter.

[FMquestions]

I see what you mean about having each parameter as a separate record so that the report can be sorted by the parameter but we want are ultimately interested in the change between each measurements. Thus for each measurement, we want all parameter to be listed together in a row so that we can see them all at once. Does that make sense?

I think your method will only provide the sum for each object but I am interested in the sum of all the object for each person.

I hope this might clear up some of the confusion:

People Table: Person1

Object Table: Cat, Dog

Measurement Table (sorted by object):

Dog

Date Height Weight

1/1/2010 30 45

1/2/2011 31 50

1/3/2012 31 50

Cat

Date Height Weight

1/1/2010 15 25

1/2/2011 15 26

1/3/2012 15.5 27

Baseline_height = 45 Baseline_weight = 70

Follow-up_height = 92.5 Follow-up_weight = 153

[comment]

I think your method will only provide the sum for each object but I am interested in the sum of all the object for each person.

No, the same summary field will provide sub-summary values for any sorted group. If you sort the measurements by person, parameter and object, you can get:

Person 1

Height

Dog: 91

Cat: 45.5

Total: 136.5

Weight

Dog: 145

Cat: 77

Total: 222

Person 2

...

using only one summary field. This is using Filemaker's default (i.e. vertical) reporting method, which is easy (and very fast). To view the results side-by-side you'll need something more elaborate - see one possible method here:

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

[FMquestions]

Thanks for sharing this. If I understand correctly, you had 22 portals displaying one record at a time. In my case, instead of having location by year, I would have dates by type of measurement. This is a really neat method but in my case, I would never know how many measurement a person can have and it looks like you need to design the layout knowing the number of measurement or number of location in your example.

[comment]

If I understand correctly, you had 22 portals displaying one record at a time.

No, each portal displays the summary value of a group of records.

it looks like you need to design the layout knowing the number of measurement or number of location in your example.

Not really. You need to know how many columns you'll use - but you can use list view for any number of rows.