Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > Counting records with duplicates
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 18th December 16:03
helpful harry
External User
 
Posts: 1
Default Counting records with duplicates


Names by themselves aren't a good indicator of being the same person.
Many people can have the same name (some even the same birth date as
well), but are actually different people. In fact, from simple data in
a database there's NO method of working our who are individual people
(eg. even using the address method, the same person can easily be in
the database under two different addresses).

But anyway ...

There's two ways to find the number of unique names:

A. Relationship
You can define a relationship linking Name to Name. Then
you can define a Calculation field that calculates as 1/Xth
where X is the number of records with that name:
ie.
ThisNameFraction {Calculation, Number result}
= 1 / Count(Relationship::Name)

Obviously adding these together will total to 1 for each
name, and therefore a simple Summary field can be used to
count the unique names by totalling this field:
ie.
UniqueNameCount {Summary, NOT Running Total}
= Total of ThisNameFraction


B. Summary Fields Only
You can also achieve the same effect by using only Summary
fields. First define a Summary field to count the number
of records for each name:
ie.
ThisNameCount {Summary, NOT Running Total}
= Count of Name

Then using the GetSummary function (not sure if this has
changed in FileMaker 7) you can calculate the 1/Xth
fraction in a similar way to above:
ie.
ThisNameFraction {Calculation, Number result}
= 1 / GetSummary(ThisNameCount, Name)

Using the "Name" field as the second parameter means the
summary data retrived by the GetSummary function will
only be for that unique name.

This gives you the same counter field as the Relationship
method, so it can be totaled using the same Summary field:
ie.
UniqueNameCount {Summary, NOT Running Total}
= Total of ThisNameFraction


Either way, the summary field UniqueNameCount will give you the total
number of unique names. It needs to be placed in a Leading or Trailing
Grand Summary part of a layout, and then sort the records by Name and
view the layout in Preview Mode or print it out.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
  Reply With Quote


  sponsored links


2 18th December 16:11
stephen larivee
External User
 
Posts: 1
Default Counting records with duplicates


Thank you very much. I have printed your answer and will pursue the summary
fields approach.

Thanks again!!
  Reply With Quote
3 18th December 16:13
stephen larivee
External User
 
Posts: 1
Default Counting records with duplicates


I did this. I am using FMP 7. I selected Summarize repetitions: All
Together (instead of individually)


I did this. Do I need the 1/ before GetSummary???

I did this, again choosing Summarize Repetitions: All Together

I followed your advice and I am coming up with the number 1 in the Leading
Grand Summary.
  Reply With Quote
4 18th December 16:17
helpful harry
External User
 
Posts: 1
Default Counting records with duplicates


Sorry, I haven't used FileMaker 7, but "Summarise Repetitions" sounds a
bit peculiar. You could prehaps try the "Individually" option and see
what happens.

The "1/" is needed since that gives each record a 1/Xth fraction of the
toal number of records for that name, then when they're all added
together you get a total of 1 for that name. Then adding together all
these you get the total number of unique names.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
  Reply With Quote
5 18th December 16:22
stephen larivee
External User
 
Posts: 1
Default Counting records with duplicates


Thank you. I will give it another try.
  Reply With Quote
6 18th December 16:25
helpful harry
External User
 
Posts: 1
Default Counting records with duplicates


One other thing - you DO have to sort the records and then print the
layout or view it in Preview mode for Summary fields to work properly.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
  Reply With Quote
7 18th December 16:28
fp
External User
 
Posts: 1
Default Counting records with duplicates


There is another way to get a count or list of unique names.
Create a value list of the name field.
Set a global text field via script to:

ValueListItems ( Get ( FileName ) ; "VLNAMEHERE" )

This will put the contents of the value list into the global text
field. To get a count of the names, count the carrage returns + 1
because the last name doesn't have a carrage return. This also allows
you to get the names out by themselves if you need to work with them.
  Reply With Quote
8 18th December 16:31
helpful harry
External User
 
Posts: 1
Default Counting records with duplicates


DOH!

Of course. I always forget about that sneaky, backdoor method. )

You don't need a script and Global field though.

You can have a "semi-live" count by simply defining a Value List to get
it's values from the Name field and then creating a new Calculation
field to count the names:

NumUniqueName {Calculation, Number result}
= PatternCount (ValueListItems(FileName, ValueListName),
"") + 1

where "FileName" is the name of the database file and "ValueListName"
is the name of the Value List you defined.

The "" symbol on the last line is the carriage return character which
is availble via a button in the define calculation windows (next to the
"-" symbol on my copy, but it may have been moved in FileMaker 7).

The only real problem here is that a new name isn't added to the Value
List until you change to another record (or possibly by committing the
new record to disk). Simply exiting the new record by clicking outside
all fields or pressing the Enter key doesn't add the name.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
  Reply With Quote
9 18th December 16:37
stephen larivee
External User
 
Posts: 1
Default Counting records with duplicates


Harry,

I had better luck with your first method (creating a Name relationship).
That is coming out fine.

Once again, you have lived up to your name!!

Thank you for your help and patience.
  Reply With Quote
10 18th December 16:40
stephen larivee
External User
 
Posts: 1
Default Counting records with duplicates


I have printed this out and will give it a try.

Thanks a lot.
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666