Need major help - mutli-block canvas

OK---i posted this problem earlier but we could not get it fixed.  I will give as much detail as I can think to give.  

Problem:  When I go from my checkin canvas to my guest_bill canvas, the tabular data block (guest_trans2) does not fill with the room service items.

Setup:  When at checkin canvas...hit checkout...takes data and places it in the Guest_Bill canvas.  The guest_bill canvas has three data blocks on it (Guest_Bill, Checkin1, Guest_Trans2).  Checkin1 fields are fname, lname which are filled from the previous canvas (this works).  Guest_Bill fields are Bill_ID, emp_num, depart_date, room_num, room_total, trans_total, tax, total.  depart_date, emp_num, room_num fill from the previous canvas (this works).  Guest_Trans2 is tabular with fields room_num, description, trans_date, rate.  

This is merely a list of all transactions from that room_num.  This is not populating.  It needs to populate from the guest_bill.room_num.

All data_block are control blocks (I think - Property -> Database Data Block = Yes)

I figured I need something like changing one from a control block to a data block...adding a where clause...and have it execute somehow.

I really need some help here

rbg
LVL 1
rbgermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FBIAGENTCommented:
a master-detail relation should be created between blocks Checkin1 (master) and Guest_Trans2 (detail) with the following condition

Guest_Trans.room_num = Checkin1.room_num

also, make sure that Defer Coordination should be set to false so that when block Checkin1 is queried, block Guest_Trans2 also get queried.
0
rbgermanAuthor Commented:
i think i need Guest_Bill as the master so it would be

GUEST_TRANS.room_num = Guest_Bill.room_num

do I need to change the Database Data Block properties in any of the data block...and i am not understanding what you mean by Defer Coordination.  

I will give it a try, but I was having trouble creating the relationship in the Data Block Wizard.  I do the Data Block Wizard to create the relationship in the Guest_Bill data block??????

rbg
0
FBIAGENTCommented:
my mistake and you're right.  the master block should be Guest_Bill.
to create a relation, just expand the block and put focus on the relation node. then click the (+) or create button to create a relation.  then (for the join condition) enter

GUEST_TRANS.room_num = Guest_Bill.room_num

that should be it.  by default, Defer Coordination should be set to false; so don't worry about it

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rbgermanAuthor Commented:
ok...i am at work and not school right now...but I sure hope this works.  i will find our around 5.  thanks again.  i will ask another question if i have one

rbg
0
FBIAGENTCommented:
by the way, you should not ask homework questions here; it's against the forum's policy.
i know i am not in any position to give advices, but you should try to do the homework yourself so that you would learn more that way.
good luck
0
rbgermanAuthor Commented:
it is not a HW question...it is December

semester is over

and i am graduating

i understand though
0
FBIAGENTCommented:
my apology
0
rbgermanAuthor Commented:
no problem...this week was finals and tonight is my last one

graduating and growing up

i try your suggestion when I get in front of my oracle server

rbg
0
rbgermanAuthor Commented:
i still have a problem.

I set the relation with the Where clause.  It gives me an error when trying to run the form:

FRM-30415: Relation's detail block is a control block
Relation: Guest_Bill_Guest_Trans2
Block: Guest_BIll
Form: Hotel
FRM-30085: Unable to adjust form for output

what is that

rbg
0
rbgermanAuthor Commented:
alright

i got it working with a master-detail block.  now my update table will not work.  is that because I had to change it to a control block from a database detail block???

what is the deal here


rbg
0
FBIAGENTCommented:
what do you mean by "my update table will not work"?
which block is your "update table"? can you be more specific or explan on what you were trying to do?
0
rbgermanAuthor Commented:
ok--this is where I am now.  I have moved my two command buttons from the guest_bill data block because it's property for Database Data Block was set to Yes b/c it was the master block; therefore, it would not update the tables.  The first button calculate works and updates, but the second table does not.  Now, I moved them to the Checkin1 data block...see if you can find some error in my code.  i will write down my errors when I run my form again and post them.  here is my code:

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
      
      --delete from checkin table
      delete from checkin
  where checkin_num = :guest_bill.checkin_num;
      
      --delete guest from guest table
      delete from guests
      where fname = :checkin1.fname
      and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;

i have tried them individually and none of them work.

thanks rbg
0
rbgermanAuthor Commented:
here is my error for all three actions (insert and two deletes)

FRM-40657: REcord changed or delete by another user

it shows it three times;

rbg
0
FBIAGENTCommented:
why do you have to move the buttons to a different block?  Making the block as a base table block does not affect the trigger associated to the buttons.
what do you mean by "therefore, it would not update the tables.  The first button calculate works and updates, but the second table does not"????  which table and which block that "does not" work?
0
FBIAGENTCommented:
ok. let me try to understand your form.

"Setup:  When at checkin canvas...hit checkout...takes data and places it in the Guest_Bill canvas.  The guest_bill canvas has three data blocks on it (Guest_Bill, Checkin1, Guest_Trans2).  Checkin1 fields are fname, lname which are filled from the previous canvas (this works).  Guest_Bill fields are Bill_ID, emp_num, depart_date, room_num, room_total, trans_total, tax, total.  depart_date, emp_num, room_num fill from the previous canvas (this works).  Guest_Trans2 is tabular with fields room_num, description, trans_date, rate. "

what do you have in the when-button-pressed trigger when the user "hit checkout"?  How do you "takes data and places it in the Guest_Bill canv"?  
0
rbgermanAuthor Commented:
if :checkin.checkin_num is not null then
      go_item('guest_bill.emp_num');
      list_values;
      
      --insert values in fields
      select fname, lname, checkin_num, room_num
      into :checkin1.fname, :checkin1.lname, :guest_bill.checkin_num, :guest_bill.room_num
      from checkin
      where checkin_num = :checkin.checkin_num;
      
      go_block('GUEST_TRANS1');
      execute_query;
      
      go_item('guest_bill.bill_date');

else
      clear_checkin_form;
      go_item('checkin.checkin_num');
      list_values;
end if;
0
rbgermanAuthor Commented:
this is what will not work for some reason unknown to me...

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
      
      --delete from checkin table
      delete from checkin
  where checkin_num = :guest_bill.checkin_num;
      
      --delete guest from guest table
      delete from guests
      where fname = :checkin1.fname
      and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;


i dont know if constraints or something are holding it up...not sure

please help..that is all I need to finish this

rbg
0
FBIAGENTCommented:
first of all, once you make block GUEST_BILL a base table block, you don't have to manually insert data into table guest_bill using INSERT INTO statement.  All you have to do is commit_form; and Oracle Forms will save whatever populated in the block into the database.

Second, why and what are you trying to do this?

     
     --delete from checkin table
     delete from checkin
  where checkin_num = :guest_bill.checkin_num;
     
     --delete guest from guest table
     delete from guests
     where fname = :checkin1.fname
     and lname = :checkin1.lname;


did you post; or commit; the changes on canvas CHECKIN prior to navigating to GUEST_BILL canvas?

you can replace both delete statements with,

go_block('checkin'); delete_record;
go_block('checkin1'); delete_record;


0
FBIAGENTCommented:
i guess you just have to replace the following

"
     --update guest_bill table
     insert into guest_bill values
     (:guest_bill.bill_id,
     :guest_bill.checkin_num,
     :guest_bill.bill_date,
     :guest_bill.room_num,
     :guest_bill.trans_total,
     :guest_bill.room_total,
     :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
     
     --delete from checkin table
     delete from checkin
  where checkin_num = :guest_bill.checkin_num;
     
     --delete guest from guest table
     delete from guests
     where fname = :checkin1.fname
     and lname = :checkin1.lname;

commit;

display_update_alert;
clear_guest_bill_form;

"

with

"

     --update guest_bill table
     -- automatic with forms
   
     --delete from checkin table
     go_block('checkin'); delete_record;
     
     --delete guest from guest table
     go_block('checkin1'); delete_record;

commit;

display_update_alert;
clear_guest_bill_form;

"
0
rbgermanAuthor Commented:
i read this
FRM-40657:  Record changed or deleted by another user.

 Cause:      Another user has deleted the record since the query was executed.

Action:      You can clear this record from your screen, but you cannot update or delete it since it no longer exists in the database.

Level:      20

Type:      Informative

also...i do not see why at least this would work

      --update guest_bill table
      insert into guest_bill values
      (:guest_bill.bill_id,
      :guest_bill.checkin_num,
      :guest_bill.bill_date,
      :guest_bill.room_num,
      :guest_bill.trans_total,
      :guest_bill.room_total,
      :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);

commit;

display_update_alert;
clear_guest_bill_form;

0
FBIAGENTCommented:
do you have primary key on table guest_bill?  i assume that bill_id is the primary key and it is populated in the form automatically via sequence?
i don't know why

"     --update guest_bill table
     insert into guest_bill values
     (:guest_bill.bill_id,
     :guest_bill.checkin_num,
     :guest_bill.bill_date,
     :guest_bill.room_num,
     :guest_bill.trans_total,
     :guest_bill.room_total,
     :guest_bill.tax,
  :guest_bill.total,
  :guest_bill.emp_num);
"
works now.  i thought that it did not work before.  if it worked, make sure that you don't insert two (2) records into the database; one by the form, the other by your insert statement.

honestly, i don't know what you are trying to do with the two delete statement.
0
rbgermanAuthor Commented:
i think it may be trying to insert it twice....how does it do it from the form automatically and how can i stop that.  

it's a hotel system....so when you check_out...you should be deleted from the checkin_table and from the guests_table
0
FBIAGENTCommented:
"how does it do it from the form automatically and how can i stop that"

create on-insert trigger in the block that you do not want oracle forms to insert automatically
in the on-insert trigger, put insert_record; if you want to perform insertion; otherwise, just put null;
in the on-insert trigger.

"it's a hotel system....so when you check_out...you should be deleted from the checkin_table and from the guests_table "

i don't know who design the database.  but when a customer check out, i would not want to delete records from that table.  why?  just to keep track of history of the customer; like how often, and when the customer had been in the hotel, etc.

but if you want to delete the record, navigate to the block and issue delete_record; instead of using delete statement.

it looks like you are trying to do many of the default functionalities that inherently comes with Oracle Forms.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rbgermanAuthor Commented:
ok...i think we are good here.  i want it to delete from checkin...there is no check_out

the history stays in the guest_bill table.

now..to finish...get my reports to run from the form....error on Report Server property name...not sure if i am typing it in correctly.  the book says

Report Server    localrepserver

i am not sure what my report server is called.......my host string is xxxxx.xxx.xxxxxx.xxx

is it asking for that

thanks

i'll increase the points

rbg
0
FBIAGENTCommented:
RBG,
by the way, i'm not interested in points.
anyway, what Oracle forms and reports version are you running?
i am not fully understand your report problem.  can you explain some more?
when do you get the error? how do you run your report from form; pls show your code.
0
rbgermanAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.