Join Cris Ippolite for an in-depth discussion in this video Defining fields and field types, part of FileMaker Pro 8.5 Essential Training.
- [Instructor] After we've defined the tables within our FileMaker profile, it's now time to define fields. You'll see those fields listed in the defined database dialogue. Actually if you select a table, and then switch over to fields, you'll see all the fields defined and in this particular table, we don't have any fields defined. Now, a field in FileMaker is a slot so to speak in a database table that can be used to hold information specific to a single database record. Now, a database table in which a record represents a single person for example or a customer, you might want to have fields for the first name, last name, birth date, that type of thing.
They're also known as attributes. So, if you've defined a table of customers, you might want to think about what some of the attributes might be. So, let's say this new table, you'll actually change to customers. Some of the fields that might make sense would be first name, last name, and so on. Each field in the FileMaker database must have a specific type. You'll see the type options listed here in this drop down menu.
When you choose the appropriate field type, it allows FileMaker to decide best to how to store the data in a field and actually how to retrieve the data and although FileMaker has some flexibility as to what kind of data can be stored in these specific types, it's still really essential to choose the appropriate field types based on your database design. So, let's take a look at what each one of these field types are, so we can get a better idea on which one might be useful when you're defining these fields. Let's look at an existing file. The Lucky You Tour file where we've got some fields already defined.
The first type of field that you see quite often in here is called the text field. We see in the country we've defined the text field. A text field can hold up to about two gigabytes of information which is the equivalent of a billion characters or 500,000 pages of English text which is actually quite the improvement from previous versions on FileMaker, and FileMaker stores this textual data internally as Unicode values which is also an improvement from previous versions, and text fields and FileMaker can start formatting information as well including character styling such as color, type face, point size and different things like that, but it's really also known as the default type of field for you to actually enter values into in FileMaker.
We'll look at some of the options for each one of these fields after we look at the types. The next type is the number field. You use a number field in any case where data needs to be treated as a number. For example, if you're using it in a mathematical equation and when we look at defining calculations you'll see how we want to make sure that certain fields are numbers instead of text so they can be used in aggregating functions. Number fields can store up to 800 digits of numeric precision which is 400 on either side of the decimal point. The next type of field is the date field.
If we take a look in here we've got a date field here which would be the start date for this trip, and in FileMaker Pro's date handling is quite flexible and you're able to store and retrieve dates anywhere between one one zero zero zero one or the first day of the first year of our first millennium all the way up to 12 31 in the year 4000, which gives you a pretty wide range, and actually FileMaker stores the date value, although there's many different options on how you can view the date itself when you're looking at it in a layout.
It actually stores it as one number and that number is the number of days elapsed from the date one one zero zero zero one up to today's date. The other option that you'll see is the time field. Now, time similar to date stores a value that represents either the time of a duration or the specific time of a day, but it actually stores the time elapse from 12 zero zero zero this morning. So, basically when the clock struck midnight all the way up until today.
It actually stores the number of elapsed seconds since that moment. Not to be confused with the time stamp option, which is a field that combines both date and time data types. So, it's a time stamp if you want to put something on a record for the time that a record was actually created for example. You can use the time stamp field, and time stamp will be the concatenation of both the date and then a space and then the time all the way down to the seconds, and that's actually stored as a number that combines of course both all of the days since the first year of the first day of the millennium all the way up until the seconds of today's date.
The next type of field that you'll see is a container field. Now, container field is pretty unique within FileMaker Pro. If we look at the container field in the layout and I'll transfer over to layout mode, we'll see that this is an example of a container field. A container field is a versatile data type that stores any kind of binary data. For example, it can store a photo. It can store a file. It can store movies. It can store audio, and what it does is it's based off of the QuickTime engine.
Any QuickTime file, a movie, a sound or other file that can be displayed within QuickTime can be stored within this container field. When we've got a container field on our record and actually if we look we'll notice we can actually right click to insert a picture, QuickTime file or a sound or just a file itself. This is kind of new. Previous versions of FileMaker before seven didn't allow you to store files in there, but if you see if we choose one of the files, it'll store this information locally within this record, and now, when this is used by other users, they can simply right click on it to say export field contents to save that file anywhere on their desktop.
You can save Word document, text documents, Excel documents, PDFs, any kind of binary file. Binary objects can be embedded directly into the container field or stored as a reference. If we take a look at inserting a file, you'll notice here that this option called store only as a reference to the file. For instance, if you want to store really large image files, you can store up to two gigabytes of information within each record in a container field which is pretty impressive actually. So, if you have a large format video or maybe some high resolution graphics, you can store them within a container file, but every time you add, for instance let's say a two gigabyte file, your FileMaker profile size increases.
So, what you can do instead, is actually store only a reference to this particular file. Now, you'll notice it looks exactly like if we have this file embedded into the system, but this can save you considerable space, but it necessitates that the external volume in this case, this volume right here, it must always be accessible to the users of the system and that the file location cannot change. Otherwise we need to update this particular reference. So, if you're interested in storing images in these particular files, or movies or large files like that, it's probably a best idea to use the store only a reference to the file option.
The next two types of fields that we're going to look at, actually later in the module are calculations fields and also we're going to look at summary fields and those are a little more complex and they actually aggregate the other fields that you've created here and you'll see some examples. We'll take a look at this in just a moment, but specifying a calculation will actually refer to some of the different fields and we'll look at that in the calculation chapter. Summary is the same thing. If we go into define a summary field, you'll see that what it does, we'll hit create and it allows us a couple of different options, either totaling, averaging, counting or finding minimums and maximums, but what it does is it gives us all the different fields.
Now, you notice that some of them are grayed out. The ones that are not grayed out are actually number fields because we can only aggregate totals and such if a field has been defined as a number. So, that's one of the other tips on how you can actually decide which one of these field types you want to actually use. We'll look a little bit more at the summary fields later on. So, understanding a little bit about the different field types that are available, you can define all the fields within your database. It's first advisable to determine what attributes you want to define within that table and then create a field for each and as you're making the decision on which fields you want to create, get an idea of what types, whether it's going to be text, numbers, dates, times or possibly time stamps or containers, and then after you're done defining all of those fields, you can create your calculations and summaries that will aggregate all that data.
We'll take a look at that in further chapters.
- Understanding database concepts
- Using FileMaker Pro template files
- Designing database layouts
- Choosing file options
- Finding and sorting data
- Creating value lists
- Printing basics
- Calculation and scripting essentials
- Defining data relationships
- Database security