Unique Field Validation in FMP7

Hi Remi,
I am not sure how this is going to test that a text field is unique
especially when you consider the 2 scenarios of uniqueness
1. The field to be entered does not have similar data in the same position
elsewhere within the table
2. The field contents exist once in the table and that same unique value is
overwritten into that same position
Could you explain how to use SUM to test for this
Best Regards Ron H.

"Remi-Noel Menegaux" wrote in message

> You may put a self relationship based on that field and set in its
> definition a calculation SUM of it having to be always 1.
> Remi-Noel
>
>
> "Ron H." a écrit :
>> Hi All,
>> I have a need to test a field at entry time to check if it is Unique. I
>> know there is a standard Unique checkbox in Field Validation, but I want
>> to validate for uniqueness using the Calculate box because I want the
>> uniqueness tested only under certain conditions - not a blanket test.
>> The problem is that I can't find a Unique function for field testing. I
>> can use the Lookup function on a Self Joining field, but that does not
>> provide the same flexibility as the standard Unique test.
>> Is there a separate Unique function, or how can I simulate it.
>>
>> Regards Ron H.
>>
>
>
 >> Stay informed about: Unique Field Validation in FMP7 

Hi,
I don't understand your item 1. A field contents may be different from
record to record. Then saying you want its contents to be 'unique' means
you want to have each record to hold a contents of that field that does
not exist in any of the other records. Period.
If you want to have it 'unique' when compared to another field contents,
that is another story - not treated here -, as both fields are on the
same record.
Item2 is obvious : if you want the field contents to be unique among the
records, you sure want this to be true at any stage when filling its
contents.
Now, you are right with your last sentence : one should not use SUM but
COUNT.
Actually there are 2 ways to insure the uniqueness of the contents of a
given field :
1 - when defining FieldA set Control / Unique, and Strict respect of the
conditions, and message like : "There are duplicates values of FiledA !!
Please correct.
2 - define FieldA as a regular text field. Create a self relationship on
FieldA. Create a field named '#_of_dupl' =
COUNT(selfrelationship::FieldA) and another field named Warning =
CASE(#_of_dupl <= 1; ""; "DUPLICATE")
Remi-Noel


"Ron H." a écrit dans le message de
gmHKe.81130$oJ.12203@news-server.bigpond.net.au...
> Hi Remi,
> I am not sure how this is going to test that a text field is unique
> especially when you consider the 2 scenarios of uniqueness
> 1. The field to be entered does not have similar data in the same
> position elsewhere within the table
> 2. The field contents exist once in the table and that same unique
> value is overwritten into that same position
> Could you explain how to use SUM to test for this
> Best Regards Ron H.
>
> "Remi-Noel Menegaux" wrote in message
>
>> You may put a self relationship based on that field and set in its
>> definition a calculation SUM of it having to be always 1.
>> Remi-Noel
>>
>>
>> "Ron H." a écrit :
>>> Hi All,
>>> I have a need to test a field at entry time to check if it is
>>> Unique. I know there is a standard Unique checkbox in Field
>>> Validation, but I want to validate for uniqueness using the
>>> Calculate box because I want the uniqueness tested only under
>>> certain conditions - not a blanket test.
>>> The problem is that I can't find a Unique function for field
>>> testing. I can use the Lookup function on a Self Joining field, but
>>> that does not provide the same flexibility as the standard Unique
>>> test.
>>> Is there a separate Unique function, or how can I simulate it.
>>>
>>> Regards Ron H.
>>>
>>
>>
>
>
 >> Stay informed about: Unique Field Validation in FMP7