Duplicate records

[Confused]

I have an extensive database of about 4000 names and addresses of patients. I am trying to figure out a way to list all the duplicate addresses on the list- I sometimes see the entire family as patients and don't want to send out mailers to everyone in the same household.

I also see a number of pediatric patients. Is there a fast and easy way to both identify these patients and be able to send a letter to the parents, using the parents name?

Your assistance would be appreciated.

[The Shadow]

Searching for "!" in a field will show all records that have duplicates for that field.

I'm not clear on how you identify which records are children from the information you mention.

It sounds like you might benefit from a little more data normalization, maybe a new "household" table that connected a family together.

Check out this discussion:

http://www.fmforums.com/threads/showflat.php?Number=107924

[Ender]

You can easily condense your address labels by using a Sub-Summary by FamilyID part for your address labels, but this depends on having a database structure that defines a family with something like a Family table.

How do you currently know whether different people are part of the same family?

[cbum]

re: "Searching for "!" in a field will show all records that have duplicates for that field."

Yes, but how do you get a list containing one and only one record from each family? (assuming you have something identifying each family uniquely)

[-Queue-]

You either use a subsummary part as Ender described or you create a self-relationship based on the field (or a calculated concatenation of the fields) that determines uniqueness. Then you create a calculated field of serial = selfrelationship::serial. Perform a find for 1 in this field and you will have a unique set containing the first record for each family.

[cbum]

Queue,

re: " Then you create a calculated field of serial = selfrelationship::serial. Perform a find for 1 in this field and you will have a unique set containing the first record for each family."

I must be missing something obvious, but why does that only identify the first record of each family? Isn't the relationship also true for other records of the same family?

thanks

[-Queue-]

Relationship::field refers to the first related record according to the sort order defined for the relationship. If no sort order is defined, it is equivalent to the first created record that matches the relationship criteria. So serial = selfrelationship::serial is only true for the first record in the file that meets the criteria for selfrelationship. Any duplicates found afterward will have a serial that is different than selfrelationship::serial, the serial for the first related record in the file.

[cbum]

thanks for the excellent description.

Does the "calculated field of serial = selfrelationship::serial" result update dynamically, for every new found set, or do you have to force a recalc every time?

E.g., if the calc result is fixed, the member flagged with the 1 may not be present in a later found set, unless you recalc the serial field...

In fact, when FM decides to dynamically update fields and when not (lookups, relations, calcs etc.) is one of the more frustrating element of learning to set things up, IMHO...

[-Queue-]

It's a calculation based on a relationship. Therefore, it cannot be stored and is always dynamic.

Calculations involving globals and related values can never be stored. So you can rest assured that such types are always updating. Lookups are only updated when a Relookup is performed or the key field for a lookup is modified or set with itself (i.e. Set Field [field, field]). Auto-enter calculations are only performed at the time of record creation or when data they rely on is first entered, and maintain their value unless manually modified. Unstored calculations (forcibly set when no global or related field is referenced) automatically update. Calculations involving Status functions *must* be set to unstored, or they will not auto-update.

Does that help some?

[cbum]

You bet. ;-)