Publishers of technology books, eBooks, and videos for creative people

Home > Articles

Creating Related Tables in FileMaker Pro 8.5

  • Print
  • + Share This
  • 💬 Discuss
Steve Schwartz shows how to create three data tables in which to record investment transactions in this FileMaker Pro 8.5 tutorial.

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.

  • squ_1.gif 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

  • squ_3.gif 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.

  • squ_5.gif 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".
  • squ_6.gif 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).
  • squ_7.gif 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.
  • squ_8.gif 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.
  • squ_9.gif 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:.
  • squ_10.gif 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.
  • squ_11.gif 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.
  • squ_12.gif 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.
  • squ_13.gif 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.
  • squ_15.gif 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.
  • squ_16.gif Save the layout changes by choosing Layouts > Save Layout.
  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus