======================================================================
FORMS NOW WORK ON R:BASE VIEWS
======================================================================
PRODUCT : R:BASE VERSION : 4.0
AREA : FORMS CATEGORY: VIEWS DOCUMENT#: 651
======================================================================
Tired of scrolling back and forth across the screen in Browse/edit to
look at data in your five-table, 73-column view? In R:BASE 4.0, you can
now create a form based on a view or a mixture of tables and views.
Although you can create a form on any view within R:BASE 4.0, you can
use only single-table views to change the data in the underlying tables.
Multi-table views are read-only. You can intermix views and tables on
the same form, but again, you can edit only tables and single-table
views.
What does this let you do that you couldn't do in prior versions of
R:BASE? You can locate both the view and the table the view is based
on the same form! This allows you to locate fields outside region boun-
daries. Or, you can use a view to programatically link tables in a form
rather than relying on R:BASE's automatic linking.
Locate fields outside the region
================================
Using the CONCOMP sample database, create the following view:
CREATE VIEW Trans_sum (transid,ext_sum) AS +
SELECT transid,SUM(extprice) FROM Transdetail +
GROUP BY transid
This creates a view that contains two columns, the transaction identi-
fication number, and the sum of the extended price column for each
transaction identification number. Note that the columns are named in
the view so they can be referenced in the form. Without the optional
collist syntax of the CREATE VIEW command, the view would have had an
UNNAMED column (from the SUM). An UNNAMED column cannot be located on
a form.
Next, create a two-table form. Name the view Trans_sum as the first
table on the form, and name transdetail as the second table on the
form. Create a region for the transdetail table and locate the model,
description, units, price, and so forth, in the region. Your form might
look like this:
+======================================================================+
|| ext_sum from the view Trans_sum ||
|| transid from view Trans_sum | ||
|| | | ||
|| Transaction #: [ ] Total invoice amount: [ ] ||
|| ||
|| Model Description Units Price Extended Price ||
|| +--------------------------------------------------------------+ ||
|| | [ ] [ ] [ ] [ ] [ ]| ||
|| +-> [ ] [ ] [ ] [ ] [ ]| ||
|| | | [ ] [ ] [ ] [ ] [ ]| ||
|| | | [ ] [ ] [ ] [ ] [ ]| ||
|| | | [ ] [ ] [ ] [ ] [ ]| ||
|| | +--------------------------------------------------------------+ ||
|| +------region on the table Transdetail ||
+======================================================================+
Edit data with the form and as you scroll through the unique trans-
action numbers in the view trans_sum, the corresponding detail records
appear in the region. If you edit data in the region that would change
the extended price column, those changes are not reflected in the total
displayed from the view. The data in the view is not recalculated. Data
displayed on the form from a view does not change unless the query on
which the view is based is re-evaluated. For example, if your view is
placed as table2 in a form, the view query is re-evaluated each time you
move to a new row in table1 and the linking column values change.
Programatically link tables in a form
=====================================
R:BASE forms automatically link tables based on columns of the same
name (common columns). In some situations you don't want these columns
to be used to link the tables in a form. For example, you may have a
column named Last_Chg_Date to hold the date data on a row was last
updated. This could be a different date in each table, so you would
need to name the column differently in each table or Forms would use
it to link the tables when editing data. This is a situation where you
want to be able to specify the columns Forms will use to link tables.
By using the optional collist syntax of CREATE VIEW, you can create a
single-table view and make sure only those columns you want to link the
tables have the same names. Look at the listing of the columns in the
tables Customer and Transmaster. If we create a form to view customers
and their transactions, there would be two linking columns: Custid and
Last_Chg_-Date. When editing data, the form will only show transactions
that happened on the day the customer record was last updated. We don't
want Last_Chg_Date to be a linking column.
+-------------------------------------------------------------------+
| Table: customer No Lock(s) |
| Read Password: No |
| Modify Password: No |
| Customer Information |
| Column definitions |
| # Name Type Index Expression |
| -->1 custid INTEGER * Autonumbering |
| Customer identification number |
| 2 company TEXT 40 |
| Customer company name |
| 3 custaddress TEXT 30 |
| Customer address |
| 4 custcity TEXT 20 |
| Customer city |
| 5 custstate TEXT 2 |
| Customer state |
| 6 custzip TEXT 10 |
| Customer zip code |
| 7 custphone TEXT 12 |
| Customer phone number |
| -->8 Last_Chg_Date DATE (.#date) |
| |
| Current number of rows: 9 |
+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
| Table: transmaster No Lock(s) |
| Read Password: No |
| Modify Password: No |
| Transaction Information |
| Column definitions |
| # Name Type Index Expression |
| 1 transid INTEGER * |
| Transaction identification number |
| -->2 custid INTEGER * |
| Customer identification number |
| 3 empid INTEGER |
| Employee identification number |
| 4 transdate DATE |
| Transaction Date |
| 5 netamount CURRENCY |
| Net amount of transaction |
| 6 freight CURRENCY (netamount*.01) |
| Freight cost |
| 7 tax CURRENCY (netamount*.081) |
| Sales tax |
| 8 invoicetotal CURRENCY (netamount+ |
| freight+tax) |
| Total amount of invoice |
| -->9 Last_Chg_Date DATE (.#date) |
| |
| Current number of rows: 19 |
+-------------------------------------------------------------------+
Create a single-table view on the Transmaster table, the second table
in the form, to rename the columns. Place the view on the form instead
of the table and Last_Chg_ Date will no longer be a linking column.
CREATE VIEW Trans_Cust_View transid,custid,empid,transdate,+
netamount, + freight,tax,invoicetotal,Trans_Chg_Date) AS +
SELECT * FROM Transmaster
Note that the only column name changed was Last_Chg_Date. Custid is
now the only linking column between the two tables (table and view,
actually). Now, when the form is used to edit data, all the transactions
records with a matching Custid will be displayed. The date changed will
not be used to link the tables. Since this is a single-table view, the
data will be editable.