Skip to end of metadata
Go to start of metadata

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.

Field sorting order in Manage Database > Fields dialog

Icon

Please note that the standards covered within this section assume you will be organizing and arranging your Relationship graph based on an explicit and controlled field order. This is accomplished using the View by: custom order setting within the Fields tab of Manage Database. This should be set before the file is hosted. See the pre-hosting checklist for more information.

Switching to "field name" or "field type"

Icon

Using the custom order setting comes with the condition that you ONLY drag your fields into the explicit desired order when the View by: setting is custom order. If you temporarily switch to field name order and then drag one single field, you will replace your previous custom order sorting. YES, this may require forming a new habit, however, the advantages to a known and fixed field order lends to quicker development time based on positional familiarity. A good example would be having all apps on an iPhone just rearrange themselves based on alpha order. Your mind maps positional locations of things and this makes things faster.

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

Serial Numbers vs. UUIDs

Icon

While not a required standard, the majority of contributors to this standard suggest using universally unique identifiers (UUIDs) for primary key values. This avoids some common issues with FileMaker's default serial value option. See the Best Practices for key values section for information about using UUIDs.

  • 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_Customersgood

    keys_Participants

    good

    keys_INVOICESgood

    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. 

     

    Pseudo-schema

    Icon

    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

Underscore allowance

Icon

Based on certain situations when global fields do not read well as a single word, you can optionally using the underscore as part of these standards. So GLOBALLANGUAGE would become GLOBAL_LANGUAGE. However, it's suggested you attempt to alter your word choices to attempt to use the single word approach.

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

    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.

    creationHostTimestamp
    modificationHostTimestamp
    
  • No labels

53 Comments

  1. Anonymous

    Hi,

    Any suggestion for the conventions be used on fields such as last modified time, last modified user etc?

    Under your note, I think we should use 

    %recordCreationTimeStamp

    %recordModifiedUser

    etc

    Thanks,Oliver

    1. Creation/Modification detail fields are more than just UI fields, I think, so the % prefix convention doesn't fit. I would just use

      creationTimestamp

      modificationUser

      etc.

      1. Sorry Jeremy, I don't agree on the order of words. If we are to treat field types/functions as prefixes we should be consistent. Non-mutable utility fields should follow logic above in respect to the initial purpose (auto enter (Create/Update): date, time, timestamp, account and user) events of the record at hand.

        Secondly all action verbs should be in past tense as it's impossible (in our interaction with the UI) to have any existing record operations take place after Get ( CurrentTimestamp ): the moment in time an operation is taking place on a field. If we could predict the future operations on a record that would be fun though!

        Personally, my preference is to prefix these with "z" for better alphabetical sorting flexibility in Define Database to keep them segmented from table data fields, but that's not the point - there are legitimate reasons for each as stated elsewhere.

        See example below: Example names directly correlate with the ten possible auto-enter options available in "Define Database -> Fields -> Field Options -> Automatically enter data into this field":

        I rely on asking actual questions for this reasoning: "Who was the last person to modify this record?"

        I hear that more frequently than "For this record, who modified it last?" in day to day interactions with users.

        Thanks,

        --Perren

        1. Hey Perren, I think Jeremy was just making a reply in respect to treating the auto-enter fields as UI/Utility fields vs. a Data fields.

          The date/time/user fields are data fields, at least in my eyes, but I hadn't considered them following the same naming standard outlined for Summary fields.

          I don't see why they shouldn't though! We can put the documentation (once a consensus is met) within the Best Practices and list the "preferred method" here. For example

          creationAccountName
          vs.
          accountNameCreatedBy
          vs.
          autoAccountCreation

          all of which being perfectly viable given developer's tastes

          One thing I've been meaning to comment on with one of Jeremy's other comments was the issue of using alpha sort order in the Manage Database fields list versus the custom option. When you use the custom option, you always control the field order and it never changes unless you drag items to change the order. This lends itself to a high degree of organizational control, both within the graph and when viewing fields. While I like the above list of auto-enter fields following the same as the summary fields, I'm wondering if we might be able to group them better with a prefix - given that they're all some what the same class.

          I was thinking auto but this may be too verbose and it may be that the simple way is better. I like

          accountNameCreation
          more than
          autoAccountNameCreation

          However, on the side of making things easier to remember, it's much easier to remember or start typing and hit the respective field you want when you have

          creationDate
          creationTime
          creationAccountName
          creationTimeStamp
          creationUser
          modificationDate
          modificationTime
          modificationAccountName
          modificationTimeStamp
          modificationUser
          

          The benefit being that you have now grouped/classed them together and you can identify them.

          Which reminds me... we should put in the best practice of using the Get ( CurrentHostTimeStamp ) as a suggested auto-enter field for all tables.

          creationHostTimeStamp

          note the discrepancy where FileMaker uses TimeStamp in the function name but uses Timestamp in the Define fields and auto-enter options

          1. Well put Matt,

            I guess my reason for the gripe is how FM files preserve the preferences for the field sorting (creation, name, custom) in local vs. hosted environments.

            From my memory adjusting the sorting option requires dropping a server/client hosted file into local mode for the changes to stick.

            My relevant example (and current motiviation) is that as a developer I walked into a server/client hosted solution and by way of business rules I only get opportunity to drop the files to local execution once a month at best.

            Do changes to custom field ordering retain when in hosted (client/server) mode? If so my point is moot, otherwise it's a relevant obstruction to getting things done on a day to day basis while not being able to change the preference in the local mode.

            I'll do some testing in the AM to find out how the preferences behave with custom order preference set in a hosted file.

            Otherwise grouping by creation / modification prefixing is fine by me, but I'd still like to see them somehow grouped away from other user input data fields in some way for ease on my users.

            --Perren

            1. Yeah, I don't have a server setup with FMP 11 currently, so it would be good to test with setting the file to custom for viewing fields. My guess would be that it should preserve this setting when opened a second time from the server.

              FileMaker loads a catalog of the fields being edited and doesn't write the catalog back to a served file until you close the dialog. I would assume that it's going to write the fields back in the order you've set if the setting is custom vs. creation order, field name or field type.

              1. OK, here we go - I worked up the problem that just drives me bonkers working on production client/server environments where I can't drop served files except for after business hours. Long and short is we should either adapt convention for not being able to workaround constraints in real life situations or really look at our opening premise of conventions for hosted solutions in a client/server framework.

                Test Log:

                Premise:

                FileMaker files hosted by FMS will preserve changes by FMP clients to the settings found via the menu: File > Manage > Database > Fields > View by drop down menu option. 

                Test Conditions:

                FileMaker Server 11 (11.0.2.217)FileMaker Pro Advanced 11.0v2 (06-11-2010)

                Mac OS X 10.6.4 Build 10F569

                Test Steps:

                1. Open hosted file using locally defined [ Full Access ] user account
                2. Open Manage Database
                3. Switch to View by: creation order
                4. Click OK button
                5. Re-Open Manage Database
                6. Quit FileMaker Pro Advanced Client
                7. Re-Open hosted file using locally defined [ Full Access ] user account
                8. Open Manage Database

                Observed Behaviors:

                1. Immediate view change after step 3.
                2. View change persists after step 5.
                3. View change reverts to initial settings after step 8.

                Conclusions:

                1. FileMaker file based settings, in this case, view preferences found via Define Database cannot be changed while the file is hosted.
                2. In order to change file based settings it is required to open FileMaker .fp7 files in a local user environment for changes to persist.

                Notes:

                1. This is consistent with Global Field behavior - values of Global Fields are not preserved between client sessions. They are considered to be session dependent.
                2. This is consistent with File > Sharing > FileMaker Network... settings. In order to change these preferences it is required to open the .fp7 file in a local user environment.

                1. I should have been more clear about the following statement...

                  My guess would be that it should preserve this setting when opened a second time from the server...

                  What I was meaning was...

                  My guess would be that it should preserve field reordering when opened a second time from the server provided the View by setting has been set to custom before hosting.

                  That's the premise, the setting for View By in fields dialog would be set to custom as the default. This is what affords the greatest degree of organizational control.

                  The comments I made about the catalog saving the changes was testing to see that field reordering, under the custom setting, would persist after closing and reopening the file over the network - not that changing the setting would persist. (sad)

                  1. I'll spare my <insert colorful language here> but wow!

                    I thought I was stuck working against alphabetical language constraints for lack of better options working as a client connected to a server!

                    For sure I owe you a drink for that hint, but at the same time this prompts the need for  a Best Practice > [File Preferences] section to account for these nuances in preparation for client/server deployment...

                    Now I am compelled to go into the office this weekend and fix some stuff. :)

                    --Perren

                    1. I did open a copy of Advanced and treated as a peer-to-peer setup. Setting to custom before hosting the file does retain both the setting and any field reordering when opened and closed by the client.

        2. Anonymous

          Having naming rules for non-mutable fields could be helpful. But more important is a distinction between system fields and fields for project data. The creation/modification fields would belong into the group system fields, but others belong to them as well. I guess, Perren, you go into the same direction, naming these fields with a prefix "z". I use an underscore ("_") because the z distracts when reading the code. Also, this would put them away from the other fields (on top of the list, when sorted alphabetically) and they will not mingle with regular data fields starting with a "z".

          Arnold Kegebein

  2. Anonymous

    Thanks for your suggestions.

    Matt, i quite like your suggestion:

    creationDate

    modificationDate etc

    As this make developers easy to recall and reference, if we can put up a convention standard on this. It would be great.

    Oliver

  3. Anonymous

    Hi Matt,

    Another comment is on global field, I appreciate on using all CAP for global field but this really make the global field very difficult to read. Could we suggest to use "_" to separate the word?

    Such as CUSTOMER_SEARCH instead of CUSTOMERSEARCH

    Any comment?

    Thanks,

    Oliver

    1. I do see some merit to this. The all CAPS does set the style so you can tell it's a global-based element. Just today I hit an issue where I was using $$APP.STARTUP.HELPPAGE and it just looked odd to me seeing HELPPAGE - it almost read "hell page".

      However, in that case it was a global variable, which could be identified by another level or me "just being ok with it" even though it doesn't read so smoothly. So it became $$APP.STARTUP.HELP.PAGE.

      Some times, it will just be a matter of re-thinking your choice to make things read better.

      I'll add an allowance for using the underscore on GLOBAL_FIELDS, but I'll leave the suggested standard as using GLOBALFIELDS. It doesn't seem like it will impede things with regards to the only other use of the underscore in foreign keys.

  4. Anonymous

    Conditional modification information

    I guess this belongs into best practices are a different section, but I like to mention it here.

    In my recent projects I implemented conditional modification information. Creation information is stored using FileMaker's auto-enter function. But auto-enter modification information is only applied under a specific condition, e. g. a special value in a global field.This requires calculated values with a reference to an additional field that would act as a trigger field.

    This allows handling records by the developer in special cases without loosing the original information who and when the data record was updated by a regular user.

    Is there any merit in this idea to include it in the coding standards or best practice? Or is this feature too special to be included?

    Arnold Kegebein

    1. Beyond the most basic auto-enter modification details, I think an audit log (in a dedicated table) would be a more robust best practice than straining the modification conditions.

      1. Yeah, let's put audit logging within Audit logging

      2. Anonymous

        I do not mind having an audit log for advanced solutions and putting it in best practice. But with an audit log you do not need creation/modification fields.

        My suggestion was not for common operations. It is for administrator/developers only in situations where they have to modify data (e. g. in case of changes in field structures that requires adjustments in the field content). Their interaction would change modification information that might be destructive without conditional modification information.

        But I agree, this feature is not basic function. It ranges between the build-in FileMaker auto-enter feature and an audit log solution.

        Arnold Kegebein

        1. I see what you mean. I suggest we put the main content within Best Practices and simply put a note or reference within the Coding Standards

  5. Anonymous

    How about a new case version which doesn't have a name yet - so maybe the double back flip will do

    $$APP.STARTUP.HELPPAGE

    becomes

    $$APP.STARTup.HELPpage

    reverse capital camel word case???

    John Renfrew

    1. Inventive, but playing with alternating capitalization schemes to differentiate between different types of elements is a SlIpPeRySlOpE. Where does it end?

      1. Anonymous

        Agree with you here Jeremy, needs to readable as well as distinnct

        Tim Anderson

  6. Anonymous

    I really like keeping the user fields away from the developer fields, with the fields sorted alphabetically.

    I am still using the convention I found suggested years ago - sorry I can't remember who suggested it - of

    • zk prefix for key fields
    • zc for control
    • zi for interface
    • zf for filters
    • zl for logs
    • zs for summary

    This makes it really easy to find fields.

    Also can I suggest that fields in the same context have the same prefix such as

    • nameFirst
    • nameLast
    • addressTown
    • addressPostCode

    which makes it much faster when show the field picker.

    Tim Anderson

    1. I can appreciate the usefulness of "keying" fields to represent their attributes. However, in favor of "easy reading" code, I personally can't get behind shorthand keys because it's both cryptic and "noisy" to look at.

      True, even writing Objective C for the iPhone or the Mac has prefixing like NSBlahBlahObject (which is the object/element type - not the name of your variable), but using plain english (or your native language) is more preferable. For example, I would rather see textFirstName or summaryCustomerCount than tnk_CustomerName. Where prefixing text to firstName is superfluous given that I can't think of a first name that is anything other than text. (wink)

      The goal for most fields is to use decipherable naming instead of explicit keys. Again for example, I think it's pretty easy to interpret what the field types are when you read firstName, invoiceAmount, userImage and summaryTotalAttendees as text, number, container and summary.

      With regards to prefixing, this is something that can be suggested in Best Practices and not something you would want to enforce in Coding Standards. You can always write up suggestions for either section in the Proposals section.

    2. I agree with Matt. shorthand keys are cryptic and noisy. Some developers I know use pre- and suffixes to more or less extend. I myself experienced with different prefix systems. In the end I only use underscores to differentiate between system and data related objects, and plain text prefixes (filterNames, sumTotalAttendees). For this Coding Standard I started to skip additional dots (filter.Names) and might use less abreviation (summaryTotalAttendees instead sumTotalAttendees).

      The Coding Standards here follow two rules: "easy reading" and "easy developer support", meaning a different developer will be able to support your database faster, without learning a cryptic naming system. The same cryptic name could mean different things for different developers. You use zi for interface and zf for filter. Other developer may interpret these as integer and float.

      The other idea, a text prefix to keep fields in the same context together (addressTown, addressPostCode), is something I using myself. It might slightly decrease readability because of the uncommon order of word parts, but increases understanding of context and helps finding fields within the same context.

  7. Anonymous

    Would it be possible to add an allowance for prefixing IDs and Keys with an optional underscore?

    How about an allowance for prefixing User Interface fields with "zz"? The percent sign would be acceptable though.

    The reason for this is to keep IDs and Keys at the top of the list and user interface fields at the bottom when the list is sorted alphabetically.

    Hal Gumbert, CampSoftware

    1. Hey Hal (or anyone else reading this thread who is currently using prefixes)

      Typically, the primary reason for using prefixes is to attain a more usable sorting order. This is because it applies an alphanumeric constraint when the View by: setting (upper right hand corner of Define Database) is set to field name.

      The solution to this is to set the "View by:" to custom order. Leave it on this setting when uploading to FileMaker server per Pre-hosting Checklist.

      The custom order setting will retain any order you set for fields. Using the standards documented here. I personally order in this fashion.

      id
      id_Foreigntable
      key_CONSTANTKEY
      dataFields
      summaryFields
      %uiFields
      GLOBALFIELDS
      

      Putting your fields in groups, when set to custom order and identified by their main class makes the field list more "digestable".

      The custom order setting can be found in Manage Custom Functions too. It's one half of the answer to applying structure to the listed field order in Manage Fields. The other half is using descriptive keywords - which are discussed in other comments above.

      I'll make a note on the actual page about using the custom order setting.

      1. Anonymous

        I see where you are coming from regarding using a Custom Order, but that seems like more work with no added benefit unless I don't understand this completely.

        The proposed method already uses prefixes of 'ID', 'Key', '%', and 'summary' to obtain a sort order, although the actual sort would be done manually via the custom sort. 

        I'm open to change, but it seems easier and faster to use an alpha sort. Only data and and global fields end up with no prefix, which is a good thing. It seems that the end goal ends up being the same, except that the fields have to be sorted manually. To achieve automatic sorting, all that would be needed would be to add underscores to IDs and Keys.

        Is there something here that I'm not 'getting'?

        Hal Gumbert, CampSoftware

        1. The prefixes of id, key, % and summary are for the purpose of classification only - in order to differentiate between field types.

          They're not intended to be used for sorting at all. It's the use of the custom order sorting option to enforce a strict field ordering (based on how you wish to see the fields grouped). This does come with a caveat however. I guess I should put that on the page too. See warning below.

          A side benefit is that this also maintains any relative positioning of table occurrences on the relationship graph (so relationship lines won't get crossed - unless you want them to). When a file is hosted, it retains the custom order setting and the options of field name and field type can be used arbitrarily while developing (for viewing ONLY). There are times we all want to see all calcs or all globals grouped together. Just don't drag fields when the order doesn't "look right" because it's likely sorted by field name or type.

          Switching to "field name" or "field type"

          Icon

          Using the custom order setting comes with the condition that you ONLY drag your fields into the explicit desired order when the View by: setting is custom order. If you temporarily switch to field name order and then drag one single field, you will replace your previous custom order sorting. YES, this may require forming a new habit. However, the advantages to a known and fixed field order lends to shorter development time based on positional familiarity. A good example would be having all apps on an iPhone just rearrange themselves based on alpha order. Your mind maps positional locations of things and this makes things faster.

          1. I haven't been able to get behind custom sort orders over alphabetical sorts; I just can't bring myself to do it. This is what has happened to me, far too many times: 

            My new client first needs me to fix this one thing in their system, which I've never seen before now. In the process of investigating the problem, I come to a field on a layout. I note the table and the name. I open Manage Database to check out the field definition, I find the table, and now I have to read the field names one-by-one (slow!) rather than scanning the list because it's in a custom sort order. The field I wanted was number 115 in the list.

            While scanning the list, I found the other field that should have been on that layout instead of 115th one. It's #43 or #87; I can't remember, and neither can anyone else. I go back to the layout, back to the field, open Specify Field, and I see the same custom sort order. Where was the field I wanted, again? A few more fixes like that, and now I'm not allowed off the Interstate in South Carolina anymore.

            Custom sort orders are only navigable if you're familiar with them. Part of the goal of these standards is to encourage developers to structure their solutions in a way that is helpful for developers who will have to figure out what we did in the future, including developers who don't get the chance to ask us about it, and even including developers who aren't aware that this standard exists. Say what you want about the grammatical constructions that we sometimes resort to to force an alphabetical sort to group related fields, but nothing is navigable — and therefore helpful to future developers — like alphabetical sorts.

            The trick is to make sure that your resulting field names remain easily interpreted, despite whatever prefixing you're doing to group related fields. For example: nameFirst and nameLast. It can be easily readable if constructed with care.

            Using specific reserved keys in field prefixes is clearly something we're used to — "id", "key", "summary" — and we're even accepting cryptic symbols as prefixes — %uiFields, #AssignScriptParameters, ~PrivateCustomFunctions, @Developertables, even $variables --- as long as there aren't too many of them to remember. Just make sure I can read it, even if I've never seen a line of your code or these standards, and I will always appreciate an alphabetical sort.

            Within the FileMaker community, the underscore "" and "z" prefixes are so deeply entrenched that these standards will do little to change those prefixes, even among the faithful. They don't even consistently represent any meaning, only "I thought these belonged in a separate group at the top or bottom [in an alphabetical sort]." We all get it anyway. It _is odd that these are mainly ordering aids, and only inconsistently have semantic meanings. A big part of what standards do is to notice what's kindof working for people, and make it really work for everybody. Most of the FileMaker code I see uses underscore "" or "z" prefixes, some if it even uses both. They're not going away, and they _will co-mingle with names inspired by this standard; we should at least give them standardized uses. Underscore prefix for keys? "zz" prefix for UI? Recognizing and standardizing meanings for those prefixes does mean the standard would have to allow for alphabetical field sorting, though, since those prefixes don't really make sense in any other context. Well, darn. It's a painful price, but I'm willing to pay it ;)

            1. Underscore prefix for keys? "zz" prefix for UI? Recognizing and standardizing meanings for those prefixes does mean the standard would have to allow for alphabetical field sorting, though, since those prefixes don't really make sense in any other context. Well, darn. It's a painful price, but I'm willing to pay it (wink)

              I don't know though. Just because something is "entrenched", "has been used before", was even "thought about" or whatever phrase we want to use, doesn't mean we have to keep using it.

              I don't think part of our goals here are to "make it comfortable" to adopt these standards. I think they should stand on their own. The merits and usefulness of the standards should define their direction.

              When I look at the use of "zz_" just to get something to sort at the bottom of a list, I see a workaround attempting to overcome a shortcoming in FMP. Granted, our history with FMP is littered with examples where we're trying to make it accommodate shortcomings in areas such as organization, but the benefit of the standards should be greater than the pain to adopt them.

              The difficulty I have with starting to multi-purpose certain characters for the sake of utility is that it dilutes the meanings of the characters. If you see the underscore currently, it's either a foreign key or someone didn't want to take the time to reword GLOBAL_LANGUAGES to something else that could be a single GLOBALFIELD (because we made the allowance there). This would be yet another allowance - therefore further dilution of meaning.

              When we ask questions like "Can we do this or change that?", are we asking because it's what we currently do? - or because changing our habits or methods is painful?

              I can see the value of your example about hunting for fields. In fact, I'm not throwing field name sort order out the window. I know what you're talking about when searching for a field. The key is, that switching to field name for the purpose of scanning and locating is perfectly fine. In fact, you can find the field you want, select it and switch back to custom order in order to find its relative placement (therefore becoming familiar with the solution - which is typically desired - not just fixing the immediate issue [unless that's your job])

              When hunting for a field, you also have the limited feature of being able to type in the first few characters to jump to that field - and repeating those steps for the next. True, not super useful beyond a handful of fields, but it's there.

              When it comes to prefixes like the %. It was chosen because of it's strong contrast to match the contrast between data and ui/utility fields. I'm personally not stuck to the character as much as I am the contrast within code. It'd be good if we could think on this particular topic because currently the underscore, as used with foreign keys, is very evident when looking at relationships in the graph. I'd be concerned that diluting it further would increase potential confusion.

              I'd be fine if we came up with a prefix (word or character - no shorthand) that didn't compete with fields or other in-use characters which also facilitated a greater degree alphanumeric sorting in the fields list. Why don't we put our collective minds to it?

              1. I did not mean to suggest that standardizing underscore and "zz" prefixes would help the standard convert people who may not use it otherwise, but that the prevalence of those prefixes indicates that so many people find it so useful. We want to avoid walking off a cliff with the rest of the lemmings; but if all the cool kids are doing it, there may be a good reason for it beyond the social value of fitting in. The cool kids do tend to do better in life.

                1. Hmm...I don't know if I buy the "all the cool kids do it" logic. I didn't fully understand the nature of selecting a sort order in a file while in single-user mode and what the resulting behavior / options were once that file was placed on a FM Server.

                  Since Matt first wrote the convention to use custom order and my subsequent discoveries I've employed it on our in-house solution. Yes, it takes a while to get used to, but it's also handy to help guide users who may have script access w/out full define database to make sure the commonly used data fields are more prevalent at the top of a list of field names vs. relying on alpha sorting. The up front time / effort to get it just right is a pain - I will not discount your point there, but if it's a file you're going to be living in for some time why not make it "just right"? If you're doing a one time touch up for a client whom you'll likely never do any major work for in the future of course it's not worth the effort to go through the pain.

                  In my case I can't just go start changing field names left and right due to external dependencies outside the scope of this topic, so my next best defense is to put fields in the order I want my users to discover them. This way if I depreciate a field I can bury it at the bottom of the custom sort order until I know I've removed all dependencies and folks are less likely to use it in a script behind my back until I've fully killed it off.

                  Consider me convinced of the true utility of custom ordering at least until FMI decides to drastically alter behaviors or allows differing sorting options in the field chooser at the scripting / layout levels.

                  1. My "all the cool kids do it" rhetoric is aimed more at the underscore and "zz" prefixes than alphabetical sorting in general.

                    Our different backgrounds as developers are clearly a strong influence on our opinions. As a consultant, of course I want my clients to keep bringing their systems back to me for more billable work. But I'll work on something else between one feature for a client and the next, so I have to deal with some routine unfamiliarity with technical details of a system even with long-term relationships. The incentives for me are to invest in what makes me effective in unfamiliar territory.

                    I also inherit a lot of systems from other developers following unfamiliar conventions, which is sometimes harder than others. I know that sometimes I'll be unavailable for a client, so another developer with The Support Group may have to take over, or (heaven forbid!) some other developer down the road. What can I do to help that developer hit the ground running? Alphabetical sorting is one of my many little ways to pay it forward for whoever that winds up being, whether they follow the same conventions as me or not. It's a succession security issue, one that can be based on familiarity in an in-house situation with multiple developers, or a developer and that system's power users.

                    Another thing that occurs to me is that grouping related fields via alphabetical sorting encourages developers to build some of the semantic structure of the schema into the field names, i.e. nameFirst, nameLast, locationLatitude, locationLongitude, summaryTotalCost, summaryTotalPrice, etc. That feels so wholesome to me, I want to make it into a blanket so I can snuggle with it.

                    1. Yes, I'm all about a comfy blanket too!

                      Here in lies the differences of different types and stages of development as well as the "ownership" of the solution and who decides conventions therein:

                      1. Integrating third party solutions where a developer has already determined alpha sort is what's going to happen. We're stuck with what's in front of us...
                      2. Working as a consultant for a client in an existing solution for "triage" fixing. Again, typically we work with the hand (or sort) we're dealt with...
                      3. Working as a consultant for a client developing a solution from scratch. Here we can utilize the conventions outlined for custom sorting...
                      4. Working in-house as an employee in a multi-developer solution. Things get a bit grey here, maybe we can change sorting, but maybe not budge on pre-exsting field naming...

                      While the above conventions mostly apply to situation #3, they are easiest to employ for #s 3 and 4, where not possible in #s 1 and 2.

                      In my current real world experience for being in-house developer with two separate solutions falling into # 1 above custom sorting is still winning. For example I have one integrated "off the shelf" solution where the developers chose custom vs. one that chose alpha sorting conventions. I'll leave names out to protect the innocent, but the third party company that distributed their solution with a custom sort has really saved my bacon in trying to digest their solution vs. the developer that chose the alpha sorting convention.

                      I believe we need to build the convention that covers the widest range of all possible scenarios and works best for the majority no matter the predetermined situation we as the "current" developer enters the picture...

                    2. Another thing that occurs to me is that grouping related fields via alphabetical sorting encourages developers to build some of the semantic structure of the schema into the field names, i.e. nameFirst, nameLast, locationLatitude, locationLongitude, summaryTotalCost, summaryTotalPrice, etc. That feels so wholesome to me, I want to make it into a blanket so I can snuggle with it.

                      To this, I say we start a Best Practices of suggested Field prefixing conventions. Why not get it on the site somewhere? (wink)

              2. Comparing potential characters for prefixes, the shift-[some number] characters (excluding characters reserved for calculations), sort in this order in Manage Database:

                _fieldName //used as prefix separator for id_... fields and readability aid in some GLOBAL_FIELDS

                `fieldName

                !fieldName //potentially confusing for C-family developers

                @fieldName //already used for developer @Tablename

                #fieldName //already used for script parameter custom functions

                %fieldName //already used for user interface fields

                ~fieldName //already used for private custom functions (intended to be called only by other custom functions)

                The accent mark is kinda whimpy. Other characters I don't know how to type on Windows include:

                §fieldName //after !

                †fieldName //after %

                ‡fieldName

                •fieldName

                Weird stuff. Considering the alternatives, I still like the underscore. The underscore does get used in some other contexts, but it's only acting as a delimiter; each of those uses has other much more significant clues to what's going on. There's the "id" prefix, and there's the ALLCAPS typography. The underscore doesn't have any meaning to dilute. It's a workaround to make things more readable, so we don't have idParenttable or GLOBALFIELDNAMETHATSWAYTOOLONG. Using it as a prefix to force placement in an alphabetical sort would be serving a similar function. (It would also sort _id above %uiField, which I like.) Further, if the underscore prefix is just for key (id) fields (generally how I use it), it would just be one more underscore in one of the places that already has them. ("_id" sorts above "_id_Anythingelse" and "_key...")

                I haven't personally used the "zz" prefix in years, but I don't know of any character that sorts after the alphabet. The uses for it have also historically been less well defined. "Everything that isn't a data field" doesn't do it for me. Most of the things traditionally covered by a "zz" prefix do already have other conventions in this standard, I suppose.

              3. Anonymous

                I just found you guys today, but I love the discussions you're having.

                I use Custom Sort. I name my fields naturally and group them in ways I feel are natural, with utility fields at the bottom. I "chunk" the groups by separating them with blank unstored calculation fields whose names consist entirely of underscores. I'll have anywhere between 2-15 fields in a group.

                I find this process to be very helpful in self-documenting. I have at times labelled my groups with all caps unstored calc fields, but I find the groups to be pretty self-explanatory.

                Do most of you keep users out of the Sort and Import dialogs? I generally leave Sort in there, opening up the field list to users so less cryptic is an advantage.

                D

  8. Anonymous

    I was thinking that shared keys would be better to have a description rather than "id_any" because sometimes you might have multiple shared keys in a table.  In a lot of my solutions shared keys are actually global fields that get set with a script.  So to me it would make more sense that since the constant keys are already prefixed with the word key to make them different (ie.  key_Constant) that the shared key should actually use the all caps.  So a shared key might be id_SHAREDKEY.  What do you guys think?  This way you could have as many constant keys and shared keys in a single table as needed and they could both have good descriptions of their purpose. - Tanner

    1. Tanner, you can certainly have as many shared keys as you want. The id_Any was only a suggestion. If you are coding your solution with more of a global based UI where globals are driving your solution logic, then I would go with your suggestions.

      If the key is not global, it would be something like id_SharedNotes vs. id_SHAREDNOTE, where the capitalization lets you know the field is global, yet you can still have a local shared key in the same table.

      If anything it may benefit us to enforce the use of the keyword "Shared" to indicate it's a special type of utility key. Where Any is a bit ambiguous.

      I don't know how common using shared keys is. I use it myself anytime I have utility relationships.

      Should we switch the spec to

      id_SharedKey
      id_SHAREDKEY
      id_SharedKey[FunctionalUse]
      

      Where the first is a common utility field local to the record, the second is a global version and the later is the specification for the case when multiple shared keys are used?

      1. Anonymous

        Hey, Matt, I just noticed this tidbit buried in the middle of the page, while I was scrolling through for something related to the bit I started at the bottom, rather related to this comment (multiple keys to the same foreign table).  

        I prefer "Any" to "Shared" as the former clearly indicates, to my mind, the possibility of any foreign table being linked via that field, while the latter seems to specify a specific constrained relationship without defining what that actually is.  Despite it's somewhat ambiguous nature, I think id_Any is actually clearer (at least to me...maybe I'm unusual in this) than id_SharedKey.  

        With regards to the third item in your list...I think I prefer using another underline to specify the FunctionalUse portion, per my suggestion below.  It keeps the suffix style similar to the prefix and is easier to type (though I grant that's not a huge consideration).  Whichever is settled upon, do you (or anyone else) have any objections to using this same style when defining multiple links to the same table, whether or not it's used with a SharedKey/Any type link or a specific table link?

        1. Anonymous

          And again.  -Matthew

  9. Anonymous

    This is great work, and an interesting discussion.

    For me, how I approach the project really depends how much access a client has to the backend. If they have access to defining fields, then 'custom order' is out of the question, and the old 'z_' kicks in. Also field naming for this type of client, boils down to a couple of rules; 1. all lowercase with underscores between words. 2. stay out of the z's. 

    1. Interesting, I think most of these standards have been taking the approach of focusing only on developers who are familiar with them and who are the primary architects of their solutions.

      In the case of client access, I would certainly suggest some deviations. It may benefit us to come up with some of these alternatives, I've just not been in that situation personally. I know Perren has people working on his files.

      Personally, I would try to come up with some character other than alpha in order to get it to sort to the bottom. We are using the % and that does break out data fields from UI fields.

      Something to think about...

  10. Anonymous

    One case I haven't seen a recommendation for here is the situation when you have multiple references to another table.  As an example, I have an Interview table, which references the People table at least twice, once for the interviewee and at least once for the interviewer(s).  How to distinguish between these?  Currently, I use:

    I attempt to make the first, un-suffixed foreign key either the one-to-one (if there are also potentially one-to-many links) or the most important of the references--though that's a subjective judgement. However, itcould easily have the same type of suffix as the other keys.

    -Matthew Miller

    1. Anonymous

      Multiple foreign keys to the same table

      If we had the tables Interview, Media, People and only one primary/secondary key to link them, then we would do as you suggest with the first instance, just call it _kfln_mediatype. But if more than one foreigh key is needed in the table we would give each of them a suffix clear explaining the use of this key.

      Here mediatype is TV, Radio, Internet, Newspaper, Magazine - Media is the actual media (eg The Times) and we can choose a key to show which media was the primary publisher and then medias quoting the interview. The quote key is a key to a join table (more medias can quote more articles and visa versa).

       

      • __kpln_interview
      • _kfln_mediatype
      • _kfln_media_primary
      • _kfln_j_media_quoted
      • _kfln_people_interviewed
      • _kfln_people_interviewer

      And we would not use the ID as a prefix. This would sort the keys down among the data fields. We want the keys at the top and we are using z_ as a prefix for table specific special/tech fields and zz_ as the prefix for special/tech fields that we use in all tables.
      Best regards
      Carsten Levin
       

    2. Anonymous

      Carsten, per the top of this page, these standards specify the use of "id_" as the prefix for all key fields.  The standards also assume we do manual sorting, with id_ fields being sorted to the top of the list and calculation fields, summary fields, etc at the bottom.  

      I do like the underscore rather than the hash/pound symbol as the suffix divider, to keep consistent with the prefix. 

      That would make this suggestion:

      -Matthew Miller

      1. OK, I can't keep my trap shut on this one...

        There's two separate ideas spawning - *how* we name TOs and how we *qualify* multiple TOs that have the same base table name.

        As per Carsten's example I might suggest:

        id_subject (person being interviewed - typical nomenclature for entities being interviewed or surveyed). This replaces _kfln_people_interviewed - we already assume the subject is a person...does it really need to be called out in our graph?

        id_reporter (person conducting the interview - other synonyms might be "administrator", "facilitator", etc. Again - we're pretty sure we know it's a person.) This replaces the _kfln_people_interviewer above.

        I'm much more fond of the qualification of the entity vs. explicit naming based on retaining the base table name. If I understand the subject matter that I'm building a solution for I'll understand context faster when I see subject and reporter vs. people_interviewee and people_interviewer.

        Just my $0.02.

        --Perren

        1. Let me defend the case for the maintenance developer. If I'm new to a solution, I wont have any idea that id_subject and id_reporter point to the same table without a peek at the relationship graph or a leap of intuition. The former could slow me down (familiarity with the database is a luxury), and the latter is just a scary thought. I find myself using the id_Name_qualifier syntax myself these days. It's in the same spirit as the group prefixing best practice, for one thing. Lack of ambiguity and not having to infer information is worth a more verbose field name, I think.

          1. Fair enough Jeremy...I appreciate you calling me out on this one.

            My indy solutions use what I discuss above, but my "day job" solution uses qualifiers...this is one area where it's really about development context more so than "the one true path to enlightenment"! :)

            Am I allowed to have one foot in each camp?

          2. My .02¢ would be to augement the standard slightly.

            Wherein

            are used. Given that a long table name like

            becomes

            Although I do like the spacial separation that the underscore provides. Should we adjust the standards to promote the underscore? I'm under the assumption that the Tablename is to be named so that it makes sense without being too long.

            Does read better, and by keeping the qualifier lowercase we don't confuse with Tablename.

  11. Anonymous

    Our solution is simple. "_ID" would be a primary key.  "_IDLineItems" would be a foreign key. If we need to qualify, then we would use "_IDLineItemsLABOR"

    Very similar to Matthew Miller's example.

    Hal Gumbert, Campsoftware.com