How can I perform a find that returns only unique records?

[HALBURN]

How can I perform a find that returns only unique records? This is a simple task in MySQL but I cannot find an easy way to do this in Filemaker.

For example, let say I poll 500 people and I ask them their favorite ice cream flavor. I want to perform a find in the ice cream flavor field and see a list of all of the flavors that were submitted...but I don't want to see 200 records for chocolate, 50 for vanilla, etc. I just want to see a found set that contains a single record for each flavor that was submitted.

I realize that I can generate a report, or a relationship list that displays the information but I want to do this with a standard find...without having to create a looping script. Is this possible?

[comment]

see a list of all of the flavors that were submitted.

You could summarize the records by flavor and show them using a layout with only the sub-summary part (and no body part).

[HALBURN]

You could summarize the records by flavor and show them using a layout with only the sub-summary part (and no body part).

I realize that I can generate a report, or a relationship list that displays the information but I want to do this with a standard find...without having to create a looping script. Is this possible?

[ejpvi]

I had a similar need the other day. I had several items in my database that have the same item number but a different version of a drop down.. blue, green, etc... So I only wanted it to count it once.

Reading the article below gave me the idea on how to figure it out. Unfortunately, I am not sure how you would do it without some kind of loop to check the flag.

You first must make sure you sort by the Field you are checking, otherwise this won't work. You can do this with any found set, or just show all records.

I then used the function GetNthRecord()

I created an unstored calculated field...

Let (

[RecNum = Get(RecordNumber)];

If(GetNthRecord ( Item #; RecNum) ≠ GetNthRecord ( Item #; RecNum - 1); 1;0)

)

http://www.filemakerhacks.com/?p=1798

From there you can basically run a loop that counts any record that has a "1" in the unique field calculation.

Hope that helps a little.

Thanks,

[comment]

HALBURN, on 30 October 2011 - 02:50 AM, said:

I realize that I can generate a report, or a relationship list that displays the information but I want to do this with a standard find...without having to create a looping script. Is this possible?

I have read your post before replying to it.

[Vaughan]

Comment's suggestion of a summary report needs only a standard find and does not require a looping script.

[comment]

Well, a 'standard find' (whatever that is) cannot create "a found set that contains a single record for each flavor" - at least not without defining a self-join relationship first. However, the suggested summary report will still show a single row for each flavor.