Naming Convention for FileMaker fields

V12, Section 1

This post is the first in a series based on our effort to revamp xBase. We are currently using xBase version 4 (Click to download. Full access login is developer:Extensitech), which was designed to leverage the capabilities of FileMaker version 10. It has served us well, and continues to do so, but it is getting a bit old. We’d like our next version to take into account all the things that have made version 4 such a pleasure to work with, but we’re taking a “ground up” approach, reconsidering everything we’ve done to ensure that we’re fully leveraging new capabilities of FileMaker, and all that we’ve learned as developers during the years since we developed version 4.

You will note, by the way, that we’re changing our version numbering system. Version 4 is, as you might expect, the fourth iteration of the xBase file. That’s probably the simplest naming we could do. However, it becomes challenging to explain to clients that version 4 was made for FileMaker 10, version 3 was made for FileMaker 9, version 2 for FileMaker 8 and version 1 for FileMaker 7. Instead, from now on, we’re going to name our xBase versions after the FileMaker version for which they are designed.

Naming Conventions

As anyone who’s ever created a FileMaker database knows, FileMaker doesn’t have too many rules about what fields should be in each table or what they should be called. In and of itself, this is a welcome flexibility.

However, for databases that are, at least potentially, going to become large and complex, and/or are going to be worked on by multiple developers, there is a definite downside to this flexibility. If a developer doesn’t devote any attention to making field names consistent and informative, then down the road that developer, or another developer, will have the frustrating experience of trying to locate a specific field and not being able to find it. For example, is the last name field called “Last Name”, “Surname”, “Family Name”, “LName”, “Name_Last”, “Name_l” or even something else that we can’t think of right now? In a small table, this may not be an issue, because you can scan until you see a likely candidate. In a larger table, though, this can be a bit of a chore. Likewise, there may end up being more than one likely candidate. For example, there might be a “Last Name” that is simple text, and a “Last Name2” that is a calculation appending a suffix field (Sr, Jr, Dr) to the last name. Experienced developers have probably all had the experience of writing a script that keeps failing, only to find that the field you’re trying to set is actually a calculated field (or a container, or a summary field) and the actual field you want to set is a text or number field with a very similar name.

To the casual developer, these may seem like trivial frustrations, and the flexibility of naming a field whatever you want to name it may seem well worth those frustrations. I’ve talked to developers who think that the whole discussion of naming conventions (or at least most of it) is foolishly arcane, academic and restrictive. If the developer is creating fairly simple databases that only one developer will ever modify, I have to say I see the point. For Extensitech, though, where multiple developers are going to continuously be modifying many large and complex databases, these small frustrations add up to a serious impediment to development, and an ongoing source of frustration and delay.

Extensitech has been using pretty much the same field naming convention for nearly a decade now, and it hasn’t changed at all for about five years. We’re working on our newest version of xBase, though, and taking a fresh look at our naming convention to try to make it better and overcome a few shortcomings. In this post, I’ll go over our current naming convention, discuss its shortcomings, and then outline our new naming convention that we’ve just created.

The reader may wonder why I would bother to discuss the old naming convention if it had shortcomings and we’re just going to replace it. There have been, and continue to be, many approaches to naming conventions for FileMaker fields. Each has had its advantages and disadvantages, and I don’t expect that either our current or our new naming convention will be any exception. Some years ago FileMaker even brought together a committee of leading developers to create a suggested naming convention, and published it. What they said at the beginning of that document, and what I’ll reiterate here, is that no naming convention is the be all and end all of naming. Specific project requirements and even specific developer preferences are legitimate reasons to deviate from a naming convention. It is far more important to understand and incorporate an understanding of what a good convention should accomplish, so that your own naming convention, even if it is totally different than ours, can take those considerations into account.

In fact, our current naming convention is much more thorough and therefore restrictive, and our newer convention is an apt example of how one might adopt the “spirit” of a naming convention, and retain a bit more flexibility and user-friendliness. As I walk through the changes we’ve made and how we kept to the objectives of the original naming conventions, you may find further inspiration for how you’d like to model (or improve) your own naming convention while still retaining the advantages of a stricter convention, or one you simply find unattractive for some reason.

Basic Considerations

Let me start by going over a couple of basic conventions that we use at Extensitech, which apply to all field types in both the current and new conventions.

First, we use no spaces in our field names. This practice actually began way back when web development for FileMaker was in its infancy, and there was some vague advice that spaces were hard to deal with on the web. Admittedly, that advice may have been apocryphal, and since then I’ve worked on web projects with FileMaker where field names had spaces, and frankly haven’t seen what that difficulty would have been. Actually, we’ve encountered some challenges with underscores when using the new ExecuteSQL function (the underscore is a reserved wildcard) but for now at least, we’re working around that challenge rather than discarding the use of the underscore.

Be that as it may, we’ve since found another reason to continue with the practice. It’s easier to select field name that has underscores instead of spaces by double-clicking. You can also use the control key and the left and right arrows to jump to the end or beginning of a word.

(To augment this, we also write our formulas with spaces both before and after any punctuation, even parentheses and semi-colons, so that the punctuation doesn’t become part of the word and this quick selection works more consistently.)

We considered camel-case (“camelCase”) but felt camel-case is more difficult to convert to human-readable form, and that it’s less easy to read as-is.

Secondly, our field names, aside from the conventions described below, should always start with the word that most broadly defines its purpose. For example, Name_First instead of First_Name. This causes fields to group nicely in alphabetical order, putting all the “Name” fields together, whereas the more “English” fields “First_Name” and “Last_Name” can end up quite far apart in an alphabetical list. In our experience, this is pretty common across most good naming conventions.

Objectives

This last consideration brings me to the objectives of our naming convention. What do we hope to accomplish?

The first objective, alluded to above, is to get an alphabetical field list to order the fields in a way that is useful to the developer. FileMaker will let you create a custom order for your fields, but maintaining that order and coming up with a separate convention for how fields should be ordered are both efforts we’d like to sidestep. On a related note, we’d like some fields to go to the top, or perhaps the bottom, of such a list. For example, we’d like keys at or near the top of the list so that when we’re linking table occurrences in the relationship graph, we don’t have to scroll to get to our most likely candidates.

The second objective is to let the developer know, at a glance, what the purpose of the field is. Is this a key field? Is it a Boolean flag (yes or no, on or off)? Is it about the data in the record, or about the record itself, as with creation and modification timestamps?

The third objective is to let the developer know, at a glance, what the field type is. When creating scripts or functions, or even just putting a field on a layout, it makes a difference whether a field is calculated, or whether it is text, or a number, or a container, or globally stored. (Yes, I know that global isn’t technically a “type”, but for our purposes, we’ll treat it as one because it has the same effect on our conventions.)

Last, but definitely not least, a naming convention should promote consistency in field naming. A developer using the convention should know right away what a new field should be called, and should be able to quickly find and identify existing fields without having to find it through trial and error. Any two developers will vary their names a bit, but the convention should at least get us close.

Current Extensitech Naming Convention

Our current naming convention follows the objectives above in a fairly straightforward (and perhaps arguably artless) manner. Let me admit up front the main shortcoming of this convention, which is easy to spot at first exposure: the field names look like something from another language, and tend to put off both developers and also users in the infrequent occasions when they see the actual field names (when sorting or doing an export/import).

On the other hand, this convention has served us quite well. The convention groups fields meaningfully. At a glance, a developer who understands this convention can see what a field is for and exactly what field type it is. Given the convention “rules” below, a fairly new developer can quickly grasp what a new field should be called and can guess, at least most of the time, the name of an existing field that he or she is looking for.

When we’ve had other developers work with us and we had this convention in place, we’ve encountered some resistance, mainly on grounds of aesthetics and complexity. Let me acknowledge that that resistance is in some ways warranted, but also ask you to bear with us, especially since we’ll follow up with a newer naming convention that addresses those shortcomings.

We currently name our fields using the following basic format: A capital letter indicating the field purpose, then an underscore, then one or two letters indicating the field name, then an underscrore, then the actual name of the field.

For example: A_t_Name_First is an Attribute, text, and is the first name. F_n_Active is a Boolean flag, numeric, indicating whether the record is active. R_m_Create is a field about the record itself, not the contents. It is a timestamp, and indicates when the record was created.

Here are the purpose codes, except for keys, which are described later:

D – Description field. We have only one per table called D_lt_Primary, and it is a calculated field that describes the record in terms the user would understand. For example, in a table of People, this would be a formula bringing all the parts of the name together in a full name. For an Invoice table, it might be invoice number, date and customer. It’s useful to have this field in every table and clearly named, since it is so often useful when you want to show a related record on a layout.
F – Boolean field, for fields with a binary value list such as Yes/No, 1/0, 1/blank, True/False
R – Record-related field, as opposed to data-related. These include details about when the record was created and last modified, and by whom. A calculation showing what record the user is on, of how many (“Record 4 of 12”) would also fall under this purpose.
L – Layout-only fields, for fields that are only for use on a layout, such as calculated text on a button or tab
S – Script-only fields, for fields that are only used in the context of a script, such as a temporary field for data entry
X – Fields used to summarize either a found set of records (summary fields) or a related set of records (calculation fields)
A – An Attribute field, the most common type, indicating that this field is an attribute of the data in the record

The field type codes are as follows, but you can also see them when you are creating a new field. We use the shortcut keys in the “type” drop-down where you set the type of a new field.

t – text
n – number
d – date
i – time
m – timestamp
r – container
l – calculation (note this is a lowercase L, not a capital i)
s – summary
g – global (as noted, not actually a field type, but treated as one here)

The last three, calculation, summary and global, require a second letter to be complete. For calculation and global, the second letter is the field type. For example, a calculation that has a text result would be lt, while a calculation with a number result would be ln. A global field that is also text would be gt, and a global field that is also number would be gn.

For a summary field, the second letter indicates the type of summary, as found on the options screen. (On a PC, these letters are underlined (the “alt” keys) for each option):

t – total
a – average
c – count
m – minimum
x – maximum
s – standard deviation
f – fraction of total

Thus, for instance, a summary field showing the average of the field A_ln_Days_Late would be X_sa_Days_Late

Keys

When we initially developed this naming convention (for xBase version 2) keys had a purpose code of K. We decided xBase in version 3, about five years ago, that there was good reason to deviate from the basic structure in the case of keys, so they no longer follow the format above.

The primary key is always named the same, in every table. It is called “__P”. That’s two underscores (to make it go to the top of the list and make it easiest to grab in the relationship graph) and a P (for “primary”). The value of naming it the same in every table, rather than, for instance, calling it “Customer_ID” in customers and “Vendor_ID” in vendors, is that it’s quickly identifiable. You can, in fact, easily calculate the primary key of any record in the database, or create a custom function to do so: GetField ( Get ( LayoutTableName ) & “::__P” ) The name of the table, which is the most common extra bit in the primary key field name, is already known, and doesn’t need to be part of the field name.

(Incidentally, our current practice is for this field to auto-enter the TLA (three-letter acronym) of the table, followed by a serial number; for example “CUS1”. This makes the key unique across the database, not just within this table. Our new practice will be to still use the TLA, but to use the new Get (UUID) function instead of a serial number. More on that later.)

Foreign keys are named with a single underscore (so they’ll sort right under the primary key) followed by the TLA (or, if you prefer the name of the table to which this foreign key relates). Thus we have _CON for a foreign Contact key, or _CUS for a foreign customer key. Again, the naming makes the keys easy to grab in the relationship graph, and easy to identify due to consistency.

Optional additional tags

Sometimes two fields may be very similar, and require some extra identifiers. They might also have a feature such as an auto-entry that’s important to how the field is used. These are not required by the convention, but are allowed. For example, if it is not otherwise obvious that a field doesn’t allow modification, you might add “_pm” to the end of the field name. If it has a complex auto-entry, you might add “_ae” to remind other developers to consider this when setting, or otherwise using, the field.

Examples from the template table

Examples will of course be useful to help you understand the practical application of the naming convention. To that end, here are fields that you would find in our “ZZZ__Template” table, which we copy and paste to create a new table in xBase. We find a template table useful because there are many fields that you don’t want to forget in a new table, since they’re either a pain to add later, and/or so commonly used that you may as well add them to every new table. (I’ve added a few extra just for better illustration of the convention, and removed a few that are xBase-specific and would require a long and distracting explanation.)

__P – The Primary key, which auto-enters the table TLA and a serial number.( In our current template table, this field is called “__P_change_me” so that developers remember to change the TLA in the serial number entry. In our new version, this won’t need to be updated, since it’s an auto-entry calculation that derives the TLA from the table name and appends a UUID.)
_1 – A calculated field equal to 1. This is often useful for relationships to flagged records
_CON – (not actually applicable to a template) This would be the foreign key to relate to a Contact
A_t_Notes – a text field for general notes
D_lt_Primary_change_me – A calculated field (which needs to be calculated for each table once fields are added) that a user can look at to have an idea of what the record is about.
F_n_Active – A Boolean number field, which auto-enters 1, indicating that the record is active. A user can uncheck this field to keep the record from showing up in standard searches, value-lists and such.
L_lr_Btn_Cancel – A calculated container showing a cancel button. The calculation equals a graphic stored in our System table, but only when a cancel button is relevant to the current view.
L_lr_Btn_OK – Similar to the Cancel button, but for an “OK” button
R_m_Create– Auto-enters the creation timestamp of the record.
R_m_Modify – Auto-enters the modification timestamp of the record.
R_t_CreateAcct – Auto-enters the creation account of the record.
R_t_ModifyAcct – Auto-enters the modification account of the record.
S_gt_Temp – A global text field to be used in scripts for data entry to be held temporarily
X_sc_Record_Count – A summary field of the count of __P, which effectively counts the records in a found set, sub-summary or related data set

Shortcomings

Still with me?

I fully realize that the current naming convention is a lot to swallow. It seems, on the surface, really restrictive and complex. Let me say, though, that that initial perceived shortcoming is not, itself, why we’re changing. In fact, we found that the convention is relatively easy to teach to a new developer, or an outside developer we’re working with, and that it accomplishes our stated objectives nicely. Once you take a little while to learn it and get comfortable with it, it makes working on our current xBase versions much easier than it would be without such a comprehensive, defined naming convention. We’ve used this in dozens of projects, some quite large and complex, and overall it has worked very well for us, and reduced or eliminated the frustrations of trying to identify and use fields in those databases.

That being said, the initial reaction and resistance to the naming convention is one factor that caused us to rethink the naming convention. After all, a naming convention is only useful if developers are willing to use it and stick with it.

A more pressing issue is that occasionally, users get to see the actual field names, and by and large they are not interested in learning a naming convention. They want names that are at least somewhat similar to the labels they see next to the fields on the layouts. Users encounter the actual field names only rarely, but the current naming convention leaves a bad taste in their mouths. When they want to do a custom sort, export records for a spreadsheet or, less frequently, import records, the “odd” field names throw them off.

In some cases, we’ve dealt with this by creating “user fields”, calculated fields with plain names that equal their less user-friendly-named equivalents. For example, we’d create @Full Name, which is equal to A_lt_Name_Full. As developers, we wouldn’t use the user fields for functions or scripts, but the user could sort or export @Full Name and get the same effect as if they had identified and used the A_lt_Name_Full field. This method was useful, but we ended up creating a lot of extra fields, and had to base those fields on what the users of a given system would “commonly” use for sorting or exporting. This method also doesn’t help at all with importing (you can’t import into a calculated field), but end users are seldom comfortable importing into a normalized database, anyway, so we end up creating custom staging tables and import routines for that purpose.

As an improvement, we’d like to name the fields such that a user could look at a field name and have a better idea of what it is, without having to know the naming convention. Underscores and/or special characters don’t seem too distracting, but the purpose and type codes, and also things like “Primary”, seem distracting and sometimes befuddling to users.

New Extensitech Naming Convention

Here is our new naming convention, which addresses the shortcomings of our current convention while still attempting to address the same concerns.

Keys

The name of the primary key is __ID. This still brings the primary key to the top of the list for easier use in the relationship graph, but rather than P for “Primary”, it’s called ID, which users and other developers can more easily understand. As mentioned earlier, we also made the auto-entry a calculation that derives the TLA from the table name and appends a uuid, so the developer doesn’t need to modify this in every new table. As before, the primary key has the same name in every table.

Foreign key fields are named as they were before, with an underscore followed by a TLA (or, if you don’t want to use three-letter acronyms, you can just use the related table name).

Reserved Fields

Earlier, we made an exception for key fields, so that they don’t follow the pattern of other fields, in the interest of getting them to the top of an alphabetized list and making them easy to find and use. In our new naming convention, we made a few other exceptions for specific fields that we often found ourselves scrolling for in a list.

:Description is now the calculated description of the record, replacing the old D_lt_Primary. The name is easier to understand, and the colon brings it toward the top of the list, right under the keys, so that it’s easy to grab when you want to show the description of a related record on a layout.

:Active is the flag indicating a record is active. It auto-enters a 1. The user can uncheck the field to take the record out of value lists and such. As developers, we can easily script searches to only find active records, or have relationships relate only to active records.

Prefixes

For some fields, it is more important to have like fields listed together than it is to make them readable. For these, we will now prefix the field name with a special character. The special characters will be less of a distraction than the former purpose and type codes, but still group these fields together in a list.

| – (pipe character) for fields that are about the record itself, and not the data in that record, such as creation and modification information

@ – Fields that are only used on layouts, as part of the user interface

Field Names

Some of the field types are best addressed by including specific words in the field name itself, rather than adding any special characters or codes. The name of the type is (or can be) a natural part of the field name.

Dates, Times, Timestamps and Global fields should include “date”, “time”, “timesamp” and/or “global” in the field name.

Container fields should include the word “file” in the field name.

Suffixes

The developer needs to be able to spot some other types and purposes by just looking at the field name, but some of these types and purposes feel unnatural as additional words in the field name, and shouldn’t affect sort order like Prefixes do. The following special characters should be appended to the field name, where applicable, in the following order:

? – Boolean flag

# – Number field, or field with a numeric result

~ – Calculated field

! – Summary Fields

Thus, a field for whether a rental tenant has pets might be called “Pets?”. A calculated field, which will be 1 or 0 depending on whether something is completed, might be called “Completed?#~”.

Examples from the template table

Examples will again be useful to help you understand the practical application of the naming convention. To that end, here are fields that you would find in our “ZZZ__Template” table. (Again, I’ve added a few extra just for better illustration of the convention, and removed a few that are xBase-specific and would require a long and distracting explanation.)

Note also how the order has changed. The fields a developer commonly uses are all up at the top, and they are also not mixed in with the fields a user would commonly be using for sorting and exporting. Also keep in mind that with both these functions, a user will start out looking at fields on the current layout, so not all of these developer-specific fields are likely to be included in the list they’d see by default.

__ID – The Primary key, which auto-enters the table TLA and a serial number.( In our current template table, this field is called “__P_change_me” so that developers remember to change the TLA in the serial number entry. In our new version, this won’t need to be updated, since it’s an auto-entry calculation that derives the TLA from the table name and appends a UUID.)
_1 – A calculated field equal to 1. This is often useful for relationships to flagged records
_CON – (not actually applicable to a template) This would be the foreign key to relate to a Contact
:Active – A Boolean number field, which auto-enters 1, indicating that the record is active. A user can uncheck this field to keep the record from showing up in standard searches, value-lists and such.
:Description – A calculated field (which needs to be calculated for each table once fields are added) that a user can look at to have an idea of what the record is about.
@Btn_Cancel_file– A calculated container showing a cancel button. The calculation equals a graphic stored in our System table, but only when a cancel button is relevant to the current view.
@Btn_OK_file – Similar to the Cancel button, but for an “OK” button
|Count! – A summary field of the count of __P, which effectively counts the records in a found set, sub-summary or related data set
|Creation_Account – Auto-enters the creation account of the record.
|Creation_timestamp– Auto-enters the creation timestamp of the record.
|Modification_Account – Auto-enters the modification account of the record.
|Modification_timestamp – Auto-enters the modification timestamp of the record.
Notes – a text field for general notes

Things left off

Note that there are a number of things addressed in our current naming convention which are left off in the new convention. They are either assumed unless otherwise specified, or their usefulness was deemed insufficient to warrant special codes or characters. Depending upon your own practices, some of these decisions may warrant review.

A field is assumed to have the purpose of attribute unless otherwise specified. This relieves us of having to specify the purpose for the vast majority of our fields.

A field is assumed to be text unless otherwise specified by a word in the name, such as “date”, a special prefix (“|” for a record-related field) or suffix (“#” for a number field). Note that number became a suffix, rather than a required word, because it’s a frequent field type and “number” would be cumbersome, but also because some field names should include the word “number” for understandability, even though they aren’t numbers, like phone numbers and social security numbers.

The specific type of summary field is left off because it’s so often total, and because other types, like average, should be evident in the field names themselves.

Script-related fields are not identified because in our own practices, we have a single repeating field in our one-record System table that we use for this purpose. We do this rather than creating one or more fields in every table. You can reference and use a global field from unrelated tables, anyway. We can simply call that field in System “Temp_Script_Global” and its purpose and type will be clear.

A review of the results

We are just now beginning to implement this new naming convention in the newest version of xBase, which is under construction. So far it seems quite easy to use, and accomplishes our stated objectives.

Key fields, and fields commonly used across all tables (like :Active and :Description) end up at the top of our field list.

Layout fields used only for the interface, and summary fields, group together and don’t get mixed with the others.

A developer who understands the naming convention can determine what to call a new field, and can make a reasonable guess as to what an existing field should be called when he or she is looking for it.

For an end user, we expect that the field names will be more approachable and understandable. Key fields remained cryptic, so even though they are at the top of the list the user won’t get them mixed up with the fields they are likely looking for when sorting or exporting. Layout and summary fields, again close to the top of the list, are easier to understand and aren’t mixed in with the fields they are likely to be sorting or exporting.

For the developer, it may actually be a little more difficult to make the naming convention second nature, since the convention is applied in only some cases, and the naming and special characters are less consistent across field types and purposes. On the other hand, the developer should find the convention more comfortable because it doesn’t “feel” like a whole new language.

Overall, we think the new convention will be a more satisfying solution to the issues that a good naming convention addresses, both for developers and for end users.

Conclusion

Congratulations on making in through a long (and admittedly, sometimes boring) post!

My intention here was not to convince you that our naming convention is the best and everyone should use it. Of course, you are welcome to use either our current or new convention in its entirety if you like it, but that’s not the point.

The point is that if you are going to develop databases that are large, complex, and/or will be used by multiple developers, and especially if, like us, you plan to create and modify a large number of databases, a naming convention is an important part of your design process.

Your naming convention doesn’t have to look anything like ours, or emphasize the things that we emphasize. However, as developers who may end up someday working with you, or working with a solution you built, we certainly hope that you give some thought to making your field names useful and consistent.

Hopefully, by taking you through the considerations we addressed in our current naming convention, you’ve gotten some ideas about how to make your own naming convention better. Also, by showing you how we adapted our current naming convention to our new one, you have some inspiration as to how you can take ideas from other naming conventions and apply them to your own, even if you don’t want to adopt someone else’s whole convention.