Find, omit duplicates

[rstebbins]

I use a database with approx 23k records. I need to be able to find a group of records then omit duplicates for a particular field. For example, I find all records entered since Jan. 1. In the field for vendor I might have multiple entries for ACME Vacuum. I want to omit all the duplicates and show just one of the ACME Vacuum records. It doesn't matter which one is shown. Is there a way to do this? The result of the find and omit will be a vendor list with each vendor represented only once.

[tinac]

There are several ways I can think of to accomplish this. One that should be fairly simple would be to create a summary field for vendor (doesn't really matter what kind of summary, I'd use a count) & then create a new layout with just the vendor field. Go to Part setup & change body to Sub summary when sorted by vendor. Then, when you do your find, be sure to sort the records by vendor. You should be able to print that new layout & get a list of vendors. (Note: if you look at the layout from Browse mode, it won't show anything, but if you do a preview the lines should be there)

Hope this makes sense

[Ugo DI LUCA]

Create a selfjoin on the Vendor's field.

Then use a calculation

c_flagduplicate = Case(Record_ID=Max(SelfjoinOnVendor::Record_ID),1,0)

This would flag the latest record with a 1. Then do a search for 1 and you'll get only one value appear for any duplicate vendor.

Another straightforward method would be that the found set be displayed in a portal at the end of your script.

Simply add this calc

c_showOnlyOne = Case(Record_ID=Max(SelfjoinOnVendor::Record_ID),Vendor_field,"").

Drop it in the portal which relationship would be sorted by the c_flagduplicate.

Let us know if you're not familiar with relationship.

[rstebbins]

Thank you for the responses. I don't fully understand either of them though. While I have been using FM

Pro to create databases for a while, my databases are fairly simple. Your responses are too technical for me to understand without some explanation.

If you type slowly and give me some step by step to follow I should be able to figure it out.

Thank you,

Randy

[Ugo DI LUCA]

OK, Sorry for that.

So you have a Unique ID or a serial for each record, right ?

Go in Define relationship and create a new reationship from the Vendor Field to the Vendor Field within the same database.

Call it "SelfjoinOnVendor".

Now for the calc.

c_FlagLatest = Case(serial = Max(:SelfjoinOnVendor::serial), 1, 0).

The Max function used with a relationship returns the "max" field value for this relationship.

What the calc is doing is that it is checking if the current serial is the max serial value. If it is, it flags the calc with a "1", if not with a "0". If there isn't a duplicate, it also flags the calc with a 1.

...So there is only one 1, and several 0's.

Then the first method would be to omit the 0's and you'll have a list of all vendors without duplicate for printing.

If you only wanted to display the results in a portal, then you'll use an adapation of this method.

In addition to the previous calc, which was boolean number result, we'd make a calc with text result.

c_showOnlyOne = Case(serial = SelfJoinOnVendor::Serial, VendorFied, " ")

This text calc will return the vendor's name if it is the latest record within the relationship on the Vnedor Name. Again, even if there is no duplicate, the Vendor Name will appear.

You'll noticed that the calc is even more straightforward. I volontarily changed the Case as you don't even need the Max (even for the first calc). I let the big cool AndyGaunt explain it if you check a post called "Calcing a field to Purge duplicate".

Now, supposing you are in the same file when performing the search, you'll create a Constant relationship using a global field (number) that you will populate with a 1 for left side and an indexed calculation ( equal to 1) for the right side.

As this is a constant relationship, call it "SelffjoinConstant".

Draw a portal with this relationship and starts to enter the common Vendor Name in it (using the selfjoin !)

So all Vendors and duplicate appears.

Now, go back to define relationship and Modify the SeljjoinConstant sort key choosing c_FlagLatest as the key.

Now, you'll notice that the first records in the list aren't duplicates.

Back to the portal, double-click the Vendor field and change it to the c_ShowOnlyOne field.

Yep, you're done.

All the duplicate are in empty fields and not show.

Et voila. I just hope it was more clear this time.

Don't forget the visit to the thread mentionned above, as it is worth the time....