Home » Developer & Programmer » Forms » Update field based on another field check box (Forms 6i)
Update field based on another field check box [message #583712] |
Fri, 03 May 2013 05:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c70e51380f6e8af6765f6e8fede51587?s=64&d=mm&r=g) |
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
:M_END_YN := :M_END_ALL;
EXIT;
ELSE
NEXT_RECORD;
END IF;
END LOOP;
FIRST_RECORD;
END;
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;
--code written on button to update those values which are checked.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
:TRANS_ACT_QTY := :TRANS_QTY ;
ELSE
NEXT_RECORD;
END IF ;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
IF :M_END_YN = 'Y' THEN
:TRANS_ACT_QTY := :TRANS_QTY ;
END IF;
EXIT;
END IF;
END LOOP;
DO_KEY('COMMIT_FORM');
END;
-
Attachment: TREEF.fmb
(Size: 56.00KB, Downloaded 1256 times)
|
|
|
|
|
|
|
Re: Update field based on another field check box [message #583796 is a reply to message #583791] |
Sat, 04 May 2013 04:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/c70e51380f6e8af6765f6e8fede51587?s=64&d=mm&r=g) |
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
thanks littlefoot i got it done by writing the following triggers , and one small doudbt, i have a when-validate-item trigger written to update one more field cut_qty in table called ot_cut ,based on values from trans_act_qty, in case user will not enter instead he selects this select all check box option, will this when-validate-item still fire.In simple terms ,
will when-validate-item trigger fire , even if he chooses the select all button.Please review the attached form.
create table ot_cut (cut_item varchar2(12),cut_qty number);
insert into ot_cut(cut_item,cut_qty) values ('TREE1',NULL);
insert into ot_cut(cut_item,cut_qty) values ('TREE2',NULL);
--trigger to select all the values and update all together
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
IF :M_END_YN = 'Y'
THEN
:TRANS_ACT_QTY := :TRANS_QTY;
ELSE
:TRANS_ACT_QTY := NULL;
END IF;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
:M_END_YN := :M_END_ALL;
IF :M_END_YN = 'Y'
THEN
:TRANS_ACT_QTY := :TRANS_QTY;
ELSE
:TRANS_ACT_QTY := NULL;
END IF;
EXIT;
ELSE
NEXT_RECORD;
END IF;
END LOOP;
FIRST_RECORD;
END;
--trigger if choses to un select some of the items
IF :M_END_YN = 'Y' THEN
:TRANS_ACT_QTY := :TRANS_QTY;
ELSE
:TRANS_ACT_QTY := NULL;
:M_END_ALL := 'N';
END IF;
--trigger written in when-validate-item to fire while he does data entry.
DECLARE
CURSOR c1
IS
SELECT cut_item
FROM ot_cut
WHERE cut_item = :item_trans.trans_item;
m_item ot_cut.cut_item%TYPE;
BEGIN
IF c1%ISOPEN
THEN
CLOSE c1;
END IF;
OPEN c1;
FETCH c1
INTO m_item;
UPDATE ot_cut
SET cut_qty = NVL (cut_qty, 0) + NVL (:trans_act_qty, 0)
WHERE cut_item = m_item;
CLOSE c1;
END;
-
Attachment: TREEF.fmb
(Size: 72.00KB, Downloaded 1192 times)
|
|
|
Goto Forum:
Current Time: Thu Sep 12 02:35:05 CDT 2024
|