Now that you know how the relationship graph for this interface works, it's time to start the actual construction. Start with the new global field. It's an easy place to begin, and you need the global field for all the next steps.
8.4.1. Creating a Global Field
Since the context of your interface is the Expenses table, that's where you add the field. Open the Customers database and proceed as follows:
The new field appears in the field list. (See the box on Section 8.4.2 to see why you've put the word global in this field's name.)
FileMaker shows you the field storage options. You first saw these on Section 18.104.22.168.
|UP TO SPEED
The Importance of Being Organized
You can easily get so carried away with new table occurrences and new relationships that you make an illegible, illogical graph. This window illustrates a common mistake that can create havoc in a database: no consistent naming system to the table occurrences, and no visual grouping to show how the relationships operate. The database may work just fine, but if it ever does need troubleshooting or updating, any developer (even if that's you) would have a hard time figuring out what's going on.
First of all, the relationships graph is about as easy to navigate as a bowl of spaghetti. Even worse, since every table occurrence relates to every other, the table occurrence menus would be useless. And the table occurrence names, though marginally descriptive, don't help you find the TO you need. If you do a little planning before you go throwing your new TO creation skills around, you can steer clear of these rocky shoals.
FileMaker creates the field, and it now appears in the Field list.
You use this new global field as a key field in the next section.
8.4.2. Creating a New Table Occurrence Group
Since the Assign Line Items layout has to be tied to an occurrence of the Expenses table, you need a new one of those. You also want to be able to show the line item attached to that expense, so you need a new occurrence for that table, too. Finally, you'll use the global field you just defined to hook up to another new table occurrence that will hold an invoice ID. With it, you'll access the following information:
A quick glance back to Figure 8-16 reveals all the new table occurrences you need to create.
|POWER USERS' CLINIC
One (Global) Field to Rule Them All
You know that fields set with global storage have the same value across every record in the table that holds them. Since they're so different from other fields, many database designers give them special names so that the fields stand out in a list. That way, it's less likely that they'll get used inappropriatelysay in a context where they don't have much meaning, or plopped down on a layout where someone can edit the values when they should stay static.
You can just preface the names of all your global fields with the word "global," like you did in the exercise on Section 8.4. But in the real world, people commonly just use a lowercase "g" as a prefix. With this scheme, you can name your global field "gInvoice ID" and save a few keystrokes. Plus, you look like a guru.
The steps are simple if you take it one table at a time:
You need to see your existing relationship graph.
It's always good to give TOs a descriptive name. When you're data tunneling, it's even more important. When you create relationships, the names help you keep the various tables and groups straight.
The Expense Assign bit says that this new table occurrence was created especially for the process of assigning expenses. "Global" says you're planning to use the new TO in global relationships and the "Invoice Job" part helps you remember which other TOs you plan to hook up to it.
When you click OK, FileMaker adds the new table occurrence to the middle of the graph. Move it out of the way to get ready for the next steps.
You now have the three table occurrences on your graph. Since the names are so long, you might have to stretch your new TOs so you can tell them apart. See Section 22.214.171.124 for tips on resizing TOs.
You drag and drop; FileMaker draws the lines.
The necessary relationships are now in place. Your new graph looks something like Figure 8-17.
Now that the occurrences and relationships are in place, you can move them to a new spot on the graph where they have some breathing room. FileMaker gives you nearly infinite space down and to the right, so pick a spot that you like.
Tip: Remember, if you like things lined up nice and neat, you have Arrange tools in the Relationships graph that let you resize and align a selected group of TOs together (Section 7.3.3).
126.96.36.199. Adding new table occurrences to a group
You're partway through creating the new table occurrence group that supports the Assign Line Items layout. You've created some of your new TOs and arranged them on the graph. It's time to take stock. The purpose of that layout is to select an invoice based on a global field, and then show the job name and a list of line items. But this group of table occurrences simply defines the relationships between Invoices, Line Items, and Jobs. You already know the layout will be tied to an occurrence of the Expenses table, so you need to make sure that from that layout, you have a relationship that pinpoints the invoice you're interested in. In other words, you need a relationship from the Expenses table to the Invoices table, based on the global Invoice ID field. Figure 8-18 shows this connection.
You need one last table occurrence to complete your group. Once you pick a Line Item ID from the pop-up menu, the Expenses::Line Item ID field gains a value. It would be nice to show you information about the line item you just selected, to reinforce the choice. Although your main group of table occurrences (the one you created in the last chapter) knows how expenses relate to line items, this new table group doesn't. Fortunately, you can add new TOs to an existing group the same way you create any other TO:
Fastest way: drag the Line Item ID field in one TO to the Line Item ID field in the other. (It doesn't matter which direction you dragjust do it.) You can see the result in Figure 8-19.
8.4.3. Building a Global Field Layout
Now that you've finished the new table occurrence group and hooked up its relationships, you're ready to create the Assign Line Item layout you first saw way back in Figure 8-14. With the graph defined, this process is surprisingly simple. First, create a new, blank layout that's based on your Expenses Assign: Expenses table occurrence, and call it Attach Line Items. See Section 5.1.6 if you need a refresher on creating layouts.
Now add these fields to the layout:
Note: Remember, this layout is associated with the Expense Assign: Expenses table occurrence. When you choose fields for the list above, in the Specify Field dialog box, from the top of the pop-up menu, select Current Table.
188.8.131.52. Using a global pop-up field to change the display in a value list
By definition, a global field has the same value in all its tables' records.. Therefore, you can change the data in a global field without affecting any of your "real" data for those records. When your global field is used in a relationship, and you change the value in the global field, the relationship changes and so do the records that are related to your current record
You use this technique to create a value list that's based on the global relationship between your new Expenses TO and the new Invoice TO. When that global relationship changes, FileMaker tunnels in the new table occurrence group from Expenses to Invoices to Line Items to get the line item that's related through the value in your global. Refer to your relationships graph to refresh your memory on how those TOs hook up, if this technique doesn't make sense without a visual aid.
This advanced technique is just like searching for records, without all the hassle of going to Find mode and actually performing a search.
To make the Line Item ID field into a pop-up menu, double-click it to see the Field/Control Setup dialog box:
The "Display values from" pop-up menu appears, with displayed.
You need a new value list based on the values in the Line Item ID field from the Expense Assign: Global Invoice Line Items table occurrence you made a few pages back.
Even though you're about to assign your new value list to a field, you want the name to be descriptive, so you can remember your thought process later on.
This field's data shows up in your new value list.
The idea is to keep your users from assigning an expense to a line item from the wrong invoice, or from assigning an invalid Line Item ID. This option tells the value list to display only line items that are related to the invoice that matches the value in your Global Invoice ID field. Since that field's on the Expense Assign: Expenses TO, then that's where the value list relationship is "starting from." To understand how this works, remember the concept of context (Section 8.3.2).
Since a Line Item ID by itself isn't very meaningful, you show the description too.
You're back on your Attach Line Items layout and your Line Item field is formatted with your new value list.
Switch to Browse mode and try out your new layout. If you type I00001 into the Global Invoice ID field, you should be able to pick a line item from the pop-up menu. When you do, the Expense Assign: Expense Line Item::Description field should show the selected line item's description. Figure 8-19 shows how you might format this new layout.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help