Duplicate checking with a concatenated field??

[John Chamberlain]

I have a a file that I wish to have checked for duplicates. The records consist of an ID # for a person who has attended a specific class on a specific date. What I want to do is concatenate the ID, date and class name fields, then use that field to identify duplicates.

I created a self-relationship based on the concatenated field. I got a warning saying the field could not be indexed, then clicked the "Go ahead anyway" button

I then created a warning field like this:

If(Count(DupCheck::Duplicate Check Field)>1, "WARNING!!! This record is a duplicate","")

I know this works with a text or number field, but it does not seem to work with the concatenated field.

Am I trying to do the impossible?

TIA for any help on this.

[Ugo DI LUCA]

What is your ChekDup exactly ?

There are lots of ways to check duplicate. Search for AndyGaunt 's response to a thread called "purge duplicate"- just enter "purge" in the search field.

On the other hand, if you cannot index your concanation calc, it will never works as there will be no relationship set...

Are you using a global, or is this calc already referencing a related field ?

[BobWeaver]

It doesn't work because, as the warning message told you, the relationship won't work. The relationship doesn't work because the key field isn't indexed.

The calculated field you created cannot be indexed if it contains references to global fields, related fields, certain summary functions, or if you have set its storage options to unstored.

[John Chamberlain]

Okay - but all of the fields are indexed??

[John Chamberlain]

Okay sgain - the concatenated field cannot be indexed because it is the one referenced in the self-relationship.

But this is not the same situation as the one you referred to, Ugo. In that one he just wanted to look for duplicates in the e-mail field - I need to compare at least two fields (and I'd rather use three). One person could attend two classes on the same date, but would not attend the same class twice.

Maybe it really is impossible???

[BobWeaver]

It should be possible, but I'd like to see just how you have defined your fields and relationship.

If your concatenated field (FieldAB) is simply defined as:

FieldA & FieldB

and both FieldA and FieldB are indexed, then you should have no problem creating a selfjoin relationship on FieldAB.

[Ugo DI LUCA]

Hi John,

the concatenated field cannot be indexed because it is the one referenced in the self-relationship.

As Bob Says, if that calc isn't referencing another database, then it should work if you simply index the calculation (check define field options). If not, tell us what exacly is the calc and relationships.

In that one he just wanted to look for duplicates in the e-mail field - I need to compare at least two fields (and I'd rather use three)

Well first a concanated field is still a field...

Your calc is OK but you just could avoid the count step using Andy's solution.

Well, here is another one as FM provides lots of solutions.

This solution does not involve another field. It just prohibit the entry of duplicate, with an Alert message.

Set the Options for Field A, B and C to a "validate by calc", strict, message.

The calc could be Count(Selfjoin::Chekdup)<1 or Max(Selfjoin::Checkdup)<1

If you calculation can be stored...

[John Chamberlain]

Please let me start over:

1. I have records that include the fields Date of Class, Identity No., and Name of Class. All of these fields are indexed.

2. I have a concatenated field called Duplicate Check Field, defined as:

Date of Class & Identity No. & Name of Class

3. I have a self join defined as called DupCheck that self relates Duplicate Check Field to itself.

4 I have a field called DupWarning that is defined as:

If(Count(DupCheck::Duplicate Check Field)>1, "WARNING!!! This record is a duplicate","")

The indexing buttons for the Duplicate Check Field are grayed out, thus I cannot index it. Maybe there is some way to get this to change, so that I can index it?

[Ugo DI LUCA]

Are these three records somhow related to another file ?

[John Chamberlain]

Yes they are - there is a file containing all kinds of other information for all of the employees and volunteers. They are related by the Identity No. field. Why is this relevant?

[Ugo DI LUCA]

It is not relevant. It could have been relevant if one of your field was set by calculation on a related value, but there is no problem with lookup field.

Back to your first post

I got a warning saying the field could not be indexed, then clicked the "Go ahead anyway" button

Did you really tried to set the Options for your calc as Bob suggested ?

First uncheck Unstored

Then check Indexed

FM doesn't automatically store a calculation...

I am clueless if you cannot index this calc. May be you could post a sample of what you are attempting, in a small test file...

[John Chamberlain]

Isn't it funny hwo the simplest things are the ones you totally overlook? I went to Storage Options, saw that the indexing ON-OFF buttons were grayed out, and never thought to turn of the "Do not store calculation results" check box.

Of course everything works perfectly now.

Ugo, if you are ever in L.A. call me at 310-791-7638, and I will be proud to buy you dinner. You too, Bob - you guys are the best! Sorry I wasted so much of your time.

Regards,

John

[Ugo DI LUCA]

Glad it worked.

I have some friends to meet in Orange and NewPort beach in april. Is it far from Torrance ? Some seafood ? Will tell you the date John