How can I list the unique values in a field?

[ihatefilemaker]

Hello.

How do I do the equivalent of SELECT UNIQUE in Find mode?

Searching help for "Find Unique" returns help on finding duplicate values, which returns every one of my records because each data value is in at least 2 records.

Thanks.

[LaRetta]

Hi there, ihatefilemaker!!

There are many approaches. Which works best for you would depend upon what you want and how you need the results displayed. Can you give us the vision of how this would ideally look (from a User perspective)? What do you plan to do with this information once you get it?

LaRetta

[dreamingmind]

Create a value list that can see the field of interest (on some relationship if you need to just see some of the records). The value list will contain only unique entries and can be manipulated in a script/calculation by setting a variable or field equal to the value list.

Regards, Don

[LaRetta]

One more thing we'd like to know ... which field(s) will make a record unique?

[dreamingmind]

Can you expand on that a bit? It's not clear what you're aiming for.

Don

[LaRetta]

:idunno: Are you asking me? Or are you asking him the same thing I was asking him?

The point is this ... a report with only leading summary might do the trick. A flat-out value list might do the trick. A value list might NOT work if it includes a related value (can't be indexed) - why I asked which fields make it unique. Another table (relationship) might be what is really needed here. That's why I was asking all the questions. :smirk:

[ihatefilemaker]

Hello LaRetta.

Thank you (and the others) very much for responding.

Here's my situation;

I'm basically trying to use Filemaker as a great big spreadsheet to enable me to open lists/files that are too big for Excel. I'm an SAP consultant, and I work with what is literally the worlds largest SAP database. Filtering and querying data extracts is a key part of my job.

I dumped a set of approximately 400,000 vendor transactions into a text file and converted it to an .fp7 file. I need the list unique vendor numbers in the data. If this was Access, I'd do a simple SELECT UNIQUE query. Since I work for Apple, I'm locked into Filemaker.

Nothing I do has to be pretty. I just need the set of unique values in the Vendor field so that I can use that list as a selection parameter for yet another query.

Thanks again.

[LaRetta]

...so that I can use that list as a selection parameter for yet another query.

I'm unsure how you mean this but ...

Regardless of the records in your file, this demo file (attached) will show a method of isolating only the first unique Vendor Number. The list can then be used to export fields from the record set (or export just the vendor number), flag the records in some way or do whatever your above quote means.

Since you end up with a list of 'unique', you might want to delete out the duplicates? If so, perform a Show Omitted Only after performing your find (which will be all the duplicates) and then Delete All Records. If you include these two steps each time you import, your unique vendor set will remain much smaller.

You can also use a value list here I believe it will be slower on very large record sets even considering that you need to search on an aggregate function here. Also, I had tried a comparison once on, instead of finding, sorting the total record set descending and omitting the zeros but I found it to be slower than a straight search. That test was in vs. 6 and it might have changed. Hwever 8/9 appears to be much quicker on unstored finds so I can safely suggest it. A value list would also take a bit more to implement but I've used that method before also.

As said, there are many ways to go and others may describe some of the other methods for you to try.

UPDATE: BTW, leave the serial alone. It can increment upwards no problem. It doesn't matter whether you delete records out or add them in. It is used to determine which Vendor Number is the oldest, which the Min() function uses.

LaRetta :wink2:

Vendors.zip

Edited August 30, 2007 by Guest
Added Update

[Vaughan]

"...so that I can use that list as a selection parameter for yet another query."

Erm, would viewing the field's index be sufficient? Command-I?

[LaRetta]

Hmmmm, well, if he viewed the unique Vendor Number in the index list and selected it? What good does it do for him to see a unique list? Would he select it while in Find Mode to isolate those records? If so, he would find them all anyway.

Having a unique list is meaningless unless one considers what will be done with the results. And having a unique Vendor Number - just for the sake of having a list of unique vendor numbers - doesn't tell him which one to select if he's looking for a specific vendor name.

Maybe I'm the one who's confused ... but if it were me, I'd want the unique records themselves so I could do things with them (like export, pull other values etc). But that's just me - you know how strange *I* am!

UPDATE: I envisioned an on-going import process - pulling in vendor information. And then wanting only the NEW unique entries (unique vendor numbers) to 'do something else' with the set.

Edited August 30, 2007 by Guest
Added update

[ihatefilemaker]

Hello again LaRetta.

Thank you very much for the example. It gives me something that I can reverse-engineer to perhaps give me my first toe-hold in understanding how to use this application for my purposes.

If you ever find yourself with a question re. SAP or it's underlying data, I'm at SAP2112 "at" hotmail "dot" com.

Thanks again.

ihatefilemaker

PS I hate it a bit less thanks to your help.