1 Reply Latest reply on Aug 6, 2010 12:51 AM by sunmoonstar.13

    Calc resulting in incorrect results



      Calc resulting in incorrect results


      I want to find duplicates in a file so I self-reference my Prospects file with two relationships, one via two indexed text fields of names, and the other via one indexed text field of email. Then I calculate errors. 

      Case (

      Count (Prospects by Name::LAST NAME) > 1 and (DupeOkay = ""); "Duplicate LastName"; 

      Count (Prospects by emails::EMAIL) > 1; "Duplicate Email";"")

      When I find for records with text in Error calc, I get back a set that includes some records that shouldn't be there and don't have text in error. They are dupes but have had the DupeOkay field checkmarked (they are same name but not same address, not true dupes). So if they DIDN'T have DupesOkay they should have Error and show in found set, but they ARE stamped so they DON"T have error but DO appear in found set (as though they do).

      Using FMP9, Error field is not stored, of type text, do not calc if empty.

      I just Recovered the file to see if rebuilding the indexes would help but it did not. The file is being served by FMServer11

      I also tried not (DupeOkay > "") with the same results and IsEmpty (DupeOkay) with worse results.


        • 1. Re: Calc resulting in incorrect results

          Your calc may need some tweaking:


          Case (

          Count (Prospects by Name::LAST NAME) > 1 and (DupeOkay = ""); "Duplicate LastName";

          Count (Prospects by emails::EMAIL) > 1 and DupeOkay = "" ; "Duplicate Email" ;"" )


          Remove the red bits and add the blue bit and see if it works.


          But instead of using self-join tables and a calc field, an alternative technique would be to simply create a script that does a Find for duplicate values (using the "find duplicate values" operator, which is an exclamation mark) and then Omit records in which DupeOkay is checked.

          Find duplicate values: http://www.filemaker.com/help/html/find_sort.5.9.html