Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Understanding field types

From: FileMaker Pro 11 Essential Training

Video: Understanding field types

Before you can define fields in your FileMaker database, you must first take the time to decide which fields you need in your database and then what table they are going to belong to. And we've gone through that exercise in the previous movie. Then you need to make a decision on what field type each field is going to be. If you go to File > Manage > Database, you'll notice that each field in a FileMaker Pro database must have a specific Field Type. You see that all these product fields have a type defined. Each field in a FileMaker Pro database must have a specific data type.

Understanding field types

Before you can define fields in your FileMaker database, you must first take the time to decide which fields you need in your database and then what table they are going to belong to. And we've gone through that exercise in the previous movie. Then you need to make a decision on what field type each field is going to be. If you go to File > Manage > Database, you'll notice that each field in a FileMaker Pro database must have a specific Field Type. You see that all these product fields have a type defined. Each field in a FileMaker Pro database must have a specific data type.

Choosing an appropriate data type for a field will allow FileMaker to decide how best to store the data in the field, and then also how to retrieve it, and then handle it internally, as well. Though FileMaker does have some flexibility as to what type of data can be stored in these fields, iy is essential to choose appropriate field types when defining these fields inside of your database. For example, Field Type will determine the behavior of sorting records. So, for example, if you've Part Number, if you choose to make a part number numeric, then it will sort them numerically: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, for example.

But if you choose to make Part Number Text, when you sort them in the database, they will go 1, 10, 11 and so on and so on. So they will have different behavior depending on their part type that you've chosen. An overview of the field types can be found by clicking on the Type dropdown in the bottom right-hand corner. These of course are the options that you have available to you, not only when you're creating a new field, but if you choose an existing field that you can change the Type by selecting a new type and hitting the Change button. But here we'll just look at the dropdown. Let's take a quick look at each field type and some of their behaviors and characteristics to help you make these decisions when defining fields inside your own database.

First, we have Text fields. A Text field is appropriate for any type of data that can be stored as text and does not need more specific handling; for example, numbers and dates can be stored inside of a Text field, but they lose their number-ness or date-ness, and they can only be addressed as plain text, Much like the example we've talked about before, if I had a Part Number that was actually numbers, but it was stored as text, it gets handled a little bit differently when you do things like sorting. A text field can hold about 2 gigabytes of information per field, which isn't really something that you want to test inside of FileMaker, but it's good to know that you have got those almost limitless possibilities for storing.

It's equivalent to about a billion characters, or roughly 500,000 pages of English text, but don't ever push FileMaker text fields to that limit. You would use a Number field whenever data needs to be treated as a number, for example, in the Part Number if we wanted to be sorted in numerical order or more specifically if you're going to use the value in a field in a mathematical formula. We'll see a lot of examples of these when we get into calculation formulas later in the title. If you define a FileMaker field in the database as a number, it won't prevent the entry of non-numeric data into a Number field; for example, text characters can be typed into a number field, but they are going to be ignored in most database operations, so it will store them; it just won't recognize it they are there.

In Date fields, FileMaker Pro is able to store and retrieve any date between the year 1/1/0001, or the first day of the first year, and 12/31 of the year 4,000. So those are the ranges that you've to work with, with your data. And the reason for that is because FileMaker has internal calendaring functions that have to be represented by each one of those days in that range. FileMaker Pro will generate an error dialog if the user attempts to enter a date that's not within that stated range, or that's not within an acceptable date format, when you enter something into a field; for example, two-year dates are not accepted after the whole Y2K situation.

Internally FileMaker will store a date as a value that is an integer, or a whole number, that represents the number of days and have elapsed between 1/1/0001 up to the specific date. So inside FileMaker Pro, it's saying, how many days have passed since the very first day of the very first year? And that's really what it's storing inside of your database when you type in a date. No matter how you type it in, that's how FileMaker is storing it. And we'll use that information when we get into creating calculations with date fields later in this title. When it comes to Time fields, FileMaker stores time as a value that represents either a time duration, or a specific time of day within a 24-hour period.

Similar to with date fields, internally, FileMaker stores the time value as an integer that represents a duration of seconds, and it represents a duration of seconds from midnight today. So, for example, 10 a.m. this morning would be stored internally as the number 36,000, which is actually the number of seconds since exactly midnight this morning. FileMaker Pro will generate an error dialog if the user attempts to enter a non-numeric data into a field that's defined as Time. A Timestamp field combines the functions from both a date and time data types.

A timestamp is really just a combination of the date and time, separated by a space, and it's stored internally in the FileMaker database as a number of seconds since midnight from 1/1/0001; for example, April 10, 2004 at 3:20 p.m. is actually stored as a number that exceeds 63 billion. Timestamp fields have the same date range restrictions as date fields, and FileMaker Pro will generate an error dialog if the user attempts to enter data that is not a valid timestamp into a field that's defined as a Timestamp field.

There are three other types of fields that we're going to look at in different movies in this title, and they are Container, Summary and Calculation fields. A Container field is a very versatile data type that can store any type of binary data. We'll cover this later on in this chapter. The Calculation field contains data that is computed based on a formula that you will define. Calculations are critical to FileMaker Pro database development, and you're going to learn a lot more about these Calculation fields in an upcoming chapter on calculations. Summary fields are a special FileMaker Pro field type that allows you to aggregate values across a found set of records and possibly for each subgroup within a sorted found set.

Summary fields with the basis of an important area of FileMaker reporting that we're going to cover later in a chapter on Reports. Once you're familiar with each field type that's available to you in FileMaker, you're going to want to assign a certain type to each of the fields that you wish to define inside of your FileMaker database, again, a critical part to planning before you go ahead and add anything to your database. You're going to go through this exercise by asking yourself what type of data will be stored in a field and how will I need to have it handled by FileMaker? Let's look at the field list that we've created in a previous movie and assign to them types, based on what we've just learned; for example, we see that Company Name could simply be Text, because it could be a combination of text and numbers, so text works fine here.

Or if we look at Zip codes, although zip codes will contain almost exclusively numeric data, they're really not going to have mathematical value. So it could be a number, if you wanted to allow Zip code ranges, but this could really go either way. We're going to just call it Text. Date Modified could be more valuable if you both the date as well as time. So we're going to choose Time stamp, and Tax Rate will in fact have numbers included, and we're going to want to use this in formulas to help us compute different invoice values. So that's something that we would want to have as a Number field. And finally you see things like Phone Numbers and Fax Numbers, and sure, they are going to have numbers in those fields, but we never going to use those with any mathematical values, so we'll keep them as Text.

Then finally down below in the Company Logo, you see we've called that a Container. We're going to discuss storing images inside of a container when we get to that section later in this chapter. And quickly, just by reviewing the Invoice Fields, we have some of the same decisions to be made. You see Invoice Date. We've selected that to be a date because then we can use it to determine how much time has elapsed when we've delivered an invoice versus when it's due, those types of things. And if you look down at things like Terms and Weight, we've decided that we want those to be numbers, so that we can maybe calculate shipping costs, or when an invoice might be due based on elapsed number of days.

Once your field list has been reviewed and each File Type is determined, now you can move on to defining these fields within the tables inside your FileMaker database.

Show transcript

This video is part of

Image for FileMaker Pro 11 Essential Training
FileMaker Pro 11 Essential Training

94 video lessons · 14867 viewers

Cris Ippolite
Author

 
Expand all | Collapse all
  1. 7m 22s
    1. Welcome
      1m 8s
    2. Understanding the FileMaker family
      2m 15s
    3. Using the FileMaker Quick Start screen
      2m 52s
    4. Using the exercise files
      1m 7s
  2. 21m 9s
    1. What is a database?
      2m 26s
    2. Flat vs. relational databases
      2m 29s
    3. How FileMaker works
      4m 48s
    4. Understanding the essential preferences
      3m 13s
    5. Touring the interface
      8m 13s
  3. 11m 44s
    1. Creating databases from templates
      2m 29s
    2. Creating new databases in the spreadsheet-like format
      5m 35s
    3. Importing tables
      3m 40s
  4. 9m 9s
    1. Determining which tables you will need
      5m 10s
    2. Creating tables in the Managing Tables window
      3m 59s
  5. 34m 47s
    1. Understanding relationship types
      6m 58s
    2. Diagramming relationships (with ER diagrams)
      8m 50s
    3. Determining which key fields you need
      5m 18s
    4. Defining primary and foreign keys
      7m 56s
    5. Creating relationships using the relationships graph and table occurrences
      5m 45s
  6. 50m 34s
    1. Deciding what fields you will need
      5m 34s
    2. Understanding field types
      7m 54s
    3. Defining fields
      9m 56s
    4. Using Auto Enter options in fields
      9m 33s
    5. Reviewing field validation options
      8m 16s
    6. Building with container fields
      9m 21s
  7. 14m 8s
    1. Creating and duplicating records
      5m 40s
    2. Editing and locking records
      4m 42s
    3. Deleting records and backing up files
      3m 46s
  8. 21m 6s
    1. Importing data
      9m 57s
    2. Importing records to refresh data
      7m 2s
    3. Exporting data
      4m 7s
  9. 18m 30s
    1. Understanding layouts
      4m 15s
    2. Using the Layout Assistant to create List views
      7m 59s
    3. Using the Layout Assistant to create labels and envelopes
      6m 16s
  10. 27m 11s
    1. Using the Layout Setup dialog box
      4m 54s
    2. Understanding layout parts
      4m 40s
    3. Understanding the new Inspector
      2m 26s
    4. Exploring the Status Area in Layout mode
      6m 46s
    5. Managing layouts and layout folders
      8m 25s
  11. 41m 9s
    1. Arranging, aligning, grouping, and locking layout objects
      11m 5s
    2. Placing and formatting objects, parts, and graphics
      4m 10s
    3. Formatting fields and applying field attributes
      8m 26s
    4. Setting field behaviors
      4m 4s
    5. Using the Tab Control feature
      9m 8s
    6. Setting tab order
      4m 16s
  12. 36m 48s
    1. Using the basic find functions
      7m 31s
    2. Reviewing new requests in the Find mode
      5m 54s
    3. Establishing search operators
      6m 43s
    4. Constraining or extending found sets
      3m 24s
    5. Finding records using date, time, or timestamp criteria
      5m 18s
    6. Using Fast Match and Quick Find
      4m 41s
    7. Working with saved finds
      3m 17s
  13. 17m 28s
    1. Sorting with one criterion
      6m 4s
    2. Sorting with related fields
      2m 18s
    3. Sorting with multiple criteria
      1m 36s
    4. Sorting using custom values
      3m 14s
    5. Sorting using buttons
      4m 16s
  14. 17m 14s
    1. Reviewing Field/Control styles
      5m 43s
    2. Creating and applying static value lists
      5m 20s
    3. Creating and applying dynamic value lists
      6m 11s
  15. 23m 52s
    1. Previewing pages and print options
      6m 20s
    2. Printing in different views
      2m 54s
    3. Sliding objects
      3m 26s
    4. Printing merge letters
      4m 53s
    5. Saving as a PDF or Excel file
      6m 19s
  16. 15m 1s
    1. Building simple reports with summary fields
      4m 36s
    2. Creating subsummary reports
      6m 51s
    3. Creating subsummary reports in Table view
      3m 34s
  17. 52m 19s
    1. Defining calculations
      2m 31s
    2. Exploring the Calculation dialog box
      5m 8s
    3. Using number functions
      12m 41s
    4. Using date and time functions
      4m 58s
    5. Using text functions
      11m 43s
    6. Using get functions
      4m 0s
    7. Using logic functions
      11m 18s
  18. 46m 56s
    1. Understanding scripts and script steps
      2m 23s
    2. Assigning script steps to buttons
      3m 54s
    3. Understanding the ScriptMaker dialog box
      8m 28s
    4. Creating multi-line scripts
      6m 44s
    5. Adding find criteria to a script
      4m 58s
    6. Understanding the If script step
      8m 36s
    7. Using script parameters
      4m 42s
    8. Reviewing the Send Mail option
      7m 11s
  19. 28m 0s
    1. Understanding script triggers
      2m 41s
    2. Using object-based triggers
      11m 58s
    3. Using layout-based triggers
      7m 51s
    4. Using file-based triggers (Open and Close scripts)
      5m 30s
  20. 56m 42s
    1. Using related fields
      7m 18s
    2. Creating portals and using portal filtering
      10m 38s
    3. Using related fields in calculations
      7m 6s
    4. Understanding multi-predicate relationships
      11m 11s
    5. Using the Go to Related Record script step
      7m 26s
    6. Creating a chart
      13m 3s
  21. 30s
    1. Goodbye
      30s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed FileMaker Pro 11 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.