Creating Related Tables in FileMaker Pro 8.5
Investment Minder is about to go relational. We’ll create three new data tables in which to record transactions for each investment: one for savings or money market deposits, withdrawals, and interest; one for stock or mutual fund dividends and interest; and another for CD interest.
While such data could be stored in the Main table as a series of repeating fields (as one might do with line items on an invoice), it’s best to record these transactions as separate records that are instead related to each investment by a key field, such as a CD certificate number, bond number, or stock name. By creating a separate record for each transaction, we have the advantage of being able to work with specific transactions (which is very difficult to do with repeating fields), perform calculations on a data subset (rather than on all transactions), and sort the transactions in any order we choose.
For example, the portal we’ll add to the Savings/Money Market tab will display all transactions for each savings account. Transactions will be recorded in a separate data table and will be related to an account by its account number.
Create the Savings Table
Like the initial data table (Main), each new table will have its own fields and layout. As stated earlier, for a table to be related to another table, it must contain a field that links the two tables—identifying which record or records in the new table are associated with a given investment in Main. We’ll begin by creating the Savings table.
- With Investment Minder open, choose File > Define > Database. The Define Database dialog box appears.
The table below shows the fields you’ll create for the Savings table.
Field Name |
Type |
Auto-Enter |
Validation |
Storage |
Account |
Text |
Indexed (minimal) |
||
Description |
Text |
|||
Transaction Date |
Date |
Creation Date |
||
Last Modified |
Date |
Modification Date; Prohibit modification |
||
Deposit |
Number |
Numeric only; Always validate |
||
Withdrawal |
Number |
Numeric only; Always validate |
||
Check Number |
Number |
Numeric only |
- Click the Fields tab. Ensure that Savings is the current table, define the fields listed on the previous page, set the field
options, and click OK.
Note that the field and label formatting reflect that of the most recently formatted fields and labels. We’ll format this new layout to look like the Main layout.
- Set the Header, Body, and Footer fill colors to match those on page 26. Use the Size palette to set the height of the Header to 0.444", the Body to 4.444", and the Footer to 0.444".
- Select only the fields. Format them as Arial, 10 pt., black text, white fill color, Engraved effect, left aligned. Set the height of each field to 0.236". Ensure that a black border surrounds each field (as explained on page 27).
- Select only the field labels. Format them as Arial, 12 pt., black text, bold. Set their height to 0.236" to match the height of the fields.
- Select the first label/field pair and align them along their top edges by choosing Arrange > Align > Top Edges. Repeat for every additional label/field pair.
- Drag the Last Modified label and field onto the left side of the Footer. Set the font to Arial, 12 pt., white and remove the boldface. Add a colon to the end of the label, changing it to Last Modified:.
- Select only the Last Modified field, set its fill pattern to transparent, set its fill effect to None, and remove the borders. Using the Format > Date command, select Format as: 12/25/03, / separator, and Zero as the leading character for both day and month numbers.
- Use the Size palette to set the positions of the Last Modified field and label to match their positions on the Main layout. When switching between layouts to check the field and label positions, you’ll be asked to save changes. Click the Save button.
- Drag the Account label into the left edge of the Header. Set the font color to white. Use the Text Tool to add a colon (:) to the end of the label.
- Format the Account field as Arial, 12 pt., bold, white. Set the fill pattern to transparent and the effect to None. Remove the field borders. Change the field’s width to 1.847". Drag it into position beside its label in the Header.
- Use Format > Number to apply a currency format to the Deposit and Withdrawal fields. Use Format > Date to apply our standard Date format to Transaction Date.
- Save the layout changes by choosing Layouts > Save Layout.