duplicate entires

[LTLHAWK]

Hello all,

I have searched the forum and have not found exactly what I am looking for so I thougth I would post the question. I have a list of 2 fields. I want to display the entire list of field 1 on one layout. I can do this easy enough, but I do not want to see duplicate text. I realize that is probably very easy, but I am new and have been trying to figure it out for a few days and could realy use the help..

thank you

LTLhawk

 

[JeanW]

I have problems to understand what you want.

Is there a mixt of field and records ?

If you only have 2 fields, you will always see your 2 fields when they are on a layout.

Therefore I think you want a list view of all your records without the possible duplicats......

 

[LTLHAWK]

Exactly.... A list view of all the records but no duplicates. Example: Say I have a list of first names and a list of last names. Say 2 of the first names are Jim, but with different last names. I want to see all the different types of first names, but only want to see the name once. Therefore my list view should only show 1 Jim.

 

[JeanW]

Here we go for the dupe story.

I don't limit this answer to your question, but give you a general overview about the what and how and when. As a reference, should you have questions later about the topic.

FileMaker has a good built-in duplicate search facility; you just type "!" in a field in Find mode. But it has some limits.
For one thing, you have to decide just what is a duplicate. It's quite possible that several people could have the same name; it's also possible that people with different names could be living at the same address, related or unrelated. Once you've decided, then you should create a concatenated (put together) field with the criteria. You can also use some of the text functions to keep the size down.

Duplicate Field Calculation
Here's an example Dupe field (calculation, text) for a contact-type file: Dupe = Left (First, 3) & Left (Last, 3) & Right (Main Phone, 4) Search with a "!" in that field and you should only get one person. There's a limit at character level, look into the manual for the exact values.

One problem with the built-in Find is that it finds all the records that are the same, including the original. Then you have to look at them to decide what to do with them. You can't write a good script that would safely delete them. If you write a looping script that finds duplicates then deletes every other record, it will screw up if it ever hits a triplicate (and if there are two, there could be three). Even if there aren't, looping scripts can be very slow.

Another limit of Finds is that you have to run the search. It would be better if you could be notified automatically while trying to create a new record. To do that you'll have to use a self-relationship. First, you must have an auto-entered serial ID for each record (always); I use a text field, with a letter followed by zeros in front (e.g., A0001).

Self-Relationship
Create the relationship (I'll call it Self\Dupe) in the same file with the Dupe field on both sides; Dupe= :upe (same Dupe field from earlier). Create a new calculated field, type number, Dupe Mark= If (SerialID = (Self\Dupe::SerialID), "", 1)
The serial ID will only equal itself for the first record of the relationship. If you enter a new record and the same data is in the Dupe field, it will get marked with the 1.
Duplicate Warning
Now create your warning. It would be a calculated text field. If (Dupe Mark = 1, "DUPLICATE", "")
Put it on the data entry layout where there's some space. Set it to not allow entry and take it out of the tab order. Make the text red, but with transparent background and no line. It normally won't show, but enter a duplicate and it will jump out. Since it's based on a relationship it's unstored, so it doesn't even require exiting a field or record to trigger it. (Which is important. It doesn't do much good to have a warning that only shows up after the user leaves the layout. You can't count on them clicking outside of any fields before exiting.)
When You Want Duplicate Records Using the same Duplicate relationship you can do a number of things. Often times you want duplicate records for a person or item in a database, and you need to track them. You may have several invoices for a person, or whatever; the principle of self-relation is still the same. Use a field that is always the same in the group of records and create a self-relationship on that field. Usually it's a text ID field.
If you want to find out how many records, just count it with a calculated number field:
Count Dupe = Count (Self\Dupe::Serial ID)
It will instantly update. You can use similar self-relationships with an identifying key to total any number fields in its set of records. For example, let's say you had a number field, Charges. Create a calculated field, Person's Charges=
Sum (Self\Dupe::Charges)
You could get the same total with summary functions, but this one is more reliable, as it doesn't depend on sort order. You can even use it in other calculations, such as, Balance Due=
Sum (Self\Dupe::Charges) - Sum (Self\Dupe:ayments)
They are unstored calculations, therefore slow, but so is sorting and going into Preview mode (another way to get this total).

Marking First or Last Entries
If you want to mark which is the first entry in the group, use this:
If (SerialID = Self\Dupe::SerialID, 1, "")
It's just the opposite of the duplicate calculation.
To find the latest entry you can use the Maximum function, but you have to do a little compensation for the fact that the Serial ID is a text field.
If (TextToNum(SerialID) = Max(Self\Dupe::SerialID), 1, "")
This will also find all non-duplicates.
To find all duplicates that are not the latest entry, exchange the 1 and "";
If(TextToNum(SerialID) = Max(Self\Dupe::SerialID),"", 1)
or use "‚" (does not equal);
If(TextToNum(SerialID) ‚ Max(Self\Dupe::SerialID), 1, "")
If you want to use other criteria for the first entry other than when it was entered, then you can do that also, by using the minimum or maximum aggregate functions.
For example, the record with the biggest payment for a person:
If (Payment = Max(Self\Dupe:ayment, 1, "")

Deleting Duplicates You can now reliably find a set of only the duplicates of records, any number of them, excluding either the first or last entries. You can then add the single script step: Delete All Records That should do it.

This are FM6 syntaxes, you'll have to substitute some functions for FM7.

Bottom line is for your question, set up a technique like above and make a search for 'duplicates'. Omitting the dupes will give you a set of record with only one name.

You can set this up for a whole record or for any given field.
Each search instance needs a script to finds dupes at each given field level.
Play around with this and if questions, feel free.

HTH

 

[LTLHAWK]

Thanks HTH for taking the time to write such a detailed reply. I will go thru this and see if I can get it to work..thanks alot