"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Create a FileMaker Calendar



Become a patron of this FREE web site!


Recent Blogs:

Window Locking
Window Locking

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


Document Management Videos


Quick Tip:

Multiple Inspectors
Maybe you figured this out already but if you haven't, it will open a whole new world for the Inspector. If you click the icon for the Inspector in the Status Toolbar, it toggles the Inspector palette open and closed. If you use the menu item called "New Inspector" under the View menu, you can open as many Inspector palettes as you desire. I am guessing three will be enough to display all three tabs of the Inspector. Now you will be able to modify any area of the Inspector without switching tabs. And, the Inspector toggle button will show and hide all of the Inspectors.



The Philosophy of FileMaker recommends PCI!


Fun Stuff:

The Wedge
Claris Corporation and the FileMaker, Inc. have been housed in a building called the wedge in Santa Clara, California since they spun off from Apple, Inc. The building is nicknamed the wedge because it looks like a wedge. For some years they had an adjacent building called the interstitial but it was let go when the company downsized to focus on FileMaker products.

The Wedge



Tips & Tricks Videos







RSS Feed
Anatomy of a Calculation
Level: Beginner
Version: FileMaker 17
Category: Calculations
Tuesday, June 12, 2018
FileMaker calculation formulas allow you to add, subtract, multiply and compare values with operators. If you are familiar with spreadsheets like Excel or Numbers, it will help you to understand FileMaker calculations. However, FileMaker Pro calculations go much further and enable you to manipulate text, work with dates, ascertain the status of FileMaker Pro, make decisions and much more. Rather than covering any specific calculation formulas in detail, the aim of this article is to focus on providing a good foundational knowledge for the complex calculations you will encounter as an aspiring FileMaker developer.

Anatomy of a Calculation

The Ubiquitous Calculation Dialog
The importance of the calculation engine in creating professional FileMaker solutions can be weighed by the number of times it appears throughout the interface. Calculations aren’t just for Manage Database calculation fields, they play a vital role in just about every area of FileMaker including automation, validation, conditional formatting, security, object visibility and more. Think of calculations as providing an extra level of decision making control to any feature where it can be attached. And, sometimes calculation formulas aren't just an add-on but control the entire feature, as is the case with the Hide Object feature. Learning how to write a calculation formula will increase your FileMaker skills tenfold, allowing you to create sophisticated solutions far beyond the standard fare.

Anatomy of a Calculation

The calculation engine, along with it's operators and functions, is identical across the entire FileMaker application. However, each calculation dialog is slightly different, so note the variances as they can change the functionality or outcome. For example, a Manage Database calculation field allows for the specification of a result type via a popup menu, while an auto-enter calculation depends on the field type to which it is attached and calculated conditional formatting requires a boolean result. These are just some of the differences so study each dialog as they are presented.

ADVICE: The only way to truly learn how to use calculations is by creating numerous formulas in the variety of different areas of FileMaker. Don't expect the understanding to come all at once. Just keep creating solutions and you will eventually become an expert.

The screen shot at the beginning of this article is from the FileMaker 17 Manage Database calculation field dialog. Below are four additional calculation dialogs including Custom Function, Data Viewer, Auto-Enter and Validation. I chose these dialogs because they are representative of the gamut of dialogs available in FileMaker 17. Still, you should become familiar with every calculation dialog on every feature so you are familiar with the nuances. Click the images below to enlarge them.

Anatomy of a Calculation

Anatomy of a Calculation

Anatomy of a Calculation

Anatomy of a Calculation


Overwhelming Functions
With nearly three hundred functions to learn, the task of writing a calculation seems daunting. No worries. You really don’t need to be intimate with every single function. Approximately twenty percent of the functions are used eighty percent of the time. The rest of the functions are either rarely or never used, except in special disciplines or situations like statistics, finance, mathematics or mobile solutions. It’s a good idea to become familiar with what’s available but concentrate your studying on the most important functions, many of which are listed below. This is my list so it may not include functions you covet.
  • Case
  • Count
  • Date
  • Day
  • Evaluate
  • ExecuteSQL
  • Filter
  • FilterValues
  • Get(AccountPrivilegeSetName)
  • Get(ActiveFieldName)
  • Get(ActiveRecordNumber)
  • Get(DesktopPath)
  • Get(Device)
  • Get(FileName)
  • Get(FoundCOunt)
  • Get(LastError)
  • Get(LayoutName)
  • Get(RecordNumber)
  • Get(RequestCount)
  • Get(ScriptResult)
  • Get(TriggerKeystroke)
  • Get(UUID)
  • Get(WindowLeft)
  • Get(WindowTop)
  • GetNthRecord
  • GetSummary
  • IsEmpty
  • Left
  • LeftValues
  • LeftWords
  • Length
  • Let
  • List
  • Middle
  • MiddleValues
  • MiddleWords
  • Month
  • PatternCount
  • Position
  • Quote
  • Right
  • RightValues
  • RightWords
  • Self
  • Sum
  • Substitute
  • Trim
  • ValueListItems
  • Year
The depth of the calculation engine is enormous which is one reason they are used in so many areas of FileMaker Pro including Manage Database, the Script Workspace, Security, Conditional Formatting, Hide Object and Placeholder Text, just to name a few. An entire book could be devoted to calculations and still would not cover the many uses. The following exercises attempt to cover some of the most important concepts but in no way will exhaust the variety of formulas which can be created. Instead, foundational concepts will be covered in order to prepare you for the formulas you will find in your journey as a budding FileMaker developer.

Calculation Dialog Interface
The calculation dialog has four main areas: Fields, Operators, Functions and the area where you construct your formula. Calculation dialogs vary across the different features to which they are attached so pay attention to the differences, usually found at the bottom of the dialog. Even with all their differences, every calculation dialog has enough in common to make you feel comfortable programming no matter the location.

Anatomy of a Calculation

Functions
Every function has two possible parts: the function name and parameters. Function names always come as a single word at the beginning and parameters are always surrounded by parenthesis. If there is more than one parameter for a function, they will be separated by semi-colons. It's also possible for a function to have no parameters and therefore no parenthesis as is the case with the Pi, Self and Random functions. Here are some commonly used calculation functions to familiarize you with their format.

Random

Left(text; numberOfCharacters)

Get(FoundCount)

If(test; resultOne; resultTwo)

SIDE TIP: Scrolling through the list of functions reveals many Get functions. Technically, there is only a single Get function but many flags that can be used as parameters.

Some calculations have optional parameters and will be designated by curly brackets. One of the most common is the Case function. It's very similar to the If function except that it can contain as many test and results as desired, making it a better choice in general. Here are some examples of common functions that have optional parameters:

Case(test1; result0) {; result1; result2; ...; defaultResult)

Sum(field{; field...})

Lookup(sourceField {; failExpression})

SIDE TIP: Never use the If function. Always use the Case function. The Case function evaluates faster, is inherently nested (not requiring a close parenthesis for every condition) and doesn't require a default result.

Other functions allow for multiple iterations of a parameter and are designated with square brackets:

Evaluate(expression {; [field1; field2; ...]} )

Substitute(text; searchString; replaceString)

Notice the Substitute function doesn't specify the optional parameter in it's definition with the square brackets. I'm not sure why that is... it's just one of those idiosyncrasies you learn to accept. I think it's a little confusing but you'll just need to get used to it. An example of multiple replaces might look like the following:

Substitute(MyField; ["J"; "John"]; ["M"; "Mark"])

Formatting Formulas
Complex formulas can become difficult to read unless they are formatted with returns and spaces. I'm not a big fan of indenting formula with spaces since it can look sloppy but I'm one-hundred percent behind the white space carriage returns offer. White space is a very important tool for visual recognition so I employ it in almost every formula create. For example, here is a Case statement with multiple tests:

Case(Color = "Blue"; 1; Color = "Red"; 2; Color = "Green"; 3; Color = "Black"; 4; Color = "White"; 5; 0)

Adding carriage returns doesn't affect the result of the formula but makes it easier to recognize where one parameter starts and another ends:

Case(

Color = "Blue"; 1;

Color = "Red"; 2;

Color = "Green"; 3;

Color = "Black"; 4;

Color = "White"; 5;

0)


Construction
Calculations can be constructed entirely with double-clicks in the fields and functions areas as well as a single click in the operators section. Clicked items are inserted at your cursor or highlighted selection in the calculation construction area. I personally prefer to type entire formulas from the keyboard for efficiency. FileMaker will even auto-complete functions and field names for you with type ahead technology. Scrolling up and down to find the desired function or field is cumbersome if you already know the name. Occasionally, I will revert to the mouse when I am working with a less familiar function or forget the name of a field or relationship. However, once you become familiar with formulas, you will be able to type them more efficiently off the top of your head rather than scrolling through lists and double clicking on selections.

Manage Database Calculation Field Dialog Features
Manage Data isn't the only place to enter a formula but it's often where newbies are introduced to the calculation engine. Pay attention to the popup menu above the list of fields as it specifies the table occurrence from which to display fields ("Evaluate this calculation the context of"). Without getting into an extensive discussion about relationships, this popup menu is critical in determining how the related fields in a formula are calculated. Most of your calculation formulas, especially at the beginning of your FileMaker career, will only include local fields so there won't be much use for these two popup menus until you start creating more sophisticated formulas. Even then, I doubt you'll ever need to change it.

Anatomy of a Calculation

There's more about context below but the rule of thumb in the calculation dialog is don't change the "Evaluate this calculation from the context of" unless you need to. So... when do you need to change it. Early in your career, just ignore this menu. When the time comes, you'll know when. However, with good relational design practices, you shouldn't never have to change it. Again, I rarely change it so that's why I say you can ignore it for right now. Let the relationships on the fields in your formula determine the perspective.

FYI: The Manage Database and Record Level Access (from Security) calculation dialogs are the only ones that allows you to specify the context. Context from other calculation dialogs is determined by the location of the object or script.

On the other hand, an extremely common popup menu to change is the one for specifying the calculation result. It appears in the lower left hand corner of the calculation dialog for calculation fields only. Auto-enter formulas determine their result by the field they are attached to and validations by boolean (true or false).

The default for a calculation field is "number" unless you have already created another calculation and specified a different result. As soon as Manage Database is exited, the default result type returns back to "number". I always tell my students to set the result type as the first step to creating a calculation. Otherwise, the tendency is to forget this option. Even with a perfectly designed formula, a result type mismatch can make searching, sorting, value lists and many other features behave erratically. Consider yourself warned! Specify this option first or you may forget and spend hours troubleshooting.

Anatomy of a Calculation

SIDETIP: Most calculation dialogs don't have a calculation result or evaluation context popup menu. The most common are the calculation dialogs that appear in scripts. Since scripts determine context by the layout where they are running and result type by the field they are manipulating, there are no need for these options.

There are several options in the standard Manage Database calculation field dialog that are rarely modified. This includes the evil repetitions feature and the do not evaluate if all referenced are empty option. Repeating fields should seldom be used since they are a legacy feature from the flat database days of FileMaker. There can be times to use repeating fields but I haven't used one in a solution for years. They certainly shouldn't be used for storing data,mostly tricks and techniques since they prevent ad hoc reporting. As far as the evaluation option, I think it's pretty clear once you read over it a few times. Basically, calculation fields update when a referenced field is updated. If you want to prevent a calculation from triggering when none of the referenced fields are filled, by all means, uncheck this option. I rarely touch it, leaving it at the default ninety-nine percent of the time.

Context
Context or perspective is a fancy word for how a related object is evaluated. I like to refer to it as the "start" and "stop". Every relationships starts somewhere and ends somewhere. How related data is displayed depends on the key field matches between the starting and stopping points on the relationship graph. In the case of a layout, the starting point is the table from which it is displaying records and the ending point is the related field or portal. These two table occurrences can be used to trace the logic of the related items in the relationship graph. It's really quite simple but all important to being a successful FileMaker programmer. When I teach classes, it's the most common mistake. If you have a problem with a script, portal, conditional menu or related field, it's often context related.

Anatomy of a Calculation

In the example screen shot above, there are two paths to the phones table. One shows all phone numbers and the other filters for mobile numbers. In other words, there is more than one route to the phones table. If a layout were to show records from Contacts then a related field from Phones could be shown from the table occurrence Phones or Phones_Text_Message. This is the essence of context or perspective. Specify the "start" and the "end" to determine what will display. In other words, on the Contacts layout, Phones will display work, home and mobile numbers while Phones_Text_Message will display just mobile numbers.

FYI: Scripting context is determined by the layout where the script is currently running, which could change many times, as well as the step that's actually running. For example, a Set Field step could perform differently depending on the current layout, target field and calculation formula and how all the relationships between them interact.

While the most common way to specify context is via the layout and related field or portal connection, the calculation dialogs in Manage Database allow the context to be set separately from the default table, creating a more complex dependency. This is in direct opposition from calculation dialogs in Manage Scripts which set context from the perspective of the layout where it performs. If no related fields are referenced in a Manage Database calculation or there is only one table occurrence for the field's source table, no changes ever need to be made to the evaluation context. Even with multiple table occurrences and related fields, changes are rarely made to the context at the top of the calculation dialog. As long as good anchor-buoy design standards are followed, context can pretty much be ignored. In my twenty plus year career, I have changed the context on less than a dozen calculation fields and only in situations where I was programming an extremely complicated solution. Therefore, keep the equation simple and don't change the evaluation context unless absolutely necessary.

Keeping track of context should be foremost in your mind while programming FileMaker solutions. Related Fields, Portals, conditional formatting, hide objects, tooltips and Scripts obey the context of the current layout where they reside. Scripts obey the current layout but the context changes depending on the layout where the script is initiated so it's a good idea to start a script with a Go to Layout step to set the context properly. Context for conditional value lists and any calculation type created in Manage Database is declared within the calculation dialog in the upper left corner. Other features, such as Placeholder text, conditional formatting and Hide Object, determine context by the layout containing the object that is being controlled.

Context or perspective can be a complicated mess when trying to explain all the possible variations. However, context always obeys one simple rule... "start" and "end". Remember this golden rules and relational concepts will become clearer the more you work with FileMaker. It took me years to truly grasp their complete nature of relationships but once I understood, I realized they were so simple.

Calculation Field Options
Calculation fields in Manage Database have a button in the lower right corner for setting storage options. The settings found in the resulting dialog are mostly the same as the storage tab for a standard field, allowing for global storage, indexing and language. However, not all the features works quite the same and there is an additional option.

Anatomy of a Calculation

In the case of global storage, a calculation field does act in many of the same ways as a regular global field. If a global calculation field references only global fields in it’s formula, a change to any of the references updates the calculation globally on every record. If the global calculation field references any regular fields, the formula is also updated based on values from the current record. At times, this behavior can be handy, allowing non-global data to be accessed from another table without a relationship.

With indexing, unchecking the option to "do not store calculation results -- recalculate when needed" allows the calculation to update anytime the screen is refreshed. This is in direct opposition to stored calculation fields which update only when a referenced field in the current record is modified. Stored fields also calculate when exiting Manage Database and when a new record is created. Even though it may appear so from the dialog, indexing is not the most important difference between stored and unstored formulas. Some calculations may not update properly unless they are unstored such as formulas containing only Get function references. Other calculation are forced to unstored if they reference global fields, related fields, summary fields or another unstored calculation field. Indexing is discussed at length in an article named In Depth Index so I don't see the need to repeat it's excellent information here.

FYI: The FileMaker window refreshes when the record, layout or mode changes.

Auto-Enter Calculation Options
Auto-Enter calculations have the power to create snapshot data. While a calculation field displays live data, an Auto-Enter will not update unless a local referenced field is changed and the option to "do not replace existing value of field (if any)" is unchecked. Otherwise, Auto-Enter calculations only calculate when the target field is empty. For example, if you want to format a phone number field, it's best to uncheck the option "do not replace existing value of field (if any)" so the formatting is performed every time the phone field is modified. For more information on phone number formatting, see the article Phone Filter Part One.

FYI: Auto-enter calculation fields will update when a related field is changed, as long as the dependency tree is triggered. Triggering the dependency tree requires the match field in the table to be updated. Merely updating the related value referenced in the calculation field in the related table will not update an auto-enter calculation to an auto-enter calculation field in another table. For more information on the dependency tree, see this article.

Validations Calculation Options
The specialty of validation calculations is curbing data entry. Validation calculations trigger only when a field is modified, or in some cases a record, as can be seen with the not empty validation option. It's best to try out these validation options to see what is meant but let me try to describe it anyhow. Imagine setting a bunch of fields to validate when empty. If FileMaker validated for empty on field modification or exit, you would have to fill the field in as soon as you touched it, causing user irritability. Better to validate for empty when a user is leaving a record. On the other hand, if you validate for a range of values, it needs to be validated upon exiting of the field so the user is alerted immediately. Of course, the validation dialog does have a special option to "validate only if field has been modified" which defaults to checked. Unchecking this option allows the validation to behave like a not empty validation whenever the record is modified.

Variables
Variables enable scripts to pass data from place to place, most commonly bridging two tables without using a relationship. Think of them like a clipboard where you can place the result of a calculation formula, except that you can create as many clipboards as you want! A common example might be to store a primary key in a variable (using a calculation with a simple field reference), go to a layout displaying records from a related table, create a new record and set the variable value into the foreign key. The user can now enter the desired data in the child record and it will be properly linked to the parent. Only one other calculation method has this ability to carry values across records or tables as easily as a variable, and that is Script Parameters. And,don't forget... calculations formulas don't have to be complicated to be incredibly useful, as shown in this variable example.

Scripts
Scripts in general allow for snapshot data. From Set Field to Replace Field Contents to Insert Calculated Result, any step that places a calculation result into a field requires the script to be run in order for the result to update. Scripts and Auto-Enter calculations often overlap in functionality, making them competitors for the same jobs. The difference comes down to the methods by which users can update a formula. While a script can be executed in a variety of scenarios including a button, event trigger or a another script, Auto-Enter calculations only update when referenced fields are modified. On the other hand, scripts are at the interface level, requiring them to be repeated across buttons, layouts or wherever you want them to be repeated. Auto-enter calculations are schema level so defining the formula once works anywhere the field is used.

Data Viewer
The Watch tab of the Data Viewer is like a cocktail napkin used to sketch out an idea at the proverbial restaurant, allowing calculations to be quickly tested against record data without disturbing the schema in any way. The Data Viewer allows formulas to be entered and tested against any record with executing a script and possibly ruining data or disrupting a pristine layout by placing a test field on it. Once a formula is perfected, it can be quickly copied and pasted to any other calculation dialog in FileMaker.

FYI: Field references in the Data Viewer require the table occurrence to be specified, since the context can change if the layout changes. When copying a formula to a calculation field in Manage Database, the local table references can be removed since the context is specified in the table where the calculation resides.

Anatomy of a Calculation

The Current tab of the Data Viewer is a completely different beast, mostly enhancing the capabilities of the Script Debugger to reveal the contents of fields and variables referenced in the active script. Prior to the Data Viewer, fields would need to be placed on any layout visited by a script prior to debugging a script. I can't tellyou how much time I wasted adding a removing fields from a layout. That doesn't even cover the number of times I forgot and a user reported the field to me. Argh!

FYI: The Data Viewer also reveals global variable values even when a script is not actively running.

CustomMenus
Adding calculation formulas to Custom Menus single-handedly reduces most menu set needs from many to one. I remember a presentation I gave regarding Custom Menus at the Developer Conference, showing off how a single adaptive custom menu set could drive the menu requirements for a multi-table solution. Without calculations, Custom Menus would be static.

Custom Functions
The idea behind Custom Functions is to centralize calculation code duplicated throughout a solution. For instance, you might have multiple phone formatting formulas attached as Auto-Enter calculations across multiple tables in a solution. If you need to enhance the feature or repair a bug, the same changes need to be added to every occurrence of that code. With Custom Functions, the code exists in one location and is referenced throughout the solution much like a related field. The time saving advantages alone make this calculation implementation worthy.

In addition, Custom Functions enable recursion or looping inside a calculation formula. For more information on recursion, see the article Phone Filter Part Two.

Record Level Access
Last but not least, the calculation engine also infiltrates security in the form of record level access. Record level access allows the viewing, editing and deleting of a record to be controlled by a boolean calculation result at a privilege set level. The classic example is preventing users from seeing each others records by including a formula that compares the creator of the record to the account name of the person currently logged on.

De-clutter
Moving formulas out of Manage Database and into other areas of FileMaker has been one of the themes of FileMaker in releases since the ground breaking FileMaker 7. Features like Conditional Formatting, Filtered Portals, Placeholder Text and Hide Object are all designed around features first programmed by FileMaker developers. By combining fields, calculations, relationships and more, developers were able to invent features that were so popular that FileMaker added them as features to help reduce the clutter in Manage Database.

Build it!
If you really want to understand FileMaker calculations, you have to create solutions till your fingers bleed. There's really no other way. This article can get you started on your journey with a good foundational knowledge but now you need to study specific calculation formulas and create your own unique formulas. Keep working an you'll soon be a FileMaker calculation expert.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

ALBERTO REYES 10/27/2022
  I am working with calculation fields on a table, i would like to get the average value of a field called exchange rate, only if the operation is ready. so all the different records with fulfilled operation should calculate the average, but if i include a single not ready, the whole calculation fails. i hope you can help.

Steve 06/13/2018
  Another great article! Thanks JMO.

Don't forget calculations for button bar labels, and (I stumbled upon this but never knew when it started) Tab control labels
Response by:   John Mark Osborne 06/15/2018
Good point Steve. I rarely use button bars so I forgot about this one (I don't like my buttons to be stuck together). Any other calculation locations I forgot, please leave a comment. Thanks.

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!