Check for duplicate via Self Join

[Fred P.]

I would like to try to reword my question.

I have a calc field generated based on the input from other fields.

I would like to check to make sure this calc field is unique, giving the user the ability to change the source fields until a conflict no longer exists.

If I have 5 records, and the dynamic fields are

322-14

322-15

322-14

335-01

332-14

I would like the 3 "332-14" records to show a conflict until the user changes the fields that the calc field uses.

Am I over thinking this? why does this seem difficult?

[bruceR]

I would like to try to reword my question.

I have a calc field generated based on the input from other fields.

I would like to check to make sure this calc field is unique, giving the user the ability to change the source fields until a conflict no longer exists.

If I have 5 records, and the dynamic fields are

322-14

322-15

322-14

335-01

332-14

I would like the 3 "332-14" records to show a conflict until the user changes the fields that the calc field uses.

Am I over thinking this? why does this seem difficult?

There is only one 332-14 record.

Overthinking? Difficult?

How would we know?

We don't know what you're thinking, and you haven't told us what you're doing.

[Lee Smith]

I would like to try to reword my question.

Where did you ask this question before, it sounds like we are missing some information, and you are double posting your question.

Lee

[TheTominator]

I have a calc field generated based on the input from other fields.

I would like to check to make sure this calc field is unique, giving the user the ability to change the source fields until a conflict no longer exists.

If I have 5 records, and the dynamic fields are

322-14

322-15

322-14

335-01

332-14

I would like the 3 "332-14" records to show a conflict until the user changes the fields that the calc field uses.

I will assume that you mean that the "322-14" value is a result of a calculation to determine the uniqueness of each record.

I will also assume that you have a typo and intended to write the list of values as

322-14

322-15

322-14

335-01

322-14

Let us say that there is an auto-enter serial number and two numeric data entry fields in your table called People.

_kp_People

PrimaryNum

SecondaryNum

Your uniqueness check calculated field could be

cUniqueKey = GetAsText(PrimaryNum) & "-" & GetAsText(SecondaryNum)

Now down to business.

Create a new table occurrence for your table People in your relationship graph with this table. Name it something like People__Duplicates. Match People and People__Duplicates together on the field cUniqueKey. This creates your self join.

Create a new calculated field in People for you to display your

feedback to the user.

cUserFeedback =

Case(

Count(People__Duplicates::_kp_People)>1; "This is a duplicate. Change PrimaryNum and/or SecondaryNum to make it unique.";

""

)

Put cUserFeedback onto your data entry layout.

Note that you can also add additional criteria to the Case() statement in cUserFeedback to handle other quality control issues that you might have. Or if you give yourself enough

space on the layout, you could concatenate ("&") additional

quality control messages.

[skearton]

I have a similar question (I think)

I'm revamping an older database, and one of the issues we constantly deal with is the duplication of customers already in the database. The sales reps will do a cursory find for a company (customer) and if they don't type the customer name correctly, they assume it doesn't exist, so they create a new one.

So, first question, how do you have filemaker do more of a wildcard search that is not based on exact spelling, that is simple for the sale reps to use and shows them a list of possible matches that they could choose from? Second question, how would you write a script that gets them to enter a unique value for that customer, like a main phone number or contact phone number (with extension) and have filemaker look for duplicates and then show the possible match?

Basically I want to attach a script to the "New Customer" button that runs a check for matching data that is unique to that Customer, but is not based on the Customer primary key field, because I can't expect people to memorize those.

[The Big Bear]

Shearton

Instead of the salesperson typing in a name why not use a value list where the salesperson would chose the customer name instead of typing it.

To find customers with similar names type in a partial name like

Lion

and FM should display any variation of Lion

Lion

Lione

Lionel

Hope this helps

Lionel