asked on

How to do a validation in a multi-record block in when-validate record

Hello Forms61 experts,
I have a problem that need a solution ASAP. I have a multi-record block based on a table.
The 3 items on the block are: Type, Description and Serialnumber.

Example: The values of the 3 items are:
Type Description Serialnumber
mm mm device 123
mm mm device2 456

The second value "mm mm device2 456" is not a valid entry. They cannot enter the same type.
How do I do the validation? so that when they enter the same type, it will give a message that "You Cannot Enter the Same Type of Device"?

Thanks,
Terry

Ora_Techie

If you want to do it in WHEN-VALIDATE-ITEM trigger, use:

DECLARE
TYPE mytype is TABLE of VARCHAR2(10) INDEX BY binary_integer;
lt_selected_Records mytype;
li_items_in_block ITEMS_IN_BLOCK;
BEGIN
li_items_in_block(1) := 'TYPE';

TABLE_FROM_BLOCK(lt_selected_records, <BlockName>, 1, all_records, li_item_in_block);
FOR x IN 1..lt_selected_records.count
LOOP
IF lt_selected_records(x).TYPE = :type THEN
Message('You Cannot Enter the Same Type of Device').
RAISE FORM_TRIGGER_FAILURE;
END IF;
END LOOP;

TComandante

ASKER

Hello riazpk,
In li_items_in_block(1) := 'TYPE' after the begin, do I have to substitute 'TYPE' with variable in my block?

Also after the LOOP, If it_selected_records(x).TYPE = :type -- Do I have to substitute (x).TYPE with my variable in the block?

I have not used this, so I kind of confused.

Thanks,
Terry

Ora_Techie

yes, 'TYPE' is the item name in block you want to check for duplication. So you will have to change this with your item name. For example, support your block name is blk1 and item name is itm1, then above code will look like:

DECLARE
TYPE mytype is TABLE of VARCHAR2(10) INDEX BY binary_integer;
lt_selected_Records mytype;
li_items_in_block ITEMS_IN_BLOCK;
BEGIN
li_items_in_block(1) := 'ITM1';

TABLE_FROM_BLOCK(lt_selected_records, 'BLK1', 1, all_records, li_item_in_block);
FOR x IN 1..lt_selected_records.count
LOOP
IF lt_selected_records(x).ITM1 = :BLK1.ITM1 THEN
Message('You Cannot Enter the Same Type of Device').
RAISE FORM_TRIGGER_FAILURE;
END IF;
END LOOP;

ASKER CERTIFIED SOLUTION

Ora_Techie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TComandante

ASKER

Hi riazpk,
My e-mail address is tcomandante@producerware.com

Thanks,
Terry