|< Day Day Up >|
Assigning Field Options
In addition to establishing fields and assigning data types, you may assign options to your fields as well. These range in function from managing
After you have named a field and
Auto-Entry Field Options
When defining non-calculation fields in FileMaker Pro, you can choose to apply a range of options as records are created and/or modified. The applications for this can range from assigning default values to fields, to automatically reformatting data, or inserting values from other fields based on certain "trigger" events.
In some cases you might not even allow users to modify these auto-generated values, such as when tracking a serial ID or applying a date you don't want adjusted afterward (see Figure 3.4).
Figure 3.4. Auto-entry of data allows you to define rules for automatically populating data into fields in your database.
Auto-entry data is inserted into a field based on some trigger event. The most common event is record creation: When a
In addition to new record creation, other trigger events include record modification and modification of a particular field.
Creation and Modification
The first two options on the Auto-Entry tab deal with tracking and applying certain values as a record is committed to your database. They behave
Values you can opt to apply comprise the Date, Time, Timestamp,
Name is something users can modify as they will via the Preferences dialog, so you
If you do not change any of the account settings of a new file, FileMaker will have established two default accounts for you: Guest and Admin. Both begin with full access to the database.
Here you're able to set a number that increments every time a new record is added to the table. Often this is used to uniquely identify individual records in a table. The value can be generated either when the record is created or when it is committed. The difference is subtle: In the case of incrementing on creation, your number
It is possible to include text
One of the common uses of auto-entry options is in establishing serialized key values, or IDs. This is a vital element of your database structure when working with more than one table, but regardless of how complex or simple your plans are, we
For every table in your database, the first field you should create is a "primary key" or ID field. It is these IDs that uniquely identify each record in your database. There are several ways one could go about having the system establish unique IDs automatically; our recommendation in most cases is to use a serial number set to increment automatically.
We can't stress this practice strongly enough. If you ever want to tackle relational data structures, these serial IDs are a vital element in doing so. Further, if you ever export your data to another system or need to interact with other databases, having a key field that uniquely identifies each record in your database will make keeping track of your data possible.
To create a serial key field, use the following steps:
If you need an ID field for a business purpose (SKUs, Student IDs, Employee IDs from your organization, and so on) we recommend that you create separate fields for such cases. Generally, users should never need to access this serialized ID field, but you may opt to put it on a layout and allow entry in Find mode so that they may search if they choose.
For a full discussion of the use of keys (or match fields ), see the discussion in "Working with Keys and Match Fields," p. 158 .
Value from Last Visited Record
Used most often as a way to speed data entry when information is often repeated for groups of records, this function copies the value from a prior record into a given new record. Bear in mind: "Visited" means the last record in which you entered data. If you enter data in a record, then view a second record without clicking into a field and activating it, it is the data from the first, edited record from which a new record obtains its value.
Here you may specify literal text for auto-entry. This is frequently used to set default states for field entry. For instance, in an Invoice table, you might have a text field called Status where you want to enter "Not Paid" as a default. Being a regular text field, the value is fully modifiable by a user.
In addition to establishing a field as a calculation field, where its value will always be controlled by its defined formula, it is possible to insert the results of a calculation into any other field ”including a container field ”as an auto-entry option.
Further, if you uncheck the, Do Not Re p lace the Existing Value For Field (If Any) option, the results of the calculation formula are entered into the field (overriding any existing value) any time a field reference by the calculation changes.
In other words, a referenced field in your calculation statement acts as a trigger ”including the auto-entry field itself. This enables you to apply calculations to data entry on the fly. One great example of this is a phone number field. You may always want phone numbers formatted as "(123) 456-7890" regardless of how a user entered the data. By using this triggered auto-entry function, you can apply the results of a calculation whenever a field is modified.
For an example of this technique, refer to Figure 3.5.
Figure 3.5. By using a self-referencing calculation, FileMaker Pro is able to replace and correct data as it is entered by the user.
The actual calculation for this Auto-Fill option looks like this (returned as text):
Let ( //define variables: [ rawNumber = Filter (Phone_Number; "0123456789") ; length = Length (rawNumber); red = RGB (160;0;0); //set error flag for a phone number that's too short error = If ( length < 10 ; TextColor ("error: " & Phone_Number; red); "") ]; // now apply the phone formatting and return results If ( error ""; error; "(" & Left (rawNumber; 3) & ") " & Middle (rawNumber; 4; 3) & "-" & Middle (rawNumber; 7; 4) & // this condition tests for extra digits // that we'll treat as an extension If ( length > 10; " x" & Middle (rawNumber; 11; length - 10); "") ) )
To learn more about advanced calculation functions, see Chapter 14, "Specialized Calculation Functions" p. 381 .
This auto-entry option copies a value from a related record into a field. Any time the field controlling your association to that
For example, if a user enters a ZIP code into a given record, it's possible you could have another table then auto-populate your city and state fields with the appropriate information.
When a user enters a ZIP code in the highlighted field in Figure 3.6, the city and state fields below are triggers to pull values from the ZipCodes table. An important fact to keep in mind is that FileMaker has
Figure 3.6. Lookup functions work somewhat like relational data, but instead of displaying values from a related record, their information is copied and stored when a trigger event occurs.
Take special note that lookup auto-entry functions work just as all auto-entry functions do: They copy or insert information into a field. You are not displaying related information, nor are you controlling content by calculation. Thus lookup values are not live links to related data: If you were to delete the records in the ZipCode table in the previous example, all your people records would
This is an important distinction to understand,
To see how to create a lookup field, refer to Figure 3.7.
Figure 3.7. Often you'll want only exact matches, but in some cases you can use the
If you get trapped in a series of validation dialogs, refer to "Validation Traps" in the Troubleshooting section at the end of this chapter.
Field storage and indexing is found on the Storage tab in your Field Options dialog and deals with how FileMaker Pro
A developer can
Global fields are often used by developers to establish variables during scripts, to display programmatic field labels (as opposed to simply leaving text on a layout), or to maintain system preferences. Globals have a wide range of applications.
One vital element to learn is when data is committed and stored for globals: In a single-user environment, any change to a global field is permanent and is saved across sessions. In the case of a multi-user environment ”where a FileMaker Pro solution is hosted on FileMaker Server or via multi-user hosting ”global values for each guest default to the value from the last time the database was in single-user mode; any change made to these defaults will then be specific only to a given user's session. Other users continue to see the default values, and after the database session is closed it reverts to its original, default state.
Using globals is a great way to keep track of certain states of your database. For example, you could use a global field to store which row of a portal was last selected. This field could then be used in scripts or calculation formulas.
For an example of using a global to drive portal behaviors, see "Selection Portals," p. 454 .
Another common use of globals is for storing system graphics. Establish a container field, set it for global storage, and paste a favorite company logo, a custom button graphic, or any number of elements that you can then control globally in a field rather than having to paste discrete elements on each and every layout.
A third use for globals takes advantage of their being user-session specific. A developer can set a global with user account information and refer to such via scripts, confidant that the same script will work for each individual user of the system. Person A's global field
The second section of the Storage tab on the Field Options dialog lets developers allow a field to contain multiple values. Such fields are known as repeating fields . On a given layout the developer can array repetitions either horizontally or vertically, and in scripts can refer to specific repetitions within the field.
Repeating fields can be
Repeating fields do have their place, however. Imagine a spreadsheet. Even though an entire row may be blank, the
In addition to facilitating data entry, one could simulate a related child table with repeating fields.
For a detailed discussion of multiple-table solutions, see Chapter 6, "Working with Multiple Tables," p. 153 .
Databases store data by definition, of course, but they are also required to perform functions such as searches and sorts with that data. FileMaker Pro, like many databases, can
some of the data in a file to increase the speed at which it
An index is somewhat like a database within a database. FileMaker Pro can store, along with a specific value in a given field, all the records in which that exact data is used. This then enables FileMaker to recall those records quickly without having to resort to a linear scan of your file. Aptly named, these indexes work just as a book index works: They facilitate finding all the locations in which a given item is used.
To familiarize yourself with the concept, take a look at a given field's index. Click into a field and select I nsert, From I ndex. If the field is indexable, and has already been indexed, you are presented with a dialog box showing all the discrete values indexed for a given field. Just as with selecting from a value list, you may opt to choose from this list rather than type.
Allowing a user to select from an index is only one of the reasons for having them. Indexes enable FileMaker Pro to perform find
New in this version of FileMaker are two kinds of indexes: value indexes and word indexes.
apply to all field types, with the exception of container or summary fields;
only apply to text fields and are based on a given language or character set. The difference between the two, and when either is
FileMaker Pro's default setting (found on the Storage tab of the Field Options dialog, displayed in Figure 3.10) is None, with the check box for Automatically Create Indexes As Needed enabled. Most developers, even the more advanced, should find that this setting serves their needs.
Value indexes are established by a database's schema definition ”as a developer defines fields and builds relationships ”and allow for relationship matches and value lists. If a developer creates a serial ID and joins a relationship via such a field, a value index is created for the serial ID field.
Unless a developer explicitly sets a field to generate one, Word indexes are created as users are interacting with and using a given database. They are utilized for find requests, or created when a user explicitly chooses Insert, From Index. If a user enters data in a find request for a field that lacks a word index, FileMaker Pro enables indexing for that field and builds one (unless its explicitly unindexed, or an unindexable calculation).
At this point you may be wondering what all the fuss is about. Why not index every field in a database and be done with it? The downside to indexes is increased file
Notice that FileMaker doesn't allow you to explicitly control word and value indices. Value indices are possible for all field types: Word indices apply only to text fields. Minimal
Only a subset of the fields in your database will ever need to be indexed, and FileMaker's "on demand" approach makes things
To explore the vagaries of storage and indexing considerations for calculation fields, see "Other Options," p. 220 .
An important point to remember is that some fields are not indexable. This means than they will be slow when used in sorts and find requests, but most importantly they cannot be used to establish relationships.
A field is unindexable if it is a calculation based on a related field, a summary field, or a global field, or if it references another unindexed, unstored calculation field.
The fourth tab in the Field Options dialog is one that many English-speaking developers will have trouble properly pronouncing, let alone using. Because of the adoption of Unicode support in FileMaker Pro 7, it is now possible to offer Asian-language double-byte language support. As a result, you can now manage Japanese.
Japanese has four alphabets. One is based on glyphs from Chinese, known as Kanji, two are based on phonetic syllables known as Hiragana and Katakana, and the last is our own Roman alphabet, adopted in the 19th century for foreign words. When working in Japanese, it is possible to render the
Suffice it to say that unless you're a student of Japanese (native or
|< Day Day Up >|