Page History

Field ordering and organization within Manage Database

The standards documented here do not use any special field prefixing of cryptic or shorthand characters. You won't find a prefix such as gkt_SomeField, where gkt may stand for Global Key Text field. Instead, these standards focus on descriptive field naming with implied use or type. For example, customerPicture or productImage clearly indicates the field type of container. These standards do not enforce (but may suggest) the use of certain keywords, such as Picture vs. Image for field names. For field types, such as key fields vs. globals, you will find strict guidelines.

Key fields

• Primary keys in each table are simply, and always, named "id" – Fields are identified by FileMaker inherently by the Table » Occurrence::id nomenclature. (e.g. Schedule » People::id)

• Foreign keys within any table are named using an underscore followed by the exact same Title case version of their table name. (e.g id_Tablename).
  ^{The underscore within the field name distinguishes a foreign key from the camelCase field names}

  id_People	good
  f_personID	bad
  id_Event	good
  kf_Event	bad

• Constant keys should be prefixed with the keword "key_" and follow the format used by GLOBAL fields. Because these types of fields are not primary keys, yet they are used for utility relationships, they can be interpreted as foreign keys of a special type.

  key_BOOLEAN	good
  zkf_ConstantOne	bad
  key_ALLCUSTOMERS	good

  ^{Note: try to name the key functionally relevant and easily identifiable, for example key_ALLCUSTOMERS may indicate an unstored constant calculation derived from another relationship using a List() function for the purpose of a cartesian relationship}

  
  

• Multi-key key fields are a feature unique to FileMaker Pro. They provide a feature which allows for the implementation of pseudo-schema (described later) or for a purely utilitarian feature of being able to drive data display within FileMaker Layouts - known as the UI of a solution. Because these multi-key values, which are simply what FileMaker calls a List (see the List() function) can be anything from a list of serial number values to UUID values to a list of concatenated keys, it becomes useful to easily identify such use of these fields. The two prefixes used to identify these fields are idList_ and keys_. Choosing one over the other can be a bit subjective, but follows these guidelines. If the field storing multi-key values relates to the structure, commonly known as the schema, for a solution and uses id values, then idList_ is preferred. The keyword "List", within idList_ implies the field stores multiple values. When the field is used for the purpose of utility, then the prefix keys_ is preferred. The keys_ prefix implies multiple values due to it being plural as opposed to the Constant key_ prefix above. When used in the context of a relationship, the trailing portion of the field name is typically the Tablename to which the foreign keys are being related to. 

  idList_Customers	good
  keys_Participants	good
  keys_INVOICES	good

  In the above examples, idList_Customers might be used to maintain a pseudo-schema relationship between a customers table and an Invoices table. This type of use would be in place of a normal Join table and is a subjective decision based on the criteria and needs of the solution. keys_Participants may represent a record local list of id values creating a many-to-many relationship from one record to many records within a Participants table. The keys_INVOICES field is clearly a global field used for the purpose of holding any number of multi-key values which may be id values, serial values or concatenated keys for the purpose of data display within a portal. 

   

  Info
  title Pseudo-schema
  When considering the principles behind database normalization, a typical approach to structuring data is to use a join table. However, in the world of FileMaker, a relationship between two tables can take advantage of a feature known as a multi-key field. This field can store more than one single key value. As stated on this web site, this feature is somewhat unique to FileMaker and does not follow the principles of data normalization. Strictly within the context of FileMaker, however, using this method of relating data is perfectly viable and is being called pseudo-schema here because of its uniqueness. The most common use of multi-key fields is for the purpose of driving data display within the user interface. It is possible, however, to maintain, as part of the solution schema, multi-key values within fields for the purpose of data structure. Use the above guidelines to direct your use of these prefixes.

  
  

• Shared keys which are fields that are foreign in nature, yet do not correlate to any one specific table, should use a fixed name within all tables throughout the solution. These standards suggest the use of the name id_Any where Any can be the primary key from any table. One example of a shared key would be a foreign key within a shared Notes table. Where the id_Any field could potentially hold the primary key value for tables named People, Events, Invoices, Estimates, etc. In this scenario, the Notes table is used to store notes for many other tables.

^{Developer note: the use of Shared keys is typically a practice which utilizes additional predicates within relationships, such as a noteTable field used to qualify which table a specific note key belongs to.}

Data fields

• Data fields, those used for solution data, use lowerCamelCase starting with a lowercase letter. Starting with lower case differentiates the string from function calls which are Title Case.
  ^{No spaces or underscores}

  customerFirstName	good
  Customer First Name	bad
  CustomerFirstName	bad

Summary fields

• Summary fields are prefixed by the word "summary" and their return type before their identity. This facilitates quick and easy recognition.

  summaryCountMembers	good
  summaryMemberCount	bad
  memberCount	bad
  summaryMaxId_event	good
  summaryMinAmountSpent	good
  summaryAvgPaid	good
  summaryTotalUsers	good
  summaryStdDevWhatever	good
  summaryFracTotalMetrics	good

^{Note the use of the underscore above, it indicates a summary of a foreign key (covered below)}

Global fields

• All GLOBAL fields are UPPERCASE and one single word. This easily differentiates global data from all other types.

  CREATE	good
  EVENTFILTER	good
  CUSTOMERSEARCH	good
  CLIPBOARD	good
  g_GlobalFieldName	bad

Unstored calculation fields

• Unstored calculated fields are prefixed with the word "unstored," and that prefix is separated from the rest of the field name with an underscore. Always try to use descriptive adjectives or actual nouns to help identify the field type (e.g. Count in userEventCount indicates numerical data type)

  unstored_userEventCount	good
  uc_EventCount	bad
  unstored_invoiceStatusImage	good
  %invoiceStatus	bad

  A discussion of the factors that lead to this decision is available on the fmstandards Google Groups forum.

Default Auto-Enter fields

• Default auto-enter options provided by FileMaker are grouped by their prefix and identified by their respective name. These include the following defaults

  No Format
  creationDate creationTime creationAccountName creationTimestamp creationUser modificationDate modificationTime modificationAccountName modificationTimestamp modificationUser

  ^{Note that not all fields are required. These are the suggested names.}

• Two additional fields suggested by these standards are creationHostTimestamp and modificationHostTimestamp as optional default fields. The method of adding these fields to your database are covered under the Host Timestamp fields section.

  No Format
  creationHostTimestamp modificationHostTimestamp