Video: Using Auto Enter options in fields

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.

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.

Using Auto Enter options in fields

When you're defining fields inside your FileMaker database, you may notice, whileyou're in the File > Manage > Database window, looking at any field list underthe Fields Tab, you see by selecting a field, you'll notice that there's anOptions button that becomes available after the field is created.You'll notice that in our _pkCustomerID field, we already have some ofthese options available.If we click on the Options button, you'll see that we've got a few differentdecisions to make in the event that we want some of this functionalitybuilt-into our field.For example, developers often design a database so that some of the fields areautomatically populated with the default data whenever a user creates a newrecord 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 thefield 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 everytime a new record is created.So the best way to think of this is, just read it as it says, Automaticallyenter the following data into this field, and then if you can imagine afterthis, 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 forautomatically 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 willonly 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 ofvalue that they want.That's of course why we check the Prohibit modification of value during dataentry option on the key fields;however, in other fields, we might want to just create an initial value andallow the user to update it if need be.

This of course could help them during data entry to save them a few keystrokeson a couple of fields.There's also a concept that uses Auto- Entry options that FileMaker developerslike to call creating housekeeping fields.For example, if we click on DateCreated, and then hit our Options, we'll seethat when we go to the Auto-Enter tab, we can choose any one of these optionsthat 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, orlast 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 iscreated, put today's date, the current time, or both of those combined as atimestamp, 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 Datefield, because otherwise we'd be violating some of FileMaker's storage rules if wetried to put time in there.

So if we check Create, and Creation, Date, and hit OK, now you see the optionsshow up in the Options column in our field list, and what this means is thatevery time a record is created, it's automatically going to take the computerdate from the machine that the user is using, and put that into the date.These are called housekeeping fields, mainly because every database tableusually has these defined.And it's a good idea for you to be able to keep track of your data or clean upyour data, or if you ever have any problems, and you're trying to figure outwhat types of things happened last Thursday, you can just do create a Found setbased on that date inside of the DateCreated field;however, if you don't create these types of fields right away, it won'tretroactively populate any fields if you decide that you're not going to definethese 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, underCreation, 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 bethe current time of creation.But in this case, we're going to choose Name, which is the name that's in thePreferences 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 reallydifferent 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 machinesor copies of FileMaker, so these really do give you two distinctly differenttypes 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 inwhen the record is created, it's going to wait till any one of the fieldsthat are defined for any given record have been modified, and then it's goingto put a Timestamp in there.So that field data will continue to update as records are being modifiedwithin 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'vegot 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 thecustomers that you're entering into your database happen to be customers fromthe State of California.So in this case, what we'd be doing is saying, inside of the field AddressStateautomatically put the abbreviation for the State of California.This way when your users are entering new customers into the database, theywon't have to enter any values into this field if they happen to be from theState of California.

But because it's an Auto-Enter field, they could just type over the CA andchange 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 definedinside 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 timethe invoice is created.

So we can use an Auto-Enter field in this case, because we want to just pullin the discount rate at the moment that record is created rather than if arecord was created last year and the customer discount rate changes, we don'twant 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 snapshotof 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 alsofor 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 theparent, we know that because Invoices is the child in the parent-childrelationship, 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 onefield, and it will only have one matching value.

Now, when a new record is created, it's going to go find the related parentand 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 notgoing to change the initial value that was placed inside the invoice discountrate 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 yourStatus toolbar, and now we look onscreen, and we notice we've got a primary keyvalue automatically created, the words CA have been added inside ourAddressState, and also the dates that these records were created, and an initialvalue for modification.

You'll notice, also, when we click into modification, that it gives us a fullstring, including both the Time and the Date.You'll also notice that if we go and we try to edit the primary key for thecustomer, by hitting the Delete field, we get a message that says, this actioncannot 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 wedon't get that error, and that's because we didn't check the prohibitmodification of value during data entry on this field.

Also, you'll notice that the modification time is updated by one minute, becausewe made a modification to the field after we created it.Using the Auto-Enter field options, developers can design a database so thatsome fields are automatically populated with default data when a user creates anew record, and these are decisions that you'll want to make when deciding whatfields 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 haveavailable to you.

Q: In the Chapter 16 tutorial, “Using Text Functions,” the instructor discusses how to calculate the First Name and Last Name from the Full Name. However, the method does not account for names ending with “Jr.” or “Sr.” or “III,” etc. How can I account for added suffixes in names?

A: For cases like this, you can create a third "Suffix" field. Then change the FullName calculation to:

NameFirst&" "&NameLast&" "&Suffix

This way, nothing will appear if the Suffix has no value, but if it does have a value the suffix will appear.

Q: What information is actually on the “Invoice Line Item” table in the examples, and how does it actually connect to the tables that it comes from?

A: The information in each line item is native to the "Invoice Line Item" table. The fields are defined in that table and each record represents "A Product appearing on an Invoice."
Each time a product is used on an invoice, a record in the line item table is created. Many of the fields, for example "Quantity," are native to that table because those values only exists when a Product is used in an Invoice, and not as attributes of a Product itself.

Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.

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.

Already a member ?

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

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

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:

new course releases

newsletter

general communications

special notices

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:

new course releases

newsletter

general communications

special notices

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