OraFAQ Forum: Forms » retrieving previous record value of the same item in a block

Home » Developer & Programmer » Forms » retrieving previous record value of the same item in a block (Oracle form builder 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

retrieving previous record value of the same item in a block [message #560778]

Tue, 17 July 2012 01:47

pgiftson
Messages: 3
Registered: July 2012
Location: Hyderabad

Junior Member

Hi all,
i am creating a record with the items - S_No, Product_Name, Selling_Qty, Price.
checking the total qty (Tot_Qty) from the purchase stock - table1 and already sold out qty (Sold_Qty) of the product from table2.
consider the following values: Tot_Qty = 200; Sold_Qty = 50;

Now i create a first record of the form as follows: here on selecting the Product_name from LOV, will display the Selling_Qty item - which can be edited (Selling_Qty = Tot_Qty - Sold_Qty) and its price.

S_No Product_Name Selling_Qty Price
1. KeyBoard 50(150) 200
2. KeyBoard 30(150) 200
3. KeyBoard 50(150) 200

Note:
[value given within () are displayed automatically after selecting the prod]

My prob is, when i am entering next record, it displays the same 150 as qty, which have to be 100. After selecting the product from LOV, it should calculate & validate as shown below: which means, previous record value of Selling_Qty also have to be subtracted and displayed.

S_No Product_Name Selling_Qty Price
1. KeyBoard 50(150) 200
2. KeyBoard 30(100) 200
3. KeyBoard 50(70) 200

Is there any built-in functions to retrieve previous record value? Help me in this...

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #560780 is a reply to message #560778]

Tue, 17 July 2012 01:56

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Maybe the simplest way would be to POST changes you've made, so that your LoV calculation knows that something has been changed and calculates these values as well.

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #560782 is a reply to message #560780]

Tue, 17 July 2012 02:03

pgiftson
Messages: 3
Registered: July 2012
Location: Hyderabad

Junior Member

thanx for your reply.

can u elaborate me.
how and what can i add in post-item trigger...
how can i retrieve the 1st record Selling_Qty value of 50 to validate and check to the 2nd record...

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #560822 is a reply to message #560782]

Tue, 17 July 2012 05:18

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

POST is a restricted procedure; you can't use it in POST-TEXT-ITEM trigger. I was thinking that you might put it into the WHEN-NEW-RECORD-INSTANCE (block-level) trigger (most probably tweak it a little bit so that it does "nothing" if values already exist, or put :SYSTEM.MESSAGE_LEVEL around the POST so that the end user wouldn't even notice it fired).

I don't know how your LoV query looks like, but I suppose that it calculates the value you are displaying ("Keyboard 50 (150)"). So, with POST, you'd let your query "see" quantities user entered (but has not committed yet).

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #560833 is a reply to message #560822]

Tue, 17 July 2012 06:29

pgiftson
Messages: 3
Registered: July 2012
Location: Hyderabad

Junior Member

Let me tell u the actual scenario... LOV of Product item retrieves data from the stock table. after selecting the product from LOV, it displays the Product_Name, Selling_Qty(initially it displays total qty of stock), Price.

In when-validate-item trigger of Product_Name, i am verifying and subtracting...i.e, ":Selling_Qty := Tot_Qty - Sold_Qty". Hence, the value of Selling_Qty item changes(for 1st record) from 200 to 150. Then the cursor moves to next item - Selling_Qty. If the user enter more than 150 for the qty, it prompts "Qty cannot be more than 150" - this is also performed in when-validate-item trigger of Selling_Qty.

Now, can u tell me where i have to use POST....?

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #560836 is a reply to message #560833]

Tue, 17 July 2012 06:35

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As I said: WHEN-NEW-RECORD-INSTANCE is one option.

Hopefully, someone else will have a better idea.

Report message to a moderator

Re: retrieving previous record value of the same item in a block [message #561018 is a reply to message #560836]

Wed, 18 July 2012 05:18

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Another option is to fetch it into a forms package variable one time (e.g. a PL/SQL array) and edit that value. That way you don't have to perform database round trips each and every time. It involves, however, quite a bit of programming...

MHE

Report message to a moderator

Previous Topic:	Upgrade from 10g to 11g
Next Topic:	call function with parameter in oracle forms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 22:02:00 CDT 2024