Home » Developer & Programmer » Forms » How to Save display forms column value when display item is more then one
How to Save display forms column value when display item is more then one [message #203189] Tue, 14 November 2006 04:03 Go to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,

I have a form which contain 10 fields(record display number is 10) which comes from a view i.e. combination of few table . i have also make a two date column from where user can enter to see the paticular record of between date range which he specify., when record are fetch 9 column from view and my form have 10 column i have make a one column which is "bank_ref_no" that column will be filled by the use and when he press save button the all record will be insert in "new_dtr" table which have 10 column corrosponding to he forms now here what i want when those records which is display in form already exist in "new_dtr" then we have to update the existing records specially "bank_ref_no" column if records is not exist in "new_dtr" then simply insert into my "new_dtr" table.,
below i given a coding which i had written but i have a little bit confusion about how to control forms field which display multiple record due to records display property, pls. help, below is the coding which i had written

when display button press
--------------------------
DECLARE
  CURSOR c1 IS 
    SELECT ea.app_trx_Number,
           ea.trx_Date,
           ea.Customer_Name,
           ea.Inv_Amount,
           ea.Receipt_Number,
           ea.rcpt_Amt,
           ea.Comments,
           ea.Deposit_Date,
           ea.Exchange_Rate,
           ea.Amount_dr,
           ea.Amount_cr,
           ea.Receipt_Date
    FROM   Ear_Export_tt_dtl_v ea
    WHERE  ea.Receipt_Date BETWEEN :EAR_EXPORT_TT_DTL_CONTROL.fFrom_Date
                                   AND :EAR_EXPORT_TT_DTL_CONTROL.fTo_Date;
BEGIN
  OPEN c1;
  
  Go_Block('ear_export_tt_dtl_v1');
  --first_record;
  
  LOOP
    FETCH c1 INTO :ear_export_tt_dtl_v1.fapp_trx_Number,
    :ear_export_tt_dtl_v1.ftrx_Date,
    :ear_export_tt_dtl_v1.fCustomer_Name,
    :ear_export_tt_dtl_v1.Finv_Amount,
    :ear_export_tt_dtl_v1.fReceipt_Number,
    :ear_export_tt_dtl_v1.frcpt_Amt,
    :ear_export_tt_dtl_v1.fComments,
    :ear_export_tt_dtl_v1.fDeposit_Date,
    :ear_export_tt_dtl_v1.fExchange_Rate,
    :ear_export_tt_dtl_v1.fAmount_dr,
    :ear_export_tt_dtl_v1.fAmount_cr,
    :ear_export_tt_dtl_v1.fReceipt_Date;
    
    EXIT WHEN c1%NOTFOUND;
    
    Down;
  END LOOP;
  
  First_Record;
  
  CLOSE c1;
END;

----------when save button press
BEGIN
  Go_Block('ear_export_tt_dtl_v1');
  
  First_Record;
  
  LOOP
    INSERT INTO Ear_Export_tt_dtl
               (InvNo,
                Customer_Name,
                Inv_Amount,
                rept_No,
                tt_Amount,
                ttNo,
                Exchange_Rate,
                tt_Date,
                Bank_ref_No)
    VALUES     (:ear_export_tt_dtl_v1.fapp_trx_Number,
                :ear_export_tt_dtl_v1.fCustomer_Name,
                :ear_export_tt_dtl_v1.Finv_Amount,
                :ear_export_tt_dtl_v1.fReceipt_Number,
                :ear_export_tt_dtl_v1.frcpt_Amt,
                :ear_export_tt_dtl_v1.fComments,
                :ear_export_tt_dtl_v1.fExchange_Rate,
                :ear_export_tt_dtl_v1.fReceipt_Date,
                :ear_export_tt_dtl_v1.fBank_ref_No);
    
    EXIT WHEN SQL%NOTFOUND;
  END LOOP;
  
  IF Name_In('system.form.status') = 'changed' THEN
    COMMIT;
  END IF;
  
  First_Record;
END;

thanks,
Saurabh

Upd-Mod: Add 'code' tags. Then put code through formatter at http://www.orafaq.com/utilities/sqlformatter.htm
Re: How to Save display forms column value when display item is more then one [message #203347 is a reply to message #203189] Tue, 14 November 2006 19:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Is this a student (one-off) or work (continuing) project? Please explain why you are creating a new table just to hold one field?

David
Re: How to Save display forms column value when display item is more then one [message #203395 is a reply to message #203347] Wed, 15 November 2006 00:39 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Thanks for reply

but dear sir i don't want to give access to my client to modify my base table thatswy i have to transfer my edited data into another table and cross check the bank reference id after validation of bank ref id then we have to update our base table


thanks,
Saurabh
Re: How to Save display forms column value when display item is more then one [message #203619 is a reply to message #203395] Wed, 15 November 2006 19:40 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please consider this alternative.

After the user has entered the date range, they will press the 'Display' button. Instead of populating your fields manually consider inserting these records into the client table NOW. Do this with an 'insert' statement but with a 'not exists' in the 'where' clause so that if the records already exist in the client table the records will NOT be selected from your table. This means that the records will only be copied into the client table once.

Then base your block on the client table and display their records. If the user has not entered the "bank_ref_no" it will be empty, but if they have ALREADY processed this date range they will see the entries that they had keyed previously.

This approach with show the user THEIR data, which your current approach does not, and it will save you having to write the second trigger.

David
Previous Topic: forms 6i will compatiable with Oracle 10g
Next Topic: Status of form(Urgent)
Goto Forum:
  


Current Time: Sun Dec 04 18:51:09 CST 2016

Total time taken to generate the page: 0.11525 seconds