Oracle form in edit mode

Hello , I have 2 tables ,      Account:  consist of master data such as : account id , account number ,,,etc.              the second table is : accounts transaction:    Consist of. Transaction date , amount ,,,etc.                                           I build a form with one block based on account transaction to record the daily transaction.                          The question is : can I bring the other table , account,  with edited mode in same canvas and be possible to edit the master data also in addition to the transaction
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Mark GeerlingsDatabase AdministratorCommented:
NiceMan331Author Commented:
I don't neeed only yes , but how , step by step ? Should it be independent block ? Or master and child blocks
Mark GeerlingsDatabase AdministratorCommented:
It depends what you want.   Oracle Forms will allow you to create a two-block form either with the blocks independent of each other, or related as master and child blocks.

Usually in a multi-block form, the blocks are related, and the master table is in the master block, and the child table (that may have multiple records for each master record) is in the child block.

But, what you described in your question sounds like the opposite of this.  It sounds like you want to be able to enter multiple transaction records for multiple master records.  We don't know your business processes, so we can't tell you what will work best for your organization.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NiceMan331Author Commented:
ok Good
let me describe again
the busness process is to services bills for all branches in our company
each service is defined as An Account with the vendor master data
when the vendor of the service issue his bill , he issued per account
The Invoice From Vendor Consis Of 2 Areas
     A- Main Area : Contain The Account Information of the service ( such is , ID , Owner , Service Type ,, etc )
     B- The Details Section , consist of the periodeical chargees per consumption (Amount , Date ,,, etc )
in my database i created 2 tables
    A - Accounts , ( which should match the vendor Master Data )
    B- Account Transactions ( To record each new bill received from vendor )
   ( Both Tables Are Linked By Account-ID )

The Process will be like this :
1- invoice received from vendor
2- user will enter the new invoice data in the block of "Accounts Transactions"
3- when the user select "Account id" which is the first item in that block , he need first 2 things :
    1- To Verify the Basic Information Of The Account is match same data with vendor
       (The Account information  is alomost Fixed unless there are some changes in the basic information)
      so , he need the block Account to query the same record of account_id in the bill , and then if there are any changes
     in the information of the account , he should be able to edit it
   2- then he need to query the historical transactions of that account from table account transactions
Then , he will continue to post the new record in table account_transactions

hope this is clear
Mark GeerlingsDatabase AdministratorCommented:
That description sounds very common.  Oracle Forms supports this very well with two blocks, connected in a master-detail (or parent-child) relationship.

Your master or parent block should be based on your Accounts table,  This block should likely display just one record, since you probably want to display (and allow the user to edit, if necessary) the name, address, phone number, etc. for the account.  

Your detail or child block should be based on your account_transactions table.  In this block the "account_id" can be hidden (not displayed).  This block will allow then user to review and/or update any historical transactions for the account that is displayed, or the user can add new transactions.  You can choose to have your Oracle Form either:
1. Automatically query the historical transactions for an account whenever an account is queried in the master block (and Forms can sort these either from oldest to newest, or in reverse order, with the most-recent ones first.
2. Leave the transactions block empty, so it is ready for the user to enter a new transaction for the account.

If you need to support multiple addresses or phone numbers for an account, you should create separate tables for these (with names like: account_addresses, account_phones, etc.) and you should create a separate block in your form for each of these tables, if you need that level of complexity.
NiceMan331Author Commented:
Ok good , I'm thinking now if I created master child blocks to display one record of account and its history in details , the historical details should be locked ( no change is possible) , I need the user to enter new record in account transaction but not in the same tabular block , it need new block ( form type ) , is ok to create a third block based on account transaction? Or to create third block unbound, fill the data , then press button to inserted in table account transaction, which one is better?
Mark GeerlingsDatabase AdministratorCommented:
Yes, you can create a multi-block form with a master block to display one record of account information.  By default in Oracle Forms, this information will all be editable, but you can change the block property "update allowed" to "No" for this master block.

I'm not sure what you mean by: "its history in details".  Is that the older "account transactions" or something else?

Then, for "the user to enter new record[s] in account transaction [table]", yes, that needs to be a different block from the master block.  But, this does *NOT* need to be a different block from the block needed to display the account's "history in details" *IF* those details are the previous account_transactions for this account.  You can use one block to both display the older records, and not allow them to be changed (simply set the block property "Update allowed" to "No") plus allow the user to enter new account_transactions records in this same block (as long as you leave the "Insert allowed" property with its default value of "Yes").

Be aware though that if you do this the users will *NOT* be able to correct a new transaction if they enter it and save it, before they notice the mistake.

Usually, I would want the users (or at least a manager or supervisor) to be able to correct (that is: update) a record after it was entered and saved.  For that to be allowed, you would need the "Update allowed" property to be "Yes", at least for some users.  This property can be changed in a "when-new-form-instance" trigger based on the user's name, if you need that flexibility.

No, I definitely do not recommend this option in Oracle Forms: "create [a] third block unbound, [that is, not based on a database table] fill [that with new] data, then press button to insert that in table: account transaction".
NiceMan331Author Commented:
Ok , understood, but plz let me describe more , the first block will be based on table account, then the child block will be account transaction as tabular form.            Till here ok , now the entry form for new bill ( in account transaction) ,  I don't need it to insert new record in the same tabular form , I need it to be done in a ( form )
Mark GeerlingsDatabase AdministratorCommented:
"I don't need it to insert new record in the same tabular form"
Why not?

I don't understand why you don't want the user to be able to add new records in the second block of the same form where they start by querying the account in the top block, then review the current transactions in the second block.  Oracle Forms can certainly support the users doing the data entry right there.  I've seen, used and developed many data entry screens just like that in Oracle Forms.

"I need it to be done in a [different] form [or block]"

What is the value you see in forcing the user to a different block (or form) to insert the new records?
NiceMan331Author Commented:
Yes , first reason , seperate  form is easier to the user who has big volume of data     , secondly, there are a formula while entering the data , the user should match the result with the final amount billed by the vendor,
Mark GeerlingsDatabase AdministratorCommented:
Logic to support a formula to calculate a total (or something else) during data entry can be added to any block of any Oracle Form.  That isn't a reason for a second form.  But, if you want a block that contains only account_transaction information, and be able to display a maximum number of rows, without using screen space to display account information, that is a reason for a second screen.

This is your application, not mine.  So, you can build it however you think it will work best for you or your users.  Oracle Forms can support either approach.  If this was my application, I would rather have one form with a master block at the top, displaying one record that may fill 3-6 rows, then a detail block that could display probably 10-15 records (assuming that the fields for one account_transaction record can all fit in a single row on the screen).
NiceMan331Author Commented:
Ok thank , if it is allowed, can I ask extra question?   If I want to track the changes done by users to the table account using the master block ,  here I think I need another table to insert information changes , on which trigger can I use to do it ,
Mark GeerlingsDatabase AdministratorCommented:
That really is a separate problem, so you should create a new question for that.  (I have almost two more hours today to watch for new questions.)
NiceMan331Author Commented:
New question added , but plz now I'm going to sleep , tomorrow morning I can see the answer , thanx
NiceMan331Author Commented:
i did the master _ chiled blocks , and is ok now
but here i have small issue
i created 3rd block based on accounts transaction
what trigger should work here when user select an account ID in order to post new transaction
how to query the same record in block account
Mark GeerlingsDatabase AdministratorCommented:
I assume your master block is on the accounts table.  But I don't know which tables your blocks 2 and 3 are based on.  Are both blocks 2 and 3 related to the master block?  Or is block 2 related, but block three is an independent block?

I don't know enough about your blocks and what relationship they have (or don't have) to answer your question.
NiceMan331Author Commented:
Yes , accounts is the master block ,  account transaction is the source of block 2 as a child block with relationships,  block 3 is based on account transaction but is independent ( design for fast posting of new bills ) , the process will be like this :   Block 3 is the essential block , the user will type into field account Id ,  block 1 should be queried immediately together with block 2 in order to view it , then the user will continue entering the next fields of block 3 then commit and go to insert new bill again in field account id on same block 3
Mark GeerlingsDatabase AdministratorCommented:
That sounds like an unusual arrangement of blocks in a data entry screen.  Blocks 2 and 3 are both based on the same table, correct?  And the difference is that block 2 is a query-only block, related to block 1 and with block two queried automatically after block 1 is queried.  And block 3 is an independent block that allows users to enter new records for *ANY* account, correct?
I’ve never seen a data entry screen like.
You apparently want users to be able to enter new transaction records for a different account without having to manually search for that new account in block 1 first.  And you want them to be able to see, but not change, existing transaction records in block two.  Are those statements both true?
Do you allow updates in block 3?  If yes, how do you prevent the users from querying (and then changing) older records there?  Or does block three not allow queries, or only allow queries of records that are quite recent?
If you allow some queries in block three, how do you decide which records can be queried there?  If you don’t allow queries in block 3, how do users ever correct a mistake after they save a new record.  Or, do your users never make a data entry mistake?  I’ve never seen users like that.
Mark GeerlingsDatabase AdministratorCommented:
If your blocks are like I described, you could use a Key-Next-Item trigger on the account_id in block 3 to first check if the account_id in block 1 is different, then go_block(accounts); execute_query; (And the relationship to block 1 will auto-query that block.) go_block(3);
End if;
Mark GeerlingsDatabase AdministratorCommented:
One other detail: you will need a pre-_query trigger in block one to copy the account_id from block 3, if that is non-null, into the account_id field in block one.

I would still recommend keeping this a much -simpler two-block form with both inserts and updates allowed in block two.
NiceMan331Author Commented:
every thing you explained are correct
what you advice also ( only 2 blocks ) is correct also
i totally agree
i admit that creating the 3rd block is more complex , but why i do like that ?
because single entry form is more prefferable for data entry rather than the tabular form of block 2

1-is more faster ( we can save one step of data_entery )
  with 2 blocks , the user withh do like this :
  A-Select account_id of block 1 and query it's record
  B-Go to block 2 , go new , start insert

while in my logic , the work will be only in block 3 , select account_id , and start typing directly
with hige amount of data , saving one step is a profit

2- There is logical test in the formula while entering the data , with single form , user can easliy see the result of the test
   else of with tabular form , he may not focus on it with multi records ( in block 2 )

Finally , you asked
If you don’t allow queries in block 3, how do users ever correct a mistake after they save a new record.
yes , you are correct
i thinking to have list_item in same form , will maintain all data from block 2
if user double_click one record from that list , block 3 will be query based on bill_id ( is the primary key in table account transaction )
Mark GeerlingsDatabase AdministratorCommented:
"creating the 3rd block is more complex" - I agree completely, especially if you want to add things like: "if user double_click one record from that list, block 3 will be quer[ied automatically] based on bill_id"

"because single entry form is more preferable for data entry"  - I agree, but I thought you were describing a single form in both cases, with either two blocks or three.  And these blocks can all be on the same tab page if you want.

"rather than the tabular form of block 2"  - What?  Block two can be a tabular block that displays multiple records whether it is a display-only block like you seem to want, or if it supports inserts and updates as I recommend (and like Oracle Forms does by default).

"we can save one step of data_entry " - What?  I have no idea which step of data entry you believe can be saved or in a three-block form.

"in my logic , the work will be only in block 3 , select account_id , and start typing directly" - not exactly.  The user cannot "select" the account_id in this block.  The user has to type it directly (or choose it from an LOV, if you create an LOV on the account_id field).  That looks like an extra step of data entry to me.  In a two-block form, the account_id can be provided automatically from the master block.  That looks like a way to save a step of data entry to me.

"There is logical test in the formula while entering the data , with single form , user can easily see the result of the test
    else with tabular form, he may not focus on it with multi records"  -  I don't understand exactly what you mean here.  Are you saying that you want the data entry block to be a single-record, form-style block, instead of a multiple-record, tabular-style block that may work best to display the older transactions?  If that is true, that may be a good reason for this to be a three-block form.

But your ideas for a three-block form still sound to me like a lot of complexity compared to what a simple two-block form could give you.
NiceMan331Author Commented:
If that is true, that may be a good reason for this to be a three-block form.      ,           Yes , exactly.           ,    If you still have better suggestion to achieve that I will be very happy to follow , thanx
Mark GeerlingsDatabase AdministratorCommented:
If you want a tabular-style block for reviewing transactions, but a form-style block for entering new transactions, that is possible.  And, it doesn't have to be complex.

I would make both of them child blocks of the master block, and I would set the "autoquery" property of the block relation for block 2 to "yes", and for block 3 to "no".  That way, the form-style block defaults to being empty and is ready for the user to start entering new transactions there.

I expect that the details for a single record in the master block can fit into just a few rows, so I would design that block to fill only the top one/third or so of the space in a normal computer screen.  Then, I would design block 2 to be a tabular-style block on the same tab page, but filling the bottom two-thirds of the screen.  And, I would design block 3 (if you need it) to be a form-style block on a second canvas (not a second tab page) that is displayed to also fill the bottom two-thirds of the screen, leaving the account block visible at the top, whether the user is in block 2 or block 3.

The users can then use the normal Oracle Forms "next block" and "previous block" keystrokes to move between any of the blocks, and Oracle Forms will always keep the appropriate canvas visible, either the main canvas with blocks one and two, or the secondary canvas with just block three (but the top portion of the main canvas will still be visible when the user is in block 3).

This way you don't have to write any special block synchronization triggers.  The synchronization will be handled automatically by Oracle Forms for you.

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
NiceMan331Author Commented:
thank you , and excellent suggestion
i'm learning new things from you
i will work with this
but sorry to ask again
is it ok for displaying the old bills to work with my suggestion
list_item for the posted bills
double click for the requested one
block 3 will be query based on value of bill_id ?
Mark GeerlingsDatabase AdministratorCommented:
"Is it for displaying the old bills to ...[use a] list_item for the posted bills"?
I never use list_items in Oracle Forms, but if you like them, they can work.

Then "double click for the requested one [I assume that is an old bill in block 2, and]
 block 3 will be quer[ied] based on value of bill_id?"  
I see some problems with that:
1. I thought you wanted block 3 to be for data entry, not queries.
2. I thought you wanted the records in block 2 to be not modifiable.  (Maybe these are usually not modified, but the users do need the ability to modify them occasionally?)
3. Yes, I think it is possible to use a when-mouse-doubleclick trigger in one block to navigate to a different block and do a query there for a particular record.  But, this adds complexity.  You will then need a pre-query trigger in block 3 to copy the bill_id from block 2 into the bill_id in block 3 (or some other combination of triggers and/or item properties to do this). A simple two-block form, where the second block does both display of previous records (plus allow them to be changed, if necessary, without requiring a double-click)  and/or adding of new records avoids the complexity of the three-block form you seem to prefer.
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.