< Day Day Up > |
It's time to dive in. This next section walks you through working in some typical FileMaker Pro situations and addresses many of the common tasks you need to be able to perform. Opening a DatabaseThe first step, obviously, is opening a database. FileMaker Pro databases can live in various places. They can sit on your own computer, just as any other document might, they can be hosted by another computer, or they can be served by FileMaker Server. Local FilesOpening a local file is a simple matter of either double-clicking its icon in either your Windows environment or in the Mac OS X Finder. You may also use FileMaker Pro's File, Open command. Remote FilesWorking with remote files requires connecting to a server ”which could be a database hosted on FileMaker Server (the software that allows you to host a FileMaker database for use across a LAN or WAN by up to 250 users) ”or simply to a file set to multi- user on another person's workstation. To a client computer, there's no distinction. FileMaker Pro treats both cases as a remote connection. To open a remote database, click the Remote button in the open dialog, or choose Open Re m ote from the F ile menu. As shown in Figure 2.10, you may choose from those hosts available to you locally (those on your network, within your domain in corporate environments, or accessible on the Internet), or you can opt to access files via a secure LDAP server. Figure 2.10. Use the Open Remote File dialog to open a database on a LAN, in a corporate domain, or (with a proper IP address) across the Internet. NOTE Note that some solutions contain multiple files. A developer can opt to hide some files from the remote hosts dialog and therefore present only a "launch" or menu file as needed. TIP If you work in a large organization, your server list can become quite cluttered. Use the Favorite Hosts menu option for those you most frequently use. It enables you to add all the files from a server or just those you want. CAUTION Opening a FileMaker database across an Internet connection is entirely possible, but you should be aware that connection speeds will vary, depending on your network, hardware, and the specific FileMaker Pro database. Don't plan on making this a deployment strategy until you've properly tested both your solution itself and your users' connections to it.
Creating a New Database from a TemplateBecause this could be your first foray into FileMaker Pro, you may not yet have a database to tinker with. FileMaker Pro's templates are a great place to start. Ordinarily, you'd likely be working with a database either you or some other developer created; however, for our purposes, let's walk through how to open and use one of FileMaker Pro's existing templates. Navigate to F ile, New Database. Then select Create a New File Using a Template. The New Database dialog box shown in Figure 2.11 appears. Figure 2.11. Dozens of templates ship with FileMaker Pro, ranging from an invoicing system to a tool to organize your personal DVD collection.![]() You are encouraged to explore FileMaker's templates. They are fairly simple databases that show by example how FileMaker Pro can be used, and can also give you a jump-start on creating your own database solutions. Creating and Deleting RecordsCreating and deleting records in FileMaker Pro is simple. Under the Records menu, choose New Record, Delete Record, or Duplicate Record. Notice also there's a Delete All Records option. For now, let's explore how to take care of simple data entry. CAUTION Keep in mind, even though there's an Undo command in the Edit menu, it doesn't work at the record level. After a record is committed, it is a part of your database. After you delete a record, it's gone forever. If you are in the midst of entering data in a record and want to undo the entry, use the Revert Record command under the Records menu. A record is saved ”or committed ”automatically when you click outside a field for the first time, change modes, change layouts, or press the Enter key. FileMaker Pro uses the term commit to indicate when a record is posted, or saved, to your database.
NOTE You never need to save a FileMaker Pro database. As users commit records, those records are automatically stored in the database file. If you want to save a copy of your database or create a duplicate for backup purposes, the Save As option under the File menu will serve.
Working with FieldsIf you're used to other productivity applications or have ever filled out a form on the Web, you should find data entry quite familiar in FileMaker Pro. Fields generally look like embossed or bordered areas with labels off to one side or the other. Keep in mind: developers control the look and feel of their systems, so it's entirely possible that someone could build a database with no labels, fields that are the same color as their background, and white text on a white background. Thankfully, when a field is being actively edited, the other fields on a given layout are highlighted by a dotted border, indicating you're in the midst of editing a record (see Figure 2.12). That at least will help you see which fields allow entry and which don't. Figure 2.12. FileMaker's field borders indicate the edit state of a record.![]() Editing fields is as easy as clicking into them, typing some text, and clicking out again. NOTE Moving from field to field can be managed on your keyboard if you simply press the Tab key. Some solutions may also support the Return and Enter keys. You'll work with a few different formats of fields in FileMaker Pro:
CAUTION We lied. Shift-clicking allows a user to select multiple values in a radio button set. Unless field validation is set up to check for it, selecting multiple values in a pop-up menu or in radio button sets is generally a bad idea. Again, you will end up with unpredictable results because you're making an exception to a formatting choice meant to allow for only one value in a given field. Figure 2.13 contains examples of these field formats. Figure 2.13. Using field formatting can make data entry more intuitive. Notice that all four types are present, though in a real database only one field would be active at a time.![]() Data in Formatted FieldsYou might find it helpful to understand how multiple-value data is stored in fields: remember that check boxes, radio buttons , pop-up lists, and pop-up menus are all nothing more than data-entry assistants. The actual data stored is a collection of values delimited by line returns. This means that you can accomplish the same result, from a data perspective, by simply entering a return-delimited list of values into your fields. This is an important thing to remember when performing find requests , which we'll cover later in this chapter. Modifying Value ListsOften, you might need to add new values to a value list ”the list that is used to create pop-up lists and menus, check boxes, and radio button items. Developers have the option of including an Edit option at the bottoms of their menu fields. Selecting Edit then brings up a dialog that you may change or add to a list as needed (see Figure 2.14). Figure 2.14. Editing value lists is a simple way to fine-tune a database to your specific needs without having to dig into programming. To edit the items in a value list, simply type text into the Edit Value List dialog, followed by a carriage return. A hyphen adds a separator to the list. NOTE Keep in mind that just because you replaced an old menu item with a new category ”for example, "autos" becomes " cars " ”it won't change the actual values stored in your database's records. Remember that field formatting is nothing more than a data entry assistant. By changing the assistant menu, you have not changed any data in your database. Using the "Other" Value in Value ListsRadio button sets and check boxes work a bit differently. Developers do not have the choice to add an edit function to these formats; rather, they may include an Other option. This allows a user to enter virtually any custom text they want, from a single value to Grandmother's recipe for apple pie. Regardless of the value, the check box or radio button option that would be visibly displayed is Other; however, the data stored and included in the field's index includes whatever your other data is. In contrast to adding values to a value list and changing the options available on all records, the Other function simply enables you to enter custom text into a specific record's field. As you can guess, developers often disable these features. Just remember that all you're doing is using field formatting to help in entering consistent data. Field TypesIn addition to enabling you to control how data gets entered into a field, FileMaker Pro databases use specified kinds of fields for different types of information. Field formatting is independent from field types. For example, it's entirely possible to format a calculation field as a check box. Although you as a user may expect to be able to click on a check box, FileMaker Pro will then prompt you and explain that calculation fields are not modifiable. It's incumbent on the developer to sensibly identify for a given system's users which fields expect what sort of data. Often field labels make this clear. For example, you can often expect a Price field to be a number, and an Invoice Date field will no doubt be a date type. The following list describes the field types available in FileMaker Pro:
Global StorageFields in your database generally pertain to a specific, individual record. The baseball team field for your San Francisco record holds "The Giants," whereas for Chicago it's "The Cubs." In some cases, however, a developer opts to define a field as global. The value in that field, then, is constant throughout the database. Some common examples might be fiscal year start and end dates, your company name , report headers, or a fixed commission rate. As a user, you might not always be able to tell which fields in your database have been defined to store global values and those that are record specific. An important thing to keep in mind about global fields is that their behavior varies depending on how you're hosting a database. If you're using a database on your own local machine, with sharing set to single user, all global data is preserved from session to session. In other words, the next time you open the database, your global details remain from the last time you worked with the system. If you're working with a database hosted on a server, all global information is session specific. It may contain default values, but if you change some data in a global field, other users of the system do not see that change, nor is it preserved for the next time you use the database. There are often cases where you need those values to "stick" across sessions and for all people. You've got two options: either take the files down from the server and open them locally on a single user machine, or, as a developer, write a script that sets your global information whenever the system is opened. Data ValidationData integrity is one of the primary concerns of any database developer or of the team using a given system. If duplicate records appear, or misspellings and typos plague your database, or worse yet the wrong data is entered into the wrong fields, your system will soon become essentially unreliable. For example, if you run a monthly income report, but in a few of your transaction records someone has entered a date where a transaction amount belongs, your monthly totals will be incorrect. FileMaker Pro ”or any application, for that matter ”cannot read users' minds and fully safeguard against bad data, but developers do have a wide range of tools for validating information as it is entered. If your organization can come up with a business rule for validation, a developer can apply that rule to a given field. Consider the following examples:
Understanding that these rules are in place will help you understand the underpinnings of your database application. When a validation check occurs, the system may prompt you with an appropriate message (see Figure 2.15). Figure 2.15. This is an example of a default validation message. If you choose Revert Record, whatever data you've entered into the field reverts to its prior state before you started editing. In addition to the default dialog in Figure 2.15, a developer can create his or her own custom text: for example, "This transaction amount must be a positive value greater than 99 cents ." If you choose Yes rather than Revert Record, your data is accepted as is and overrides the validation requirement. In some cases you may not have the option of posting an override.
Manipulating Records in PortalsBy now you've probably read the word "relational" a few hundred times already in this book. Get used to it. One of FileMaker Pro's core strengths is how it allows you to view and work with related information from a different but connected contextual set of records from other tables. For example, in a table of car manufacturers you would likely have records for "Audi, BMW, Chrysler, Mazda," and so on. Imagine that you also have a table for car models: "Mazda 6, MX-5 Miata, Protege5," and so on. FileMaker Pro allows you to view a car manufacturer, and on the same layout also see the models specific to that manufacturer (see Figure 2.16). Figure 2.16. You are in the manufacturer's table, looking at a record for Mazda, while also being able to see records from the car models table.![]() FileMaker Pro allows developers to build portals in which related information can be displayed. Understanding the Mechanics of a PortalA portal is simply a view into another table and includes specific related records. Developers determine the rules by which records appear in portals and at times these can become dynamic or even display other records in the same table you're currently viewing. Most portals have a scroll bar on the right. They feel a bit like list views, and act much the same way. To browse through your related records, simply scroll up and down through the list. Data entry works the same way it does in other areas of FileMaker: Simply click into a field and enter whatever data is appropriate. At times developers include buttons in portals. In this case there appears one identical button in each portal row, and each button acts on the row whose button you clicked. Creating and Deleting Portal RowsOften, a developer disables the behaviors we're about to describe; however, they're handy to understand and to look for when they are available. To create a new portal row ”which then creates a new child record ”scroll to the bottom of a portal and click into the blank row. Child records is a term often used to describe related, hierarchically dependent records ”for example, Company and Employee. Employees would be considered "children" of a Company. NOTE Your developer might have turned off the ability to add or delete portal rows, in which case he or she has likely provided an alternate means of adding related records. If a developer has allowed for such, you can delete a portal row by following these steps:
Portal SortingSorting records is covered later in the chapter. For now, simply note that a developer determines by what means a portal is ordered and that there is no "built-in" portal sorting command in standard FileMaker Pro portals. There are a number of ways a developer can build a dynamically sortable, command-driven portal, but this is not default behavior in FileMaker Pro. Working with a Found SetUp to this point, we've discussed working with a single record and the fields on a given form layout. Refer to the Status Area on the left of your screen again. Notice the Found: and Total: numbers. At every moment of working with an open FileMaker Pro database, you are working with some number of "found" records. Notice the plural. It's also possible that your found set contains only one record, or even none; however, generally speaking, you are likely to have many records in your found set. This is an important point to remember. Even though you may be able to see the contents of only one record's fields (more than likely in Form view), you can still work with either all the records in your table or a subset of such. Think of it as working with a deck of cards. There are 52 total cards in your deck, some of which are in your hand, and one of which is front-most ”visible. Your current record would be akin to that front card and your found set like those cards in your hand. In FileMaker Pro many functions apply to a found set. A good example is sorting: You are organizing only those records in your found set. Many FileMaker Pro databases offer layouts tailored to be viewed either in Form view, where one record encompasses the information on the screen, or in List view, where layouts resemble spreadsheets or tables and display multiple records at once. To grasp visually what we're discussing, see Figure 2.18. Figure 2.18. List views pull data from multiple records. There is a black bar in the left margin that indicates which record is current. The active field and the dotted lines indicate you're currently editing this record.![]() Working with groups of records is important mainly for comprehension and processing of your information. Data entry usually occurs on one individual record at a time, unless you're importing or performing some other function that applies across multiple records. It's in the reporting and analyzing stage that working with multiple records becomes necessary. One of the first ways to work with a group of records is to simply scan the list. Nothing beats the human brain for processing information. Summary fields often lie at the bottom of a list view, as shown previously in Figure 2.18, and can total numeric data based on a current found set. For a quick example of how this might work, imagine a sales database. If you were to find (or search) for all records in January, your summary fields could then total January's sales. If you find again for the year 2003, your totals are annual. The value of the summary field varies depending on your found set. If you perform different find requests, the information on your screen can deliver different results, specific to a given group of records. NOTE Summary fields are quite powerful, but they do require processor time. If you have a large found set of thousands of records, waiting for a summary field to evaluate can take some time. You can press the Esc key to cancel the summary, or simply avoid scrolling or viewing that portion of a layout. Summary fields evaluate only when they are visible on the screen. One last important note about found sets: They can be comprised of records from only one table. You cannot display records from an automobile table and a manufacturer table in the same List view or Table view. Using Find Mode to Perform a Find RequestFor performing search queries in FileMaker Pro, you need to use one of three options to change to Find mode: the tabs at the top of the Status Area, the menu on the bottom left of your application window, or the V iew menu. Developers may also opt to put various Find buttons into their systems. After you're in Find mode, FileMaker Pro waits for you to enter data for your find request. A find request is a single entry in Find mode that encapsulates the criteria by which you want to perform a search. It behaves and looks much like a record. You enter data into fields just as you would in Browse mode, but instead of saving records, these requests serve as a means of matching to your actual data. You can add a new request, create multiple requests, and delete requests. A Find button appears in the Status Area in Find mode. FileMaker Pro enables you to search for any number of criteria throughout your database. Enter whatever fraction of words or data you want on the same layouts you've used in Browse mode, then click the Find button. Any records that match your request are then placed in your found set, replacing the set you had before performing the find. Figure 2.19 shows the full data set in a database of coffee beans (an inventory database). Figure 2.19. Notice this is the full found set. There's no "Found:" record count, just a total.![]() To find a specific set of records, a user would enter Find mode (choose V iew, F ind mode) and type a criteria by which to search. Refer to Figure 2.20. As an example, a user might have typed dark roast in the Roast column and then would click Find in the Status Area. Figure 2.20. FileMaker matches the find criterion "dark roast" against the data in the database after a user clicks Find.![]() Notice in Figure 2.21 that there are eight records in the resultant found set, all of which are dark roasts. Notice also that the summary total changed appropriately. Figure 2.21. Note that the first record in the set is always the active, or current, record after a find is performed.![]() If you perform another find, your found set is replaced by the records matching your new requests (see Figure 2.22). In this example, the search criteria was all coffees with "<3" in the Lbs in Stock field. The result is four coffees that are almost out of stock. (We cover special symbols such as "<" shortly.) Figure 2.22. This figure shows a new found set: These four records are of different roasts, but all are low in inventory with less than 3 lbs. in stock.![]() Search SymbolsTo create the found set seen in Figure 2.22, a < (less than) symbol was used to act as an operator on the find request. Switch to Find mode again and notice the symbol menu on the left (see Figure 2.23). Figure 2.23. Special symbols enable you to search for a wide range of match criteria.![]() The less than, less than or equal, greater than, greater than or equal, and exact match symbols should be fairly obvious. ">3" finds all records with a value 4 and above. "<=100" finds all records with values of 100 or lower (including zero and negative numbers). NOTE You need not use the symbol menu at all: A < and = typed from your keyboard work just as well as inserting the symbol from the pop-up menu in the Status Area. The ellipsis ( ) for ranges is a commonly used search symbol. "1/1/2003 12/31/2003" returns all records for the year of 2003. (If you're lazy, just type two or three periods from your keyboard.) Use * and # for wildcards. The # symbol is for one digit exactly. "5#" finds all whole numbers from 50 to 59. The # alone finds just those numbers 1 “9. "1#1" finds 101, 121, 131, and so on, but not 211 or 1211. The ~ for relaxed search looks intriguing, doesn't it? Some fuzzy logic, perhaps? No such luck. It's used to search for common base characters in two-byte Asian phonetic alphabets. It doesn't do anything for any other languages. Multiple Find RequestsFileMaker Pro also enables you to perform complex searches involving multiple find requests. To find both the dark roast and French roast coffees, a user would simply enter Find mode, type dark roast into the appropriate roast field, and then create a new record/request. Just as you can create new records in Browse mode, you can create and delete requests in Find mode. This process is identical to creating a new record in Browse mode. In the second record, a user would enter French roast in the roast field. A user can flip between requests, using the book icon in the Status Area, and can delete requests as necessary. As soon as the user is satisfied with a series of requests, clicking Find on the left performs the Find and returns the user to Browse mode with a new found set. Multiple Find Requests can also include requests meant to be omitted. Say that a user wanted to find all the coffees from Colombia but exclude the premium coffees. Take a look at Figure 2.24. Notice two requests appear in List view, the second of which is active. Notice too the checked Omit check box in the Status Area. Figure 2.24. Notice which request is applying an Omit request. The prior request is a normal find request with the Omit function turned off. Your result will be all records from Columbia excluding those with "premium" in their names.![]() Constrain and Extend RequestsPerforming find requests is all well and good, and as you can imagine they can become quite complex. For example, you could search for all coffees with "roast" in their names, with 10 “14 or >20 lbs in stock, excluding those of premium quality from Colombia. Now imagine if, after getting all that put together, you forgot you'd wanted to omit Brazilian coffees as well. Rather than re-create your find requests, enter Find mode again, create one request: omit Brazilian. Now instead of clicking the Find button in the Status area, choose R equests, C onstrain Found Set. This new find request will be performed only on the existing found set rather than on the entire database. Using R equests, E xtend Found Set works in a similar fashion by retaining the existing records and simply adding more to them. For example, if you have a found set of all French roast coffees and decide you want add Italian Roast to this found set of records, you don't have to start the find process over. You simply switch back to Find mode, type Italian roast in the Coffee Roast column, and choose R equests, E xtend Found Set. The resulting found set would include both French and Italian roasts: French from your original found set, and Italian from the results of your second find. Modify Last FindModify Last Find is a great feature for find requests. In Browse mode, choose R ecords, Modify Last F ind. You are placed in Find mode with the last set of Find requests you performed. This is handy if you want to continue to play with a particularly complex set of find requests, or simply are performing a series of similar requests. Finding on Multiple LayoutsFileMaker Pro's find functionality really is quite flexible. While you are in Find mode, it is entirely possible to change layouts. As long as the layouts on which you enter your requests are all associated with the same base table, your find performs just as though you had a layout with all the fields on it you needed. Finding is not layout specific. Finding is, however, always table specific. Some more advanced FileMaker Pro solutions comprise multiple tables. Although it is possible to search across related information in FileMaker Pro, your find results will always display a found set of records from a single table. Each layout in your database is associated with a given table. When you perform a find, FileMaker Pro returns your set of records on the layout, from which you may choose Requests, Perform Find, Constrain Found Set or Extend Found Set. Omit and Show All RecordsAfter performing a find, you can opt to omit individual records from a resultant found set. Choose R ecords, O mit Record (to omit a single record), or Omit M ultiple (to omit a specified number of records). To restore your found set to the full set of records in your current table, choose R ecords, Sho w All Records. SortingWhen working with multiple records, an obvious requirement is the ability to sort. FileMaker Pro doesn't store its records in a sorted order ”it stores them in the order in which they were created. When you first open an unsorted table, the records follow that order. There aren't any real mysteries here; for a view of the Sort dialog, see Figure 2.25. Figure 2.25. You can control how a field is sorted: ascending by type (alpha or numeric generally), descending, or in custom order by value list. To sort the records from a table in your database, move fields from the right side of the dialog into the left. There you can choose to have a field sort ascending, descending, or based on the order in which values appear in a specific value list. (Choosing descending, for example, sorts a number field from largest to smallest.) If you move multiple fields into the dialog, FileMaker sorts all records by the first field, and in cases where records contain the same values, the first field then uses the second as an additional criterion. TIP Sorting by value list enables you to set up your own order in which things should appear. For example, if you have a workflow process that flows from "Pending" to "Approved" to "Complete," you can have your records sort in that order rather than alphabetically . By adding multiple fields to your sort criteria, you are specifying secondary sorts: First sort by last name, then by first name, for example. PrintingPrinting is fairly straightforward in FileMaker Pro. Choose F ile, P rint. In the subsequent dialog that appears, you have the choice to print your found set, just the current record, or a blank record showing field names. If you'd like to see what something will look like before wasting paper on something you don't want, use Preview mode (via the mode tabs at the top of the Status Area, or the V iew menu). Choose the layout from which you want to print and change to Preview mode. After you're there you can see where page margins will fall, and the Book icon enables you to step through the pages you will send to the printer. Keep in mind that Preview mode shows you what will be sent to the printer if you choose to print current records. Presenting Data with Subsummary ReportsOne prevalent type of report is a subsummary report. A subsummary report enables you to group records that share some bit of common data. Let's start with a non-summarized report. For example, a standard list view report might look like the one shown in Figure 2.26. Figure 2.26. Notice that this report has been formatted for paper: It is black and white, vertically oriented, ready for print.![]() The records are sorted by region. Instead of having a report like this, where a column simply repeats for dozens of rows, a subsummarized view of this data enables you to "collapse" the information under a header that represents the group (see Figure 2.27). Figure 2.27. Visually, this report is far more comprehensible. Your eye can see the delineation between regions instantly.![]() It's possible for a developer to create quite complex reports. More often than not, he or she will also provide scripts to drive those reports. As a user, it's valuable for you to understand that subsummary reports act on one's found set, and depending on sort criteria, some elements can collapse and be summarized (see Figure 2.28). Figure 2.28. This somewhat more complex report, complete with formatting, should give you an idea of what's possible in FileMaker Pro.![]() Notice in Figure 2.28 that the data fields with Brazil as a region have been collapsed into sub-headers for individual rows. Importing and Exporting DataHaving to manually type every bit of data into a database can be an excruciating experience (almost as bad as having to write a book about it). Fortunately FileMaker Pro has excellent capabilities for importing data from a wide variety of sources. Integration with other systems is covered in later chapters. For now, keep in mind that there are options other than spending all day at the keyboard. Importing and exporting data isn't necessarily for the faint of heart. Depending on how complex your data structure is, you may be somewhat baffled as to how to match the fields of your incoming data with that of your FileMaker system. This is a primary problem all database developers face: getting the information from two different systems to integrate properly. |
< Day Day Up > |