Calculating # of Value List Instances

[Calantha]

Hello there, i'm requiring your help yet again.

I have a database of 313 graduates that lists their current area of employment via a value list

i.e. archival, a/v tech, multimedia etc.

I would like to have a layout that calculates and tells me how many of my graduates are in each field.

i.e. Archival - 1, A/V Tech - 8, Multimedia - 16

I thought that it would need to be done by some sort of if loop, but i'm unsure how to go about implementing that.

Your advice is greatly appreciated!

[albud1962]

Check out the post about counting. These guys answered my exact same questions. Drop me a line if you would like to see how I tackled the problem.

http://fmforums.com/forum/showtopic.php?tid/169124/post/172804/hl/+albud1962/#172804

[albud1962]

I used a portal database to display the results of the counting. I used zipcodes as the common key.

sample.zip

[Calantha]

Thanks for your quick reply! I'll try that out!

[Calantha]

Actually I am a but confused, how is it I would use the count function?

I'm unfamiliar with it, sorry.

If my database is called School, and the field I would like to count is called Employment, and the list item from the Employment field I would like to count is "Archival" how would I do that, something like:

Count(Employment::Archival) ?

[comment]

The Count() function only checks if the field has a valid, non-blank value. ANY value. To count SPECIFIC values within a field, you need to break them out into separate fields.

If you have many possible values, you can use a repeating field for the break out, then use a summary field to count (actually, sum) the repetitions individually. See the demo I have posted in this thread.

[albud1962]

no it would be

count( school:employment)

In your portal, lets call it talley, with a field called employment. Then talley:employment would be related back to school:employment. Talley:employment would have the career types you wished to count. I am assuming the categories would be limited to at the most 10.But if that is not the case, the way you could insure that you had all the different employment types in talley:employment would be to make it unique, then import the school:employment field into Talley. There would be only one listing for each category of employment. This is basically what I did with the zipcode example. I hope this is understandable. I have some more example files which might make this a little clearer.

[Calantha]

Hello

Unfortunately I am not up and running with FM 7 yet so I am unable to open the file. I already have them all listed in a Columnar Report if that helps? I'm unsure how to use repeating fields to break it out...

[comment]

Ooops. Sorry, haven't noticed your version. I have never used 6 so I don't know it will work. Probably not (check in Define fields, when you define a summary field of type Total, if you have the option to summarize repetitions individually).

If not, you have two options:

1. Create a calculation field for each category, that will return 1 if the record belongs to that category. Then a summary field for each category (Total of calculation field).

That's a lot of fields, so you'd probably would be better off following albud's suggestion, which in a nutshell is:

2. Create another file, Categories, where each record is a category. Define a relationship from Categories to your School file, so that

Categories::Category = School::Employment

In the Categories file define a calculation field =

Count ( School::PersonID )

This will return the number of graduates in that category.

[Calantha]

Thank you, I will give that a try and see if I understand correctly and whether I can do it without running into any snags!

Thanks again!

[albud1962]

Take a look at the file I posted here

http://fmforums.com/forum/showtopic.php?fid/31/tid/164109/pid/174268/post/last/#LAST, example.zip. It was written in fmp4.0 so you should be able to follow it. The reason why I checked for dups was to avoid totaling given zipcode more than once.

[Calantha]

I apologize for being so unbelievably daft, but I still have not figured out how to do this, even with all of your examples...

..I'm sure I am just overlooking on small piece of information.

I have now upgraded to FileMaker 7 (and I can't even find the define fields command...!)

[LaRetta]

File > Define Database

Yep, they moved them a bit from vs. 6, combining Define Fields and Define Relationships. :wink2:

[Calantha]

Thank you!

I had just found it myself and then felt stupid about posting about it, hah hah!