Jump to content
Sign in to follow this  
heyarn

Using the 'count' function based on conditions

Recommended Posts

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!

:)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Nuri Baba 
×

Important Information

By using this site, you agree to our Terms of Use.