Fanfare please! FileMaker Inc., has released their latest upgrade to the FileMaker family of products. There are new versions now shipping for FileMaker Pro, FileMaker Pro Advanced, FileMaker Server and FileMaker Server Advanced.

The FileMaker Pro products have a swag of new features that will satisfy new users and experienced developers alike. Leading the way is charting. Yes, it is here! Now you can create a dynamic chart in FileMaker Pro. There are five chart types to start - bar, horizontal bar, line, area and pie. That should get you started.

Also look out for the very cool QuickFind. Now you can search all fields on a layout in one simple step.

In FileMaker Pro Advanced, there is a new feature I just love - the ability to copy/paste or import custom functions between files. Yay!!!

There are heaps of new features and the best place to learn about them will be the FileMaker web site. In coming days, we will be posting articles here about specific new features and how you might use them.

The FileMaker Server products have had an upgrade too. There are new options for backups, client statistics and the ability to set time limits on server-side scripts. In FileMaker Server Advanced there is now NO LIMIT to the number of FileMaker Pro users who can connect concurrently. And you can create Admin Groups for server management.

If you are looking to become a FileMaker 11 Certified Developer, have a look at the sessions on offer at the FileMaker Developer Conference in the FileMaker Training Series track. These sessions will be extremely useful for you and will provide some very handy resources for further study.

It's on again! The FileMaker Developer Conference 2010 in sunny San Diego. DevCon will run 15-18 August, 2010 at the Sheraton San Diego Hotel & Marina. Right now you can save US$200 with the early bird enrolment. Special DevCon room rates are available but hurry because there are a limited number available.

uLearnIT is proud to announce that David Head will be presenting two sessions at this year's FileMaker Developer Conference. These will be two sessions in the FileMaker Training Series track. Sessions in this track will cover key topic areas needed to help you prepare for FileMaker Certification. The FileMaker Training Series is the official curriculum published by FileMaker, Inc.. All attendees will receive a CD of the course material, exercise files, and videos. An additional fee is required. Limited seating is available.

Get all the information about the FileMaker Developer Conference at the FileMaker web site.

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.