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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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