Re: Checking for duplicate items, Forms4.5

Andy Hardy wrote in message ...
>In article <7gt0nk$smv$1_at_news5.svr.pol.co.uk>, Simon Hedges
><shedges_at_hhhh.freeserve.co.uk> writes
>>You should be able to check the data in the Database as follows
>>
>>select MYTABLE.MYTEXT from MYTABLE where MYTABLE.MYTEXT =
>>:MYBLOCK.MYTEXT and :MYTABLE.MYKEY <> :MYBLOCK.MYKEY
>>
>>This will return any row which exactly matches the current text from the
DB,
>>EXCEPT for the current row.
>
>Oh dear, sometimes I just look for the hardest way to do these things!
>Thanks!
>
>Can you confirm that Forms does nothing itself to check for uniqueness,
>and that if I relied on the normal response I'd just get the 'cannot
>INSERT' message with no indication of the field causing the problem?
>
>Andy

There is no particular field that causes the problem in every case: if your unique key is compound, then it could be a combination of several items.

However, you will know which fields are unique (in your original example it would be the TEXT field), and so can trap the error returned from the Oracle database which will pass back the constraint name which is being violated, and then give out an Alert saying something like 'You have duplicated a value in the TEXT field. Please make sure that this field is unique'.

[Quoted] There is something in forms (a property on either the block or item - can't remember which) which will check for the uniqueness of a primary key (I'm not sure about Unique Key), but for some reason Oracle state that you should avoid this this as it is a big overhead.

[Quoted] Incidentally, my proposed solution will work when the user commits each row each time they move to a new record, but if user creates, say, 3 identical records in the form, then 2 unique records, and then a further 6 duplicates, etc, and then tries to commit them all at once, the user will not know which of the records has violated the constraint without examining each record individually. If there is a risk of this you should force the Form to commit (or post) each record to the database each time the user leaves it.

[Quoted] If your Form is generated from Designer, it probably will have code which checks for uniqueness, but this will depend on the template form used, and the various generation settings.

Simon Hedges
Gloucester
UK Received on Sat May 08 1999 - 12:49:58 CEST