Duplicates and Empties . . .

[Wickerman]

Okay, I'm going nuts here  I'm trying to do a 2-criteria search:

Find all records which have

1) Duplicate values in Field A

AND

2) in which Field B is Empty for two or more *of those duplicates*

In other words, I'm trying to find pairs (or trios, etc) of records that have matching values for Field A, and for which Field B is Empty.

Can anyone instruct me on the right formulation for this find?

[Ocean West]

assuming field A and B are in the same table and not looking at related fields.

In find mode

• put ! in field A and put = in B

or you can find for ! in a then once you find that set find again and put = in B and select constrain found set.

 

 

[Wickerman]

That was my first approach )both ways) . . . but it just isn't working!  I get a set of 2476 records (out of around 28,000), and while some of the desired pairings are in there, the vast majority are non duplicates.  I when I perform a Sort on Field A, I'm not getting sequences of film pairs with the same Field A value!  

When I simply seek duplicates in Field A, it works fine --  get 16,617 records and after sorting I'm not getting any singletons.

When I simply seek Field B = Empty I get 14,085 - fine

. . . but something's going amiss in the constraining step that I just can't make sense of.  

These are both simple text fields placed on the layouts as simple Edit boxes. 

I've even tried entering a 'Q' in Field B for all the empty Field B records so I could do an affirmative search for Duplicates in Field A and Q in Field B . . . sill get the same 2476 records.  I'm going nuts here!

[Tom Assia]

Hey,

If you have access for creating fields, you could make a field with calculation.

After sorting your record in the right way, in the calculation make

If (value = GetEniemeRecord (french name) (value ; GetCurrentRecord Number - 1)

and

field b =

; 1; 0

Then search for every field C with 1 value.

i made an example

 

Tom

 

duplicate.fmp12

[Wickerman]

Thanks -- I'll give that a try if I can't figure out why the simpler Find process isn't working . . . there must be something going on here . . . ?

[Tom Assia]

You said that the sort on field A didn't work well i think the problem is here

Tom

[Wickerman]

I can say with confidence it is not a matter of the sort.  I can take the Field A Value for many of these 2476 'hits' and just do a simple search for that value and see that whether there are say 2, or 3 or 9 'duplicates' for that field, there remains only a single one whose Field B is empty! 

[Wickerman]

Tom, I just checked out the helpful file you provided and discovered that I'm not conveying what I'm trying to achieve.  I'm trying to get ONLY PAIRS (or trios etc.) of records that

(a) SHARE  the Field A Value

AND

(b) Have EMPTY Field B's 

So in your File, I do not want to get a hit on the 'a' record with a blank field_b.

I DO want to get the TWO 'B' records

. . . in the image below, I want ONLY the two records at the bottom of the list, not the 4th record.

. . . having it laid out like this makes it easier to see why my 2-step approach isn't working . . .but I'm still not finding my way to what will work!

Edited August 9, 2018 by Wickerman

[Tom Assia]

Hey,

Have a look at this

There is many solution to do this.

Field Field_Duplicate is the solution.

Tom

 

 

duplicate.fmp12

[NLR]

There's a quirk in the way Filemaker finds duplicates when there's a constrain involved.   I know your pain.  In the past I spent many hours trying to understand why and finally saw the answer.  If you remove the index from the field where duplicates are being found, it should solve the problem.

I came to the conclusion that Filemaker internally takes advantage of indexing (if it exists) when it searches for duplicates, and the duplicates it comes up with are always across the ENTIRE Table.  Unfortunately found sets are ignored!   However if there's no index, Filemaker will properly constrain itself to the found set when it looks for duplicates.

To turn off indexing make sure automatic indexing is turned off as well as specifying NO index.  If you really need indexing to remain on this field, just create another (unindexed) calc field based on it, and use it instead.

Edited August 10, 2018 by NLR