Hi, all:

 

Many of us are familiar with the quick-'n'-dirty way of seeing duplicate records by using a self-join table with the primary key as a reference and the connection between table occurrences using the field of question for comparison; it also uses an unstored calc similar to this:

 

Case ( _kpln__TableID__lxn = Table 2::_kpln__TableID__lxn ; "Duplicate" ; "" )

 

...and in this example, the field to be checked is a text field containing a color (red, blue, green, etc. See attached.)

 

My question is, if there are two (or more) records with red in the test field, why does the Duplicate calc only see one of them? I would think that every record with red in the field would trigger a Duplicate response, but it doesn't.

 

Color me puzzled.

temp.fmp12.zip

The formula only "sees" the ID of the first matching record, which may or my not be the ID of your current record. A check on the inclusion of the current ID would include List (), but this would also trigger for unicates. You could combine it with counting the number of matching records, but then this test should by itself be sufficient: 

Count ( Table 2::_kpln__TableID__lxn ) > 1

btw, I changed the data type for Color from 'Number' to 'Text', and the relationship predicate from 'Cartesian' to 'Equals' …

temp_Mod_by_eos.fmp12.zip

Wow--thanks! I really appreciate the new way (for me) of doing this. 

 

...and thanks for catching the number/text output; yet another reason I need to s-l-o-w d-o-w-n.