create an additional item in your tabular block. Set it as formula-item and in the formula write something like
CASE WHEN :BLOCK.STATUS='E' THEN :BLOCK.INTEREST_YEAR ELSE 0 END
Then create a summaryitem which sums that new formula-item
Hi AW, actually i have already tried doing this before, but i was getting error for the formula i had created.
Even now when i tried with your code , i 'm getting below error.
PL/SQL ERROR 103 at line 1, column 1
Encountered the symbol "CASE" when expecting one of the following:
( - + mode not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
count current exists max min prior sql stddev sum variance
cast <a string literal with character set specification>
<a number> <a single-quoted SQL string>
HOW TO ADD FORMULA for a text_field IN ORACLE FORMS BUILDER 6I ???
Can you provide me a link where i can learn how to code for formula??
Case when not work in forms 6i try decode it will help.
Hi Vijetha please try this,
when validate item trigger of :block.status
if :block.status='E' then
:block.<newly created column>:=:block.interest_year;
:block.<newly created column>:=0;
Then create a new summary item which sums the above mentioned <newly created column>.
Hope this will help you.
Not working means?
Is it give any error? or Is it give wrong value?
I am not able to see your desktop from here. Please post some essential details.
There is no need for calculation field.
On my previous post I suggested simple if command to store value into :<newly created column>. That is working or not.
On which field you are stucking? :status? :<newly created column>? or on your :<summary column>?
Priyasagi is right, CASE won't work in 6i, try this as formaula instead
DECODE(:BLOCK.STATUS, 'E', :BLOCK.INTEREST_YEAR, 0)
When you write the formula for a calculated item, it does not allow you to use any PL/SQL expressions (to include the DECODE built-in) in Forms 6i. If there was no conditional checks on your calculation, you could simply make your item a summary item and perform the summation on the interest_rate column. However, since your calculation is dependent on the value of the STATUS column you will need to use a combination of a Calculated item and a Summarized item because you can't use an IF statement, DECODE or any other PL/SQL in the calculated item's formula. Consequently, you will need to create a Function in the Program Units node and call the function in your formula. I tested this using the following code and it worked correctly.
First, create the following Function in the Program Units node of the object navigator.
FUNCTION calc_interest RETURN NUMBER IS n_ret_val NUMBER := 0; BEGIN IF ( :YOUR_BLOCK.STATUS = 'E' ) THEN n_ret_val := :YOUR_BLOCK.interest_rate; END IF; RETURN n_ret_val; END calc_interest;
First, you will need to change the BLOCK Property Query All Record = Yes
Then, open the property pallet of your calculated item and set the following properties:
1. Calculation Mode = Formula
2. Forumla property = CALC_INTEREST
3. Database Item = No
Now, create a second non-based table item in your block which will display the summarized interest amount. Open the property pallet for this item and set the following properties:
1. Data Type = Number
2. Calculation Mode = Summary
3. Summary Function = Sum
4. Summarized Item = "Name of your Calculated Item"
5. Database Item = No
6. Canvas = "Your Canvas"
When you query your block, you should see the sum of all records where the STATUS = 'E'.
This worked for me in my sample form I created so it should work for you.
Thank You very much Craig, it works fine.
But i have a small problem that is , when i press the button 'ENTER_QUERY' (push button) for the second time to query,
I'm getting the following error.
FRM-41370: cannot modify calculated item KEC_FDACCT_MSTR.TOTAL_AMOUNT
I browsed on net for the above error & it says the following.
If the calculated item is a formula item, then its formula determines its value at all times.
It may be appropriate to modify the formula. Or it may be appropriate to change the calculated item to
a non-calculated control item whose value is set in various triggers. Level: 99 Type: Error
How do i suppress this error ??
Interesting...I did not get this error in my test form, however, I was not calling the ENTER_QUERY() built-in, I was just using the Toolbar button to place the form in "Enter Query" mode. To be honest, I'm not fond of supressing errors...I would rather eliminate the cause of the error. I'll need to play with this a little more to see if I can replicate the error and solve it. I'll have to get back with you on this.
If you really want to just supress the error you can trap it in the On-Error trigger.
Well, I've played around with my sample form and I can't replicate the error you are getting. I can "Enter Query" mode through the toolbar as well as a button without any errors. Double-check your calculated items to ensure the Database Item property is No.
I resolved that problem,
Actually , the problem is when I press 'EXIT' (PUSH-BUUTON) in Enter_Query mode; I'M GETTING THE FOLLOWING ERROR:
FRM-41370: CANNOT MODIFY CALCULATED ITEM
Anyways , i will figure it out myself.
Thank You so much Craig..
How can you solve your "How to do a sum of text_filed with where condition?" Could you please let me know?