Cascading Delete Self-join or Same-Table relationships are very useful (two table occurrences from the source table related to each other), but be careful! Don't ever turn on the option to allow deletion of related records! If you do, whenever you delete a parent record you may delete more than you want. What happens is that since your related records are also your parent records, deleting records can cascade out of control. I have seen the deletion of a single record cause the deletion of all records in all tables.
Logical functions are some of the most important and widely used functions. Think of decision making when considering logical functions. They enable you to decide if a formula will return one result or another. Which fork in the road will the formula take? Testing for true or false is often referred to as boolean in the FileMaker interface so get used to the terminology. While it is common for amateur developers to overuse these functions, if utilized properly, they are extremely powerful. Logical functions include Case, If, IsEmpty and Choose, to name a few of the most important.
SIDETIP: In FileMaker, boolean false is an empty or zero (0) result. Boolean true are all others results, including 1, 2, -10, 14.56, etc. In FileMaker 6 and earlier, text such as "true", "false", "yes", "no", "y", "n", "t" and "f" used to carry boolean results but caused too many technical support calls and was removed.
Dueling Logical Functions The most common logical functions are those that allow for a test to determine at least two possible results. These include, Case, Choose and If. Case and If are virtually the same in all aspects including their goal. Let's start by showing the basic differences between Case and If using an example simplified down to just placeholders instead of fields (the Choose function will be covered later):
Case(Test; Result1; Test2; Result2; Result3)
If(Test; Result1; If(Test2; Result2; Result3))
The most visible difference is... Case is inherently nested while If requires manual nesting to accomplish the same task. Any function can be nested within itself or another function, but a few functions like Case, Substitute and Choose are designed to naturally allow for iterative formulations. The downside for nested If formulas are the additional parenthesis required to close each statement. If you have fifty nested If statements, you will have fifty close parenthesis.
Case vs If: Example Let's move on to a real example to further compare and contrast these two decision making functions. Start by creating a parsing formula for grabbing the first name field from a single field containing title, first and last names:
SIDETIP: The carriage returns in a formula will not affect the result. They are added to make the formula easier to read by separating the parameters.
The PatternCount function returns a value representing the number of times a string (or contents of a field) in another string (or field). PatternCount is not case sensitive but some functions, like Substitute, are case sensitive.
The Case statement tests, using the first parameter, to see if a condition is true or false. If the result of the test is true, the value from the second parameter is calculated. If the result of the test is false then the third parameter is returned.
In this example, the PatternCount function searches for the first word of the first name field in the string "MrMsMrs". The word functions do not grab punctuation at the end of words so the periods are excluded. If the first word is located in the string, the statement returns a value of "1" or true and grabs the second word as the first name. Otherwise, the first word is returned. Using the example data above, the result is true so the result is the second word or "Gretchen".
When compared to an If statement, the two functions accomplish the same task. There's really no difference:
The difference between the Case and If statements is only clear once a more complex example is examined for extracting the last name:
PatternCount("MrMsMrs"; LeftWords(CUSTOMERS::name_first; 1)) and WordCount(CUSTOMERS::name_first) = 4;
not PatternCount("MrMsMrs"; LeftWords(CUSTOMERS::name_first; 1)) and WordCount(CUSTOMERS::name_first) = 3;
The formula for the last name calculation is a little more complicated, requiring multiple tests to determine how many words to grab from the right. The first test determines if a title exists and if the number of words is equal to four. If both of these statements are true, a two word last name must exist and the formula will return the result of the right two words.
If, and only if, the first test is false will the second test be considered. The second test begins with the NOT function. Think of it as a negative sign. It simply reverses the result of the statement that follows. In this case, it makes the PatternCount function return a true result if a title doesn't exist. The WordCount function also has to return a three, indicating a two word last name must exist without a title. If all the tests are false, the Case statement will default with the last word because a two word last name does not exist. Using our sample data, the result is the last two words or "Del Rio".
When compared to the same formula using an If statement, the differences become crystal clear:
PatternCount("MrMsMrs"; LeftWords(CUSTOMERS::name_first; 1)) and WordCount(CUSTOMERS::name_first) = 4;
If(not PatternCount("MrMsMrs"; LeftWords(CUSTOMERS::name_first; 1)) and WordCount(CUSTOMERS::name_first) = 3;
The first noticeable difference is the repetition of the If function name, mentioned previously, but worth repeating with additional points. Whereas the Case statement is inherently nested, the If function has to be nested to include multiple tests. This also explains the additional close parenthesis at the end. For every nested If statement, an additional parenthesis will be required to close the statement.
This reminds me of the good old days in technical support before Case was introduced in FileMaker 3.0. When writing complex decision making formulas, I was forever counting my close parenthesis. It was too annoying to skip to the end of the formula to close each If statement since I like to type my formulas linearly. Unfortunately, this required counting the If functions at the end, which I usually got wrong. Sometimes it required a significant amount of time to get the formula properly closed.
Another difference between Case and If is the requirement of a default result for If. The default result for Case is null or empty while If requires a result as the default. In other words, to specify a blank result, you must include quotes surrounding nothing (""). There are no exceptions for the If statement default result, making the Case statement more flexible.
The most incredible difference, however, is how the two logical functions are evaluated. A nested If statement must evaluate all tests and results, due to it’s nested nature. All nested functions evaluate from the inside-out. If is no exception so it must evaluate starting from the last test and doesn't stop till it's evaluated all surrounding tests, finally returning it's results. A Case statement will exit as soon as a true test is encountered, only evaluating the tests required to locate the first true statement. With a lengthy nested condition, applied to thousands of records in a looping script, this could make make a significant speed difference.
The conclusion is to always use the Case statement. The If structure is an antiquated system for compatibility with legacy solutions. Case is easier to construct, review and a potential source of efficiency. Remove If from your vocabulary except when working with scripts, as there is no Case equivalent in scripting. Besides, when If script constructs are assembled correctly with nested Else and Else If steps, exiting occurs as soon as a true statement is reached. So, the If script step is more akin to a Case than an If statement.
Choose The Choose function is a widely misunderstood function. While it has limited application, knowing how it works can significantly reduce the size of a formula and speed the execution when compared to a Case statement. Given the limited application of the Choose statement, it is tough to provide an example with a simple premise. Let’s pretend a teacher is evaluating student's grades on an average. A Case statement would construct the formula in this manner:
Not all conditional formulas will fit into the Choose parameters. Only if the tests are similar in nature and result in a sequence starting with zero. In this case, the same test was being repeated over and over and the result returned a number from zero through five. The Choose function rounds test result down to the nearest integer, explaining how division can always result in whole numbers.
The Choose function can simplify and expedite a result by performing a single test but is rarely used because of the difficulty of fitting a condition into a single test result. When it fits, it works wonderfully. Just don’t spend a lot of time forcing a Case statement into a Choose format when it won’t noticeably enhance the user experience or the complexity of the programming task.
It’s a good idea to understand alternative solutions available. For example, the following solution also solves the problem at hand:
Which solution is the best choice, may or may not make a difference in the given situation. For example, if any of these formulas are placed in a calculation field, the user experience is approximately the same. The result and processing time are the same. Time spent researching and testing different solutions may not provide adequate enhancement to the user. Change the situation slightly to a script that loops through tens of thousands of records and applies the formulas, it is likely the Choose or Ceiling variation will triumph over a Case version in terms of speed, making the expenditure of time worthwhile.
To give the Case function a fair shake, the Let function can fix the speed issues and much of the formula complexity:
The Let functions allows for the repeated test to be done once at the beginning, rather than once for every test. This can make a significant speed difference if the test is complicated or based on related fields.
SIDETIP: The at signs (@) aren't necessary but help identify the variable declarations throughout the formula. It also allows for the use of reserved words like function names.
IsEmpty The IsEmpty function is a workhorse for the FileMaker calculation engine. It seems like every conditional formula uses it at least once as a test. Fortunately, it is very simple to incorporate into any calculation you may be writing, requiring a single parameter referencing a field. If the field is empty, the result is true. If the field contains one or more characters, the result is false. For instance, a simple formula can determine whether or not a picture has been added to a customer record and display a help message advising the user to insert a picture when it is empty:
Case( IsEmpty(picture); "Insert a picture using the Insert menu" )
Place the calculation field above on top of the container field it references. Make sure the calculation field is translucent so the user can see the contents of the container field below. Do not allow entry into the calculation field via the Inspector so the container field can still be selected or, alternatively place the calculation field on the layout as a merge field. I also like to turn the text of the container to a light grey so it doesn’t compete with the data entry text. With consistency, all messages in a light grey will be considered help messages to the user, without any explanation.
Calculation Homes As you can see, the picture message calculation fields works just fine but it clutters up Manage Database. Whenever you think of adding a calculation, you need to consider the various places it can reside. In an effort to tidy up and relieve Manage Database of some of it’s duties, FileMaker, Inc. has released a variety of features over the years to lessen the dependance on Manage Database formulas.
Learning when and where to place a calculation formula comes with experience but let’s work with this container field message formula to see how we can improve it. One approach to free up Manage Database would be to use conditional formatting. Type the message text in the light grey color directly on the layout and then use the arrow tool to select it. Choose Conditional... from the Format menu and the Conditional Formatting dialog will appear. Click the Add button and enter a simple formula:
Apply a text color of white, or the same as the layout background, when the condition is true, making it disappear when the container field has a value. The Not function is employed in order to avoid typing the text in the same color as the layout background and changing it to grey with the condition. The reasoning is simple. If the text is the same color as the background, you won't be able to see it in layout mode.
This all works great but what if you don’t have a solid background color. Might be better to use the Hide Objects feature since it makes objects completely disappear. Using the same text block, type the following formula into the Inspector in the Hide Object When area under the Data tab:
It’s the same formula used in the Conditional Formatting approach because it’s the same concept. They only differ in that Conditional Formatting camouflages the text while the Hide Object feature removes it completely from Browse mode.
We have now discussed three methods for displaying a picture message. A calculation field clutters up Manage Database. Conditional Formatting moves the formula to layout mode but only camouflages the text block containing the message. The Hide Object feature is the best so far, completely removing the object from the layout when the conditions are met. But, the best solution may not even involve a formula. The Placeholder Text feature in the Inspector under the Data tab is designed specifically for this purpose.
While the Placeholder Text feature has the ability to incorporate a calculation formula, it is only in terms of what text is displayed. It essentially has the IsEmpty test built into it, hiding the result of it’s formula when the field is not empty. Therefore, in this case, what is needed in the Placeholder Text formula is simply the message enclosed in quotes.
"Insert a picture using the Insert menu"
The only downside of the placeholder text approach is it requires FileMaker 14. The Hide Object feature was introduced in FileMaker 13. Conditional Formatting has been around since FileMaker 9, making it the most compatible. But, a straightforward calculation field show in the first example has been around since the early days of FileMaker and is by far the most suitable if you have users with older versions of FileMaker.
Even though a calculation was not necessary to implement this feature, the discussion was important in establishing a logical decision making process. Don’t just shove a calculation formula in the first place you find. Think about the advantages and disadvantages of each place where the calculation engine resides.
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!
John please save.
I created a table called time, where I have 3 fields. the first field is time_used, the second is time_contracted where you can enter just three number, 00:00:00, 00:30:00 and 1:00:00 and the third is result with a calculation.
(time_used ≤ Time(0;5;59) and contracted_time = Time (0;30;0)); 1;
(time_used ≤ Time(0;5;59) and contracted_time = Time (1;0;0));2;
(time_used ≥ Time(0;6;0) or time_used ≤ Time(0;30;59)) and contracted_time = Time (0;30;0); 3;
(time_used ≥ Time(0;31;0) or time_used ≤ Time(1;0;59)) and contracted_time = Time (0;30;0); 4;
(time_used ≥ Time(0;6;0) or time_used ≤ Time(1;0;59)) and contracted_time = Time (1;0;0); 5;
(time_used ≥ Time(1;1;0)) and contracted_time = Time (1;0;0); 6;
I can´t get Result = 4 or 6. Please, can you tell me where is the problem in my logic because unfortunately I don´t see.
Also, is there a way to use range function in this script?
Let me congratulate you and Michael Rocharde for Fireside Filemaker podcast. I enjoy every minute of it.
Glad you are enjoying Fireside FileMaker! I'd be glad to help with your formula but I'm not sure what the end goal is. Send me an email to email@example.com along with your formula and a description of what you are trying to accomplish. Thanks.
Steve M MARTINO
Yay! New Jaymo!! Another fine article, as always. Quick question, is this a typo?