Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.
Your file was successfully uploaded.