FileMaker Pro Session 3
Getting Around and Data Manipulation
Navigation
How to move around through your information:
- scroll through the little book in the upper left by clicking
on pages to move +1 or -1 record. => slow, good when there are only a few
records.
- drag the bookmark => the best way to quickly navigate
through records.
- click on the record number, type in the desired record
number and hit the enter key.
- Navigation Keyboard Commands You can use keyboard
commands shown below to move through fields, records, find requests, and layouts.
- There may be navigation buttons (arrows) that take you
from the record that you are looking at to the first, last, next, and previous
records. The next and previous buttons let you look at records near the current
record and are the most useful.
- Im FM7 you can use Control up arrow and control down arrow
Use the keyboard:
- tab = in browse and find modes, moves forward 1 to
the next field.
- command — tab = in all modes, moves forward 1
to the next record, request or layout.
- command — shift — tab or command —
option — tab
- = in all modes, moves backward 1 to the previous record,
request or layout.
- very handy for quick scrolling.
- all keyboard commands can be linked thru the help menu
Using the Index
- FileMaker Pro can create an index of values for each
text, number, date, time, and calculation field.
- FileMakerPro can show you all the possibilities (every
possible data result) alphabetically. To view the index for a selected field:
Choose Paste Special from the Edit menu, and then choose From Index or hit Command-I in a field.
- To have two words treated as one by the index (for example,
Jean Louis): View the index by whole values or type a hard space (press Option-Space
bar) between the words.
- We can find any record by any single or multiple piece
of information.
- Select... Find... to request a find
- command — f
- For a clean data file with clean information, when you
spot errors in data, make the change now or you will never do it.
- Select... Find...
- Once we have all in a found set,
how do we change multiple records with the same information without altering
other records or other fields?
Replacing Data in Records
- You can replace the contents of a field in all records or
in a set of records with a new value, an expression, or an updated serial number.
You can also add a prefix or suffix to the existing value.
- Edit... Replace... Replace feature called a "global
replacement" changes 10-50 records per second.
- Edit... Replace... Replace with Serial Numbers... increment
of... auto-enters information.
- Use the substitute calc to do a search and replace within a set of fields, i.e. replace St. with street
Use automatic (1) entry and (2) validation (to check my
work)
Entering Data Automatically
To save time and ensure accuracy, you can set FileMaker
Pro to enter data in text, container, number, date, and time fields for you,
including dates or times, names, unique serial numbers for records, and custom
data. You can also prevent the automatically entered data from being modified.
To set options for automatic data
entry:
-
Choose Define Fields from the File menu.
-
In the Define Fields dialog box, select an existing
text, container, number, date, or time field, or define a new one.
-
Click Options (or double-click the field name).
-
In the Auto-Enter area of the Entry Options dialog box,
select the value you want entered automatically.
Your choices are limited by the type of field you are defining.
For example, you can't choose Creation Date for a time field. To turn off automatic
data entry, choose Nothing.
Checking Field Data for Accuracy
You can set entry options to ensure that data is entered
correctly. For example, you might want the data to be a date instead of text,
or a value to be within a specified range. When data verification options are
set, FileMaker Pro displays a message if data is entered incorrectly.
To choose data validation options:
-
Choose Define Fields from the File menu.
-
In the Define Fields dialog box, select an existing
text, container, number, date, or time field, or define a new one.
-
Click Options (or double-click the field name)., and
then choose validation from the pop-up menu.
-
In the Validation area of the Entry options dialog box,
select one or more options.
Define Field Options:
- auto-enter an incremented serial
number (used for sequentially numbered certificates, tickets, etc.);
auto-enter the creation or modification date.
- Auto-entry can check for duplicate
values in a field, and verify that it is unique (no duplicated data).
- Use a predefined value list. (You
can edit the things that will be accepted.) Format later as radio buttons.
Calculations
How to set up calculations using the many functions, operator
codes, numerical pad.
Round (formula, number of places)
Sum (repeating field)
Warning: If calculation fields
are daisy-chained and one field is wrong, they will all be wrong.
FileMakerPro is a database that can work (look and act)
as a miniature spreadsheet, but it lacks full SS power because it is not designed
to be a full-fledged spreadsheet.
We could spend an entire session or two on calcs. Here's a simple one:
If(First Name ≠ ""; First Name ; "") &
If(Last Name ≠ ""; " " & Last Name ; "") & "¶" &
If(Address ≠ ""; Address & " " ; "") &
If(Street ≠ ""; Street & "¶" ; "") &
If(City ≠ ""; City & ", CA " ; "") &
zipcode;
returns
Michael Clasen
131 Sequoia Ave
Walnut Creek, CA 94595
