I have created a form using multi record block. I request solutions for these two queries.
1. How to stop the user from entering a duplicate
value in these multi records?
2. While doing the data entry in these multi record
blocks, user is creating a blank record in the end,
and that record is not getting saved because of NULL
values or ir not moving ahead, becase some fields
are set to Required-Yes. I have the navigation
style property of the block set to change record,
because, user does not want to use mouse to click on
next record and start entering.
1. I think there is no easy solution to this, as Forms does not allow you access other records in the when-validate-item/record triggers. Maybe you can use a database constraint, but it will be validated only when the user tries to save the data.
2. I can't see the problem, it is a normal forms behaviour to automatically create an empty record at the end, that is not inserted in the database if the user does not enter anything on it.
You have to check for duplicate records manually. To do this you can have a control string that store the primary key of your records. Everytime you insert/delete/update a record you have to check against this control string to ensure that duplicate record doesn't exist already.
Beside using a control string, you can use record group for storing your primary key in the program.
Although you cannot normally read other records in a multi row block without navigating to them, I have found a cunning method to do this validation using the power of calculation properties. You need three extra hidden fields, two of which have calculation properties, and a little function. (If you want to see how it works, try making the hidden fields visible).
Form program unit:
function COMPARISON (in1 number, in2 number) is
if in1 = in2 then
3 new hidden fields:
calculation mode: formula
formula: COMPARISON(:control.PK_COPY, :datablock.PK)
WHEN_VALIDATE_ITEM on DATABLOCK.PK
:control.pk_copy := :datablock.pk;
if :control.number_of_matches > 1 then
message('matching key found');
but i have other problem. Once the block has been validated correctly and the records are going to be changed in the database if the value of any key column has been exchanged between two records there is a failure. for example, if we have in the database:
and we exchange that two records in the block:
the validation of the block is correct, but when the first record is going to be update in the database there is a failure, because of being already a record with ID = 2. Any suggestion?
The problem you have is that when you save after making all of your changes you still have another record with the value 2 when you update the first record to 2. If you post the data when you change each record then you won't be in that situation. I'm assuming that since Kevin's method works you must be setting the field to an intermediate value before setting it to the value of the other record, so you never have the same value in both records at the same time.
I'm not sure if you think the solution won't work or if you think it will prevent you from saving all the records. Try it and find out.