Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In FileMaker Pro 11 Essential Training, Cris Ippolite demonstrates the principal features and functions of this popular database software, including creating tables and relationships, managing fields and records, and working with layouts. The course shows FileMaker developers how to find, sort, and share data as well as how to create reports, calculations, and scripts. It also covers brand new features in FileMaker Pro 11 such as the Inspector tool, charting, and portal filtering. Exercise files accompany the course.
When you're defining fields inside your FileMaker database, you may notice, while you're in the File > Manage > Database window, looking at any field list under the Fields Tab, you see by selecting a field, you'll notice that there's an Options button that becomes available after the field is created. You'll notice that in our _pkCustomerID field, we already have some of these options available. If we click on the Options button, you'll see that we've got a few different decisions to make in the event that we want some of this functionality built-into our field. For example, developers often design a database so that some of the fields are automatically populated with the default data whenever a user creates a new record in that database.
These types of field options in FileMaker are called Auto-Enter options. And they do just what they say really, it automatically enters a value in the field based on the developer settings. So you'll see this example that we did when we created the primary key fields. We want to automatically enter a serial value into the field _pkCustomerID every time a new record is created. So the best way to think of this is, just read it as it says, Automatically enter the following data into this field, and then if you can imagine after this, it would say, each time a new record is created. That's what Auto-Enter means.
FileMaker allows developers to choose from many different options for automatically populating the contents. You can access these under the Auto-Enter tab. One thing you should be aware of is whatever settings you put in here will only give instructions to FileMaker on what data to put in the field when a record is created. After that point, a user could change that value into any other kind of value that they want. That's of course why we check the Prohibit modification of value during data entry option on the key fields; however, in other fields, we might want to just create an initial value and allow the user to update it if need be.
This of course could help them during data entry to save them a few keystrokes on a couple of fields. There's also a concept that uses Auto- Entry options that FileMaker developers like to call creating housekeeping fields. For example, if we click on DateCreated, and then hit our Options, we'll see that when we go to the Auto-Enter tab, we can choose any one of these options that are available to us. Let's concentrate on these first two options in the tab. They relate to Creation and Modification data. A field can automatically track information about when a record was created, or last modified, and within some limits, by whom it was modified or created.
Both the Creation and Modification options have the same set of choices: Date, Time, Timestamp, Name, and Account Name, meaning when this record is created, put today's date, the current time, or both of those combined as a timestamp, or the name or copy of FileMaker that was used to create the record; however, you'll notice in this case that only one of these options is available. That's because when we defined the DateCreated field, we made that field type Date. It only makes sense to allow a date to be automatically entered into the Date field, because otherwise we'd be violating some of FileMaker's storage rules if we tried to put time in there.
So if we check Create, and Creation, Date, and hit OK, now you see the options show up in the Options column in our field list, and what this means is that every time a record is created, it's automatically going to take the computer date from the machine that the user is using, and put that into the date. These are called housekeeping fields, mainly because every database table usually has these defined. And it's a good idea for you to be able to keep track of your data or clean up your data, or if you ever have any problems, and you're trying to figure out what types of things happened last Thursday, you can just do create a Found set based on that date inside of the DateCreated field; however, if you don't create these types of fields right away, it won't retroactively populate any fields if you decide that you're not going to define these until you've already got thousands of records in your database.
So it's a good idea to do these before you start adding data. Another example of it is RecordCreatedBy. Notice this time this is a text field. I'm going to hit the Options button, and now we'll notice that we've got, under Creation, we've got the ability to choose all of these. That's because any of this type of data can be stored inside of a text field. We could choose current Date, like we did earlier, or the Time, which would be the current time of creation. But in this case, we're going to choose Name, which is the name that's in the Preferences Pane of each user's copy of FileMaker. And when we did our Preference Preview, we looked at how you can modify that.
So now, every time a record is created, we're going to automatically get a new serial number. We're going to put a Creation Date, or we'll put a Creation Name. You also have the option, if you double- click here, that you can check Account Name. And although Name and Account Name may seem similar, they're really different concepts. The Name is the name that's inside the Preferences in your copy of FileMaker Pro. Account Name is going to be the name or account name used when you logged into the database. Those are all settings that you'll create when you set up security for your database. Keep in mind that any user could log in through any number of different machines or copies of FileMaker, so these really do give you two distinctly different types of information.
You'll also notice that we've got Timestamp and quickly, we see DateModified. In this case, we'll choose Modification and have it put a Timestamp in. This one is a little bit different, because instead of putting a value in when the record is created, it's going to wait till any one of the fields that are defined for any given record have been modified, and then it's going to put a Timestamp in there. So that field data will continue to update as records are being modified within your database. All of these are good fields to have and good fields to define early on. There's another use of Auto-Enter. If we go to AddressState, for example, and hit Options, you'll notice that we've got this box here called Data.
When we check this, our cursor goes inside of this text entry box. In this case, let's enter in CA. The reason we're doing so is, let's say, for example, that most of the customers that you're entering into your database happen to be customers from the State of California. So in this case, what we'd be doing is saying, inside of the field AddressState automatically put the abbreviation for the State of California. This way when your users are entering new customers into the database, they won't have to enter any values into this field if they happen to be from the State of California.
But because it's an Auto-Enter field, they could just type over the CA and change it to a different state name, if that one applies. Now let's switch over to the Invoices table, where we see we've got our fields defined. We're going to use another one of the options for Auto-Enter. You'll see we have a field for InvoiceDiscountRate. Now, what's interesting about this field is that even though it's defined inside of an Invoices table, this is actually going to pull data from the customer record. Now, you might think to yourself, why would we define this field inside of Invoices? Well, that's because the discount rate has to have a value at the time the invoice is created.
So we can use an Auto-Enter field in this case, because we want to just pull in the discount rate at the moment that record is created rather than if a record was created last year and the customer discount rate changes, we don't want it to propagate backwards and then screw up our totals and kind of mess up our books. So what we'd rather do is use the Auto-Enter feature to insert a snapshot of data at the moment of creation, and that way it doesn't get updated down the road. Auto-Enter can be useful for savings some keystrokes during data entry, but also for taking a snapshot of data at the time a record is created.
Let's take a look at how that would work. We'd hit Options and choose this value down here: Looked-up value. By hitting Specify, we're going to choose to look up data from customers. Remember that we have a relationship between customers and invoices. So if we're in the context of an invoice layout, and we're looking up to the parent, we know that because Invoices is the child in the parent-child relationship, that means that there will only be one related parent record. That way if we use a look-up, going upstream, so to speak, we can point to one field, and it will only have one matching value.
Now, when a new record is created, it's going to go find the related parent and pull the value that's inside the DiscountRate field in the related parent customer record; however, if I go into this field a month later and change that value, it's not going to change the initial value that was placed inside the invoice discount rate at the time the invoice record was created. So now let's hit OK, and now we're back in Browse mode. So let's take a look at what happens when we create a new record. One of the ways you can create a new record is by hitting the button in your Status toolbar, and now we look onscreen, and we notice we've got a primary key value automatically created, the words CA have been added inside our AddressState, and also the dates that these records were created, and an initial value for modification.
You'll notice, also, when we click into modification, that it gives us a full string, including both the Time and the Date. You'll also notice that if we go and we try to edit the primary key for the customer, by hitting the Delete field, we get a message that says, this action cannot be performed because the field is not modifiable. That's because if we go under File > Manage > Database, we see that it says, can't modify data, Prohibit modification during data entry; however, if we were to go in and change any of these other values, for example, California to Washington, and then click outside to commit the record, we see we don't get that error, and that's because we didn't check the prohibit modification of value during data entry on this field.
Also, you'll notice that the modification time is updated by one minute, because we made a modification to the field after we created it. Using the Auto-Enter field options, developers can design a database so that some fields are automatically populated with default data when a user creates a new record, and these are decisions that you'll want to make when deciding what fields you need to create in your database. So in addition to deciding what tables, what fields, and what field types, you're also going to want to consider some of the field options that you have available to you.
Find answers to the most frequently asked questions about FileMaker Pro 11 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.