Jump to content

Can't find duplicate records


This topic is 7548 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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!!!", "")

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7548 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.