These logical functions test a condition and evaluate it as true or false. The If and Case functions are similar in structure and use, so they are covered together here.

If ( test ; resultOne ; resultTwo )
• Input: test - any test or numeric expression to be evaluated
• Output: results - any - according to the expression

Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )
• Input: test - any test or numeric expression to be evaluated
• Output: any - according to the expression

The If function evaluates a single test and returns one of two results. The first result is returned when the test evaluates as true, the second when the test evaluates as false. The second result is not required - if it is not specified and the test evaluates as false, the function returns a null (empty) result. However, it is often considered better programming to be explicit and include a second result, even if that is null.

The Case function can evaluate a series of tests, each with their own result. When a test evaluates as true, the result for that test is returned; when a test evaluates as false, the Case function moves on to evaluate the next test. There can be a default result included at the end and this will be returned where all tests evaluate as false.

It is important to note that the Case function is short-circuited - it stops evaluating tests when it finds a test with a true result. This means that it is more efficient to structure tests so that the most commonly true results are tested first.

When structuring tests in a range, if you do so in order you do not have to specify a low and a high range in each test. For example, if we are testing a wage or salary for a tax rate, we might write:

Case (
  Salary < 10000; 0.15;
  Salary < 20000; 0.17;
  Salary < 30000; 0.19;
  Salary < 40000; 0.21;
  Salary < 50000; 0.23; 0.25 )

The Case statement can be used in place of any If statement by specifying a test and one or two results. However, where there is a single test with two results, the If statement reads better. Using the If function cues the reader to a single test whereas the Case function is a cue for a series of tests.

Examples of Use

1. Using a field flag as a test result

There are many cases where a field may be displayed as a checkbox with a single value of 1. If the field id checked, it contains 1; if it is unchecked it is empty. The field can therefore be used as a Boolean result since FileMaker Pro considers 1 to be true and 0 or empty to be false. If you carefully name the field, it can aid in the interpretation of the expression.

Some items we sell may be exempt from tax (GST). Let's say we have a field in a line item record called GSTexempt. If GST is 10%, we can calculate the tax on the line cost of the item:

LineCost * If ( GSTexempt; 0; 0.10 )

2. Calculate charges by destination

We could calculate freight charges by destination state with a Case statement. Where two states have the same rate, we can either separate these tests or combine them with a logical "or".

Case (
  State = "NSW"; 5.00;
  State = "QLD" or State = "VIC" or State = "ACT"; 8.00;
  State = "SA" or State = "NT"; 10.00;
  State = "WA" or State = "TAS"; 12.00;
  15.00 )

Now we move onto the date functions. Obviously these are used to manipulate and process dates.

The Day, Month and Year functions take a valid date as an argument and return a number. They are used therefore to extract any of these three parts of a date.

Day ( date )
Month ( date )
Year ( date )
• Input: date
• Output: number

The Date function takes arguments for day, month and year. The syntax reflects the American date format (ordered as month, day, year) and does not change for other countries like Australia and New Zealand. The Date function returns a date valid for the locality of the database.

Date ( month; day, year )
• Input: number, number, number
• Output: date

The arguments in the Date function may be supplied as constants, field names, variables or expressions that are evaluated. Each is then used to return a valid date.

It is important to understand that FileMaker Pro is able to evaluate "out of range" values for any of the arguments. For example, if 13 is supplied for the month, the date will be evaluated as January in the following year. This will also apply where the day exceeds the number of days in the specified month. A complex example is:

Date ( 13; 32; 2009 )

This will first be evaluated as:

Date ( 1; 32; 2010 )

The month is rolled over to January and the year on to 2010. Then the day will be evaluated:

Date ( 2; 1; 2010 )

So the final result is February 1, 2010.

Examples of Use

1. First day of the next month, last day of this month

The Date function makes it simple to calculate the date of the first day of any month. You can simply use the month and year of theDate, and 1 for the day:

Date ( Month ( theDate ); 1; Year ( theDate ) )

The first day of the next month is therefore given by adding 1 to the current month:

Date ( Month ( theDate ) + 1; 1; Year ( theDate ) )

And finally, the last day of the current month is given by subtracting one day:

Date ( Month ( theDate ) + 1; 0; Year ( theDate ) )

Day 0 is evaluated as the day before day 1.

2. Birthday or anniversary in the current year

If we know a person's date of birth or the date of an event, we can use that to calculate the anniversary date in the current year:

Date ( Month ( eventDate ); Day ( eventDate ); Year ( Get ( CurrentDate ) ) )

2. Final payment date

If we know how many monthly payments (numPayments) are required and the start date for payments (payStart), we can calculate the date of the final payment:

Date ( Month ( payStart ) + numPayments - 1; Day ( payStart ); Year ( payStart ) )

The Random function is one of the few FileMaker functions that has no parameters - it is simply the function name with no parentheses.

Random
• Input: none
• Output: number

As of FileMaker Pro 10.0v3, the Random function returns a random number greater than or equal to 0 and less than 1. It will never return 1. In previous versions, FileMaker Pro could return 1. This information was provided in a recent FileMaker Knowledgebase article 5464.

Examples of Use

1. Generate a Random Character

Since characters can be referenced by a decimal number, we can use the Random function to generate random characters within a range. If we write:

Char ( Int (Random * 26) + 65 )

this will return a random character in the range 65-90 which defines all the capital letters of the alphabet.

2. Going to a random record

You may want to use this technique to select a random record from a found set for a lottery or to choose a random customer to call. We can use the current found count to define the range of random numbers found and use a simple script step to go to a specified record number in the found set:

Go to Record/Request/Page [ Random * Get ( FoundCount ) + 1 ] [ No dialog ]

In this case, the random decimal number generated is multiplied by the found count and then 1 added. This means that the range of numbers generated for say 20 records is a number between 1 and 20.999999.

When the Go to Record script step is passed a decimal number, the number is truncated to determine the destination record. So when the calculation returns 13.86345233, it will go to record 13.

The Mod and Div functions are presented together because they share some similarities in structure and behaviour. Each takes a number and divides it by the divisor. The Mod function returns the remainder after that division; the Div function returns the number of times the divisor goes into the number (ignoring the remainder). So Mod ( 10.5; 3 ) returns 1.5 while Div ( 10.5; 3) returns 3.

Mod ( number; divisor)
• Input: number, number
• Output: number
Div ( number; divisor)
• Input: number, number
• Output: number

When dealing with negative numbers, a more accurate description of the Div result is required - it is the next lowest integer value after dividing number by divisor. This is the same as Floor(number/divisor). This means that Div ( -10.5; 3) returns -4 since the division result of -3.5 is taken down.

Examples of Use

1. Convert inches to feet and inches

If we are given a measurement in inches, we may want to display that in feet and inches. We can use Div to calculate the number of feet:

Div ( measurement; 12 )

And then we can use Mod to calculate the remaining number of inches:

Mod ( measurement; 12 )

We can put these together to display the converted measurement in feet and inches:

Div ( measurement; 12 ) & "ft " & Mod ( measurement; 12 ) & "in"

2. Odd or Even?

The Mod function is commonly used to return if a number is odd or even. The logic is that an even number has no remainder when divided by two whereas an odd number always has a remainder of one. So we can write:

Mod ( number; 2 )

This will return 0 for even numbers and 1 for odd numbers. As such, the expression is used to return a Boolean result for "number is odd" - zero is false, one is true.

3. Calculate repayments

If an item will be paid off with a certain number of payments, we want to calculate the amount of each payment and any final payment required. For example, to pay off $1000 in six payments, each payment will be:

Div ( total_price; 6 )

This will return $166 per payment. Since six does not divide evenly, we will need to add a small amount to the final payment. The final payment will be:

Div ( total_price; 6 ) + Mod ( total_price; 6 )

This is a group of Number functions in FileMaker Pro that return whole numbers (integers). The Int function is a lot like truncating to zero - it simply drops any decimal places. Ceiling will return the next highest whole number and Floor will return the next lowest whole number.

Ceiling and Floor return obvious results except when applied to negative numbers. For the number 1.2, the ceiling is 2 and the floor is 1. However, for the number -1.2, the ceiling is -1 and the floor is -2.

Int (number)
Ceiling (number)
Floor (number)
• Input: number
• Output: number

Examples of Use

1. Calculate two payments

Let's say that you are going to sell a product in two payments - a deposit and a final payment. We could simply halve the total price but that might result in an awkward payment. So we want to make the deposit a whole number amount:

Ceiling ( amount / 2 )

This will mean that a product costing $115 will have a deposit of $58 (rather than $57.50). To calculate the final payment:

amount - Ceiling ( amount / 2 )

2. Which quarter is that date?

It is a simple mental exercise to work out the quarter of the year for any given date - January, February and March are first quarter, etc. But how do we calculate that?

Ceiling ( Month ( date ) / 3 )

The Month function returns the number of the month of the given date. So a date in May will return 5. This is divided by 3 to give 1.66667. The Ceiling function returns the next highest whole number, 2. So May is in the second quarter.

3. How many years old?

To calculate a person's age accurately, we compare the current year with their year of birth and then ask if they have had their birthday yet this year. We can use an example of two people born in 1970 - one in January and the other in March. It is currently February 2010. So both people are approximately 40 years old (=2010-1970). To be more precise, the person born in January is 40 (because they have had their birthday); the other is still 39.

A rough approximation of age is given by dividing age in days by 365.25 (accounting for leap years). We can calculate age in days by subtracting the date of birth from today's date:

Int ( ( Get ( CurrentDate ) - DOB ) / 365.25 )

The Int function is used to get the whole number of years returned. Note that the Floor function would work equally well here.

We now move onto some of the Number functions in FileMaker Pro. The Round and Truncate functions perform similar actions in reducing the number of decimal places of a number. However, Round accounts for the dropped digits where Truncate does not. For example, if you round the number 12.345 to two decimal places, the result is 12.35. The truncated result is 12.34.

Round (number; precision)
• Input: number, number
• Output: number
Truncate (number; precision)
• Input: number, number
• Output: number

Unlike number formatting, using a function to return a result modifies the actual number input. Where a field on a layout is formatted to display say two decimal places, the actual number in the field may still have more decimal places.

Both Round and Truncate allow the use of negative numbers for the precision. Using a precision of -2 will drop all decimal places and round or truncate to the nearest 100.

Examples of Use

1. Round to nearest five cents

With no one or two cent coins in Australia, invoice amounts are often rounded to the nearest five cents. To do this is simple - multiply the amount by 20, round to zero decimal places, and then divide by 20:

Round ( amount * 20; 0 ) / 20

2. Truncate to the nearest 1000

Let's say that a tax is applied to each $1,000 in a purchase price. We need to be able to calculate how much to charge. If the total price is $34,567, the tax will be applied to $34,000 only. We can use:

Truncate ( price; -3 ) * tax_rate

Alternatively, if the tax was applied per thousand dollars or part thereof, we would need to add $1,000 unless it was exactly a multiple of $1,000. We can write:

( Truncate ( price; -3 ) + If ( price > Truncate ( price; -3 ); 1000 ) ) * tax_rate

The Replace and Substitute functions both replace strings of characters in text. Replace has more limited and specific operation than does Substitute.

Replace (text; start; numberOfCharacters; replacementText)
• Input: text, number, number, text
• Output: text
Substitute (text; searchString; replaceString)
• Input: text, text, text
• Output: text

The Replace function requires four parameters. You specify the text which you want modified. Then you specify where to start and how many characters to replace. Finally, you specify the replacement text to use. The function effectively removes the characters and then inserts the replacement text at that point in the string.

The Substitute function will replace every instance of a string in the text. The function is case sensitive, so if the search string is "A", only uppercase instances will be substituted.

Substitute also allows multiple and progressive substitution with pairs being enclosed in square brackets:

Substitute("Adam" ; ["A"; "a"]; ["d"; "l"]; ["m"; "n"]; ["A"; "Z"])

Using the above expression, "Adam" would be returned as "alan". Note that the last substitution pair has no effect on the result since there are is "A" left at that point. The pairs are processed sequentially.

Examples of Use

1. Display of a credit card number

You may want to obscure a full credit card number for display. We can use:

Replace ( cardnumber ; 1 ; Length ( cardnumber ) - 4; "XXXX..." )

This will display the replacement text and the last four digits of the card number.

2. Create a license code based on license name

Many license codes are based on the licensed user name. This is done so that license codes can only be used by a specific user. A very simple version could be:

Let ([
string = Filter ( Lower ( user ) ; "abcdefghijklmnopqrstuvwxyz" )
];
Substitute ( string ; ["a" ; "4" ]; ["b" ; "C" ]; ["c" ; "5" ]; ["d" ; "8" ]; ["e" ; "P" ];
  ["f" ; "2" ]; ["g" ; "/" ]; ["h" ; "6" ]; ["i" ; "T" ]; ["j" ; "R" ];
  ["k" ; "3" ]; ["l" ; "4" ]; ["m" ; "7" ]; ["n" ; "#" ]; ["o" ; "3" ];
  ["p" ; "L" ]; ["q" ; "D" ]; ["r" ; "X" ]; ["s" ; "8" ]; ["t" ; "$" ];
  ["u" ; "H" ]; ["v" ; "S" ]; ["w" ; "1" ]; ["x" ; "Y" ]; ["y" ; "E" ]; [ "z"; "9"] )
)

For a user "John Appleseed", the above would return a license code of "R36#4LL4P8PP8". Validation built into the solution would then need to reverse engineer the code to validate the code against the user.

Here are four text functions that all count something. That something could be a defined string of characters, the number of values, the number of words, or the number of characters.

PatternCount(text;searchString)
• Input: text
• Output: number
ValueCount(text)
• Input: text
• Output: number
WordCount(text)
• Input: text
• Output: number
Length(field)
• Input: text
• Output: number

The PatternCount function requires that you provide the string to search for. This could be hard-coded (as in "Monday") or it could be the name of a field or variable. In the latter case, the contents of that field or variable would be searched for.

The PatternCount function is not case-sensitive meaning that PatternCount ( "apple"; "PP" ) will return 1 even though the case if different.

PatternCount is also not inclusive meaning that PatternCount ( "banana"; "ana" ) only returns 1 even though the search string can be found twice. FileMaker Pro processes the text such that when it finds the first "ana", all that is left to process is "an" so only one instance is found.

FileMaker Pro defines a value as text separated by a return character (¶). So the string "green apple¶yellow banana¶red cherry" contains three values. Values can also be empty so "green apple¶¶red cherry" still contains three values. However, the last value may end with a return character but this is not counted as another value - "green apple¶yellow banana¶red cherry¶" is only three values.

FileMaker Pro defines a word as text between word separators. The following characters are currently recognised as word separators:

space ! @ # $ % ^ & * ( ) _ + = { } [ ] | \ : ; " ' < > ? / * ~

From FileMaker Pro 7, a hyphen, colon or forward slash is not a word separator if the characters before and after are numerals. This means that "a/b/c" is counted as three words whereas "10/02/2010" is one word.

Examples of Use

1. Word occurrences

You can count the occurrence of a string in a passage of text. So let's say we have a databases of famous speeches from history. We can search the "I have a dream" speech of Martin Luther King for the string "free".

PatternCount ( speech ; searchfor )

Note that speech is the field where the speech transcript is stored and searchfor is a global field where a search string is entered.

In this case, the count returns 25. However, since the PatternCount function is looking for a string and not whole words, this count includes instances of the word freedom. To have a more specific search for words, we could use:

PatternCount ( speech ; " " & searchfor & " " )

This will now return a count of instances where the string exists with a leading and trailing space. This returns a count of 3. Better, but now it does not include an instance of the word where it is followed by a full stop (period) at the end of a sentence. A more complex expression is required to search for whole words.

2. Creating related records

We might provide a checkbox list in a global field as any easy way for a user to select values for the creation of related records. In this example, the user selects interest groups for a person by clicking checkboxes. These selections are held in the global field as a value list. A script is then used to create a related record for each group selected.

The script might look like this:
If [ ValueCount ( Member::selected groups ) > 0 ]
 Set Variable [ $counter; Value:ValueCount ( Member::selected groups ) ]
 Set Variable [ $key; Value:Member::ID // remember primary key ]
 Loop
   Go to Layout [ “Interest” (Interest) ]
   New Record/Request
   Set Field [ Interest::ID member; $key // set foreign key ]
   Set Field [ Interest::interest group; GetValue ( Member::selected groups ; $counter ) ]
   Exit Loop If [ Let ( $counter = $counter - 1; $counter = 0 ) ]
 End Loop
 Go to Layout [ original layout ]
 Set Field [ Member::selected groups; "" ]
End If

The starting If statement uses ValueCount to test if there are any values to be processed. If so, the Set Variable step uses the value count to set a $counter variable which will be used to keep track of how many related records to create.

3. Validating data types

In Australia, a postcode consists of four and only four digits. We can test for that by testing the total length and also comparing the length of the data entered with a filtered version:

Length (postcode) = 4
and
Length ( Filter (postcode; "1234567890" ) ) = Length (postcode)

This will fail if either the data entered is not exactly four characters or if the filtered data (only digits) is shorter than the actual data.

This is another group of functions that can be learned as a group. Each takes text as an input, modifies it and returns text.

Upper ( text )
Lower ( text )
Proper ( text )

The Upper function returns the text as uppercase; the Lower function returns the text as lowercase; the Proper function returns the text with each word capitalised and all other characters lowercase. So if a field called suburb contains text such as st iVES,

Upper ( suburb ) = ST IVES
Lower ( suburb ) = st ives
Proper ( suburb ) - St Ives

None of the functions affect non-alpha characters such as numbers or punctuation.

Unlike text formatting on a layout object, these functions change the actual characters rather than just their display. So a field formatted on a layout to display in uppercase may still contain lowercase characters. This can be an issue when exporting text or displaying it on another layout without formatting.

Examples of Use

1. Ensuring Data is Stored in Uppercase

Some fields should always be entered as uppercase. An example is the state abbreviation in an address. Sometimes it is required that the city or suburb in an address is entered in uppercase.

This can be enforced easily with a field option that auto-enters a calculation and replaces the field contents:

Upper ( Self )

The Self function is used here to refer to the field where the option is defined. It can easily be copied and pasted for use in many other fields without editing. When set, this auto-entry will update the field text to be uppercase when the user exits the field.

2. Check for Case Usage

The Exact function can be used to test whether exclusively upper or lower case characters have been entered. Using this expression:

Exact ( codeField; Lower ( codeField ) )

This is comparing the data in codeField with an all lowercase version of the data. If it matches exactly, the function returns 1.

3. Mixed Case in Codes

Let's say we have a code field and the rules for entry of a code are that:

• codes are ten digits,
• any letters in the first five characters must be uppercase, and
• any in the last five must be lowercase

We can enforce this with an auto-entered calculation that replaces the field contents:

Upper ( Left ( Self; 5 ) ) & Lower ( Right ( Self; 5 ) )

This article covers nine functions that can all be understood as a group in three subgroups:

Left (text; numberOfCharacters)
LeftWords (text; numberOfWords)
LeftValues (text; numberOfValues)

Middle (text; start; numberOfCharacters)
MiddleWords (text; startingWord; numberOfWords)
MiddleValues (text; startingValue; numberOfValues)

Right (text; numberOfCharacters)
RightWords (text; numberOfWords)
RightValues (text; numberOfValues)

So each subgroup takes characters, words or values from the left, middle or right of the text. The middle functions require a starting value - which character, word or value to start from.

These functions are used extensively when parsing text - extracting parts of text defined by the starting position and number of characters from that point. There are countless examples of use of these functions. Here are some selected examples.

Examples of Use

1. Getting an Initial

If you know a person's first name (David), then you also know their initial (D). This is a simple calculation that uses the Left function to return the first letter from the left of the text:

Left ( firstName; 1 )

2. The Last Word

Sometimes you may be provided with data that has a person's full name in a single field. So you would like to extract the last word as the last name. You could use this expression:

RightWords ( fullName; 1 )

That is simple but you need to understand what FileMaker considers to be a word break. Obviously, a space is a word break. But a dash is also a word break. This means that a double-barrelled name like Fotherington-Smythe is actually two words. Using the above expression will return only Smythe from the full name.

One solution is to substitute out the dash with something that is not a word break character, extract the last word and then resubstitute the dash. So what is not a word break character? Any letter or number. We can't use letters because they may already exist in the last name so when substituting the dash back in we may end up with more dashes than expected. So let's use the number 1.

Let ([
 name = Substitute ( fullName; "-"; "1" );
 lastname = RightWords ( name; 1 );
 lastname = Substitute ( lastname; "1"; "-" )
];
lastname
)

3. The Domain Name from a URL

We may want to extract the domain name from a URL such as:

http://www.ulearnit.com.au/course_book.php

The logic here is that we want everything from the first character after the http:// to that before the next /. In fact, if we assume that every URL starts with http://, the first character we want will always be the eighth. So then we need to calculate the position of the third / to get the end point.

Let ([
 start = 8;
 end = Position ( URL ; "/" ; 1 ; 3 )
 ];
 Middle ( URL ; start ; end - start )
 )

The end is calculated as the position in the URL field of the / symbol starting from the first character and being the third occurrence. The domain name is then extracted with the Middle function starting from the eighth character and being "end - start" characters long. So if the end character is at position 18, the domain is 18 - 8 characters long.

The above assumes that that URL starts with http://. What if it starts with https:// or ftp://? Or what if it just starts with www? To account for all these possibilities, we would need to be more specific about the calculation of the start and end variables. However, the principle remains.

3. Reducing Value Lists

When we considered the GetValue function, there was an example of a recursive custom function that processed the first value in a list and then passed the list back to itself minus the first value. It kept processing until there were no values left.

Our custom function, ListLastWord ( thelist ), was this:

If ( ValueCount ( thelist ) > 0;
   RightWords ( GetValue ( thelist ; 1 ); 1 ) & ¶ &
   ListLastWord ( MiddleValues ( thelist ; 2 ; ValueCount ( thelist ) - 1 ) )
  )

In the context of this article, notice also that the custom function uses the RightWord function to extract the last word from the first value in the list.

The reduced value list is calculated using:

MiddleValues ( thelist ; 2 ; ValueCount (thelist) - 1 )

We can now look at this specifically and see how it works. It returns values from a variable called thelist. It returns values starting from value number 2. And the number of values it returns is the number of values in the list minus one. Thus, it is returning thelist without the first value.

You could also use this expression:

RightValues ( thelist ; ValueCount (thelist) - 1 )