Number of times...

[Addam]

Is there a way I can find out how many times a value is in a field?

Ex:

Field1= "1" 4 times...

Field1= "2" 6 times...

How can I get file maker to tell me how many times "1" is in this field?

Thanks!

~Addam~

[SteveB]

Use PatternCount(fieldname,"1") to count the number of times "1" appears

[Addam]

That works but... it doesn't give me the number of times it has been used in THAT field in THAT database.

sorry If I wasn't clear...

[Vaughan]

You confuse "record" with "field".

Perform a find.

[Jvilla]

This is similar to the problem I have--I need to find the number of times a value appears in a range of fields within a record. -- I need 4 summary fields which count the number of

times 4, 3, 2 or 1 appears in

each teacher's class record student1_e1 through student37_e1

Would Pattern count do that? how do I ask it to count across a range of fields?

[Fitch]

Addam and Jvilla,

I'm pretty sure that what you're asking for is a Summary[Count] field.

I just posted a follow up to the same question here.

[CWH]

I have a similar problem, but using PatternCount seems like a cumbersome solution for it. I have applications that are assigned 3 different reviewers (Primary, Secondary, Tertiary). I want to be able to tell how many applications each reviewer has of each type. Using PatternCount, it seems I would have to set up a separate calc field for each reviewer, and there can be as many as 30.

[Fitch]

(...taking a deep breath...)

CWH, if you sort by reviewer and then by application type, and have a summary field Count(application), put it in a summary part, go to Preview... see the link in my previous post.

The only reason to use PatternCount() is when you have a field formatted with a value list and checkboxes, so there's more than one value in a field. Otherwise, why bother?

Even then, once you've separated the values, it's back to the summary as I've described.

[CWH]

Fitch, thanks for your suggestion. It works pretty well, but I think I wasn't clear in describing our situation. The applications are the same, it's the reviewers who are assigned as either Primary, Secondary or Tertiary. In order to get a count of how many reviews of each type each reviewer has, the way I implemented your suggestion required 3 summary fields and subsummary parts for each of them. Then to see the results, it requires sorting 3 different times for each review type. Maybe the clunkiness is due to the way the database is set up or maybe I'm missing something.

By the way, I don't know what the protocol is for the forums, but I would feel guilty asking for this much free advice (you already helped me earlier this week), except that I work for a non-profit that funds pediatric AIDS research.

Thanks again,

Chuck

[Vaughan]

CWH -- the solution would have been easier and more elegant if it was relational. The reviews would be related records, not fields. Getting stats out of multiple records is easy, much easier than geting them out of multiple fields in each record.

[CWH]

That looks cool, but when I try to set up the first Count field, FM says it can't find the One::reviewer field, even tho I set up that relationship. Any thoughts?

[Fitch]

Define a calculated field, and in the definition dialog box, type Count()

Put your cursor in between the parentheses, and in the upper left, where it says "Current file," click to pop up a menu of relationships you've defined. Select the one you want and you'll see all the fields available. Double-click on the desired field. You're done.

In a nutshell: don't type in the calculation, do it all with the mouse, that's the POLR way.

[Fitch]

Chuck, as far as I'm concerned, you can ask for all the help you want! You don't hurt anyone by asking, and nobody is forced to respond. I'm always happy to help where I can, and now and then I get a gig out of it. The only people that annoy me a little are those that don't put any effort into describing their problem, and/or that don't even make an attempt to search for the answer before they ask. But usually I answer their questions anyway.

Now back to your question:

What I need to know is, what constitutes a record in your database? Is each application a record? I'm guessing: yes.

Next, the reviewers -- are you saying EACH application has 3 reviewers? Is there a separate field for each reviewer? I.e., a field called Primary, another called Secondary, and another called Tertiary? I'm guessing: yes.

Like you say, this is clunky. Vaughan is right, you'd have done better to set up a related file, related by application number ( a unique i.d.). Each record in the related "Reviewers" file would consist of the app. i.d., the reviewer name, and type (1,2, or 3). Then you'd do your summaries in the Reviewers file, you can see how that would be pretty easy.

So, you can either implement that system, or here's a wacky approach:

Make a related file, Reviews

Define fields: reviewer

Define relationships to the main file:

One -- reviewer::primary

Two -- reviewer::secondary

Three --reviewer::tertiary

Now define some calculated fields:

Count(One::reviewer)

Count(Two::reviewer)

Count(Three::reviewer)

Make a columnar layout with all your fields on it. Enter a reviewer's name. (You might want to set up a value list based on reviewers from the main file.) Make a new record for each reviewer, and you'll have your totals.

[Addam]

THANK YOU ALL!!

FITCH!! you are the BOMB!! Your answer was exactly what I was looking for.

KUDOS!!

~Addam~

[CWH]

Fitch: Your suggestion seems cleaner so I tried it but I think I'm out of my league with relational databases. I'm not understanding what the field "reviewer" represents. I set it up this way:

relatinship One is reviewer= ::Primary Reviewer

Relationship Two is reviewer= ::Secondary Reviewer and so on

The latter fields are in the database with the applications, but the "reviewer" field is in the separate database with all the reviewers info. Then, the calc field in that db looks like

Count(One::Primary Reviewer)

All fields are blank when I set up a columnar layout in the reviewers db

[Fitch]

Let's "review" heh...

Our main file is called "applications." Let's say we've got a reviewer named Bob, who is listed as Primary for 3 applications, Secondary for 2 apps, and Tertiary for 5 apps. I'm picturing that you enter actual names into these fields, e.g.:

Primary ..... Secondary ..... Tertiary

Bob ............. Joe ............. Xena

Bob ............. Ramon ........... Joe

Sam ............. Bob ............. Jane

Bob ............. etc.

So, now we make a file called "Reviewers."

We define a field called "reviewer."

In this field we enter "Bob."

OK, so how do we match this Bob up with the Bob in the applications file? We make a relationship in THIS file (Reviewers), from our field, "reviewer" to -- what? Well, if we want to see in how many apps Bob's name is in the field "Primary," we make our relation like this:

reviewer::Primary.

That's the relation I called "One."

So when we put a name in our "reviewer" field, it's going to match all the corresponding names in the other file's "Primary" field. So now we define a field Count() and instead of fields in the current file, we say use fields from our "One" relationship... and now I see where I messed you up!

Instead of this garbage:

Count(One::reviewer)

Count(Two::reviewer)

Count(Three::reviewer)

What I MEANT to say was:

Count(One::primary)

Count(Two::secondary)

Count(Three::tertiary)

Sorry about that! But... it looks like you did it the right way, if I'm reading your message right. The bottom line is, you don't make ANY modifications to your existing applications database. You just make this reviewers database, define one text field, 3 relations, and 3 calculated Count() fields, and you're done! Make a new record, and put a reviewer's name into the "reviewer" field. If it matches a name in applications, you will see the totals. I've tried it, it works.

In fact, here's my lame little demo:

a tiny download

Wow, this is getting long... better stop before I bore everyone to death.

[CWH]

Fitch: Talk about above and beyond the call of duty!

Thanks for the helpful example databases. I'm going to try it again with ours, although I think I'll use a calc field in the reviewers database that combines first and last name so the name is more apt to be unique.

thanks again

PS After giving this a go, I've run into another hitch. I need to see only the reviewers from the current cycle. I'm going to experiment with an If function to see if that can look at the Cycle field first and only calculate the totals from a specific cycle. If anyone thinks I'm headed down the wrong path, please let me know.

Chuck

[ February 20, 2002, 12:54 PM: Message edited by: CWH ]