Home » Developer & Programmer » Forms » Save Record
Save Record [message #133740] Mon, 22 August 2005 00:56 Go to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
Hi,

I have created a form which as two block as master and detail.
I am facing two problem in my detail block.
First: When I populate records in detail block, it prompt a message 'Do you want to save record ?'.
Second : When I alter any value in detail block and move to next record, it prompt a message 'Do you want to save record ?'

Is there any way where, system don't prompt me a message for saving record and user can continue with changes and save the records when he desire.
Re: Save Record [message #133748 is a reply to message #133740] Mon, 22 August 2005 01:37 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Did you use the default relationship wizard to join your two blocks or did you write your own code?

David
Re: Save Record [message #133757 is a reply to message #133740] Mon, 22 August 2005 02:12 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
I am using default relationship.
The relation between Master and Detail is set and it is working fine.


Master
Re: Save Record [message #133933 is a reply to message #133757] Mon, 22 August 2005 17:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please reply with the names of the triggers that you have at the form level, block level (each block separately), and at the item level.

For completeness, also list the Oracle generated triggers.

David
Re: Save Record [message #133977 is a reply to message #133740] Tue, 23 August 2005 00:52 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
I have created following triggers:

Form Level :

PRE-FORM
KEY-EXIT
PRE-UPDATE
KEY-DELREC
0N-CLEAR-DETAILS (Oracle generated trigger)
WHEN-NEW-FORM-INSTANCE
WHEN-NEW-RECORD-INSTANCE

BLOCK - MASTER
WHEN-VALIDATE-RECORD
KEY-DELREC
ON-POPULATE-DETAILS (Oracle generated trigger)
POST-QUERY
PRE-DELETE (Oracle generated trigger)

ITEM
VOU_TYPE_CD WHEN-VALIDATE-ITEM
BILL_NO WHEN-VALIDATE-ITEM
PARTY_CD WHEN-VALIDATE-ITEM

BLOCK - DETAIL
ON-POPULATE-DETAILS (Oracle generated trigger)
POST-QUERY
ON-INSERT

ITEM
BILL_CD WHEN-VALIDATE-ITEM
BILL_AMT KEY-NEXT-ITEM

For reference I am sending you a screen shot of my form.
When you click on POPULATE EXPENSE button, the records are inserted into detail block and at that point alert popup.

Master

  • Attachment: bill.JPG
    (Size: 97.46KB, Downloaded 313 times)
Re: Save Record [message #133981 is a reply to message #133977] Tue, 23 August 2005 01:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Wow!! That is a serious set of triggers!! Thank you for replying fully, so many particpants make me ask two or more times.

Please verify that you understand the difference between When-New-Record-Instance and When-Create-Record. The first fires for every record. The second fires on 'insert' and when you go 'past' the 'last' record.

Please verify your WNRI logic to make sure that you are not repopulating a database field as you go up and down the records. Something is setting at least one of your database items to a 'new' value, it may be the same as the current value but the form is tagging it as a 'change'. Consider testing the current value before repopulating it.

Please review your code in light of my comments and get back to me as soon as you can.

David
Re: Save Record [message #134022 is a reply to message #133740] Tue, 23 August 2005 05:53 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
David,

Thanks for your update.
As I shown in screen shot, I am populating the data from other table into detail block which all column based on a table.

Is there anyway where we can populate the data and avoid this alert.

The other option can be to create a non-database block and populdate the value and then save it manually.
This option will work fine while inserting new record but for update and delete we need to populate the data again.

Pl. let me know your view point.

Master
Re: Save Record [message #134025 is a reply to message #134022] Tue, 23 August 2005 06:08 Go to previous messageGo to next message
danycd
Messages: 6
Registered: April 2005
Location: France
Junior Member
I already encountered this problem.

What kind of operations do you make in the POST-QUERY trigger of you detail block ?

As your detail block is based on a table, if you manipulate based fields in this trigger the record status change.

So, I recommand you to use the following command at the end of the POST-QUERY trigger :
SET_RECORD_PROPERTY(TO_NUMBER(NAME_IN('SYSTEM.TRIGGER_RECORD')), <DETAIL_BLOCK_NAME>, STATUS, QUERY_STATUS);
Re: Save Record [message #134050 is a reply to message #133740] Tue, 23 August 2005 07:41 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
Hi,

I am not using post-query trigger to populate detail block.
While inserting a new record, when user click on button and
in when-button-pressed trigger, I have used cursor to get records from tables and populate the same into detail block.

I have tried to set_record_property but it didn't work.

Master
Re: Save Record [message #134210 is a reply to message #134050] Tue, 23 August 2005 19:50 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
The following is from the reference manual on set_record_property.
Quote:

The following table illustrates the valid transition states of a record.
Current Status  Target Status
                NEW     QUERY   INSERT  CHANGED
NEW             yes     yes1    yes2    no
QUERY           yes4    yes     no      yes
INSERT          yes4    yes3    yes     no
CHANGED         yes4    no      no      yes
1 Adheres to the rules described in footnotes 2 and 3.
2 This transition is not allowed in query mode, because QUERY and INSERT are not valid in query mode.
3 If this transition is performed while Runform is running in Unique Key mode and not all of the transactional triggers exist, then you must enter a valid value in the ROWID field. Put another way, if you are connected to a non-ORACLE data source that does not support ROWID, but you are using a unique key, you must supply the key for a record that goes from Insert to Query, in one of the transactional triggers, either On-Lock, On-Update, or On-Delete. Otherwise Form Builder returns an error.
4 Records that have been changed but not yet committed or cleared cannot be assigned a status of NEW.
This may explain why your attempt to use the set-record_property did not work.

Master, you say you are not using the post-query trigger to populate the detail block, but you have said it does exist. Please supply the contents of the detail block Post-Query trigger.

I asked
Quote:

Please verify that you understand the difference between When-New-Record-Instance and When-Create-Record. The first fires for every record. The second fires on 'insert' and when you go 'past' the 'last' record
Please be so kind as to respond to this question.

David
Re: Save Record [message #134237 is a reply to message #133740] Wed, 24 August 2005 01:10 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
Hi,

There is minor error in my earlier post about triggers.
There is no post-query trigger on Detail Block.
Detail Block has following triggers.

BLOCK - DETAIL
ON-POPULATE-DETAILS (Oracle generated trigger)
PRE-DELETE
ON-INSERT

As I mentioned in my earlier posting, I am populating Detail Block only when user click on 'POPULATE EXPENSE' button as shown in image.

I have written WHEN-BUTTON-PRESSED trigger on 'POPULATE EXPENSE' button.

The code is as follows:

declare
alt_id alert;
al_butt number;
ln_amt number;
ln_rcpt_srno number;
ln_st_totamt number;

CURSOR c_billhead IS
select M_BILL_DETAIL.BILL_CD,BILL_NAME,ST_FLG
FROM M_BILL_DETAIL,M_BILLHEAD
WHERE M_BILLHEAD.BILL_CD = M_BILL_DETAIL.BILL_CD
AND M_BILL_DETAIL.CO_CD = :T_BILL.CO_CD
AND M_BILL_DETAIL.BR_CD = :T_BILL.BR_CD
AND M_BILL_DETAIL.VOU_TYPE_CD = :T_BILL.VOU_TYPE_CD
order by m_bill_detail.bill_cd;

lr_billhead c_billhead%rowtype;

Cursor c_billexp (lc_bill_cd VARCHAR2) is
Select co_cd,br_cd,party_cd,bill_cd,expense_cd
from m_party_billexpense
where co_cd = :t_bill.co_cd
and br_cd = :t_bill.br_cd
and party_cd = :t_bill.party_cd
and bill_cd = lc_bill_cd;

lr_billexp c_billexp%rowtype;

Cursor c_jobexpense (lc_expense_cd VARCHAR2, ln_job_no NUMBER) is
select t_voucher.vou_no,t_voucher_dtl.vou_srno,t_voucher_dtl.expense_cd,
t_voucher_dtl.expense_amt
from t_voucher,t_voucher_dtl
where t_voucher.co_cd = :t_bill.co_cd
and t_voucher.br_cd = :t_bill.br_cd
and t_voucher.work_blk = :t_bill.work_blk
and t_voucher.job_type_cd = :t_bill.nbt_job_type_cd
and t_voucher.job_no = ln_job_no
and t_voucher_dtl.co_cd = t_voucher.co_cd
and t_voucher_dtl.br_cd = t_voucher.br_cd
and t_voucher_dtl.work_blk = t_voucher.work_blk
and t_voucher_dtl.vou_type_cd = t_voucher.vou_type_cd
and t_voucher_dtl.vou_no = t_voucher.vou_no
and t_voucher_dtl.expense_cd = lc_expense_cd;

lr_jobexpense c_jobexpense%rowtype;

Cursor c_jobrcpt (ln_vou_no NUMBER, ln_vou_srno NUMBER) is
select vou_no,vou_srno,recp_no,recp_date,recp_amt
from t_voucher_rcpt
where t_voucher_rcpt.co_cd = :t_bill.co_cd
and t_voucher_rcpt.br_cd = :t_bill.br_cd
and t_voucher_rcpt.work_blk = :t_bill.work_blk
and t_voucher_rcpt.vou_no = ln_vou_no
and t_voucher_rcpt.vou_srno = ln_vou_srno;

lr_jobrcpt c_jobrcpt%rowtype;

begin
if (:system.current_block != 'T_BILL_DTL') then
go_block('T_BILL_DTL');
end if;

:global.ln_st_totamt := 0;

IF :SYSTEM.BLOCK_STATUS = 'NEW' THEN
FOR lr_billhead IN c_billhead LOOP
ln_amt := 0;
open c_billexp(lr_billhead.bill_cd);
fetch c_billexp into lr_billexp;
While lr_billexp.co_cd = :t_bill.co_cd
and lr_billexp.br_cd = :t_bill.br_cd
and lr_billexp.bill_cd = lr_billhead.bill_cd
and c_billexp%FOUND Loop

open c_jobexpense(lr_billexp.expense_cd,:t_bill_job.job_no);
fetch c_jobexpense into lr_jobexpense;
While lr_billexp.expense_cd = lr_jobexpense.expense_cd
and c_jobexpense%FOUND Loop

ln_amt := ln_amt + lr_jobexpense.expense_amt;
:t_bill.bill_amt := nvl(:t_bill.bill_amt,0) + lr_jobexpense.expense_amt;

open c_jobrcpt(lr_jobexpense.vou_no,lr_jobexpense.vou_srno);
fetch c_jobrcpt into lr_jobrcpt;

ln_rcpt_srno := 0;
While lr_jobexpense.vou_no = lr_jobrcpt.vou_no
and lr_jobexpense.vou_srno = lr_jobrcpt.vou_srno
and c_jobrcpt%FOUND Loop

ln_rcpt_srno := ln_rcpt_srno + 1;

:t_bill_rcpt.bill_srno := ln_rcpt_srno;
:t_bill_rcpt.recp_no := lr_jobrcpt.recp_no;
:t_bill_rcpt.recp_date := lr_jobrcpt.recp_date;
:t_bill_rcpt.recp_amt := lr_jobrcpt.recp_amt;
fetch c_jobrcpt into lr_jobrcpt;
End loop;
close c_jobrcpt;

fetch c_jobexpense into lr_jobexpense;
End loop;
close c_jobexpense;

fetch c_billexp into lr_billexp;
End loop;
close c_billexp;

:T_BILL_DTL.BILL_CD := lr_billhead.BILL_CD;
:T_BILL_DTL.BILL_NAME := lr_billhead.BILL_NAME;
:T_BILL_DTL.BILL_AMT := ln_amt;
CREATE_RECORD;
END LOOP;
first_record;
else
raise form_trigger_failure;
end if;
go_item('T_BILL_DTL.BILL_AMT');
synchronize;
SET_RECORD_PROPERTY(TO_NUMBER(NAME_IN('SYSTEM.TRIGGER_RECORD')), 't_bill_DTL', STATUS, insert_STATUS);
end;

Master
Re: Save Record [message #134258 is a reply to message #134237] Wed, 24 August 2005 02:00 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
For the third time!!!
Quote:

Please verify that you understand the difference between When-New-Record-Instance and When-Create-Record. The first fires for every record. The second fires on 'insert' and when you go 'past' the 'last' record

Please supply the code that is in your when-new-record-instance. Also consider 'feeding' the code through http://www.orafaq.com/utilities/sqlformatter.htm.

David
Re: Save Record [message #134280 is a reply to message #133740] Wed, 24 August 2005 03:11 Go to previous messageGo to next message
master
Messages: 10
Registered: June 2005
Location: India
Junior Member
David,

I have understood the difference between When-New-Record-Instance and When-Create-Record.

Here is a code of WHEN-NEW-RECORD-INSTANCE at Form Level.

:system.message_level:=25;
POST;
:system.message_level:=0;

Master
Re: Save Record [message #134439 is a reply to message #134280] Wed, 24 August 2005 23:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I'm sorry, this is too complicated for me to handle. I HATE (with a passion) the way that you guys have been instructed to populate detail records through cursors. It is just WRONG!! You should base a block on a table and then use the Post-Query to fill in the other values. If you have to sort by one of these 'extra' values then build an updateable view and use it.

I am not unhappy with you, Master, but I am unhappy with the style that you have been forced to use.

Make your life simple - just change the POST to a COMMIT and, with luck, the form shouldn't raise that error.

David
Re: Save Record [message #538579 is a reply to message #133748] Sat, 07 January 2012 06:06 Go to previous messageGo to next message
vineet.bhushan
Messages: 6
Registered: January 2009
Junior Member
wizard
Re: Save Record [message #538585 is a reply to message #538579] Sat, 07 January 2012 08:28 Go to previous messageGo to next message
Littlefoot
Messages: 19883
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What did you mean to say by that word, Vineet.bhushan? Should someone use a wizard? Do you call someone a wizard? Or ...?
Re: Save Record [message #570645 is a reply to message #538585] Tue, 13 November 2012 09:12 Go to previous message
D_ORA
Messages: 32
Registered: November 2012
Location: UK
Member
I appreciated the way David sincerely explaining the things..Thanks a lot for your sincere time
Previous Topic: Form 6i Displaying oracle Only negative sign
Next Topic: CCTV Connectivity with Forms 6i
Goto Forum:
  


Current Time: Thu Dec 18 08:45:31 CST 2014

Total time taken to generate the page: 0.13159 seconds