Calculating (counting) duplicate entries.

[Christopher L. Miller]

I have a field:

"RecID"

The different records would be:

abc-1

abc-1

abc-1

abc-2

abc-2

abc-3

I have a field:

"CountRecID"

I want to be able to show how many of each (summary) there is. So the records would look like this:

RecID | CountRecID

abc-f | 3

abc-f | 3

abc-f | 3

abc-a | 2

abc-a | 2

abc-v | 1

I can get the CountRecID to return the number "6" because there is six total entries, but I cannot get it to return how many similar entries there are. It seems as if I am messing up the "breakfield"

I eventually have a field set up to tell me if CountRecID is greater than some number (using the system to flag entries).

Thanks!

Edited March 31, 2007 by Guest

[mr_vodka]

Run the summary report grouped by RecID. Put the summary field that counts the records on the same subsummary part as the RecordID field. You should be able to delete the "body part".

Your report will look like:

RecID | CountRecID

abc-f | 3

abc-a | 2

abc-v | 1

which IMHO is what you would want. I dont see the necessity to repeat the count for every record.

[Christopher L. Miller]

I will give that a try, but I do want the count on each record, since I will be using that count as a check to see if there is more than the allotted number when the record is being browsed.

The next thing I would be doing is something like this:

if ( CountRecID > MaxNumPer ; "F" ; "")

Make sense?

[mr_vodka]

If you want to use it in browse mode in the way that you mention, then you will need a self join instead that is keyed off RecID.

Then in a calculation, use a count (SelfJoin::RecID).

You can then use that calc in the compare

Case ( cCountRecID > MaxNumPer; "F" )

[Christopher L. Miller]

okay, I followed your directions, no luck. i followed the directions in FM Help: "Identifying duplicate values using a self-join relationship".

I create field A as a calculation of two fields, results are set to "text". (which would tell me if there is a duplicate).

I joined field A to itself.

I create field B and set it: count (table1::field A)

NOTHING. i tried changing the field B to be: count (field A) and all the records just show the number 1.

Now, field B does work when i change field A to be JUST A TEXT FIELD and I enter in the same value into that field, it than starts to do a count.

Is there a problem with calculation fields on the server?

If I change field A to just a text field

[mr_vodka]

Like this?

Dup_count.zip