Home | About MACC | Clients | DADI | FMP Tutorial | MacFantastique

Filemaker Pro Session 2

Field Types Definition

  • What is a database? The various types of manual databases include Rolodex card files, yellow ruled note pads etc.

Browse screen grab

  • You will spend most of your time in FileMaker Pro in Browse mode (see Mode in the menu bar) in order to enter, delete, or edit data or records. Notice the "book" on the left hand side which shows how many records are being displayed at present. This is the actual result (a mock rolodex or index card file.) It is a different mode than the define fields or Find mode.
  • You cannot hurt the data in Layout mode; only change the way it appears.

Repeat Fields

  • FPRO can split a field. One field can repeat itself up to one thousand times on a single record. This is similar to subfiles in a relational database.
  • For Total Amount Paid , use an extra bonus, the repeat field. Show multiple values in separate boxes in one field. Usually 100-150 repetitions maximum because of speed considerations. But you can take it up to 1000 repetitions!
  • repeating fields are no longer use except for special circumstances such as storing graphics for use throughout a database. Screen Grab

Container Fields

  • You can record an actual message in each record. Double-click on the comments field. You can record up to one minute per field.
  • Defining container fields
  • Use calculation field to store drawings, pictures, movies, photos, bitmaps, clip art, sounds (Macintosh), or OLE objects (Windows).
  • You can paste pictures or movies from the Clipboard or import them. FileMaker Pro brings them into memory when you display a layout that includes a container field.
  • You can paste sounds from the Clipboard or record them (if you have the required hardware and software for recording).
  • Note: Container fields cannot be searched. If you need to search for container field items, create a text field on a layout and store a short description of the container field contents.
  • FMP 7: Container: Multiple streams of binary data totaling no more then 4 gigabytes.
  • container

To define container fields:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, type a name for the new container field in Field Name.
  3. For Type, select Container.
  4. Click Create.
  5. To set data entry or validation options, click Options.
  6. Continue defining fields or click Done.
  • Tip: Keep your comments brief or you will slow the total operation. You can have multiple recordings in one field. Maybe have repeating comments and date fields. Click on the sound icon to play it back. This is also a picture field so you can paste a picture (PICT file) in it.
  • The pointer cursor can change into a "repositioning" tool. Click & drag to shift fields in a list. View by field type, custom order, or alphabetical order.
  • screen grab

Text

  • Zip Code, telephone number, credit card number, social security number are all text (not number) fields. They can have dashes, hyphens, brackets around them (which are characters of the alphabet, not numerals!) They will sort numerically.
  • Tip: Use member number as a text type field used to make sure there is a field with a unique value in each record. You can use the social security number. No two records have the same value. Joe Baker = BAK600 — 1 distinguishes him from all the other Joe Bakers.

Defining text fields

  • Use text fields to hold text. Text fields can hold values that are combinations of letters, numbers, and special characters, like postal codes or serial numbers.
  • FileMaker Pro indexes the first 20 alphanumeric characters of each word (including the ampersand, straight quote, left single quote, right single quote, and slash) in a text field. Text fields sort in alphabetical order (left to right, character by character).
  • FMP7 has 3 levels of Indexing: None, Minimal, and full
  • minimal is 30 characters
  • full is all characters
  • indexing

To define text fields:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, type a name for the new text field in Field Name.
  3. For Type, select Text.
  4. Click Create.
  5. To set text entry or validation options, click Options.
  6. Continue defining fields, or click Done.

Number

  • Defining number fields
  • Use number fields to store:
  • numbers for use in calculations and summaries (15-digit accuracy) Balance Due = Total Amount — Sum of (Total Amount Paid)
  • numbers that need to be sorted in numeric order (FileMaker Pro indexes the first 120 numeric characters (numbers, decimal points, or signs) of the field, ignoring letters and other symbols)
  • values that are a combination of numbers, letters, and special characters that still need to use the numbers in calculations and sorting (for example, $400 or best offer)
  • text that must be evaluated as a Boolean value (where nonzero numbers evaluate to Yes or True and zeros evaluate to No or False).
  • Use text fields instead of number fields to store postal codes, phone numbers, and other values with leading zeroes or characters like hyphens or parenthesis.

To define number fields:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, type a name for the new number field in Field Name.
  3. For Type, select Number.
  4. Click Create.
  5. To set data entry or validation options for numbers, click Options.
  6. Continue defining fields or click Done.
  7. Number Options for FMP 7
  8. numb index

Date

Defining date fields

Use date fields to store:

  • dates used in calculations or summaries
  • dates you need to sort or search through

Enter dates by separating the numbers with any character, like slashes (/), periods (.), dashes (-), or spaces. You can use any character to separate dates if they are identical (for example, 12!12!96).You must enter a month and day, but FileMaker Pro assumes the current year when you don't enter one. You can display date fields in a variety of formats (like July 18, 1995 or 7/18/95) by formatting the fields with the Date Format dialog box in Layout.To define date fields:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, type a name for the new date field in Field Name.
  3. For Type, select Date.
  4. Click Create.
  5. To set data entry or validation options for dates, click Options.
  6. Continue defining fields, or click Done.
  7. FMP7 options

Time

Defining time fields

Use time fields to store times you want to use in calculations and summaries. Also use time fields when you need to search through or sort stored time values. You can display time fields in a variety of formats by formatting the fields with the Time Format dialog box in Layout.To define time fields:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, type a name for the new time field in Field Name.
  3. For Type, select Time.
  4. Click Create.
  5. To set data entry or validation options for times, click Options.
  6. Continue defining fields or click Done.
  7. time options

Global Field

This is a variable. This allows you to enter information into one field whereby every record in your database has that information in that field. e.g. key codes, a date (such as one based on today’s date.) In contrast, a calculated field would be recalculated for each record (what if you had 20,000 records?) A global field (date, number, or text) is calculated for only the records in the found set.When you are finished defining your fields, click the done button .Using global fieldsA global field contains one value that's used for all records in the file. Use a global field as a fixed value to be used in calculations across all records in a file, or in conditional script steps, such as If, Else, End If, Loop, End Loop, and Exit Loop. Global fields are included in all field lists and can be used on layouts.To define a global field:

  1. Choose Define Fields from the File menu.
  2. In the Define Fields dialog box, select an existing global field or define a new one.
  3. Click Options (or double-click the field name).
  4. In the options for Global Field dialog box, choose a data type (text, number, date, time, or container) from the pop-up menu.
  5. To create a repeating field, select Repeating field with maximum of <value> values and, in the text box, type the maximum number of times you want the field to repeat.
  6. Click OK, and then click Done.
  7. Global fields have changed in FMP7 Now any field can be a global field
  9. being able to use calculation fields as a global opens up a wide range of opportunities in relationships.
  10. simple relationjs

Calculation

Defining calculation fields

Use calculation fields to perform numeric, container, text, date, or time calculations. Calculation fields perform calculations with values from other fields in the current record, from related fields, or from global fields. The data in a calculation field is the result of a formula that you specify. The result can be text, number, date, time, or container.To define calculation fields:

  1. Choose Define Fields from the File menu
  2. In the Define Fields dialog box, type a name for the new calculation field in Field Name.
  3. For Type, select Calculation.
  4. Click Create.
  5. In the Specify Calculation dialog box, use the pop-up menus, lists, and buttons to build a formula or type the formula directly into the formula box.
  • The name of the field followed by an equal sign appears above the formula box. You can double-click a field name operators, and functions, or click symbols to have FileMaker Pro copy them into the calculation formula box. To list field names from a related file, choose the file from the pop-up menu. To list the functions by category, choose the category from the View pop-up menu.
  1. Choose the type of result you want (Text, Number, Date, Container, or Time) from the Calculation Result list. It defaults to Number so be sure to reset it if you want a text result returned
  • To create a repeating field, select Repeating Field with Maximum Of <value> Values, and then type the maximum number of times you want the field to repeat.
  1. To perform a calculation even when all referenced fields have no value (are empty), deselect Do not evaluate if all referenced fields are empty.
  2. By default, calculations that include a related field, summary field, global field, or a reference to another unstored calculation are unstored.
  3. 9. Click OK
  4. 10. Continue defining fields or click Done.
  5. FMP 7-Here is an example of a calculation performed after entry in phone number field to add formatting
  6. phone calc 1
  7. calc phone 2
  8. Just for fun please note that is calc is using a preference from a global field called Phone format:
  9. globalcalc pref

Summary

Defining summary fields. Use summary fields to calculate values such as subtotals, averages, and grand totals across multiple records. Summary fields are associated with groups of records. The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, and whether the records are sorted.Data in a summary field reflects records currently being browsed; either all the records or a group of found records. If you change a value in one of the fields on which the summary formula is based or if you change the found set, FileMaker Pro recalculates the result in a summary field.You can't change data in a summary field manually, but you can copy it. You can also perform calculations on summary fields.To define summary fields:

  1. Choose Define Fields from the File men
  2. In the Define Fields dialog box, type a name for the new summary field in Field Name.
  3. For Type, select Summary.
  4. Click Create.
  5. In the Options for Summary Fields dialog box, select the type of summary to perform, and then select the name of the fields that contains the values to summarize.
  6. summary

  next - Updated

Jump Menu