Omit everything EXCEPT duplicates?

[Steve G]

It's amazing how many Google results I got which were the exact opposite of what I'm trying to do here.

I built a script which, depending on user input, generates a found set which may be as few as a dozen or as many as a couple thousand records. There is a "name" field which is populated on every record.

What I need is to omit every record from the found set which does NOT have a duplicate. In other words, if the pre-processed found set returns (in the "name" field)

John

Mary

Peter

Peter

Beth

I want to omit John, Mary and Beth but keep the two Peter records.

I tried this with the Constrain Found Set script step (using "!" as the criteria) but it didn't do what I want. In fact, I'm not sure what it did. It omitted about 9 records from a found set of about 450 but there were both duplicate and non-duplicate records in there, so I don't know what it omitted.

[comment]

Do you mean you want to omit records that do not have duplicates IN THE CURRENT FOUND SET, or records that do not have duplicates AT ALL? I rather suspect you want the former, but what you're doing is the latter.

[Steve G]

In the current found set. Let me try again:

My table has 50 records. My current script generates a found set that contains 5 records (John, Mary, Peter, Peter, Beth). I need a means of removing the not-Peter records from that found set. Thx.

[Fitch]

Might it be possible to turn your script around?

1. Find duplicates

2. Show omitted

You now have the set of unique records.

3. Perform your original find as a constrain.

[Steve G]

BTW, is there a FileMaker forum that you're NOT on? }:(

[Steve G]

Clever, but it won't work in this case. There's a good amount of record-omitting happening in the script to get from 50 to 5 records, so if I show omitted records then I'll get all those records that I just omitted out.

EDIT: I missed your Step 3. Let me try that. }:(

Edited October 14, 2008 by Guest

[comment]

I don't think it's possible do do what you want by finding. You can constrain a found set, but the CRITERIA cannot come from the current found set itself (at least not directly).

What you could do is sort the found records by name and loop through them, omitting the ones that are unique.

is there a FileMaker forum that you're NOT on?

This is the only forum I currently post on.

...

Oh, you meant a section. Don't take the name too seriously - it's just one big forum, with subdivisions. Perforce the categories are defined rather loosely, since a question posted in Finding & Searching can very easily require an answer from the realm of scripting...

But yes, there are sections where you won't see me (yet?).

[Steve G]

1. Find duplicates

2. Show omitted

3. Perform your original find as a constrain.

Okay, though I see where you're going with this it doesn't work. Two reasons.

First, by doing a "find duplicates" and "show omitted" I wind up with a set of unique records. This is the opposite of what I'm trying to do; I don't want the unique records, I want to OMIT the unique records.

In theory that could easily be solved by not running the "show omitted" step. But the problem with that is, the "find duplicates" function looks for duplicate records whether or not the record has been omitted. That's the road block I'm running into now.

For example, let's say I have ten records in total:

Howard, Robin, Artie, Gary, Artie, Sal, Richard, Steve, Robin, Benji

If I do a search for duplicates I come up with:

Robin, Artie, Artie, Robin

If that's all there was to it I'd be set, but my script goes through a number of constraints (based on other values) that need to be taken into account. So, going back to my original ten names, my script would parse them thusly:

Howard, Artie, Gary, Artie, Robin

So far, so good; what I need here is a way to remove Howard, Gary and Robin. But at this point, if I do constrain duplicates it will give me:

Artie, Artie, Robin

...because there's a second "Robin" record in the omitted set. That's what I need to avoid; I need only "Artie, Artie" in my finished set.

Hopefully this all makes sense.

[comment]

If I do a search for duplicates I come up with:

Robin, Artie, Artie, Robin

If that's all there was to it I'd be set, but my script goes through a number of constraints (based on other values) that need to be taken into account. So, going back to my original ten names, my script would parse them thusly:

Howard, Artie, Gary, Artie, Robin

No. A series of constrains cannot ADD records to the found set. However, if ONE of the Robins meets the additional criteria and the other doesn't, you'll be left with:

Artie, Artie, Robin

same as before.

[Steve G]

No. A series of constrains cannot ADD records to the found set.

Nowhere did I say that it did. You missed the line where I said "So, going back to my original ten names...".

[comment]

Well, then I misunderstood what you said. I still don't understand how a script that "goes through a number of constraints" would be ever dealing with the "original ten names" - but I don't think it matters here.

[Fitch]

Looks like you'll want to sort and loop through your records as Michael suggested earlier.

[Steve G]

Well, then I misunderstood what you said. I still don't understand how a script that "goes through a number of constraints" would be ever dealing with the "original ten names" - but I don't think it matters here.

Well, that's easy enough to answer. The script that pares down the "original ten names" has to start SOMEWHERE, does it not? I mean, you can't wind up at a destination without first having a source. The script would naturally start from the perspective of "all records", e.g., "original ten names", and then constrain it down to a final set. Simple.

[Steve G]

Looks like you'll want to sort and loop through your records as Michael suggested earlier.

Yeah, looks like. Thanks.