Getting a count of all fields with X value

[DVkid]

This is my first post and so far I'm finding your information very usefu.

I am working on a database in which show times for a presentation will be selected via a value list. In a layout I want to be able to display the number of records with 6:30 selected in that field.

I have looked at doing it with Count but don't see a way to specify a specific value I want for count. And Sum didn't seem to be able to do it either. Does anyone have any clue how I might do this?

Thanks.

[Lee Smith]

Here is a link that I think shows what you are looking for.

Link

HTH

Lee

[DVkid]

Thanks for your help.

I've got PatternCount working, but it only applies to the currently viewed record. Is there any way to show it for all records? I read the post you linked to but it seems to deal more with summaries. My understanding is I can't apply a summary here because I'm working with specific values not entire sets of field values. (IE only the records with purple in the favorite color field, not all the files with something in the favorit ecolor field.)

Thanks for your help so far!

[Genx]

Why not just make your calc field say something like:

If( table::color = "purple" ; patterncount( table::field ; "x" ) )

Then you can just use a summary field on that.

[DVkid]

That worked great, thanks.

My only problem with it is that when I update the favorite color field it immediately updates the intermediate calculation field (the one with the patterncount in it) but the Summary doesn't update right away. You have to go to a different record and come back to view it.

Also, the If is not necessary, the patterncount returns a 1 if the color is purple and then the summary picks it up just fine.

[DVkid]

Just kidding about not needig the if. Upon further testing I found it is necessary.

Is there any way to make this solution apply to the entire table, not just the found set?

Thanks!!!

[Genx]

Ah... Okay lol.

Yes, unfortunately, the summary won't update until the window is refreshed.. You could do this with a script and a little refresh button to refresh the window.. or you could trigger the window refresh when your field changes by plugin... but it's probably not worth the effort.

[AudioFreak]

Honestly I think having it work based on the found set is more functional. Allows the user to search for what he is looking for and get the numbers instantly.

Show all records would be a fast easy way to get the whole table. I would suggest keeping the search option for the users myself.

Michael

[Genx]

Ummm... for the whole table you say.

You could do this via a self-join instead.. but this is what happens when you don't make what you want entirely clear.

[DVkid]

So...create a self join and use that relationship as the context for the calculation?

[Genx]

Yes... going on with our color example..

We will call our first TO "primary" and our second TO "secondary", our color field "color" and our pattern counting field "pattern"

Make a calculation in your table and call it "calc_const_color" or something, make it a stored calculation and give it the value of "purple".

Go into your relationships graph and add another table occurance of your table -- name it secondary. Relate primary to secondary via calc_const_color = color ...

Finally, make another calculation field in your table and call it "total_for_color" - set this field to be equal to sum(secondary::pattern) - ensure that it is evaluating from the context of primary (see up the top of the calc define window).

... and you can get rid of the if in your pattern calc field now if you want.

HTH