Using the 'count' function based on conditions

[heyarn]

Hi,

I want to be able to count records related to my parent table based on the field's value.

The parent table is called 'group' while the related table is called 'individual'. The 'individual' table has a field called 'survey' which has integer values from 1 to 5. I want to create a summary field in the 'group' table which will count based on the value of the said field. For example, I want the summary field to count the number of 3's in the related field.

I tried the 'count' function but this counts regardless of the value in the field. Can someone please help? What's a good way to make this work? :

Thanks in advance!

:)

[Fitch]

This is a fairly common situation where the reporting/presentation requirements were not sufficiently considered when creating the data schema.

You could create five calculated fields like so:

One: survey=1

Two: survey=2

etc.

... and then use Sum or Count to total them. But why bother? You might as well just break "survey" into five separate fields to begin with.

Note that we're talking about calculation fields, not summary fields. You actually can use the data as is and use a Count summary field -- but only in the related table. You could write a script that finds the related records, summarizes them and returns the values to the parent record. Just a thought.

[bcooney]

You leave us hanging, Fitch. How would you have modeled this? I would imagine that survey couldn't easily be five separate fields, because you'd want to have radio-button behavior.

I don't see a better way. I'd probably have the one field and then the five flag fields, too.

[David Jondreau]

If you had Advanced, you could use the TypeSumField custom function or just create your own.

[Fitch]

Barbara, it's not that hard to set up 5 fields as radio buttons. You could use a single script that takes a parameter depending on which of 5 buttons was clicked. (I'd rather make my presentation layer a little messier than have messy data.) I think you could also accomplish this with auto-entry options on the 5 fields, and no script/buttons.

[bcooney]

I think it's a matter of style. I used to be all over techniques that had hidden fields, buttons with a million small parts grouped, but I've learned that those techniques are hard to maintain, and demand a lot of communication between developers (it's not just me on the project).

So, I feel the one field formatted with radio buttons, and then flag_calc fields to analyze which is selected would be my preferred method.

btw, I also don't declare variables (global or local) in button script params if I don't absolutely have to.