Home » Developer & Programmer » Forms » Update record in detail block (Forms 6i)
Update record in detail block [message #663660] Tue, 13 June 2017 13:41 Go to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
My question is to all this forum that I am updating record in detail block something like this

Icode. Qty.
1001. 1
1002 1+1
1003 1
If 1002
My question is if same record will again like 1002 record should be update only qty mean + 1

Thanks in advance


[EDITED by LF: fixed topic title typo]

[Updated on: Tue, 25 July 2017 01:12] by Moderator

Report message to a moderator

Re: Upate record in detail block [message #663665 is a reply to message #663660] Wed, 14 June 2017 00:50 Go to previous messageGo to next message
prabhakarkamath
Messages: 15
Registered: February 2006
Junior Member
Sorry, can you please elaborate bit more on what you are trying to achieve. I could not get from the details you have mentioned here.
Re: Upate record in detail block [message #663671 is a reply to message #663665] Wed, 14 June 2017 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Indeed it's not all clear what the OP is trying to do.
You need to add more explanation.
Re: Upate record in detail block [message #663673 is a reply to message #663671] Wed, 14 June 2017 03:26 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks for quick response sir actually I have designed point of sale application and I am using barcode scanner picking one by one record from stock such as item code, item name price and quantity = 1
As u I have mentioned in above post

Text_field taking barcode entry
And in detail block as
Item_code      item_name    price      qty 
0001           laptop       260 Rial    1
0006           mobile       150 Rial    1
0008           led          60  Rial    1
Now if item_code 0001 or 0006
again comes thn record should not be add into detail block only qty should be add +1 means qty will be 2 if duplicates record comes thn qty should be add sorry for my this much bad English


Thanks

LF applied [code] tags]

[Updated on: Wed, 14 June 2017 13:52] by Moderator

Report message to a moderator

Re: Upate record in detail block [message #663674 is a reply to message #663673] Wed, 14 June 2017 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you would need to loop through the detail block, using first_record, next_record, find the row a matching item_code and modify qty accordingly.
The main problem is what trigger you're going to use to run this. A when-button-pressed would be the usual choice but key-next-item might be acceptable in this case.
Re: Upate record in detail block [message #663675 is a reply to message #663674] Wed, 14 June 2017 04:21 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks for reply sir yes key-next-item will be acceptable for barcode reader sir i tried code this for checking duplication and also successfully adding in qty but record also added in the detail block

Declare
   A number (1 )
   F_name varchar2 (35)
Begin
F_name:=:zz

First_record
While :zz is not null loop

If f_name=:zz then
   :b:=:b+1
A:=A+1
Select icode,inane......
   End if
Next_record
   End loop
Previous_record
End;
It's working fine but also detail are adding in detail block such as icode, inane
I wanted only qty should be +1 not detail
Sir kindly assist me where am wrong


Thanks




CM: added end [/code] tag

[Updated on: Wed, 14 June 2017 04:34] by Moderator

Report message to a moderator

Re: Upate record in detail block [message #663676 is a reply to message #663675] Wed, 14 June 2017 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
f_name needs to be set to the value entered, which is presumably in a different datablock.
You should exit the loop once a match has been found.

As for adding a new record - what code is currently doing that?
Re: Upate record in detail block [message #663678 is a reply to message #663676] Wed, 14 June 2017 04:51 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks 4 quick response sir kindly brief in code it will be highly appreciated

Sir actually
F_name:=barcode

and
:qty:=:qty + 1
SIR kindly correct the code I will be thankful to u




Re: Upate record in detail block [message #663720 is a reply to message #663678] Thu, 15 June 2017 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can't correct the code unless I know the names of all the datablock items involved.

:qty := :qty + 1
will be fine.
I have no idea where the variable barcode came from - or is it a datablock item and you left off the : ?
Precision matters when you want help with syntax issues.

[Updated on: Thu, 15 June 2017 03:36]

Report message to a moderator

Re: Upate record in detail block [message #663722 is a reply to message #663720] Thu, 15 June 2017 04:15 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks sir barcode is not a datablock it is residing in a control block which is non database item here is only datablock which is example emp block


Regards
Re: Upate record in detail block [message #663723 is a reply to message #663722] Thu, 15 June 2017 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
A control block is still a datablock.
In which case barcode should be :<block name>.barcode.
Always use block names when referring to items, it helps avoid confusion.
Re: Upate record in detail block [message #663724 is a reply to message #663723] Thu, 15 June 2017 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Code should look something like this:
DECLARE

  l_match BOOLEAN := FALSE;

BEGIN

first_record;

WHILE :system.last_record != 'TRUE' AND NOT l_match LOOP

  IF :detail_block.item_code = :control_block.barcode THEN
  
    :detail_block.qty := :detail_block.qty + 1;
    l_match := TRUE;
    
  ELSE
  
    next_record;
    
  END IF;
  
END LOOP;

IF NOT l_match THEN

  --Need to add new record at end
  next_record;
  :detail_block.item_code := :control_block.barcode;
  <whatever else you need to set>
  
END IF;

END;
  
Re: Upate record in detail block [message #663734 is a reply to message #663724] Thu, 15 June 2017 07:48 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Thanks for reply

DECLARE 
l_match boolean:=false;
BEGIN 
	
			
	FIRST_rECORD;
	WHILE :system.last_record != 'TRUE' AND NOT L_MATCH LOOP

		IF :EMP.item_code=:CONTROL.barcode THEN 
		   :EMP.qty:=:EMP.qty+1;
		L_MATCH:=TRUE;
		ELSE
	   NEXT_RECORD;
		
			
	END IF;

		
	END LOOP;
	IF NOT L_MATCH THEN
	
	select empno,ename into :emp.item_code,:emp.ename from emp where empno=:control.barcode;
		NEXT_RECORD;
		:EMP.item_code:=:CONTROL.barcode;
		
			END IF;
		
		END;

sir I tried your code but qty is not + result is same adding a duplicate record record

[Updated on: Thu, 15 June 2017 07:54]

Report message to a moderator

Re: Upate record in detail block [message #663737 is a reply to message #663734] Thu, 15 June 2017 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You probably need a go_block at the start to go to the emp block as the code is being fired while the cursor is in the control block.
Also, why does that select exist? If you're creating a new entry then it shouldn't have a match in emp should it?
Re: Upate record in detail block [message #663742 is a reply to message #663737] Thu, 15 June 2017 13:32 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir your code is almost is working but sir qty is only first time adding second time only adds only code like


7902 1
7900 2 first time it's fine
7934 1
7900 second time adding only code instead + in qty

Best regards


Re: Upate record in detail block [message #663743 is a reply to message #663742] Thu, 15 June 2017 13:47 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir I have checked this is problem in last updated item code if I will update last code same time again it will add only code not will + in qty.

7900 3
7902 2
7934 2
7844 1 last code not is not adds in qty
7844 ...

Best regards
Re: Upate record in detail block [message #663746 is a reply to message #663743] Thu, 15 June 2017 14:11 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir one more thing that I am picking item code, item name , price from stock table which is non datablock

Thanks & best regards
Re: Upate record in detail block [message #663747 is a reply to message #663746] Thu, 15 June 2017 14:34 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir everything is working fine only last record is not updating properly as I mentioned in detail my above post

Thanks
Re: Upate record in detail block [message #663757 is a reply to message #663747] Fri, 16 June 2017 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's cause the loop exits the moment the last record is reached, I didn't think that one through.
Need to move the point system.last_record is checked:
DECLARE 

  l_match boolean:=false;
  l_continue boolean := true;
  
BEGIN 
	
	go_block('emp');	
	FIRST_rECORD;
	WHILE l_continue AND NOT L_MATCH LOOP

    IF :EMP.item_code = :CONTROL.barcode THEN 
      :EMP.qty := :EMP.qty+1;
      L_MATCH := TRUE;
    ELSE
      IF :system.last_record != 'TRUE' THEN
        NEXT_RECORD;
      ELSE
        --checked last record, time to stop
        l_continue := FALSE;
      END IF;
    
      
    END IF;

  END LOOP;
  
  IF NOT L_MATCH THEN
  
    ..........
    
  END IF;
    
END;
Re: Upate record in detail block [message #663758 is a reply to message #663757] Fri, 16 June 2017 05:00 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Work done....Thank you very much sir you are really genius..Thanks once again


Best regards
Re: Upate record in detail block [message #664793 is a reply to message #663758] Fri, 04 August 2017 08:36 Go to previous messageGo to next message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir @cookiemonster there is small error in above code if I will enter correct code let suppose 1001 it will work 100 % fine but when I will enter wrong code it will give error "Record must be insert or delete first" kindly sir assit me


Thanks
Re: Upate record in detail block [message #665040 is a reply to message #664793] Fri, 18 August 2017 14:38 Go to previous message
Amjad_1975
Messages: 82
Registered: January 2017
Member
Sir still this error is coming record must be entered and delete first I tried to control it frm-40375 by using on-error trigger its working by cursor is not going to the particular block and field can any one guide me where is the mistake in above code I have 3 blocks here
1. Master_block
Fields date,invoice No,customer name
2. Transactions block
Item_code,item_name,qty,price

3 control block
Barcode --here I write the code in key-next-item trigger

problem is as I mentioned in my previous post

[Updated on: Fri, 18 August 2017 14:50]

Report message to a moderator

Previous Topic: Forms PLL vs Forms Program Unit
Next Topic: Trying to get data in Excel sheet from Oracle reports 10g
Goto Forum:
  


Current Time: Thu Jul 29 22:28:58 CDT 2021