Bernard,
The short answer is yes. The process follows:
Suppose we use the ConComp database.
TransMaster has the following columns:
Transid,Custid,Empid,Transdate,Netamount,Freight,Tax,Invoicetotal
TransDetail has the following columns:
Transid,Detailnum,Model,Units,Price,Extprice
TransID is the foreign key in transDetail that links back to
TransMaster.
Suppose you create a variable for each column in TransMaster:
fvTransid,fvCustid,fvEmpid,fvTransdate,fvNetamount,fvFreight,
fvTax,fvInvoicetotal
Now create a table which will only be used so you can create
forms against it, and put a single null row in the table.
CREATE TABLE formTable (formInteger INTEGER)
INSERT INTO formTable VALUES NULL
Create a table to hold transDetail data for a single
transMaster entry.
CREATE TABLE `ttTransDetail` +
(`Transid` INTEGER , +
`Detailnum` INTEGER , +
`Model` TEXT (6) , +
`Units` INTEGER , +
`Price` CURRENCY , +
`Extprice`= ( units* price) CURRENCY )
Create a form called Transactions based on the FormTable.
Across the top of the form place the form variables (fvXXXX)
which represent the transmaster columns:
fvTransid,fvCustid,fvEmpid,fvTransdate,fvNetamount,fvFreight,
fvTax,fvInvoicetotal
In the Form Layout, Add or Remove Tables,
add the table just created 'ttTransDetail'.
The original table is FormTable, so this will be the
second table.
In the Form Layout, Set Current Table
choose ttTransDetail
Create a region covering the bottom of the form.
Inside the region place the columns of the temporary table.
I call it a 'temporary' table, but I set it up as a permanent
table because I may want to reference it following QUIT TO
commands, or the following day. But it is intended to hold
detail for only one transaction, and be copied to the
TransMaster and TransDetail tables.
Your region will look something like this:
Transid,Detailnum,Model,Units,Price,Extprice
extend it to the bottom of the form to show as many
rows as you wish.
Transid does not need to be listed because it can be defined
later since all detail items pertain to the same transaction.
Detailnum can be an autonumbered field in ttTransDetail.
The cursor would first land in the model column.
Perhaps you have a choose box there and skip to the
Units column. Enter the quantity.
Price is a lookup from the product table in the listprice column.
The only fields that permit user entry are model and units (Qty).
I would run this form from a button on what I call a menu form.
This form only has menu buttons, each runs button.eep USI
a different number. The program which calls the menu form
with the command: 'Edit using MenuStartup' follows that command
with a case statement which has different commands based on the
choice made.
The button [Enter a new transaction] might initiate the
following commands:
CLEAR VAR fvTransid
CLEAR VAR fvCustid
CLEAR VAR fvEmpid
CLEAR VAR fvTransdate
CLEAR VAR fvNetamount
CLEAR VAR fvFreight
CLEAR VAR fvTax
CLEAR VAR fvInvoicetotal
then
SET VAR fvTransid INTEGER = NULL
SET VAR fvCustid INTEGER = NULL
SET VAR fvEmpid INTEGER = NULL
SET VAR fvTransdate DATE = NULL
SET VAR fvNetamount CURRENCY = 0
SET VAR fvFreight CURRENCY = 0
SET VAR fvTax CURRENCY = 0
SET VAR fvInvoicetotal CURRENCY = 0
DELETE ROWS FROM ttTransDetail
INSERT INTO ttTransDetail (DetailNum) +
SELECT NULL FROM transDetail +
WHERE LIMIT = 20
This simply drops in 20 rows (assuming there are
20 rows in the existing transdetail table.)
To autonumber them:
AUTONUM detailnum in ttTransDetail +
USING 1,1 NUM
'EDIT USING transactions' will bring you a form listing
up to 20 items which may be entered on one transaction.
On the form you can place buttons to add and continue,
add and exit or clear and exit.
Clear and exit is easy. Simply QUIT TO the command
that shows the previous menu or BREAK if you are in a
case statement. Since you are not in the actual
Transmaster table, you do not 'waste' a consecutive number.
To add and exit you have a couple of steps:
1. INSERT INTO transMaster +
(Custid,Empid,Transdate,Netamount,Freight,Tax,Invoicetotal ) +
VALUES .fvCustid, .fvEmpid, .fvTransdate, .fvNetamount, +
.fvFreight, .fvTax, .fvInvoiceTotal
2. capture the value of the newly created transaction
SELECT transID INTO fvTransID FROM transMaster +
WHERE COUNT = INSERT
3. Insert data from ttTransDetail into transDetail
INSERT INTO transDetail +
(Transid,Detailnum,Model,Units,Price,Extprice) +
SELECT .fvTransID, Transid,Detailnum,Model,Units,Price,Extprice +
FROM ttTransDetail +
WHERE (Model IS NOT NULL)
DELETE ROWS FROM ttTransDetail
perhaps you may wish to print this transaction
PRINT Transaction +
WHERE transid = .fvTransid
CLEAR VAR .fv%
BREAK if inside the case statement or
if the case statement runs a command: RETURN
One advantage of this method is that nothing hits either your
transMaster or transDetail tables until you are satisfied they
are correct, or done or whatever.
You don't have to ADD any of the detail lines, they come with
the form. The autonumbered lines do not affect anything you
already have in your transDetail table.
You can put eeps to:
SELECT SUM(extPrice) INTO fvNetAmount +
FROM ttTransDetail
and for the other various calculations.
Unless there is a special reason, the quick forms are easier
to create and process. But they sometimes require complex
work to fine tune in spite of their tempting simplicity.
Randy Peterson
Bernard Lis wrote:
> Randy,
> Am I correct in that you are creating 20 rows in a dummy table with just an
> order no. and entering the region in edit mode instead of entry mode? and
> then you have to move the data from the dummy table to the real order entry
> tables?
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
