Field defining formula issue

[Dr. Carleton Bradley]

How do you setup a simple database with only four fields



Last name


Middle Name


First Name


Age


It will only allow me to have one field as a "unique field"


I need all four to be taken together as one unique field so that I don't
have two people in the database with the same identical information in
all
four fields.


It is unlikely that I would have two different people who are identical
in
all four fields.

Dr. CB

[Marc-André Paiement]

"Dr. Carleton Bradley" <carleton_bradley@sbcglobal.net> wrote in message
news:MPG.1a0b3c1e973be5ba989839@news.houston.sbcgl obal.net...

> How do you setup a simple database with only four fields
>
>
>
> Last name
>
>
> Middle Name
>
>
> First Name
>
>
> Age
>
>
> It will only allow me to have one field as a "unique field"
>
>
> I need all four to be taken together as one unique field so that I don't
> have two people in the database with the same identical information in
> all
> four fields.
>
>
> It is unlikely that I would have two different people who are identical
> in
> all four fields.
>
> Dr. CB

Hello,

One thing you could do is create an additional field, a calculation, that
concatenates the 4 fields. Define the calculation as:

First name & " " & Middle name & " " & Last name & " " & Age

and specify the result as text. You should also turn indexing on (in storage
options) so as to speed up find requests on that field.

Then, you can find with an exclamation mark in that field, and this will
give you the list of duplicates. Sort the found set by this calculation
field and if the number of duplicates is not too large, you can manually
delete one record out of every pair. If you're dealing with a large number
of duplicates, though, you can use a script instead. Post again if you'd
like more information on this.

This method is for finding and removing duplicates in an existing record
set. However, calculation field cannot be validated for uniqueness, so it
doen't help in preventing the creation of a new duplicate (when adding a new
record). Obviously, one could perform a find to see if a record allready
exist before addind a new record, but there are also more sophisticated
ways, that rely on scripting the creation of new records. Let me know if
you'd like more information on these techniques.

Good luck!

Marc-André Paiement
Montréal

[Mariano Peterson]

Create a calc field, all_info. This should return text and be indexed:
Last name & " " & Middle Name & " " & First Name & " " & Age

Then create a self relationship:
Relationship Name: Self.All_Info
Relationship Files: test.fp5 :: test.fp5
Relationship Fields: All_Info :: All_Info

Then create the following field validation (calculation) to each of the four
fiields:
not isvalid( Self.All_Info::All_Info)

In the validation calculation dialog, make sure that "Validate only if field
has been modified" is NOT checked. Now, users can't create duplicate
records based on the combination of those four fields. Hope this helps!

-Mariano