What's wrong with find duplicates in 7?

[michele]

Hello,

I just started using fmp7 (after 5 and 6 of course) and somehow finding duplicates doesn't work correctly.

I keep getting wrong results. Searching number and text fields. Looking in related fields but also in 'native' fields.

Is this a specifice fmp7 problem? Might not have been looking thoroughly enough but haven't found any remark on this subject.

I know I can also look for duplicates through a self-join but I was just wondering what was wrong with find duplicates.

Thankyou for reading and eventually answering this question.

[Søren Dyhr]

I wonder what you're trying to achieve, if you in findmode put a "!" in the field and perform the search will all fields with more than one occurence be the found set. This isn't a particular handy selection to be honest! What you probably are after is to have only first occurrences left in your found set.

It can be accomplished by having a self-join as you suggested where comparisons are made.

Another way is to have a summary field (count) and let it determine where next first occurrence is by sorting and a GetSummary( function in the scripted loop.

A third way is to sort and via script-parameters examine records next to each other in the sorted set.

All methods have their use, depending how willing you are to litter you table with extra fields or not, but also the number of records in the set makes one prefer one solution to the other.

The scripting to make it with less alterations to the table, could be something like this:

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

     Sort Records [ Specified Sort Order: theTable::FieldWithDupes; ascending ] [ Restore; No dialog ] 

     Go to Record/Request/Page [ First ] 

     Perform Script [ "SingleOut"; Parameter: theTable::FieldWithDupes ] 

Else 

     Go to Record/Request/Page [ Next; Exit after last ] 

     Loop 

           Exit Loop If [ theTable::FieldWithDupes ≠ Get ( ScriptParameter ) ] 

           Omit Record 

     End Loop 

     Perform Script [ "SingleOut"; Parameter: theTable::FieldWithDupes ] 

End If 

--sd

Edited August 13, 2005 by Guest

[comment]

Nice one, Søren - no fields, no relationships!

---

Perhaps <> would be a better replacement for the "not equal" sign than =/ . It actually works.

I wonder, can this new software display this:

not equal: a ≠ b

greater or equal: a ≥ b

less or equal: a ≤ b

not equal: a ≠ b

greater or equal: a ≥ b

less or equal: a ≤ b

Looks fine in preview.

Looks fine to me after posting - Mozilla on OS X.

Edited August 13, 2005 by Guest

[michele]

Thank you very much for your (extensive) answer.

What I'm up to is actually of no importance (this isn't meant rudely), i was just trying to find duplicate foreign keys (numbers) with indeed the "!".

I don't get all the fields with the same occurence, that's what's weird! (it would've worked fine in 5 or 6). When I look at the ommited records there are duplicates over there as well and I'm talking numbers in a field defined as a number so there can't be a slight difference.

So my question remains, is there something wrong when you search with "!" in 7.

I wouldn't mind uploading the file for you folks to try it for yourself and see what I mean but I don't think that's possible or is it?

[Søren Dyhr]

i was just trying to find duplicate foreign keys (numbers) with indeed the "!".

Which is a bit strange as purpose - it must be to find all records that have more than one related record? If you make the search "!" would there be nothing in found set, because you make the search from the other table ...if you go to the related table will the field just be an ordinary field in which the "!" search could be done. Let it be said that I can't reproduce your bug.

If the content in the clipboard could be tampered with, could you turn to a layout only containing the foreign key-field from the many table. Then issue a Copy All Records and paste it to a global and make a GTRR(SO) with the global as key-field to the other table. This will make the found set in the other table with all records having more than one related.

I would say that this hasn't changed from previous versions ...there must be something else gone wrong in the migration process??

What I'm up to is actually of no importance

Think again!!! When asking a weeping child under 3 years where it hurts, will they always point right at their stomach even when hitting a toe at a sharp thing!! I might be mistaken but I think the majority comes to here to get their problems SOLVED, not to be confirmed in believes and harvest sympathy.

This might be way out of the way you usually use a community, but the best way to get help is to get challenged on the assumtions, standing in the way for propper debugging.

--sd

[michele]

Here's an update.I figured out what didn't work.

Even though I don't understand why right now.

Just to explain (in case anybody bumps into the same problem I had):

2 fields with numbers (my foreign keys but that's not important)

I wanted to find similar combinations of the 2 fields f.ex.:

field1-field2

114 - 210

115 - 150

114 - 210

So here 114 and 210 should appear in my search results.

I tried finding them by typing an '!' in field1 and field2 in my search.

This gave strange results (this is an AND statement I thought but I must be missing something).

What I did. Made a calculation:

Field1&"_"&Field2

Searched for duplicates in this calculation, this worked.

Thank you for your answers.

[-Queue-]

Were you performing your find in the local table or from a related one? A local find should have worked, although it would have found records where both of the fields were duplicates, but not specifically exact duplicates of another record. For example, if you had records with

114 210

115 150

114 210

115 210

a find would have produced

114 210

114 210

115 210

but not

115 150

because 150 is not a duplicate. So 115 210 would seem to be out of place because it is not an exact duplicate of another record.

A concatenation is indeed a better way to go since you want to find exact duplicates.

[Søren Dyhr]

A concatenation is indeed a better way to go since you want to find exact duplicates.

I was pusseled by this - is this almost prehistoric measure needed today??

--sd

[-Queue-]

Alternately, you could create a self-relationship matching field1 to itself and field2 to itself, then create an unstored calc of Count(selfrel::serial) > 1. But I would think this will be slower and less efficient than storing a concatenation and searching on it.

You could probably also sort all records and use nested loops to compare the values. I would think this could be even slower, though, depending on file size.