Chapter 4. Adding Power to Your Database

In the last chapter, you created a custom database for storing Lease Documents. You learned how to create fields of the appropriate type to store your data, and then you customized two layouts—one for viewing the data as a form and a second for viewing the data in a list.

If you never went any further than that, you’d still have a database that tracks documents. But now that your business is more efficient, it’s time to teach the database some new tricks. You’re already storing the signing date and lease duration data for each lease. In this chapter, you’ll create a field that lets you know when each lease is ending. Then you’ll create a new table and all the supporting mechanisms for recording monthly payments.

Creating a Simple Calculation

On Managing Field Types, you learned about field types as you created them in Table view. You chose a field type based on the kind of data you wanted to enter into each field. But calculation fields can create data under their own power. For instance, your database already stores monthly Rental Fee and Lease Duration data. If you wanted to figure out the value of that lease over its life, you could enter your data into a calculator and then manually enter the result into your database. But a calculation field calculates and enters data automatically and can even update if the data in either field changes. All you have to do is write a formula that tells the calculation field how to get the answer you want.

Note

This tutorial picks up where Chapter 3 left off. If you didn’t complete those tutorials, or you want to start with a clean file that’s already got some data in it, download the samples from this book’s Missing CD page at www.missingmanuals.com/cds/fmp12mm.

Creating Fields with Manage→Database

In the last chapter, you created fields in Table View using the + button (Creating Fields). Like Table view, that’s a bare-bones approach. When you need to harness all the control FileMaker gives you, call up the Manage Database window (Figure 4-1). That’s how you’ll create your first calculation field:

The Manage Database window is the most efficient way to create and manage fields. You can also Duplicate or Delete fields here. Rename a field by selecting it from the list, typing the new name, and then clicking the Change button.

Figure 4-1. The Manage Database window is the most efficient way to create and manage fields. You can also Duplicate or Delete fields here. Rename a field by selecting it from the list, typing the new name, and then clicking the Change button.

  1. Choose File→Manage→Database, and then click the Fields tab (if it isn’t already active).

    The Fields tab is where most of your field creation takes place. (See the box on Use the Keyboard for more information about using this window.)

  2. In the Field Name field, type Lease Value.

    As with other database elements, it’s best to use simple, descriptive names for your new calculation field.

  3. From the Type pop-up menu, choose Calculation, and then click Create.

    The Specify Calculation window appears. Here’s where you’ll write the formula that gives you the Lease Value. It’s based on data in the Lease Duration and Rental Fee fields.

  4. In the Current Table list, double-click the Lease Duration field.

    The field’s name appears in the calculation area of the window. You could type the field’s name, but if you make a typing error, FileMaker will bark at you, so it’s usually easier to point and double-click.

  5. In the Operators section of the window, click the * sign.

    The asterisk means multiplication.

  6. In the Current Table list, double-click the Rental Fee field.

    The formula now reads Lease Duration * Rental Fee. Your calculation should look like Figure 4-2.

    The Specify Calculation window lets you write a formula that’s attached to a calculation field. You can perform calculations on text, number, date, time, timestamp, or container fields and get results of each of those types, too. The option “Do not evaluate if all referenced fields are empty” can speed up your database, because FileMaker doesn’t have to try to calculate a value if data is missing from all the fields that make up the calculation.

    Figure 4-2. The Specify Calculation window lets you write a formula that’s attached to a calculation field. You can perform calculations on text, number, date, time, timestamp, or container fields and get results of each of those types, too. The option “Do not evaluate if all referenced fields are empty” can speed up your database, because FileMaker doesn’t have to try to calculate a value if data is missing from all the fields that make up the calculation.

    Note

    Because both fields in your calculation are formatted as Number, Number is automatically selected for the “Calculation result.” But you should always check this setting when you’re creating calculations, since some calculations don’t work as you intend if the calculation result type isn’t set properly. You’ll see one such example in the next tutorial.

  7. Click OK, and then OK again to close both windows.

    Depending on the settings in your Preferences window, you may not see the new calculation field appear immediately on your layout.

If the new calculation field appears on your layout, switch to Layout mode, and then move the field into place below your existing fields. (If the new field doesn’t appear, see the next page to learn why. The next section shows you how to create the field manually.) Switch back to Browse mode to see the calculation in action. Change the data in the Lease Duration and Rental Fee fields to see the calculation work dynamically.

Tip

Now that you’ve created fields using the Manage Database window, you may never want to go back to creating them in Table view. Change FileMaker’s Preferences by choosing Edit→Preferences (Windows) or FileMaker Pro→Preferences (Mac). Then turn on the “Use Manage Database dialog to create files” option. The next time you create a new database, you’ll get the Manage Database dialog box instead of a table. Note, though, that once you set Manage Database as your preferred method for creating fields, new fields no longer automatically appear on your layouts when you create them in Table view unless you also turn on the “Add newly defined fields to current layout” option in Preferences.

Adding New Fields to a Layout

You determine whether or not newly created fields appear automatically on your layout when they’re created using the Manage Database dialog box. In Windows, choose Edit→Preferences; in Mac OS X, choose FileMaker Pro→Preferences and then select the Layout tab to see the “Add newly defined fields to current layout” option. When this option is turned on, FileMaker just plops the field down at the bottom of your layout and, if necessary, increases the Body part to accommodate the new field. Since you’re going to invest a lot of time making layouts look just so, you’ll usually work with this option turned off. That means you have to place any newly created fields on your layout manually.

FileMaker provides three ways to add a field to a layout, all of which require you to be in Layout mode. First, you can choose Insert→Field. When you do, FileMaker asks which field you want and then drops it in the middle of the layout for you to move into place. The field tool (Figure 4-3) gives you more control, so it’s used more often than the menu command. But you get the most control by Shift-dragging (Option-dragging) a field to copy it as you move it. Fields created this way have all the formatting of the original, so this method can save you lots of trips to the Inspector.

The arrow points to the Field tool, and a tooltip identifies the tool and adds a usage tip. The Field tool works a little differently than most tools. You have to drag the tool down onto the layout where you want the field to land. When you’re creating fields, turn on dynamic guides, so they can show you the size, shape, and location of the new fields. The dotted horizontal line represents the field’s baseline.

Figure 4-3. The arrow points to the Field tool, and a tooltip identifies the tool and adds a usage tip. The Field tool works a little differently than most tools. You have to drag the tool down onto the layout where you want the field to land. When you’re creating fields, turn on dynamic guides, so they can show you the size, shape, and location of the new fields. The dotted horizontal line represents the field’s baseline.

To add the Lease Value field to the layout, follow these steps:

  1. Drag the Field tool down onto the layout.

    As you drag, FileMaker shows a small field and the dynamic guides.

  2. Drag to where you want the field to land, and then release the mouse button.

    The Specify Field window appears (Figure 4-4), showing a list of the fields in your current table.

  3. Choose the Lease Value field, and then select the “Create label” option (if it’s not already selected).

    Although you could copy and paste an existing field label and then change the text, it’s usually easier to have FileMaker create the label for you and then use the Format Painter to change the font and size (if the label doesn’t appear in the same format used for your field labels).

    When you add a field to the layout, FileMaker asks which field you want by showing you the Specify Field dialog box. Another way to quickly create a field with the formatting you want already on it is to copy and paste an existing field and its label. Double-click the new field to show the Specify Field dialog box. Select the field you want, turn off the “Create label” option, and then change the field’s label.

    Figure 4-4. When you add a field to the layout, FileMaker asks which field you want by showing you the Specify Field dialog box. Another way to quickly create a field with the formatting you want already on it is to copy and paste an existing field and its label. Double-click the new field to show the Specify Field dialog box. Select the field you want, turn off the “Create label” option, and then change the field’s label.

  4. Click OK.

    The field and its label appear on the layout.

The new field isn’t quite as wide as the original, so now is a good time to notice that your other currency field, Rental Fee, is much wider than it needs to be. Make the Rental Fee and Lease Value fields the same width as the Lease Duration field. Use the Format Painter to make Lease Value match the other fields on your layout. Don’t forget to use the Inspector’s Data tab to apply Currency formatting. Most people prefer to see currency data aligned to the right. You can do that in one of three ways:

  • Choose Format→Align Text→Right.

  • Click the Right Align button in the Formatting bar (Changing Text Appearance).

  • Go to the Inspector’s Appearance tab, Paragraph section, and click the Right Align button.

Now you’re ready to switch to Browse mode to view the data in your new calculation field.

Deleting a Field from a Layout

In the Manage Database window, you can delete a field by selecting it and then clicking Delete. You’ll see a dialog box asking you to confirm that you really do want to delete the selected field. There’s a caveat, though: Calculation fields (like Lease Value) make reference to other fields. That is, the formula for Lease Value is Lease Duration * Rental Fee. If you try to delete either Lease Duration or Rental Fee, FileMaker shows you a warning dialog box and refuses to delete the field. If you absolutely, positively have to delete either field, you have a choice: Either change Lease Value’s formula so it doesn’t refer to the field you want to delete, or delete Lease Value first. Then when you try to delete the field again, FileMaker still asks you to confirm that you want to delete the field, but it doesn’t need to warn you about the other ones that refer to it—because there aren’t any.

Note

You’ll also get a warning if you try to delete a field that’s used in a script. But in that case, you get the choice to proceed anyway or cancel the delete. If you delete a field used in a script, be prepared to do some repair work. The script can’t do its work properly once a field it needs is gone from your database.

Discard Changes

There’s a fallback position if you delete or change fields and then change your mind or realize you’ve made a mistake. Click the Manage Database dialog box’s Cancel button. FileMaker asks whether you want to “Discard ALL changes made in this dialog to tables, fields, and relationships.” Click Discard, and FileMaker performs none of the changes you’ve made since you opened the Manage Database dialog box. You may lose work you would have preferred to keep, but that’s often a better option than losing a field with a tricky calculation that you can’t quite remember.

Creating a Related Table

Now that you’ve got the basic information stored for your leases, you realize that you can centralize your data if you record rent payments in the same database as the Lease Documents. Your first thought might be to create a field or two (Date Paid and Amount Paid) to record each payment, but you quickly realize that since some leases last 36 months, you’ll have to create 72 fields and then place them on your layout. Worse yet, for your 12- and 24-month leases, most of those fields will be empty.

If you start signing 48-month leases, you’ll have to create a whole new slew of fields and start rearranging your layout again. And what if your tenants make more than one payment each month? It’d be so much more efficient if each record could create only the fields it needed.

You’re on the right track—instead of adding fields in the Lease Agreement table, what you need is a set of new records in a related table (see the box on These Terms Are Relational for a definition of table and other terms you’ll need to know for this section). That’s where a relational database comes in. You need separate tables to store each type of information. Because when you think about it, monthly payments aren’t really part of a Lease Agreement table. Date Paid and Amount Paid data pertains to a specific Lease Agreement, but it doesn’t belong with the name of the tenant or the PDF of the lease agreement itself. What you need is a new Payment table, where you can add 12 records (one per month, of course) to the 12-month leases, or 24 records to the 24-month leases. With a separate table, even if you start offering 5-year leases, you’ll never have to add more fields or stretch a layout to accommodate a change in the way you do business.

And you don’t even have to create a new file for your new table. FileMaker lets you put dozens, even hundreds, of tables into the same file. There’s an art and science surrounding how to figure out which tables you need and how to relate them to one another. You’ll learn that in Chapter 5. For now, you’ll learn about the tools you need to create a related table and enter monthly payment records on the Lease Agreement layout.

Understanding the Elements of a Relationship

Now that you’ve decided to store lease information in the Lease Agreement table and Payment information in a new Payment table, you need to make sure payments match the right Lease Agreement record. First, you start with a unique identifier called a key field, which uses the auto-enter field option to create a serial number that’s unique for each record in the table. Then you use the Relationships graph to match the two tables’ key fields. Finally you create a special layout object, called a portal that lets you view, create, and edit payment records on the same layout where you store data about each Lease Agreement.

Creating a Key Field with an Auto-Enter Serial Number

To ensure that Lease Agreements and Payments records match properly, you need a unique identifier in the Lease Agreement table. One of FileMaker’s field options, called Serial Number, automatically assigns a unique number to each record when it’s created. Here’s how to create a key field and then apply an Auto-Enter Serial number option to it:

  1. Choose File→Manage→Database, and then if it’s not active, click the Fields tab.

    The Manage Database window appears, with the Lease Agreement table’s fields in a list.

  2. In the Field Name field, type agreementID.

    This field name may seem odd (no spaces and a mix of upper- and lowercase letters), but it’s one of many naming conventions used by developers to help them quickly identify fields that they’ve created to make the database work. In this naming convention, the field name starts with the name (or one-word abbreviation) of the table for which it is the key field. The “ID” at the end confirms that the field is used as a key.

  3. From the Type pop-up menu, choose Number.

    Key fields are most often number fields. (See Keys for more information on choosing and creating a good key field.)

  4. Click the Create button, and then click the Options button. See Figure 4-5.

    The “Options for Field ‘agreementID’” window appears.

  5. Turn on the “Serial number” checkbox, and then click OK until you’re back on the Lease Agreement layout.

    A serial number is created in the agreementID field for each new record you make.

It’s important to know that even if you can’t see the agreementID field, the serial number is added to it when you create each new record. And if you still haven’t turned on the “Add newly created fields to current layout option” in the Preferences dialog box, do that now. Create a few new records to watch serial numbers being created. Notice though, that the records that were already in your database don’t have serial numbers yet.

Auto-enter serial numbers are most often created as a record is created (“On creation”), but if you choose “On commit” instead, FileMaker doesn’t generate the number until the first time the user commits the record. Usually, you use this option when your database has multiple users (as you’ll learn on page 267). For instance, a user might create a record but then delete it instead of filling out its data. In that case, there would be a gap in the serial numbers, because the numbers assigned to deleted records are never reused. When you first set up an auto-enter serial number field, the next value is always “1” unless you change it. If you’re importing data from another source, you should find the highest serial number in the imported data, and then set your field’s “next value” to one number higher than the imported data. And while the most common “increment by” value is 1, you can make the numbers increase by any other number you choose instead.

Figure 4-5. Auto-enter serial numbers are most often created as a record is created (“On creation”), but if you choose “On commit” instead, FileMaker doesn’t generate the number until the first time the user commits the record. Usually, you use this option when your database has multiple users (as you’ll learn on page 267). For instance, a user might create a record but then delete it instead of filling out its data. In that case, there would be a gap in the serial numbers, because the numbers assigned to deleted records are never reused. When you first set up an auto-enter serial number field, the next value is always “1” unless you change it. If you’re importing data from another source, you should find the highest serial number in the imported data, and then set your field’s “next value” to one number higher than the imported data. And while the most common “increment by” value is 1, you can make the numbers increase by any other number you choose instead.

Using Replace Field Contents to Add Serial Numbers

When you create a key field after data has been entered, you don’t have to go to each record and manually enter a serial number. You can use the Replace Field Contents command (Using the Replace Command) to add in the missing serial numbers and reset the field option’s “next value” counter at the same time. Since you have to have a field on a layout in order to use the Replace Field Contents command, you may have to put the agreementID field on the Lease Agreement layout if it isn’t already there.

  1. In Browse mode, choose Records→Show All Records.

    Every record has to have data in its key field in order to relate to another table, so make sure you aren’t looking at a found set of just some of your records.

    Warning

    If you’re using this tutorial on a database where some records have serial numbers and some don’t, find only the records without serial numbers in this step. In that case, you wouldn’t want to replace the serial number in any record that already had one. Make sure you set the “next value” higher than the largest number in the record with serial numbers.

  2. Click in the agreementID field.

    If you don’t click into a field first, the Replace Field Contents command will be dimmed.

  3. Choose Records→Replace Field Contents.

    The Replace Field Contents window appears. See Figure 4-6.

    In the Replace Field Contents dialog box, the default button is not the one that does the action you’ve just set up. The Replace command can’t be undone, so FileMaker is saving you from unintentionally destroying good data if you hit Enter too soon. Instead, pressing Enter cancels the replacement and leaves everything as it was. Replace Field Contents is a lifesaver when you have to retrofit a table with a key field after you’ve created records. However, replacing data after you’re created relationships between tables is risky—if the value in the key field in either table changes, the child record gets disconnected from its parent.

    Figure 4-6. In the Replace Field Contents dialog box, the default button is not the one that does the action you’ve just set up. The Replace command can’t be undone, so FileMaker is saving you from unintentionally destroying good data if you hit Enter too soon. Instead, pressing Enter cancels the replacement and leaves everything as it was. Replace Field Contents is a lifesaver when you have to retrofit a table with a key field after you’ve created records. However, replacing data after you’re created relationships between tables is risky—if the value in the key field in either table changes, the child record gets disconnected from its parent.

  4. Select “Replace with serial numbers,” and then turn on “Update serial number in Entry Options,” if it isn’t already selected.

    The first option tells FileMaker how you want the numbers created in your existing records. The second option ensures that records you created after the Replace don’t reuse any of the numbers entered in the current found set. If you didn’t choose this option, you’d have to look through your records to find the highest number, and then return to the Manage Database dialog box to change the “next value” setting for the agreementID field manually after the replace is done.

  5. Click Replace.

    Serial numbers are created in all the records of your database.

Flip through the records to see the serial numbers. You can go back to the Manage Database dialog box and check the auto-enter options for the agreementID field to see that its “next value” has been updated. While you’re there, select the “Prohibit modification of value during data entry” option at the bottom of the window. You don’t want people changing your carefully crafted serial numbers. Create a new record to see the next value appear automatically in the agreementID field.

Tip

Because it can be cumbersome to create a key field and populate it with data months or even years later, it makes sense to create a key field in every table you create even if you have no immediate plans to relate the table to any other table. That way, you don’t have to deal with all this rigmarole if your needs change.

Creating a New Table

Your Payment table needs to store information about each monthly payment for a specific Lease Agreement. The Payment table’s attributes are the date the rent was paid and the amount paid. You also need a key field for hooking up Payments to the Lease Agreement table. And since it’s good practice to create a key field in every table, just in case, you’ll also add a paymentID field.

Two key fields in one table? It may sound crazy, but it’s not uncommon for a table to have 10 or more key fields that let it relate to that many other tables. The first key field you’ll create (called paymentID) uniquely identifies each Payment record and could be used when you figure out a reason to link the Payments table (as a parent) to another table. (See Keys for information on primary and foreign keys.) The second key field (agreementID) will hold the value that matches the value in the key field of a specific record in the Lease Agreement table. That’s how a Payment record (the child) matches up with the proper Lease Agreement record (the parent).

  1. Choose File→Manage→Database, and then click the Tables tab.

    This tab is where you create, edit and manage your tables.

  2. In the Table Name field, type Payment, and then click Create.

    Remember: When you created the Lease Agreement table, you decided to use the singular case for your database elements, so stay consistent. The Payment table appears in the Tables list.

  3. Click the Fields tab.

    You’re viewing the field list for the new Payment table. FileMaker is smart like that and switched to the selected table for you. But if you need to, you can switch tables using the Table pop-up menu above the list of fields.

  4. In the Field Name field, type paymentID, and then select Number from the Type pop-up menu. Click Create.

    It’s a good habit to create a table’s key field first thing. That way, you won’t forget to do it.

  5. Click the Options button and make the paymentID field an auto-enter serial number.

    All your Payment records will have serial numbers because you created a key field right at the beginning, before you created any records. And since you have no records yet, the next value should be “1.”

  6. At the bottom of the Options window, turn on “Prohibit modification of value during data entry.”

    This prevents users (even you, the developer) from changing the data in a field. Protecting the data in a key field is critical to keeping your records properly related to one another. But if you ever do need to change this data, just head back to the field’s options and switch off this option.

  7. Click OK.

    You’re ready to finish creating the rest of the fields for the Payment table.

Use the skills you learned earlier in this chapter to create these fields, with the following types:

  • Date Paid (Date)

  • Payment Amount (Number)

  • agreementID (Number)

Click OK when you’re done. Because you’re still viewing the Lease Agreement layout, you don’t see evidence of your new table yet. But it’s there.

Viewing the New Table’s Layout

If you click the Layout pop-up menu (in either Browse or Layout mode), you’ll see a new layout called Payment. FileMaker created it for you when it created your new table. And much like the boring layout you got when you created the Lease Agreement table, there’s nothing there except the standard lineup of layout parts and your newly created fields. In Browse mode you can see that this new table doesn’t have any records yet. If you click in a field as if to enter data, you’ll see the warning message in Figure 4-7.

When you see this message, just click OK. But don’t bother creating a record. You’ll do that later—from the Lease Agreement layout.

Figure 4-7. When you see this message, just click OK. But don’t bother creating a record. You’ll do that later—from the Lease Agreement layout.

You could dress this layout up any way you want to. For instance, you might turn it into a list layout so you can report on your payments. But since you’ll be creating Payment records and entering data from the Lease Agreement table, for now just note that the layout is here, and go back to the Lease Agreement layout.

Creating a Relationship Between Two Table Occurrences

You just saw that when you create a table, and then add fields to it, FileMaker makes a bare-bones layout for that table. It also makes a Table Occurrence for the new table on the Relationships Graph. That graph is found on the only tab of the Manage Database window that you haven’t seen yet. And true to its name, that’s where you create the relationship between the Lease Agreement and Payment tables.

Note

Your Relationships graph can have more than one instance of any table, and each instance is a different view into the table. Each instance of a table is called a table occurrence. It’s important to know whether you’re referring to the table itself or an occurrence of the table. A word of caution, though: FileMaker isn’t all that consistent about using the term in its own windows and help files, so it’s not your fault if you’re confused.

Also true to its name, the Relationships Graph is a visual representation of your file’s tables and how they relate to one another. And you create relationships in perhaps the easiest way possible: You drag from one table to another to create a line. Here’s how to create a relationship between two tables, using their key fields.

  1. Choose File→Manage→Database, and then click the Relationships tab. Or use the shortcut Shift+Ctrl+D (Shift-⌘-D).

    You’ll see two table occurrences: one for the Lease Agreement table and the other for the Payment table. You need to draw a line between the agreementID fields in each table, but that field is not visible in the Lease Agreement table occurrence.

  2. Click and hold the tiny triangle at the bottom of the Lease Agreement table occurrence.

    That scrolls the field names so that you can see the one you need. Or you can drag the bottom border of the table occurrence to make it tall enough to show all its fields.

  3. In the Lease Agreement table occurrence, click the agreementID field, and then drag to the agreementID field in the Payment table occurrence. Release the mouse when it’s pointing to the proper field.

    As you drag, you’ll see a line with a box in the middle, and the key field is highlighted to help you stay oriented. The pointer changes shape to show that you’re creating a relationship. As you hover over any field, it highlights. When you release the mouse, the two fields you’ve connected jump above a new divider at the top of each table occurrence. They still appear in the list below and are italicized to show that they’re used as keys (Figure 4-8).

  4. Double-click the box in the middle of the relationship line.

    The Edit Relationship window appears (Figure 4-9). The window is divided into halves, showing Lease Agreement on the left and Payment on the right.

  5. On the Payment side of the relationship, turn on “Allow creation of records in this table via this relationship” and “Delete related records in this table when a record is deleted in the other table.”

    This setup is typical of the child side of a relationship. Here’s how to think about it: You’ll be creating Payment records from the Lease Agreement layout, so the relationship’s options need to allow record creation. And if you delete a Lease Agreement record, there probably isn’t much use for the Payment records, so that second option deletes Payment records that would otherwise be “orphaned” when the parent record is deleted.

  6. Click OK until you return to the Lease Agreement layout.

    Or the Payment layout, if that’s the one you were viewing at the start of this tutorial.

Notice that the line between the two Table Occurrences is straight on the Lease Agreement end and has a “fork” at the Payment end. This crow’s foot helps you know which is the parent and which is the child side of the relationship. But how does FileMaker know which is which? Lease Agreement’s agreementID field has the Auto-Enter Serial number option turned on, which is standard operating procedure for a field that’s meant to be used as a parent table key field. Payment’s agreementID isn’t set as a serial number, so FileMaker knows that it’s the child table’s key field.

Figure 4-8. Notice that the line between the two Table Occurrences is straight on the Lease Agreement end and has a “fork” at the Payment end. This crow’s foot helps you know which is the parent and which is the child side of the relationship. But how does FileMaker know which is which? Lease Agreement’s agreementID field has the Auto-Enter Serial number option turned on, which is standard operating procedure for a field that’s meant to be used as a parent table key field. Payment’s agreementID isn’t set as a serial number, so FileMaker knows that it’s the child table’s key field.

If naming fields in both tables with the same name seemed confusing as you were doing it, you’ve just seen why it’s a good idea. In a large or complex database, some tables can have many key fields for relating to other tables. But if you use the same name in the child table as the key field in the parent table, it’s very easy to find the proper key field and then drag a line between the two tables.

Note

Purists will say that the “tables” you just connected by their fields are really table occurrences. Turn to Understanding Table Occurrences to find out why.

The Edit Relationship window lets you define how a relationship works. It’s divided in half vertically, with one table’s information on the left and the other table’s information on the right. For most people, it’s easiest to visualize a relationship when the parent table is on the left and the child table is on the right. If your window is flipped (Payment on the left and Lease Agreement on the right), it’s because your table occurrences are flipped on the Relationships graph. To change the display, close the Edit Relationship dialog box and then drag the table occurrences to rearrange them. In practice, it doesn’t matter which side the tables appear on because relationships are bidirectional (you’ll learn what that means on page 250).

Figure 4-9. The Edit Relationship window lets you define how a relationship works. It’s divided in half vertically, with one table’s information on the left and the other table’s information on the right. For most people, it’s easiest to visualize a relationship when the parent table is on the left and the child table is on the right. If your window is flipped (Payment on the left and Lease Agreement on the right), it’s because your table occurrences are flipped on the Relationships graph. To change the display, close the Edit Relationship dialog box and then drag the table occurrences to rearrange them. In practice, it doesn’t matter which side the tables appear on because relationships are bidirectional (you’ll learn what that means on page 250).

Creating and Using Portals

Once your tables are related to one another, you can freely display related fields on layouts. For example, say you want the Date Paid and Amount Paid fields from the Payment table to appear on your Lease Agreement layout. You could simply add those fields to the layout, but you’d quickly find a big problem. There’s only a single instance of each field, and the point of a related table is to have multiple records from the child table related to the parent table.

The problem is solved with a portal, which is a layout object that displays multiple records from a related table. Not only can the portal display related records, but you can also use a portal to create, edit, and delete related records.

Adding a Portal to a Layout

A portal can display as many related records as you want, limited primarily by the size of your layout and the height of the portal. As with other layout objects, you can format portals to match your database, using fills and lines the way you would with other drawn objects. Here’s how to create a portal on the Lease Agreement layout:

  1. In Layout mode, drag the bottom edge of your Body part to make room for your portal. Or use the Inspector to make the Body 500 points high.

    Figure 4-10 shows you how much space you’ll need.

    In the upper right of the Status toolbar, you see the Portal tool selected. On the layout you can see an outline where the portal will be when you finish creating it. The Portal Setup dialog box is set to allow you to create, edit, and delete Payment records without ever having to visit a Payment layout.

    Figure 4-10. In the upper right of the Status toolbar, you see the Portal tool selected. On the layout you can see an outline where the portal will be when you finish creating it. The Portal Setup dialog box is set to allow you to create, edit, and delete Payment records without ever having to visit a Payment layout.

  2. Click the Portal tool to select it, and then drag on the layout to create a portal.

    As with all drawn objects, it’s usually easiest to start in the upper-left corner and drag to the lower-right corner where you want the portal to appear. When you release the mouse, the Portal Setup window appears.

  3. From the “Show related records from” pop-up menu, choose Payment.

    This is the Payment table occurrence you saw when you hooked up the Lease Agreement and Payment tables in the Relationships graph (Creating a Relationship Between Two Table Occurrences).

  4. Turn on “Show vertical scroll bar.”

    A scrollbar lets you see more related records in a smaller space.

  5. Turn on “Allow deletion of related portal records” and then click OK.

    The “Add Fields to Portal” window appears. When the Allow deletion option is turned off, you can’t delete Payment records using the portal and the regular Delete command.

  6. From the list of available fields on the left, select and move the Date Paid, Payment Amount, and agreementID fields.

    Each field appears in the list on the right as you move it. It isn’t necessary to add a related table’s key field to a portal. In the real world, you don’t usually want to see that data. But while you’re learning about portals and relationships, it’s very helpful to display the key field so you can see how portals work.

    Tip

    Pay attention to the way the field name appears in the available fields list. You’re seeing the fully qualified field name. That is, the field is represented by its table name, followed by a pair of colons, and then the field name itself. It’s kind of like you being called by both your first and last names. This nomenclature helps you keep the Lease Agreement and the Payment tables’ agreementID fields straight.

  7. Click OK.

    FileMaker adjusts the portal object to show the number of fields you specified, and the related fields you selected in the previous step appear in the portal (Figure 4-11).

No matter how many rows the portal is set to show, you see only one row of fields, and they’re always in the portal’s first row. Notice that the fields fit precisely in that top row. In fact, FileMaker used the automatic format of your fields to figure out how many rows it could fit in the space you drew with the portal tool.

The Payment fields are evenly divided within the width of your portal. You may need to adjust the fields’ widths, change their text alignment, and create field labels.

Resizing and Moving a Portal

Just like any other layout object, a portal shows selection handles when you click to select it. But the location of its bottom selection handles may surprise you. They’re at the bottom of the portal’s first row, not at the bottom edge of the portal. If you drag the bottom middle selection handle downward, you change the row height and not the number of rows the portal will display. That’s useful where you want to show lots of data from each related record and a single row of fields won’t get the job done. You can change the width of a portal by dragging one of the middle handles on either side or by using the Width box on the Inspector’s Position tab.

In Layout mode, the portal displays information about the data it contains along its lower-left edge. The related table’s name (Payment) appears, followed by “[1…6+].” That means that the portal is tall enough to display 6 rows of related records, starting with the first record. The “+” sign means that the portal will show an active scrollbar when it contains more than six child records. Notice that the Lease Agreement::agreementID field has been moved out into the gray space beyond the edge of your layout. Objects in that space don’t show up in Browse mode, so it’s a handy place to put things you might need for troubleshooting but you don’t want to see while you’re entering data.

Figure 4-11. In Layout mode, the portal displays information about the data it contains along its lower-left edge. The related table’s name (Payment) appears, followed by “[1…6+].” That means that the portal is tall enough to display 6 rows of related records, starting with the first record. The “+” sign means that the portal will show an active scrollbar when it contains more than six child records. Notice that the Lease Agreement::agreementID field has been moved out into the gray space beyond the edge of your layout. Objects in that space don’t show up in Browse mode, so it’s a handy place to put things you might need for troubleshooting but you don’t want to see while you’re entering data.

To move a portal, click to select it and then drag it to the new location. It’s safest to click in the gray area below the fields, because if you click a field and then drag, only the field you click will move. You may want to move fields within their portal—say to rearrange their order or to scoot them around to make room for new fields—but make sure they stay inside the border of the portal’s top row. If they’re off by even a point, two problems can occur. First, only one row of data from the related table will appear, no matter how many related records there are. Second, if you try to move the portal, the fields that stick their necks up get left behind when you drag the portal.

Delete a portal by clicking it and then pressing the Delete key (or choosing Edit→Clear). The portal and its fields are deleted together. You can copy a portal and its fields with the Copy and Paste commands or keyboard shortcuts. But the Shift-drag (Option-drag) shortcut is quickest. See the box on Power to the Portal to learn why you might want to copy a portal.

Adding Rows to a Portal

Making a portal taller doesn’t give you more rows. If you want to add more rows, change its options. Do that by double-clicking on the portal to make the Portal Setup dialog box appear. That’s the same one you saw when you created the portal. To add or remove rows, just type the number you want in the “Number of rows” field. When you click OK, the portal will resize to accommodate its new settings.

Warning

If the layout isn’t tall enough to fit the adjusted portal, you’ll see a warning message that tells you that the layout size needs to be increased. If you don’t want FileMaker deciding how tall your layout should be, click No in that warning dialog box, and the portal will stay the height you drew it. Then you’ll have to increase the layout size manually and then try to change the row display again.

Context

Back in Browse mode, you can see the portal, but there’s no data in it. That’s because the Payment table doesn’t have any records yet. If you’re thinking about choosing the New Record command, don’t act on that thought, because it won’t work. At least it won’t create a new Payment record. It will continue to work as it always has, by creating a new Lease Agreement record. To understand why, backtrack just a bit to take a look at your layout’s setup. Switch to Layout mode, and then choose Layouts→Layout Setup (Figure 4-12).

The Layout Setup dialog box tells you that the Lease Agreement layout shows records from the Lease Agreement table occurrence. The “Include in layout menus” option lets you determine which of your layouts show up in the Status toolbar’s Layout pop-up menu. Deselecting this option is a good way to keep users off a layout you don’t want them to see, like your Payments layout or layouts you create for printing envelopes or mailing labels.

Figure 4-12. The Layout Setup dialog box tells you that the Lease Agreement layout shows records from the Lease Agreement table occurrence. The “Include in layout menus” option lets you determine which of your layouts show up in the Status toolbar’s Layout pop-up menu. Deselecting this option is a good way to keep users off a layout you don’t want them to see, like your Payments layout or layouts you create for printing envelopes or mailing labels.

Each layout is tied to one, and only one, table occurrence. That table occurrence tells the layout which table’s records it can show. Another way of saying the same thing is that the table occurrence gives the layout its context. Context is fundamental to many aspects of your database, because without the proper context, the layout can’t show you the records you want to see. Layouts have context, and so do portals. The portal you added to the Lease Agreement layout has the context of the Payment table. Keep context in mind as you’re reading the next section, which covers the things you can do with related records using a portal.

This database has only two tables and only one occurrence of each table, so context seems obvious. But real-world databases often have dozens (even hundreds) of tables, and they may have several occurrences of each table. So if you get used to thinking about context now, you’re laying a good foundation for working with more complicated databases later. No matter how complicated a database gets, though, you can thread your way through the most tangled mess by answering two simple questions: Where am I, and what records do I need to see? Right now, you’re on the Lease Agreement layout, and you need to see Payment records. The relationship you created on Creating a Relationship Between Two Table Occurrences shows you the records you need.

Creating Records Through a Portal

Once you’ve set up a portal on a layout, you can create records without going all the way back to the Payment layout. On a Lease Agreement record, in Browse mode, click in any field. You can now tab into the Payment portal’s fields, just as if they were a part of the Lease Agreement table. The fields are active even though there aren’t any related records, because when you defined the relationship between the Lease Agreement and Payment tables, you chose the option that allows related records to be created. View your related table by choosing Window→New Window. A new window appears that’s a duplicate of the original. Move the new window over to the side so you can see both windows, and then switch the new window to the Payment layout (Figure 4-13).

  1. On the Lease Agreement layout, go to Browse mode, click in the Payment portal’s Date Paid field, type a date, and then press Tab.

    As soon as the insertion point moves to the next field, you’ll see the record appear in the Payment window. Even though you didn’t enter it, the new record’s agreementID field now has a value, and it matches the value in the Lease Agreement::agreementID field.

  2. Type a number in the Payment Amount field, and then press Tab again.

    The agreementID field in the portal is active. Because it’s a foreign key, it’s not set up to prohibit data entry. Try this experiment: Change the number in the Payment::agreementID field (it’s in the portal). After you commit the change, the Payment record is no longer related to the Lease Agreement record. It might even be related to the wrong parent record now. Luckily you have the Payment window open, and you can just change the Payment::agreementID data back to match the value in the parent record you’re viewing. When you commit the Payment record, it will show back up in the portal.

Use the portal to create a few more payment records just to watch how the process works behind the scenes. Notice how the Tab key travels through the rows in the portal, much like a spreadsheet.

In the window on the left, the Omar Little record is active, and you can see that the portal shows field boundaries as if there’s a new record. But in the window on the right, you can see that the Payment table doesn’t have any records yet. Keep your screen set up like this for the next tutorials, so you can watch how and when related records are created, edited, and deleted. This is also a good troubleshooting tip when something is wrong with a relationship and you’re trying to figure out how to fix it.

Figure 4-13. In the window on the left, the Omar Little record is active, and you can see that the portal shows field boundaries as if there’s a new record. But in the window on the right, you can see that the Payment table doesn’t have any records yet. Keep your screen set up like this for the next tutorials, so you can watch how and when related records are created, edited, and deleted. This is also a good troubleshooting tip when something is wrong with a relationship and you’re trying to figure out how to fix it.

Remember you wouldn’t usually put a foreign key field in a portal because as you just saw, when the value is changed, the child record disappears from the portal; it’s no longer related to the parent record you’re viewing. Plus it’s confusing to users, since the value in every child record is, as it should be, exactly the same. Still, viewing an ID field in a portal, along with a second window opened to a layout that has the context of the related records, is a good way to learn how relationships work. Plus it’s a great way to troubleshoot when something is not working the way it should.

Editing Records Through a Portal

You can edit any related record by clicking in the field you want to change, and then typing the new info. Tab to the next row, or if you’re on the last row in the portal, click into a blank space on the Lease Agreement record to commit the changes.

Deleting Records Through a Portal

Unlike the New Record command, the Delete Record command can work on a portal record—if you’ve formatted the portal to allow related record deletion (luckily, you did just that back on Resizing and Moving a Portal). But you have to click in a portal row first to set up the context so FileMaker knows which related record you want to delete.

  1. Click in any field of the portal row you want to delete.

    You’ll see the insertion point blinking in the field you clicked in.

  2. Choose Records→Delete Record.

    The delete master or related record warning appears (Figure 4-14).

    Just clicking in a field on a portal row doesn’t give FileMaker enough information about context, so it asks you which record you want to delete. If you click on Master or Related (but not Cancel), you’ll get a second window asking if you’re sure you want to delete the record.

    Figure 4-14. Just clicking in a field on a portal row doesn’t give FileMaker enough information about context, so it asks you which record you want to delete. If you click on Master or Related (but not Cancel), you’ll get a second window asking if you’re sure you want to delete the record.

  3. Click Related.

    A warning dialog box appears, giving you a chance to change your mind and keep the record instead of deleting it.

  4. Click the Delete button.

    The related record is deleted.

The terms master and related are FileMaker-speak for parent and child. If you’re not on a portal row at all—neither an active field nor a portal row highlight—the Delete Record command works exactly as it does on a layout that has no portals. That is, you’ll delete the Master (or parent) record.

There’s a way to avoid dealing with two different warning dialog boxes when you want to delete a related record. You have to select the whole portal row instead of just clicking in a field (Figure 4-15). Click between the fields in a portal row instead of in one of its fields. When the portal is highlighted, choose Records→Delete Record. FileMaker now knows that you want the related record deleted and it skips right to a warning dialog box that asks if you want to permanently delete the related record. Click Delete, and the payment record is gone.

The Payment record for December 15, 2012, is highlighted. No field in the portal row is active, but a highlight shows on the whole row. Selecting a row this way sets the context for a portal row delete. However, you can’t enter or edit data in a portal row that’s highlighted this way, because the insertion point isn’t in a specific field.

Figure 4-15. The Payment record for December 15, 2012, is highlighted. No field in the portal row is active, but a highlight shows on the whole row. Selecting a row this way sets the context for a portal row delete. However, you can’t enter or edit data in a portal row that’s highlighted this way, because the insertion point isn’t in a specific field.

Performing Finds with Related Data

You can search in related fields just as easily as you can search in the “local” table’s fields. So if you wanted to find all Lease Agreements with November 2012 records, just switch to Find mode, click in the Payment portal’s Date Paid field, enter your search criteria, and then click Perform Find. You get a found set of all the Lease Agreement records that have related Payment records dated November 2012. Each Lease Agreement record will still show all its related records, and not just the ones that match your search criteria. As you flip through your found set of Lease Agreement records, you might think that some records shouldn’t be in the found set. But remember, not all the related records may be showing in the portal. So if you think a record showed up when it shouldn’t have, scroll down in the portal. You’ll find a November 2012 Payment record in there somewhere.

But if what you wanted was a list showing only the November 2012 payment records, you have to search using the Payment layout, which is set to show records from the Payment table occurrence (because it has the context of the Payment table occurrence, remember). That way, your found set will contain just the records with November 2012 payments.

Using Tab Controls

Adding a portal to the Lease Agreement layout may have streamlined your workflow, but it required you to increase the size of you layout. And if you want to add a large Notes field, you’ll have to increase the size even more. Plus, the Lease Document container field would be more useful if it were larger. But if you make it too big, that field will dwarf all the other data on the screen, making it hard to focus on the text data.

That’s where Tab Controls come in. The name makes it seem as though Tab Controls have something to do with the Tab key on your keyboard, but they don’t. (The “Tab” part of Tab Control is a tribute to the antique database called a file cabinet. Our forefathers used a paper device with a protruding tab, called a folder, to organize their documents.) Tab Controls provide a way to organize data on a layout so you can focus on a few chunks of data at a time. Tab Controls also let you put far more information on the same layout without making it gigantic or cramming it with data. You’ve seen similar objects in other software programs, even other places in FileMaker itself. For example, the Inspector has three tabs that are used for organizing all the information it has to hold. A Layout Tab Control works much the same way (although without the collapsible sections). Figure 4-16 shows the Lease Agreement layout reorganized using a Tab Control.

This version of the Lease Agreement layout has a large Tab Control covering most of its area. The Tab Control has three tabs, with the existing fields divided among them. The Agreement tab has the basic data, plus a big new field for storing notes about the Lease Agreement. The Payment portal has been moved to the Payments tab, and the Lease Document container field has been moved to the Document tab. Check the finished sample file to see what those tabs look like.

Figure 4-16. This version of the Lease Agreement layout has a large Tab Control covering most of its area. The Tab Control has three tabs, with the existing fields divided among them. The Agreement tab has the basic data, plus a big new field for storing notes about the Lease Agreement. The Payment portal has been moved to the Payments tab, and the Lease Document container field has been moved to the Document tab. Check the finished sample file to see what those tabs look like.

Creating a Tab Control

Tab Controls are easy to draw, but since it can be tricky dividing the objects on an existing layout among the new tabs, you’ll need to do a little prep work. The process is easier on a larger monitor but can be done even on a small one. First, expand the database’s window as large as your monitor will allow. If it’s not big enough to show you about double the width of space as you currently have showing, try Zooming out. Take a look at Figure 4-16 to see the new design you’re aiming for.

Next, follow these steps:

  1. In Layout mode, drag all of your fields and field labels over in the gray space to the right so they’re past the visible edge of the Lease Agreement layout.

    If you don’t move your fields, the new Tab Control will be on top of them in the layout’s stacking order, and it won’t be easy to select them afterward. Although you’d think that the “Send to Back” command would fix things, it doesn’t always, so it’s just better to empty out the space first.

  2. In the Status toolbar, click the Tab Control tool. (It looks like a tiny tab.) Then, draw a large tab panel on the layout.

    It should be as wide as the dividing line between the Header and the Body part and nearly the height of the Body. Put the left edge of the Tab Control on the left edge of the layout, and its bottom edge on the bottom edge of the Body part. When you finish drawing, the Tab Control Setup dialog box (Figure 4-17) appears.

    These are the settings for the Tab Control shown in Figure 4-16. Tab widths are automatically determined by the length of their names. But you can use the Tab Width pop-up menu to add extra space, set a minimum or fixed width, or make all tabs the width of the widest label. All Tab Width options are overridden if you select Full justification, though.

    Figure 4-17. These are the settings for the Tab Control shown in Figure 4-16. Tab widths are automatically determined by the length of their names. But you can use the Tab Width pop-up menu to add extra space, set a minimum or fixed width, or make all tabs the width of the widest label. All Tab Width options are overridden if you select Full justification, though.

  3. In the Tab Name field, type Agreement, and then click Create. Repeat this step for two more tabs: Payments and Document.

    FileMaker adds the three tab names to the Tabs list. The first tab you create is set as the Default Front Tab. That means the Agreement tab will be the active one whenever you switch to the Lease Agreement layout—no matter which tab was active the last time you left it.

  4. Select Full from the Tab Justification pop-up menu.

    The Full option will make the tabs appear all the way across the top of the Tab Control itself.

  5. When you’re done, click OK to close the Tab Control Setup dialog box.

    Your new tab panel, complete with three tabs, sits highlighted in place on your layout.

Notice the selection handles and a dark box around each of the tabs. Any formatting changes you make with this selection will affect all three tabs. It’s easiest to make formatting changes to the Tab Control right after you set it up, because it’s a bit of a pain to reselect all your tabs later. Try it now: Click outside the Tab Control to deselect it. Then click the Agreement tab. To select the others, you have to press Shift and then double-click the other tabs.

Switch to Browse mode, where you’ll see that your Tab Control is already working. You can click tabs, and each one comes to the front, just as you’d expect. But a Tab Control without objects is pretty useless.

Switch back to Layout mode to divide your objects and move them onto their proper tabs. First, select the fields and field labels that belong on the Agreement tab (refer back to Figure 4-16, if you need a refresher). Then drag those fields onto the Agreement tab. Choose File→Manage→Database to create a Notes field (Text type), and then put it on the Agreement tab, too. Use the Inspector to add a vertical scroll bar to the Notes field.

Click the Payments tab and move the Payment portal and its fields to the Payments tab. Double-click the portal to view the Portal Setup dialog box, and then increase the number of rows to nine. Finally, move the Lease Document field to the Documents tab and make it larger. Try to maintain its proportions: remember, it’s displaying an 8.5 x 11 inch document printed in portrait orientation. Finally, now that the tabs are filled, put copies of the Name fields above the Tab Control. That way, you know whose record you’re looking at, no matter which tab is selected.

Note

If you use the Arrange→“Send to Back” command for an object that’s on a Tab Control, the selected object goes behind other objects on the same tab, but not behind the Tab Control itself.

Editing Tab Controls

As you just saw, you can add a Tab Control any time you need to fit more stuff on a layout. And once it’s there, you can add or delete panels or change the control’s appearance. Edit a Tab Control by double-clicking it in Layout mode to summon the Tab Control Setup dialog box.

Adding, Removing, and Reordering Tabs

In the Tab Control Setup dialog box, you can add new tabs by typing a name, and then clicking Create. The new tab appears at the end of the list of tabs and to the right of the existing tabs in the control. You can also rename an existing tab: Select it in the list, enter a new name, and then click Rename.

To delete a tab, select it in the list and then click Delete. When you delete a tab from a layout, you also delete all the objects on that tab. (If the tab you select for deletion has any objects on it, FileMaker warns you first, and asks whether you’re sure you know what you’re doing.)

Finally, you can control the tab panels’ order. FileMaker draws the tabs in the order they appear in the Tabs list. The leftmost tab panel is the one that appears at the top of the list, and so on. Rearrange the list using the arrows to the left of each name. FileMaker is smart enough to move the objects on each tab along with the tabs themselves when you reorder.

Default Front Tab

When you first switch to a layout but before you’ve clicked a tab, FileMaker needs to decide which tab to show automatically. You tell it which one by choosing the appropriate tab name from the Default Front Tab pop-up menu. While it’s possible to choose any of the tab panels, be aware that most places where tabs appear, the leftmost tab is usually the front tab. If there’s a compelling reason for a tab to always be in front when you first see a layout, it’s pretty likely that that tab should be on the left, too.

Tab Justification

If the total width of all your tabs is less than the width of the Tab Control itself and you haven’t chosen Full justification, then FileMaker lets you choose where the grouping of tabs should be positioned. It’s a lot like aligning a paragraph of text: Choose from Left, Center, or Right, and the tabs will bunch up according to your selection.

Note

If you have more tabs than can fit given the size of the Tab Control, then FileMaker simply doesn’t show the extras. You can either force the tabs to be narrower using the Tab Width option (see below), make the Tab Control itself bigger, or make the tab names shorter.

Tab Width

The Tab Width pop-up menu has several choices to influence the width of the tabs:

  • The standard setting, Label Width, makes each tab just wide enough to hold its label, so each tab could be a different width.

  • Label Width + Margin of adds the amount of additional space you specify around the label text. The label’s text will be centered within the tab.

  • If you prefer all your tabs to be the same width, choose Width of Widest Label. FileMaker figures out which label is biggest, sizes that tab appropriately, and then makes the others match. This setting may push some tabs out of view if they won’t all fit with the new width.

  • If you’d like all your tabs to be a nice consistent width, but with the ability to accommodate the odd long label, choose Minimum of. Enter a minimum width (75 points, say), and every tab will be that width, unless the label is too big to fit, in which case that one tab will widen enough so the label fits.

  • If you want the utmost in control and uniformity, choose Fixed Width of, and then enter a width in the box. Every tab is exactly that width. If the label’s text is too big, then FileMaker cuts it off at the edges.

Note

Full tab justification overrides some settings in the Tab Width pop-up menu, so if you’re making changes and don’t see what you expect, make sure you haven’t selected Full in the Tab Justification pop-up menu.

Formatting a Tab Control

Out of the box, Tab Controls are matched to the theme you chose when you created the layout. But like other objects on a layout, you can change their formatting. Use the Line and Fill tools on the Inspector (or the Formatting bar, if you prefer). Change the setting in the Corner Radius tool to make the tabs squared off or rounded. You’ll usually format all the tabs on a single Tab Control the same way, but you can select each tab panel individually; the choices you make apply only to the currently selected tab. To select the whole Tab Control (all its tabs and all the objects on each tab), use the selection rectangle or Shift-click each tab panel.

Tip

If you accidentally format a single tab panel when you meant to format the whole Tab Control, select your newly formatted tab and use the Format Painter (Making Two Layouts Match) or the Inspector’s Copy and Paste buttons to apply formats to each unformatted tab panel.

Deleting a Tab Control

If you don’t want a Tab Control after all, just select it, and then choose Edit→Clear, or press Delete or Backspace. FileMaker warns you that it’s about to delete all objects on the tab panel as well. If that’s all right with you, click OK. If you need to keep fields or objects on the tab panels, though, click Cancel, and then move the keepers off the panel for safekeeping.

Adding Merge Fields

Your First and Last Name fields are up above the new Tab Control so you can tell which record you’re dealing with as you switch tabs. But all the rest of your fields are on the Tab Control, and if you’re chunking data, it’s not the best idea to have some of your data entry fields up there above the Tab Control. That’s one place Merge Fields come in. You can create a text object that contains merge fields to display the data from each record’s First Name and Last Name field. A merge field is a text block containing the field’s name, surrounded by a pair of double angle brackets like this:

<<First Name>>

Besides being useful for displaying data on layouts, merge fields are often used for things like form letters, labels, or envelopes. Either way, merge fields expand and contract to use only the actual amount of space required by data inside the fields they represent. You can’t enter or edit data using a merge field, nor can they be searched in Find mode (but the Quick Find in Browse mode will search merge fields). But that’s no problem here—you have the normal First Name and Last Name fields down on the Agreement tab for that purpose. In this case, a pair of merge fields help orient you as you switch tabs and are a more attractive way to display data than using normal fields would be. Start by deleting the copies of the name fields you placed above the Tab Control.

  1. Click the Text tool (the letter “T”) and then drag to create a text block where you want the new merge fields to appear.

    A blinking insertion point appears.

  2. In Layout mode, select the Text tool, click in the blank space above the Tab Control, and then change the formatting to 18-point bold.

    This is the format you want the merge fields to use.

  3. Choose Insert→Merge Field., Or use the shortcut Ctrl+M (Windows) or Option-⌘-M (Mac).

    The Specify Field dialog box appears.

  4. Double-click the First Name field and then press the space bar.

    The First Name merge field, “<<First Name>>,” appears inside your text block. You add the space so the Last Name doesn’t run onto the First Name.

    Tip

    If you know the exact name of the field(s) you want, you can type it instead of using the Insert→Merge Field command. Just be careful to get the angle brackets and name of the field exactly right. If you make a mistake, all you’ll see in Browse mode is what you typed, and not the data you expect.

  5. Make sure that the insertion point is still blinking after the First Name and space in the text block, and then Choose Insert→Merge Field again. Double-click the Last Name field.

    The text “<<First Name>> <<Last Name>>” now appears inside the text block.

Click outside the text block to switch tools, and then move the text block if necessary. Switch to Browse mode to see that the field’s contents appear inside the merge fields there.

You can format merge fields just like any other text block. But since they also contain data, if you apply formatting from the Inspector’s Data tab to text blocks that contain merge fields, FileMaker displays the data according to your formatting. So if you put a merge version of the Rental Fee field on the Payment tab (so you can see what the Rental Fee is supposed to be as you record each payment), don’t forget to format the text block as currency.

You can use merge fields to create a form letter (you’ll get one copy for each record in the found set, with appropriate data for each record). Just create a new layout, select your context, and then type the text of the letter inside a large text block, and insert merge fields within the text as appropriate. Because your new layout shows you records from a specific table occurrence, you’ll never have to run another “merge” operation. Just find the records for the folks who should get a letter, and then print all the records in your found set (Preserving the Automatic Order). You’ll also see heavy use of merge fields if you use the Layout/Report Assistant to create label or envelope layouts for sending out your merged letters.

Using Symbols to Show Important Info

Merge fields aren’t the only things FileMaker can use to show dynamic information. You use one of a handful of special symbols—stand-in characters that are replaced with info when you view your database in Browse, Layout, or Preview mode. For example, see Figure 4-18, where each record on the Lease Agreement List layout is numbered. The record number symbol displays an automatic number for each record.

Note

FileMaker offers a host of other symbols besides the record number symbol. See the box on Other Symbols for details.

To add a record number to your Lease Agreement List layout, you’ll need to slide your fields and their labels to the right to make space. Then click the Text tool to select it and click in the space you’ve just made. Choose Insert→Record Number Symbol. You now have a text object that contains “{{RecordNumber}}”. It takes up way more space than it needs to in Layout mode, but it’ll be fine in Browse mode. It’s annoying having it cover part of the First Name field, so select the text block and then choose Arrange→“Send to Back” to place it behind the name field. It’ll work just fine there without being distracting. If you want to format the record number, just select it and apply the changes you want. You don’t need to bring it forward first. Switch to Browse mode, where you see that FileMaker puts the current record number in place of the symbol.

The number to the left of each record on the Lease Agreement List layout comes from a special symbol placed on the layout. Sort the list, and notice that the records change order, but the record numbers themselves stay in sequence. The record number is meant to help you figure out where the record is ordered in a list, but not to identify any specific record. See page 159 for a way to assign a permanent ID number, or key, to a record.

Figure 4-18. The number to the left of each record on the Lease Agreement List layout comes from a special symbol placed on the layout. Sort the list, and notice that the records change order, but the record numbers themselves stay in sequence. The record number is meant to help you figure out where the record is ordered in a list, but not to identify any specific record. See page 159 for a way to assign a permanent ID number, or key, to a record.

Like Merge Fields, you can insert symbols into existing text objects. Just click into the text object first, as if to type. Then when you choose Insert→Record Number Symbol, FileMaker adds the record number symbol to the existing text.

Writing a Basic Script

Now that you have a record number on the Lease Agreement List layout, and have sorted the list, you can start to see how useful the layout really is. The Lease Agreement layout is great for revealing detail, but when the Lease Agreement List layout is sorted by Last Name, it’s a cinch to scroll to the record you need without entering Find mode, typing in search criteria, and then performing a find. But it could be easier to sort the records on the layout. As it is, you’ve got to choose Records→Sort Records, select the field you want to sort by, and then click Sort. All this stuff is easy, but efficiency is king in your world. The solution is to write a script to do these things automatically.

If you’re familiar with macros in other programs, then you already get the idea of scripts in FileMaker—you set up scripts to perform tasks for you. The task at hand—sorting—is just one command, but it has several steps. They’re all quick steps, but when you have to repeat them several times a day and so does everyone else in your office, all that wasted time adds up to real inefficiency. Also, any manual process leaves room for human error. When you make a mistake, no matter how harmless, you have to undo or redo what you just did. A script that handles your sort is more efficient and less susceptible to error.

Creating a Sort Script

Here’s how to write a script that sorts the records on your list layout alphabetically by Last Name and then First Name:

  1. Choose Records→Sort Records. Set up the window to sort by Last Name and then First Name (Sorting Records), and then click Sort.

    Every time you open the Sort window, the last sort order is already in the window. So save yourself some time by performing the sort before you start creating your script. That way, the order will already be in the Sort window.

  2. Choose Scripts→Manage Scripts.

    The Manage Scripts dialog box appears.

  3. Click New.

    The Edit Script dialog box appears (Figure 4-19).

    The Edit Script window contains everything you need to write a script. Here the list of available script steps is filtered by View: Found Sets, but you can also sort all the script steps alphabetically if you prefer. Many of the script steps available are the same as the commands in FileMaker’s menus. If you know how to use those commands manually, you know how they’ll behave as script steps. But there are some commands that you can access only through scripting, and the subject is so deep and wide that this book has three Chapters, 11, 12, and 17, devoted to the subject.

    Figure 4-19. The Edit Script window contains everything you need to write a script. Here the list of available script steps is filtered by View: Found Sets, but you can also sort all the script steps alphabetically if you prefer. Many of the script steps available are the same as the commands in FileMaker’s menus. If you know how to use those commands manually, you know how they’ll behave as script steps. But there are some commands that you can access only through scripting, and the subject is so deep and wide that this book has three Chapters, 11, 12, and 17, devoted to the subject.

  4. In the Script Name field, type Sort by Last Name.

    Always use descriptive names in FileMaker. Mature databases can have hundreds of scripts, so good naming is the first step in keeping things organized.

  5. In the View pop-up menu, choose Found Sets.

    The list of script steps is now filtered so you can easily pick the one you need.

  6. Double-click the Sort Records script step.

    The Sort Records script step appears in the window’s Script pane on the right.

  7. Turn on “Perform without dialog” in the Options section below the Script pane.

    Without this option selected, you’d see the regular Sort window every time you run the script. Don’t turn on this option when you’re writing a script that lets the user choose a custom sort as the script runs.

  8. Turn on “Specify sort order.”

    The regular Sort window appears, with Last Name and then First Name set up already. As you know, FileMaker remembers your most recent sort order, but it’s good practice to verify everything when you’re scripting. And if you want a different sort order, you can change it and the script will remember your changes.

  9. Click OK to close the Sort window, and then, in the Edit Script window, click the Close button.

    FileMaker asks if you want to save the script’s changes.

  10. Click Save.

    FileMaker saves the script and closes the Edit Script window.

  11. Make sure “Include in Menu” is turned on for the “Sort by Last Name” script, and then close the Manage Scripts window.

    Now that the Manage Scripts window is closed, you need some way to run the script you just wrote. Look in the Scripts menu. It appears there, along with a shortcut. You can run the script by choosing Scripts→“Sort by Last Name” or by using the shortcut.

Tip

Windows users can save scripts before closing the Edit Script window by choosing the Edit Script window’s File→Save Script command. Both PC and Mac fans can use the Save Script shortcut Ctrl+S (⌘-S).

Now that the script is ready to go, Unsort your records (choose Records→Unsort), and then run the script.

Creating a Button

Running the script from the menu saved you a few steps, but you can make it even more convenient by attaching the script to a layout object, which then becomes a button. Then whenever you click the button in Browse mode, the script runs automatically. Here’s how:

  1. In Layout mode, click the Last Name Field’s label to select it.

    It’s a common convention to click a column label to sort a column, so help your users out by adopting that principle.

  2. Choose Format→Button Setup.

    The Button Setup window appears (Figure 4-20).

  3. In the script step list, click Perform Script.

    This “controller” script step lets you run any script you’ve written by attaching it to a button.

  4. In the Options section of the window, click Specify.

    The Specify Script window appears, showing a list of all the scripts in your database. You’ve only got one, but it’s not uncommon to have hundreds.

  5. Click the “Sort by Last Name” script, and then click OK until you’re back on your layout.

    Your button is ready to use.

The Button Setup window lets you choose from most of the same steps you see in the Edit Script window. The difference is that you can only choose a single script step when you define a button this way. Any time you need a process that requires two or more script steps, create a script and then attach it to the button. But even if the process is a single step, you may still want a script, so you can set it to appear in the Scripts menu. Even better, if you apply a script to several buttons throughout your database, you can change the script and all the buttons will run the edited script automatically. But if you had attached a script action to those buttons instead of a script, you’d have to change each one manually.

Figure 4-20. The Button Setup window lets you choose from most of the same steps you see in the Edit Script window. The difference is that you can only choose a single script step when you define a button this way. Any time you need a process that requires two or more script steps, create a script and then attach it to the button. But even if the process is a single step, you may still want a script, so you can set it to appear in the Scripts menu. Even better, if you apply a script to several buttons throughout your database, you can change the script and all the buttons will run the edited script automatically. But if you had attached a script action to those buttons instead of a script, you’d have to change each one manually.

Switch to Browse mode, and then Unsort your records, if they’re sorted. Finally, click the Last Name field label to see the script run. You haven’t put anything on your layout to indicate to your users that the field label does anything useful. FileMaker changes the pointer to a hand icon when it’s positioned over any button, but you have to give users a reason to wander up there with their mouses. So change the label’s formatting (make it a contrasting color, or put a border around it so that it looks like a button) to help users out. (Learn more about buttons on Buttons.)

Note

If your pointer doesn’t change to a hand when you hover over a button, go to Layout mode and double-click the button. Select the “Change hand to cursor over button” option and click OK.

Applying a Script Trigger

The script was nice, and the button improved things, but you’re still not done learning how useful and intuitive scripts can be. Since the point of going to the List layout is to quickly scan a list so you can find a particular Lease Agreement record, it’d be even more convenient if the list just knew to sort itself every time you switch to the layout. And that kind of thing is what Script Triggers are for.

You’ve just seen that you can run a script from the Scripts menu or from a button. But you can also tell a script to run when you do other things, like enter data in a field or go to a specific layout. Here’s how to make the “Sort by Last Name” script run every time you go to the Lease Agreement List layout:

  1. On the Lease Agreement List layout, switch to Layout mode, and then choose Layouts→Layout Setup.

    You’ll learn about this dialog box’s other options in Chapter 7. For now, you’re interested in the Script Triggers tab.

  2. Click the Script Triggers tab.

    The Script Triggers tab appears (Figure 4-21).

  3. Select the OnLayoutEnter option in the Event list. Windows users may need to scroll the list to see that option.

    Selecting an Event tells FileMaker when to run a script. Once you make a selection, the Specify Script window appears.

  4. Click the “Sort by Last Name” script to select it, and then click OK.

    The Script Trigger tab is now set up. Notice that you’re letting the script run only while you’re viewing the layout in Browse mode.

  5. Click OK.

    The script will run each time you switch to the Lease Agreement List layout.

To test the script trigger, switch to Browse mode and unsort your list, and then switch to the Lease Agreement layout. Then switch back to the Lease Agreement List layout. The script runs and sorts your list for you.

Script triggers give you a more automated way to run a script than by using the menu or creating a button. This script trigger will run a script called “Sort by Last Name” every time the layout is visited in Browse mode. When you apply a script trigger with the Layout Setup dialog box, it affects only the layout you apply it to. Script triggers are enormously powerful, but they can be tricky. Learn more about them on page 461.

Figure 4-21. Script triggers give you a more automated way to run a script than by using the menu or creating a button. This script trigger will run a script called “Sort by Last Name” every time the layout is visited in Browse mode. When you apply a script trigger with the Layout Setup dialog box, it affects only the layout you apply it to. Script triggers are enormously powerful, but they can be tricky. Learn more about them on page 461.

Creating a Dynamic Report with the Assistant

Your database is getting pretty smart now. It can do math and perform some housekeeping duties on its own. But one of the main purposes of storing data is to be able to analyze it. You’re storing information about lease agreements, but so far, there’s no way to take a look at any trends that might show up. For instance, you offer leases of 12, 24, or 36 months. If you sort and count your leases by duration, you may be able to spot interesting trends, like people who are willing to sign longer leases are also willing to lease your more expensive properties, for example. Or, maybe the opposite is true and they’re less willing. If so, you’ll want to come up with some incentives to get the high rollers to sign longer leases. But you’ll never know until you create a report.

You’ve already seen how the Layout/Report Assistant makes it a breeze to create a new layout (Creating a New Layout). Many of the assistant’s panels are already familiar to you. But the assistant can also build some special layout parts and create fields for you that summarize your data. Better still, the report you’ll build is dynamic. If you add a new record to the list while you’re viewing the onscreen report, the record is automatically sorted into place, and your summary data updates immediately. Here’s how to create a dynamic report:

  1. In Layout mode, choose Layouts→New Layout/Report.

    The New Layout/Report assistant starts up, and the Create a Layout/Report panel appears. Some panels in the assistant show sample report icons as you make selections in the assistant’s windows. Watch the icon for clues about the type of report you’re designing.

  2. In the “Show records from” pop-up menu, choose the Lease Agreement table if it isn’t selected. In the Layout Name box, type Lease Agreement Report and then choose Report from the “Select a layout type” list. If they’re not selected, choose “Include subtotals” and “Include grand totals.” Finally, click Next.

    After you click Next, the Specify Fields panel appears.

  3. Move the First Name, Last Name, Rental Fee, and Lease Duration fields to the “Fields shown on layout/report” box, and then click Next.

    This should be familiar territory by now. Remember that you can use the arrow to the left of each field to move them up and down in the list on the right. After you click Next, the “Organize Records by Category” panel appears.

  4. Move the Lease Duration field into the Report categories list, and then click Next.

    The sample report changes as you move fields into the Report categories list. After you click Next, the Sort Records panel appears.

  5. Move the Last Name and First Name fields into the Sort order list, and then click Next.

    Lease Duration is already in the list, because that’s how the report will categorize the list. But you want records with the same Lease Duration value to be sorted alphabetically by Last Name and then by First Name. After you click Next, the Specify Subtotals panel appears (Figure 4-22).

  6. From the Subtotal Placement pop-up menu, choose “Above record group” and then click on Specify under “Summary field.”

    The Specify Field window appears.

  7. Click Add.

    The “Options for Summary Field” window appears (Figure 4-23). You’ll create a special field that counts the records in each category. The field will appear above the record group it summarizes because of the choice you made in the previous step.

    Choices you make in this window determine how many subtotals your report will have. You can also place the subtotal above or below the records it’s summarizing. You need to have at least one Subtotal line item in the Subtotals box at the bottom for the summary to work, though, so make sure you click Add Subtotal after you create or choose a summary field (you’ll learn about them later in this tutorial).

    Figure 4-22. Choices you make in this window determine how many subtotals your report will have. You can also place the subtotal above or below the records it’s summarizing. You need to have at least one Subtotal line item in the Subtotals box at the bottom for the summary to work, though, so make sure you click Add Subtotal after you create or choose a summary field (you’ll learn about them later in this tutorial).

  8. In the Summary Field Name box, type Count Leases, and then select “Count of.” Now choose Lease Duration from the “Choose field to summarize” list, and then click OK until you’re back on the Specify Subtotals panel. Now click Add Subtotal. Click Next.

    FileMaker creates a new Count Leases field (Summary type) and at the end of this process, you’ll see a Subsummary part (you’ll learn how to create them manually on Fraction of Total of) based on the options you just chose. The summary field counts each record in the sorted category group you selected and displays a count of records for each group in its own Subsummary part. After you click Next, the Specify Grand Totals window appears.

  9. Click Specify.

    The Specify Field window appears. The Count Leases field you just created appears in the list.

  10. Click the Count Leases field to select it, and then click OK. Leave the “Grand total placement” pop-up menu set to “End of report,” and then click Add Grand Total. Finally, click Next.

    Make sure the Lease Agreement::Count Leases field appears in the Grand Totals list at the bottom of the window before you click Next. Summary fields are smart enough to display different data depending on the layout part they’re placed in. So you can use the same summary field in a Subsummary part and a Grand Total part, and it will display appropriate data in each part. This version of the Count Leases field will appear at the end of the report and will give you a grand total count of all the records you’re viewing. After you click Next, the Select a Theme window appears.

    You’d see a nearly identical window if you used the Manage Database window to create a summary field. Summary fields do just what their name implies: They summarize groups of data. You can apply one of several mathematical operations to the fields, including Totals, Averages, and Counts. You can count any field, but you can’t apply math to text fields. So if you’re trying to select a field, but it’s grayed out, check the operation you’re trying to perform. It may not be the right option for the field you want to summarize (or the field’s definition may be set to the wrong type).

    Figure 4-23. You’d see a nearly identical window if you used the Manage Database window to create a summary field. Summary fields do just what their name implies: They summarize groups of data. You can apply one of several mathematical operations to the fields, including Totals, Averages, and Counts. You can count any field, but you can’t apply math to text fields. So if you’re trying to select a field, but it’s grayed out, check the operation you’re trying to perform. It may not be the right option for the field you want to summarize (or the field’s definition may be set to the wrong type).

  11. If it isn’t already selected, select Cool Gray from the Layout Themes list, and then click Next.

    The Header and Footer Information window appears.

  12. From the Header’s “Top right” pop-up menu, choose Layout Name. From the Footer’s “Bottom right” pop-up menu, choose Current Date. Then click Next.

    The “Create a Script” for this Report window appears.

  13. Click the “Create a script” option, and Lease Agreement Report appears in the “Script name” box. Select the “Run script automatically” option, and then click Next.

    FileMaker writes a script for you that goes to the report layout and sorts your records properly (the data in Subsummary parts doesn’t show up unless the records are sorted by the field specified in their definition). The “Run script automatically” option attaches an OnLayoutEnter script trigger (explained on OnRecordRevert) to the new report layout so that you don’t have to remember to run the script every time you switch to the report layout. After you click Next, the final window tells you that you’re finished. But actually, you aren’t quite done.

  14. Click Finish.

    Your sorted report appears (Figure 4-24).

Graphically speaking, the report as created is nothing to write home about, so you’ll need to put your layout design skills to use on this layout. However, the Layout Assistant gave you some nice tools to help you start analyzing your data. You can see that your current group of tenants trend toward longer leases. And it looks like your higher-priced properties are leased out longer, too. Except for that one guy with the $1,850 lease who’s only signing on for 12 months. (While you’re learning FileMaker, have your assistant find out what it’ll take to get that guy signed up for another couple of years.)

Figure 4-24. Graphically speaking, the report as created is nothing to write home about, so you’ll need to put your layout design skills to use on this layout. However, the Layout Assistant gave you some nice tools to help you start analyzing your data. You can see that your current group of tenants trend toward longer leases. And it looks like your higher-priced properties are leased out longer, too. Except for that one guy with the $1,850 lease who’s only signing on for 12 months. (While you’re learning FileMaker, have your assistant find out what it’ll take to get that guy signed up for another couple of years.)

There were a lot of steps and a lot of selections to make. But the hard work’s been done for you. In Chapter 15, you’ll learn how to create a sorted subsummary report completely by hand. Once you’ve done that, you’ll appreciate how much easier it was to make selections in an assistant. However, you need to format your fields and generally beautify the layout so that it matches the rest of your database.

When you’ve got the layout looking fine, switch to Browse mode so you can see how the report updates dynamically. For example, click on the Toni Bernette record to select it, and then click the New Record button in your Status toolbar. Finally enter the following data in the new record:

  • First Name: Janette

  • Last Name: Desautel

  • Rental Fee: 1295

  • Lease Duration: 12

Your new record is created right below your active record. When you commit the new record, it’s sorted into the 12-month group, and the Count Leases field is updated immediately in both the Subsummary and Grand Total parts. The same thing is true if you edit data in the field on which the sort is based (remember that this report is always sorted by the Lease Duration field, and then by Last Name and First Name). Change some data in a Lease Duration field, and then commit the record. It will sort into the proper group. If you add data that’s not in an existing group (say you type 48 in the Lease Duration field), a new group will be formed with a Count of “1.”

Subsummary layouts show their summary data only when your records are sorted by the field that’s attached to the Subsummary part (explained on Summary Fields and Subsummary Parts). So if you unsort your records, or do a sort that doesn’t include the Subsummary part’s field (say you sorted by Last Name and First Name only), the groups and subtotals don’t show on the layout. To get them back, sort the records again, and this time, make sure you include the proper field (in this case, it’s Lease Duration) in the sort order.

Note

Sorted subsummary reports are great for looking at trends in your data, but you probably wouldn’t usually use them as the primary way to add new records or edit existing ones. But on those occasions when it’s suitable (say the sorted report makes it clear that some data wasn’t entered correctly), it’s convenient that you don’t have to leave the report layout to make corrections.

Creating a Trailing Group Report

The dynamic report you just created is perfect for printing out when you want to analyze your data. Even if they don’t want to print the report, your users can just switch to the Lease Agreement Report layout and get an up-to-the-minute categorized report on your properties. But what if they need a quick analysis of the data using a category that you haven’t set up for them? Do they have to wait until you have time to add a new report? Or maybe you need a last-minute, one-time report that won’t be printed (you don’t even have time—there’s a meeting in 5 minutes and you’ve been told to get the data), so you don’t want to bother creating a new layout and spending time making it match the rest of your database. Either way, FileMaker’s Trailing Group reporting feature is the solution.

A Trailing Group report requires a Table view on a layout that shows records from the table you want to report on. It accomplishes the same thing as a dynamic subsummary (sorts records automatically by the category you choose, with an optional summary field), but it’s temporary and doesn’t actually add a Subsummary part to the layout. To start this tutorial, switch to the Lease Agreement layout, and then click the Table View button.

  1. Click the triangle to the right of the Lease Duration field’s column heading. (The triangle appears when you place your mouse over the heading.) Choose “Add Trailing Group by Lease Duration” (Figure 4-25).

    The Lease Duration column’s pop-up menu appears when you click the triangle, and then when you choose the Trailing Group command, a gray summary row appears on the table and the records are automatically sorted by Lease Duration.

    The contextual menu for column heads in table view lets you create quick Trailing Group reports, but it also lets you change the field’s type or options, delete the field, add new summary fields, or sort the records you’re viewing. You can even change the way the Table view behaves by adding or deleting fields from the layout or changing a column’s width. In this case, the “Trailing Group by Lease Duration” field would disappear from Table View. But if you’ve created a summary field for the Trailing Group, the field isn’t deleted from the table.

    Figure 4-25. The contextual menu for column heads in table view lets you create quick Trailing Group reports, but it also lets you change the field’s type or options, delete the field, add new summary fields, or sort the records you’re viewing. You can even change the way the Table view behaves by adding or deleting fields from the layout or changing a column’s width. In this case, the “Trailing Group by Lease Duration” field would disappear from Table View. But if you’ve created a summary field for the Trailing Group, the field isn’t deleted from the table.

  2. From the Lease Duration column’s pop-up menu, choose Trailing Subtotals→Count (Count Leases).

    The count appears in the summary row below each Trailing Group.

FileMaker sorts and groups the records for you, so you can get the information you need from the ad hoc report without fuss. As with the dynamic report in the previous section, the records must be sorted by the Trailing Group you chose for the summaries to show up. So if you switch back to Form View and sort your records another way, you won’t see the Trailing Group Report when you return to Table View. Select Sort Ascending from the Lease Duration column pop-up menu to sort the records, and the trailing groups reappear.

If you switch to Layout mode, you’ll see that the layout doesn’t have a new part added to it. Nor does the Count field actually appear on the layout. That’s why this type of report is temporary. It’s meant to let you get a quick, bird’s-eye view of your data and then get right back into your other tasks (or to the meeting on time and with the data you were told to have at your fingertips).

You can get creative with your ad hoc reports by adding multiple trailing groups at the same time. In a small database like the one you’re working on, you may find that nearly every record gets its own group. But in a database with lots of records, you can use this technique to get fine-grained reports very quickly. Remove a Trailing Group by clicking the column head menu associated with that group. Choose “Remove Trailing Group by Lease Duration,” and the group no longer shows up.

Creating Charts in Table View

Text-based reports, whether they’re meant for print or screen, are not the be-all and end-all of data analysis. Most people find information easier to read in the form of a chart instead of a mass of numbers. So FileMaker gives you a quick method for creating charts from Table view. To see it in action, on the Lease Agreement layout, start in Table view.

  1. From the Lease Value column head’s pop-up menu, choose Chart→“Chart by Lease Value.”

    The Chart Setup dialog box appears showing a column chart of the data in the Lease Value column.

  2. Click “Save as Layout,” and then accept the name suggested by the “Save as Layout” dialog box. Click OK.

    FileMaker creates a new layout showing a chart of all your lease agreement values and switches to the new layout so you can see the chart.

    FileMaker creates a column chart using the context of Lease Agreement and the Lease Value field. Currently, the chart displays all the records from the table, so if you had hundreds of records, this chart wouldn’t be very useful. But there’s a quick trick for making it show you just some of your records. As you might expect, the solution is found in Layout Mode. All you need to do is put a field on the layout so you can do a search to limit the number of records in the found set.

  3. Switch to Layout mode, click the Field tool to select it, and then drag to create a field down in the gray area to the right of the chart.

    The chart takes up the whole visible part of your layout. Anything in the gray area doesn’t appear when you’re in Browse mode. When you release the mouse, the Specify Field window appears.

  4. From the Specify Field window, choose Lease Duration, click OK, and then switch to Browse Mode.

    The chart looks just like it did before, but now you have a way to change the records you’re viewing.

    In the Quick Find field (Figure 4-26), type 36 and then press Enter.

    FileMaker searches the only field on the layout (Lease Duration) and finds the records with 36-month leases. The chart changes dynamically with the found set.

The Quick Find field always lurks on the right side of your Status toolbar. If you can’t see it, you may need to make your window wider. Or check to see if your toolbar has been customized (page 44) to remove it.

Figure 4-26. The Quick Find field always lurks on the right side of your Status toolbar. If you can’t see it, you may need to make your window wider. Or check to see if your toolbar has been customized (page 44) to remove it.

Changing the Default Account

You know that FileMaker creates a lot of elements for you when you first create a new database. One thing you may not know is that it even created a login account for you and it assigned that account name to be entered automatically each time you open the file. You can see that setting, and then turn it off, by choosing File→File Options (Figure 4-27).

It’s not industrial-strength security, but FileMaker created an Admin account for you and has been secretly using it every time you open the Lease Agreement database. Really it’s not that much of a secret, because you have to figure that at least half of FileMaker’s millions of users know about this account and the hackers amongst them would try the “Admin” account name (it doesn’t even have a password), if they wanted to break into your database. One of the first things you can do to make any database more secure is to turn off the “Log in using” option of this window.

Figure 4-27. It’s not industrial-strength security, but FileMaker created an Admin account for you and has been secretly using it every time you open the Lease Agreement database. Really it’s not that much of a secret, because you have to figure that at least half of FileMaker’s millions of users know about this account and the hackers amongst them would try the “Admin” account name (it doesn’t even have a password), if they wanted to break into your database. One of the first things you can do to make any database more secure is to turn off the “Log in using” option of this window.

This default account is created in every database FileMaker creates, so to protect your new database from prying eyes, turn off the option to log in using the automatic account, and then change the default account’s name and password. Here’s how to change the default account:

  1. Choose File→Manage→Security.

    The Manage Security window appears. It shows two built-in accounts, but only the Admin account is active, as shown by the checkmark in the Active column.

  2. Click the Admin account, and then click Edit.

    The Edit Account window appears (Figure 4-28).

  3. Type your first name’s initial and your last name in the account box.

    This first initial/last name scheme is standard for creating account names. But you can use whatever scheme you like, so long as you can remember it.

  4. Type a password into the password box.

    You know the drill by now: Create a password that has a mix of letters and numbers and, for extra strength, even a symbol or two. Just make sure you remember exactly what you typed, because you’re about to have to retype it.

    Account Names appear in the Account Name box just as you type them, but characters in the password box are obscured by a password font. That keeps your typing safe from someone who may be looking at your computer screen over your shoulder, but it does mean that you have to be very careful as you type. Because once you add a password to an account, even you can never see what was entered in the box. (Go to Chapter 19 to read all about security.)

    Figure 4-28. Account Names appear in the Account Name box just as you type them, but characters in the password box are obscured by a password font. That keeps your typing safe from someone who may be looking at your computer screen over your shoulder, but it does mean that you have to be very careful as you type. Because once you add a password to an account, even you can never see what was entered in the box. (Go to Chapter 19 to read all about security.)

    Note

    Case doesn’t matter for account names, but it does matter for passwords.

  5. Click OK until you’ve dismissed all the windows.

    Before you’re allowed to click OK for the last time, you’ll have to re-enter the new account and password you just created. And if you get it wrong, you can’t close the Manage Security window. If you can’t quite get the combination right, go back into the account you’ve just edited, and then retype your password. Once you get the combo correct, the window closes, and you’re back on your layout.

You know that you’ve got the account name and password right, because you were able to close the window. But you should test the new login account by closing the file (if you still have multiple windows open, make sure you close them all), and then opening the file again. This time, you’ll be asked to enter the new account name and password.

FileMaker guesses that the account name used for each file it opens is the same as the name you entered when you first installed FileMaker. But you can change this name in the program’s Preferences window. Choose Edit→Preferences (Windows) or FileMaker Pro→Preferences (Mac) to get the window shown in Figure 4-29.

Type the account name you want to appear in the User Name box, and FileMaker will use that as the automatic account name from now on. On the Mac these options are a little different. You can choose from the System’s Admin Account name or choose Other, and then type a custom name. Either way, it saves you a few keystrokes every time you open the database.

Figure 4-29. Type the account name you want to appear in the User Name box, and FileMaker will use that as the automatic account name from now on. On the Mac these options are a little different. You can choose from the System’s Admin Account name or choose Other, and then type a custom name. Either way, it saves you a few keystrokes every time you open the database.

Summing Up

Over the last two chapters, you’ve created a database from scratch. In that process, you’ve learned the basics of FileMaker’s major features. You know how to create tables and fields, and you can create relationships between them. You can create layouts and layout objects, and you can change the format of the most common layout objects. You’ve even tried your hand at writing a script, creating some data analysis reports, and adding security to your file. Before you delve deeper into these topics, it’s time to switch gears and learn more about planning a database. In the next part, you’ll learn how to think like a database developer.

Get FileMaker Pro 12: The Missing Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.