Quick Question on Finding Unique Values

[charlesoutcalt]

I _know_ this is a dumb question,but I've spent a couple hours trying to do this, and I just can't figure it out:

I have a list of about 38k records. Of these, there are about 200 unique values. How can I cut this list from 38K to ONLY those 200 unique values? I've tried a few different ways--setting up a portal then running a script, creating a "Counter" value, as suggested in the FM help, but it's not working.

I am sure there is a simpler way of doing this!

Thanks

[sbg2]

I can't figure out if you want to do a find for records that contain a unique value or if you want to create a value list of the unique values.

To do a find for unique values:

View>Find Mode

Check the Omit checkbox

Insert the Duplicate symbol (Exclamation point)in the field that has unique values

Perform Find

[charlesoutcalt]

I think I'm getting closer, but this didn't exactly work. I think that what your suggestion found was a set of records that did not have any duplicates in the file. Unfortunately, this did not find an instance of records that do have a duplicate.

Here's what I have: 38,609 records, each of which has a sole field called Award Type. Within that field, there are about 200 unique award types (SL100, SL101, SG800, SG802, OL400, etc.).

I would like to delete all records except for one instance of each award type, so that I have, in the end, about 200 records, one for each award type.

I know there must be a way to do this, but I just can't figure it out.

[mr_vodka]

There are tons of past posts on here regarding omitting or deleting duplicate records.

First, i would start with the FM help and type 'Finding duplicate values'. It will give yuo some basic info.

Then check out some of these posts.

Thread1

Another method

Also do a seard with +delete +duplicates or +omit +duplicates and you will find a ton more.

[sbg2]

1) Make a copy of the file

2) Make a second copy of the file just to be safe

3) Make sure you have done steps 1 and 2 so that if you mess up you have at least 1 good backup.

4) Use one of the copies of the file and export every field for all the records

5) Make sure you have your backup copies then Delete all the records (in the copy file)

6) In your copy file Import the records you exported in step 4 and use the following options:

select the "update matching records in the found set" radio button and set your "unique" field as the matching field.

[charlesoutcalt]

Thanks for this idea, this seems very promising. Here's the problem I'm having:

How can I update matching records in the copy file, if I have deleted all the records in that file? That part isn't quite making sense for me yet.

Thanks again!

[charlesoutcalt]

Thanks for these threads. I have read them, and am left as confused as ever (as I was by the FM help). I've been working on this problem for a couple hours now, studying the past posts, FM help, and my hand FM manual.

I can't believe something seemingly so important could be this tough!

I appreciate your taking the time to find those past threads for me.

[sbg2]

Whoops, I missed a few things.

- add a blank record before the first import.

- On the validation tab for the match field select Validate: Always, uncheck "allow user override" and check Require: "Unique Value"

- When you do the import also check the "add remaining data as new records" checkbox.

[charlesoutcalt]

Hey! I wanted to share a resolution with you all. This is not the most elegant solution in the world, but it took five minutes, and so I thought I would pass it on in case it's useful to anyone.

I imported all the records, all 38,000 of them. Next, I created a value list based on the award type field. Next, I displayed that field as a checklist, based on that value list. When I stretched that field wide enough, it showed all 200 values, with checkboxes next to teach.

Next, I created a serial value field and set it to increase by one each time I created a new record. In the next step, I created a new record, checked a value from my value list, then created another new record, each time checking the next value in the value list. This part took the longest, but it's really just a few minutes.

Finally, I found all records with no value in the serial field, then deleted them, leaving me with just those 200 records. I changed the format of that award type back to the normal edit box.

As I said, not elegant, and it relies on clicking more than programming, but it worked.

Thanks again to you all for your great help! I always know where to turn for good FM advice.

[charlesoutcalt]

Looks like you posted your follow-up while I was posting mine. I'll keep your method in mind for future use. Looks very handy!

Thanks again--Charles

[sbg2]

I can't believe something seemingly so important could be this tough!

I think the request is out of the ordinary but I can only speak from my experience. Normally when someone wants a list of "unique" values they only want the value from a particular field. A Value List handles this quite nicely by only using one of each value, it does not duplicate a value. Your request for the entire record is what makes it out of the ordinary IMO.

[charlesoutcalt]

What you say makes sense to me. Still, as I read through the past posts on this, and as I saw all the work you put into trying to help me, I couldn't help but think how great it would be if FM had a great big "de-duplicate" button somewhere.

Thanks again for all your great help.