multi record block

[saritha]

Hello all,
I have a multi record block which has about 10 records displayed. It has a field called amount and Credit/debit Indicator. Everytime the user enters amount and navigates to Credit/debit indicator the sum(amount) should be displayed in a Total field which is at the bottom of the screen.Usually the transaction are Debit when it should add the amount to previous amount if its a credit then it should subtract the amount. How to navigate to each item in a multi reocord block so that I will have the previous record's amount?

How to navigate to each & every item in a multi record block.

Please let me know

Thanks
Saritha

[kris109]

Write a WHEN-VALIDATE-ITEM trigger at the block level that calculates the total each time it validates an item. You can write at the record level also.

for example:

:block.total := 0;
FIRST_RECORD;
While :System.Last_Record <> 'TRUE' LOOP
:block.total := :block.total + :block.amount;
NEXT_RECORD;
END LOOP;

Note that total is calculated each time an item is validated and it does not depend on either the navigation or location of the cursor.



[Edited by kris109 on 03-06-2002 at 04:54 PM]

[Shestakov]

What user can do on this screen?
-- Type new value in :ammount (with or without changing indicator)
-- change value of indicator (without changing ammount)

Probably u need:

1) set to ur :indicator values:
credit = -1
debit = +1
2) create :add_value field with "type-->Calculation mode = formula" (invisible)
:ammount * :indicator
3) create :total field (in separate single row block)
and make this field -->Calculation mode = summary (SUM for field ->:add_value )
4) set in ur main block (with 10 rows) propertry -> all rows

[saritha]

I tried 1st way It gives me error saying "Illegal restricted procedure FIRST RECORD FRM-40737 " .

2nd method: credit /debit indicator are databse fields they take either "D" or "C" values.

I think 1st method might work only the trigger we are using matters. Can you please focus more light on 1st method?


Thanks
Saritha

[kris109]

Oops. It slipped my mind that FIRST_RECORD is a restricted built-in.

Try when-new-item-instance trigger which allows restricted procedures. It will do the same job and will calculate the total as soon as the input focus changes to the next item.

:block.total := 0;
FIRST_RECORD;
While :System.Last_Record <> 'TRUE' LOOP
:block.total := :block.total + :block.amount;
NEXT_RECORD;
END LOOP;
LAST_RECORD;
:block.total := :block.total + :block.amount;

This should give you the complete total. I will let you know if I can think of something better.

[Shestakov]

Write small function:

function sign_indicator(p_ind char) return number
is
if p_ind = 'C' then return -1;
elseif p_ind = 'D' then return +1;
else return 0;
end;
--------------------------------------
change formula
from ---> :ammount * :indicator
to ---> :ammount * sign_indicator(:indicator)

and that's it.

[saritha]

That works fine thanks but I also need to check credit_debit_flag condition. If the previous record or first record's credit_debit_flag is 'D' then it should add the present record's amount to previous record's amount elsif if the present record'd credit_debit_flag ='C' then it should substact the present record's amount from the previous record's amount.
so I did like this


begin
:CG$CTRL.ctl_sum_amount :=:ts_direct_payins.payin_amount;
FIRST_RECORD;
While :System.Last_Record <> 'TRUE' LOOP
if :ts_direct_payins.cr_dr_flag = 'D' then
:CG$CTRL.ctl_sum_amount :=:CG$CTRL.ctl_sum_amount + :ts_direct_payins.payin_amount;

elsif :ts_direct_payins.cr_dr_flag = 'C' then
:CG$CTRL.ctl_sum_amount := :CG$CTRL.ctl_sum_amount- :ts_direct_payins.payin_amount;

end if;

NEXT_RECORD;
END LOOP;
LAST_RECORD;
end;


its not checking the condition, irrespective credit/debit it just adds the amount to previous amount.

Please let me know whats wrong

Thanks
saritha

[kris109]

I do not see anything wrong. My suggestions are

1. use NAME_IN such as
IF NAME_IN(:ts_direct_payins.cr_dr_flag) = 'D' ..

2. Make sure that your check box is actually passing 'C' or 'D'. Put a message and verify the value.

3. You need to add one more time for that last record unless you want to use a do-while loop.

[saritha]

When I added name_in its still gives me error...
I think I know whats happening, because its a multi record block it deosn't know exactly which item when we say name_in(:block.amount), it can be 1st records amount or second record's amount...etc I mean if it know which records amount it should check then it might do what we want.

Thats just my thinking....but I still need help

Thanks
saritha

[kris109]

Oracle exactly knows which record it is looking at. No confusion there.

When you say first_record - it means that record's amount and the credit_debit_flag. When it goes to the next record, it takes those values.

My guess is that your credit_flag check box is not set up properly.