Displaying and counting unique records

[Ritche M]

How do i retrieve unique records and count them.

[dbruggmann]

I understand, that you want to find and count all records, which have a unique value for a particular field ("particularField"). I think there are several ways, to achieve this. I couldn't come up with an easy way (maybe it's too early in the morning ), but I found a complicated one! It goes like this:

1) You need a self join between "ParticularField" and "ParticularField", call it "Self by ParticularField"

2) Define a calculation field "c_ValuesCount1" = Count(Self by ParticularField::ParticularField). This will give you the number of occurences for every value in "ParticularField"

3) Create a field "Constant" with the value 1 in every record (for example a calculation = 1) and set up a second self join relationship: This time between "c_ValuesCount1" and "Constant". Call it "Self ValuesCount1|Constant". (The easiest way would be to set up this relationship in reverse, to count all the records, where there is a 1 in "c_ValuesCount" (=unique)

UniquesCount.fp5.zip

[MoonShadow]

If several fields make the record 'unique', create a concatenated calculation (text, index on) out of them (example: FirstName & " " & LastName). Join your unique single field (or concatenated calculation) to itself (self-join).

To count them:

Count(selfjoin::UniqueID)

Here's a trick I learned from Andy Gaunt to retrieve them:

Your unique ID needs to be numeric or you can just create a calculation (number) called RecordID with: Status(CurrentRecordID)

Create a calculation (number) with:

Case(Max(selfjoin::RecordID) = RecordID, 1)

Search for a 1 in this calculation. That will be one of each 'unique' record - your last one...

Oh, I forgot to mention that your found count will be your count of unique. You don't need Count() at all - only the second calculation. I hadn't had my coffee yet!

[-Queue-]

You can also use selfjoin::RecordID = RecordID to tag the first related record (according to the sort defined for the relationship). If there is no sort defined, this will mark the original record with 1 and all duplicates with 0.