how do I make programm this?

[Apprentice101]

I have a master table (contracts table) with about 20 variables. (about 20 records/ contracts in it) the records are static, they only chage once every 5 years.

The idea is that a user queries a customer, if the customer does not have a contract assigned to him/her, the user will open a LOV to see the list of contracts. Selects one, the contract is displayed (the 20 variables) on the screen after the selection in the LOV, then somehow as the user clicks a button labeled "assign this contract to this customer" the contract ID gets saved as part of the customer record, therefore, next time the customer gets query, somehow the contract should also come up with the user reassigning it.

So here are the rules:

A customer can have only one contract. A contract can have many or more customers.

can you guys please give ideas about how to start with this? what features in forms can I use to make this happen?

thanks a bunch.
joey

 

[BJCooperIT]

There are lots of ways to approach this based on your users and the expected look-and-feel of your GUI interfaces.

This is how to use the approach that utilizes forms functionality.

a=Customer block, a database block (master table) query only
b=Customer Contract block, a database block (child table) updateable

 ----------------------------
|  a______________________a  |
| |          1----1  2--2  | |
| | Customer |ABC |  |\/|  | |
| |          1----1  2--2  | |
| |                        | |
| |a______________________a| |
|                            |
|  b______________________b| |
| |          3-----3 4--4  | |
| | Contract |     | |\/|  | |
| |          3-----3 4--4  | |
| |                        | |
| | ---------------------- | |
| | ---------------------- | |
| | ---------------------- | |
| |b______________________b| |
|                            |
 ----------------------------

On startup the cursor is in the customer field (#1) and the form is in ENTER-QUERY mode. The user then enters the customer number or selects a customer from the list of values by clicking on item the LOV button (#2) and then executes the query.

By linking block a to block b on the customer number, when the user queries a customer, any associated contract will automatically be queried.

The form is now in data entry mode and the cursor is in the contract field (#3). If there is no associated contract the user can enter one in #3 or select it from a list by clicking on the LOV button (#4).

All inserts, updates and deletions are handled automatically by forms when the user saves.

ALTERNATIVE METHOD
Some places want an interface that does not force the user to learn how to query or know about blocks, tables and rows, but use a "fill-in-the box" approach.

There could be one non-database block.

 ------------------------
|          1----1  2--2  |
| Customer |ABC |  |\/|  |
|          1----1  2--2  |
|                        |
|          3-----3 4--4  |
| Contract |     | |\/|  |
|          3-----3 4--4  |
|                        |
| ---------------------- |
| ---------------------- |
| ---------------------- |
|                        |
 ------------------------

On startup the cursor is in the customer field (#1) and the form is in data entry mode. The user then enters the customer number or selects a customer from the list of values by clicking on item the LOV button (#2).

The validation trigger on the customer field (#1) makes sure the customer number is valid, and then selects any existing contract data, placing it on the screen.

The user can now enter or change the associated contract #3 or select it from a list by clicking on the LOV button (#4).

All inserts, updates and deletions are handled by triggers you must write on KEY-COMMIT.

Hope this gets you started.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus