Can't find duplicate records

[John Chamberlain]

Can someone tell me why the duplicate record check on the attached file does not work. I established a field (ZCONCAT) defined as:

Date of Event&Event ID&Zip&Person ID

I established a self relationship based on this field. Then I created a field (Dup Warning) defined as:

If(Count(ZCONCAT)>1, "WARNING!!! This record may be a duplicate!!!", "")

To me this looks like the same structure I have used on other files where the duplicate warnings work perfectly.

[BobWeaver]

I didn't look at your file, but from your post it looks like the error is in the formula. When you are using aggregate functions like Count, Sum, Max and Min, you have reference the field through the relationship, not directly. So it should look like this

If(Count(SelfRel::ZCONCAT)>1, "WARNING!!! This record may be a duplicate!!!", "")

[John Chamberlain]

Hi Bob -

I tried your suggestion, but it did not help. Also, there are no functions involved here (at least not to my understanding). The ZCONCAT field is composed of a combination of text fields, and the relationship seems straightforward to me.

BTW, I have looked in all 10 of my FMP manuals and books and I have never found a solid discussion regarding checking of duplicate records. Everything I know about the subject came from answers to questions posted here. Seems to me that someone should post an article or something that explores this subject in depth.

[ESpringer]

John,

you do have a Count function, and that's what Bob was referring to... Indeed, you can make sure your relation works well by doing a test calculation just on Count(SelfRel::ZCONCAT), and see whether it returns 2 on an intentionally duplicated record...

Indeed, testing for duplicates is one of those things I also learned by brutal trial & error...

-ESpringer

[Ugo DI LUCA]

Yes, as pointed by Bob and John, you have a relationship established just for this, but you forgot to use it...

Duplicate Checker::ZCONCAT should appear in the calculation box, where Duplicate Checker is your relationship, which you can select from the drop-down just above the field list.

About checking duplicates, I consider Andy Gaunt technique as the quicker.

You can download a demo at his Web Site...

fusiondzine.com

You'd need a record_Id, or a serial though, which I recommand you create anyway.

There are several scripted methods around also...

HTH

[John Chamberlain]

Hi Ugo -

Your solution (changing the formula to: If(Count(Duplicate Checker::ZCONCAT)>1, "WARNING!!! This record may be a duplicate!!!", "") was the solution. I still don't understand why, when I chose the self relationship and select the ZCONCAT field FMP does not automatically enter the relationship name and two colons before that field name.

Thanks again guys.

[Ugo DI LUCA]

It sure should ! Huh