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
m.abdulhaq
Messages: 136
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 31 times)
Re: Update field based on another field check box [message #583723 is a reply to message #583712] Fri, 03 May 2013 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why wouldn't you use a WHEN-CHECKBOX-CHANGED trigger? It fires when you change checkbox value, and right there you can set other item's value, instantly. No loops at all.
Re: Update field based on another field check box [message #583744 is a reply to message #583723] Fri, 03 May 2013 09:26 Go to previous messageGo to next message
m.abdulhaq
Messages: 136
Registered: April 2013
Location: Ajman
Senior Member
thanks littlefoot for a very prompt response, but i have another table also to be updated at the same time , can i use the same check-box-change trigger at the same time without using button.
Re: Update field based on another field check box [message #583746 is a reply to message #583723] Fri, 03 May 2013 09:53 Go to previous messageGo to next message
m.abdulhaq
Messages: 136
Registered: April 2013
Location: Ajman
Senior Member
littlefoot i actually tried your method but i want all the items to be selected and get updated at once , so i wrote this procedure in select all, but i want to refresh the same upon changing values of each check box .


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;

Re: Update field based on another field check box [message #583791 is a reply to message #583746] Sat, 04 May 2013 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 19885
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i have another table also to be updated at the same time , can i use the same check-box-change trigger at the same time without using button.

Sure, why not?

Quote:
i want to refresh the same upon changing values of each check box

Refresh what? If you modified checkbox and, according to its value, some other items as well, a "refreshed" state is already on the screen. *Maybe* you'd need to add the SYNCHRONIZE call at the end of your code.

Or, re-execute query. You might need to know previously used WHERE clause for that block; check GET_BLOCK_PROPERTY and its LAST_QUERY property. Obviously, you'd set block's DEFAULT_WHERE (or ONETIME_WHERE) property with SET_BLOCK_PROPERTY.
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
m.abdulhaq
Messages: 136
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 32 times)
Previous Topic: Get Total number of records
Next Topic: Oracle Forms & Reports
Goto Forum:
  


Current Time: Thu Dec 18 22:42:16 CST 2014

Total time taken to generate the page: 0.08758 seconds