A simple count solution

[paulage77]

This is probably easy to do, and I'm not thinking about it correctly.

I have a field called s_Zone, which ends up having a value "Zone 1" thru "Zone n". What I would like is a way to display, via field, the count of each Zone. That is, something like this:

Zone 1 : 100 records have s_Zone containing the value Zone 1

Zone 2: 65 records have s_Zone containing the value Zone 2

...

Zone n: 1200 records have s_Zone containing the vlaue Zone n

easy right? do I need a script to populate n global fields? I know it's very easy to obtain this info by doing finds, but I'd like to have a seperate layout that contains this info.

Thanks all

[Lee Smith]

I think you want to use the Pattern Count fuction for this.

Lee

[Ugo DI LUCA]

Hi,

Use the ValueList Item function to display all references from that field.

Then Patterncount(VList) +1 will give you the number of records for that field.

Then create as many global field (gzone 1 = "zone1") and relationship g_zone1::zone, g_zone2::zone as the count of your patterncount.

The easiest part IMO is the count iteself with a count(relationship::record_id) for each relationship.

[Ugo DI LUCA]

Well,

a simple Count(selfjoinOnZone::Zone) would also work as fine.

Then, if you had more than one record with same zone and you want only to display one record at a time, use a workaround

Case(record_id = Max(selfjoinOnZone), 1, 0).

Display all records in a constant relationship portal and sort by the Case calc given,

or

Search for all records with 1 and display the records within a list report.

[Lee Smith]

Or a separate calculation field for each "Pattern".

or two fields, one a global using and the other calculation using the pattern count and the global field. I use this all of the time in find layout I have, so I can see how many of what word, or phase or pattern there is in the field being search. The global is also the filed holding the search critiera. So the pattern count is very handy after I've done a search.

HTH

Lee

[paulage77]

Thanks guys. Ugo, I thought maybe it would be n globals and some self joines. Actually, I did not figure on the multiple joins, which is the key.

[paulage77]

Hmm.. I get an error saying the relationship will not work with the global. Says since it can not be indexed? I chose to ignore the error. Do you see this as well?

[paulage77]

The easiest part IMO is the count iteself with a count(relationship::record_id) for each relationship.

what is the record_id you mention here?

[paulage77]

Ha, I see... I defined the relationship backwards, and the record_id is the unique_id or primary field. I'll think harder before asking next time

But thank you, it works.

[Ugo DI LUCA]

Paul,

Note that I'm brain dead tonight but I cannot see why this shouldn't work.

Forget the globals and use a classic selfjoinOnZone Zone::Zone.

Supposing the zone is a field in each record, the record_id could be any serial you have there.

So "returnOnlyOne" = Case(serial = Max(SelfjoinOnZone::serial), 1,0) .

This calc cannot be indexed though, so if you want to display these zone lists, use a portal with a constant relationship (1 at left and right side for example) and sort on "returnOnlyOne".

At the end, keeping the former, you may like to have another calculation =

"returnOnlyOneNew" = Case(serial = Max(SelfjoinOnZone::serial), "Count(SelfjoinOnZone) & "records have the zone" & " " & zone & " " & "in it"),"") which is the litteral calculation you wanted to display..

[paulage77]

Ah! That works as well. easier maitenance.

Thank you much!