Create the Portals
One of the advantages of a relational database is that a layout can display data from the current table, as well as data from related tables. There are two ways to accomplish this. You can place related fields directly on the layout or create portals on the layout.
You place individual related fields on a layout when there’s a one-to-one or many-to-one relationship. For example, a bookkeeping database might have separate Invoice and Address tables, related by a Customer ID. By placing the related fields (Name, Address, City, State, and Zip) from the Address table on an Invoice layout, you could display the customer’s address on all her or his invoices but only have to enter the data once (in the Address table).
You place portals on a layout when you have a one-to-many relationship, as we do in Investment Minder. Any single investment in Main can have many related records in the Savings, Stocks, or CD table. A portal is designed to display a series of fields for multiple related records, giving you a window into the related table. You can also specify a sort order for the portal records, as well as edit the portal data (simultaneously changing the data in the related table).
CD portal. Because the CD portal is the simplest one—it has only two fields—we’ll create it first.
- Switch to Layout mode (View > Layout Mode). Select the Main layout and display the CD tab panel.
- Select the Portal Tool in the tool panel. Draw a portal beneath the fields as shown here.
- When you finish drawing the portal frame, the Portal Setup dialog box appears.
- Click the Sort portal records check box or its Specify button. In the Sort Records dialog box that appears, double-click
the Date field to move it into the Sort Order list. Click OK to close the dialog box.
When viewed in the portal on the Main layout, CD interest records will be listed in date order.
- Like other fields on a layout, you can format fields in a portal, too. Select the Date and Interest fields, and format them as Arial, 10 pt., plain text (not bold), black. Set the fill color to white and the effect to Engraved; remove the borders.
- Change the size of the portal fields by decreasing the width of the Date field and increasing the width of the Interest field. Right-align both fields.
- Select the Date field, choose Format > Date, and assign our usual date format (12/25/03) to the field. Select the Interest field, choose Format > Number, and assign the currency format to the field.
- Select the portal. Set its fill color to white and the effect to Engraved. Complete the portal by adding the text labels shown below.
When viewed in Browse mode, this portal will show all interest credited to a particular CD, sorted in date order.
Stock/Mutual Fund portal. Follow the instructions for creating the CD portal, but use the following fields from the Stocks table (in order): Dividend Date, Dividend, and Interest. Sort the portal records by Dividend Date.
Savings/Money Market portal. Follow the instructions for creating the other portals, but use these fields from the Savings table (in order): Transaction Date, Check Number, Description, Deposit, and Withdrawal. Sort the portal records by Transaction Date and Check Number.