Duplicate Names

[longncsu]

I have a database with First Name and Last Name. When users enter a new name I want it to check to see if that First Name and Last Name have already been entered. I know you can check the unique box in the field definitions, but how do you check 2 fields at the same time.

[CobaltSky]

When you say 'check', I'm not sure whether you mean that you want a validation which prevents or alerts the user when this occurs, or whether you simply want it flagged in some way.

In either case, what I suggest you do is:

1. Create a stored and indexed calculating field of result type text called 'WholeName' with the formula set to: First Name & "|" & Last Name

2. Create a self-join relationship called 'NameReference' which links the WholeName field to itself.

Once you have done these two things, you can create a validation for both your name text entry fields, using the formula: not Count(NameReference::WholeName) with a suitable custom message where the validation fails. Bearing in mind that there can be any number of 'John Smiths' (and a few of them are actually legitimate ), I guess it might be best to avoid making the validation 'strict' - better to leave it so that it can be over-ridden by the user at need...

Alternatively, you could create an unstored calculating field with a formula along the lines of: Case(Count(NameReference::WholeName), " * "), which will produce an asterisk for each record where the combination of first and last names for a given record in not unique (all instances of the non-unique combination will simulatneously acquire the asterisk).

Of course there's nothing to stop you doing both if it suits...

[longncsu]

What I really want is a script or something to pop up letting the user know that the wholename they entered is a duplicate, they then can decide to procede or delete the record. On top of that I don't want a record to be created until after it checks for duplicates (I'm asking a lot, I know).

I understand everything except, not Count(NameReference::WholeName). I know you put it in the field definition under validation. Can you explain what the "not" does.

[Vaughan]

Search the TechInfo library for the article "Validation by Calculation: An Example" it gives full instructions.

http://www.filemaker.com/ti/104433.html