Home » Developer & Programmer » Forms » Calculation mode need closing value as shown in image (3 merged)
Re: Property Palette > Calculation Mode > Formula [message #660585 is a reply to message #660580] Tue, 21 February 2017 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Topics merged

Re: Property Palette > Calculation Mode > Formula [message #660594 is a reply to message #660580] Tue, 21 February 2017 11:35 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
yes
Re: Property Palette > Calculation Mode > Formula [message #660602 is a reply to message #660594] Wed, 22 February 2017 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
My mom told me: forms = insert data; reports = display data.

Of course, my mother didn't tell me anything and the above isn't entirely true. However, I'm just trying to say that I agree with the last Cookiemonster's message.

If you want to make your life easier, avoid such a (complex) things in forms, keep them as simple as possible. If you want to display the result like the image in the opening message, create a report. Or, alternatively (CM again), a view (which utilizes code good people provided for you) and create a (query only?) block which would show the information.

I don't expect you (or anyone else) to agree with that; it's just my opinion.
Re: Property Palette > Calculation Mode > Formula [message #660606 is a reply to message #660602] Wed, 22 February 2017 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can users insert records in the middle or only at the end?
Can users update either debit or credit?

If they can't update those columns and can only insert at the end then it's not particularly difficult, if they can it's going to be very awkward to implement.
Re: Property Palette > Calculation Mode > Formula [message #660615 is a reply to message #660606] Wed, 22 February 2017 12:04 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Test Case
CREATE TABLE ITEMTRAN_BODY
(
VRNO VARCHAR(11) NOT NULL,
CODE NUMBER(11) NOT NULL,
QTYRECD NUMBER(12,3) DEFAULT 0.000,
QTYISSUED NUMBER(12,3) DEFAULT 0.000,
CONSTRAINT PK_ITEMTRAN_BODY PRIMARY KEY (VRNO)
);
INSERT INTO ITEMTRAN_BODY (VRNO, CODE, QTYRECD, QTYISSUED ) VALUES ('CC','2','0','2');
INSERT INTO ITEMTRAN_BODY (VRNO, CODE, QTYRECD, QTYISSUED ) VALUES ('DD','3','1','0');
INSERT INTO ITEMTRAN_BODY (VRNO, CODE, QTYRECD, QTYISSUED ) VALUES ('AA','1','1','0');
INSERT INTO ITEMTRAN_BODY (VRNO, CODE, QTYRECD, QTYISSUED ) VALUES ('BB','2','2','0');
INSERT INTO ITEMTRAN_BODY (VRNO, CODE, QTYRECD, QTYISSUED ) VALUES ('EE','3','0','2');
COMMIT;
In PlSql My Query & its result...
SELECT VRNO, CODE, QTYRECD, QTYISSUED, SUM (QTYRECD-QTYISSUED) OVER (ORDER BY VRNO,CODE) CLOSING FROM ITEMTRAN_BODY;
1	AA	1	1.000	0.000	1
2	BB	2	2.000	0.000	3
3	CC	2	0.000	2.000	1
4	DD	3	1.000	0.000	2
5	EE	3	0.000	2.000	0
In Oracle Form Builder
I have 2 Block ITEM_MAIN_BLOCK and ITEM_QUERY_BLOCK
1 Text Item in ITEM_MAIN_BLOCK name... CODE_LOV Where List of value defined for CODE1 when i press F9 Showing LOV, Suppose i selected 2
A Trigeer is defined under CODE_LOV
KEY-NEXT-ITEM
DECLARE
	CURSOR C_CODE IS
	SELECT VRNO, CODE, QTYRECD, QTYISSUED FROM ITEMTRAN_BODY
	WHERE CODE = :CODE_LOV;
BEGIN
	GO_BLOCK ('ITEM_QUERY_BLOCK');
	CLEAR_BLOCK(NO_VALIDATE);
	FIRST_RECORD;
	FOR CUR IN C_CODE LOOP
		:VRNO1 := CUR.VRNO;
		:CODE1 := CUR.CODE;
		:QTYRECD1 := CUR.QTYRECD;
		:QTYISSUED1 := CUR.QTYISSUED;
		NEXT_RECORD;
	END LOOP;
	FIRST_RECORD;
	GO_ITEM ('CODE_LOV');
END;

2nd ITEM_QUERY_BLOCK, in Property Palette No of Item Displayed '10' and Insert Allowed 'No'
5 Text Items name... VRNO1 CODE1 QTYRECD1 QTYISSUED1 CLOSING1
Then Result... In form
SELECT VRNO, CODE, QTYRECD, QTYISSUED, SUM (QTYRECD-QTYISSUED) OVER (ORDER BY VRNO,CODE) CLOSING FROM ITEMTRAN_BODY WHERE CODE='2';
1	BB	2	2.000	0.000	2
2	CC	2	0.000	2.000	0

Which Trigger i define to get Running Total in Oracle Form
Can any one post me the FMB or Solution for this...
  • Attachment: TEST.fmb
    (Size: 52.00KB, Downloaded 112 times)

[Updated on: Wed, 22 February 2017 14:20]

Report message to a moderator

Re: Property Palette > Calculation Mode > Formula [message #660629 is a reply to message #660615] Thu, 23 February 2017 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to answer the questions from my previous post
Re: Property Palette > Calculation Mode > Formula [message #660630 is a reply to message #660629] Thu, 23 February 2017 03:37 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
User cant insert or update....
Re: Property Palette > Calculation Mode > Formula [message #660633 is a reply to message #660606] Thu, 23 February 2017 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then as mentioned earlier - create a view that includes the analytic and base the datablock on that. No forms code is needed.
Re: Property Palette > Calculation Mode > Formula [message #660635 is a reply to message #660633] Thu, 23 February 2017 04:08 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Can you post a Test Case for me or send some link to create view...

as i attached test.fmb........

and how i execute view on form???
Re: Property Palette > Calculation Mode > Formula [message #660636 is a reply to message #660635] Thu, 23 February 2017 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
The oracle documentation is comprehensive and contains everything you need. Such create view.

As for how to use it in the form - you can base a block on a view in exactly the same way as basing it on a table. You don't need to do anything different.
Re: Property Palette > Calculation Mode > Formula [message #660637 is a reply to message #660636] Thu, 23 February 2017 04:39 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
thank you....
Re: Property Palette > Calculation Mode > Formula [message #661002 is a reply to message #660637] Sat, 04 March 2017 07:44 Go to previous message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
SOLVED...
Previous Topic: FORM LEVEL AUTO SERIAL NO
Next Topic: Insert into form first then into the database
Goto Forum:
  


Current Time: Sun Jan 19 21:22:27 CST 2020