Posted on

The A-Z of FileMaker: Y is for Year

Year (date) is one of the family of FileMaker date functions. It does something very simple – it extracts the year out of a given date. Now why would you ever want to do that? I’m glad you asked!

Reasons to use it

Summarising by year

When you want to summarise a set of records by the year of a date, you need access to the year itself. For example, if you wanted to group customer invoices by year (of invoice date), you can certainly sort the records by the date field. However, to summarise you will need a break field which is the year. It is a simple calculation field:

calculation yearThis will be the field used to define the sub-summary part:

subsummary by year

calculations based on year

There are a lot of useful calculations that involve dates. It is important to understand that a date is stored as a number. This means that when one date is subtracted from another the result is a number of days. If we are to use this result to calculate a person’s age in years, we would need to divide by 365 and then take the integer of the result. This will never be accurate around a person’s birthday due to having to account for leap years.

The basic calculation of a person’s age takes the current year and subtracts the year they were born. So we use the Year function on each date:

Year ( Get ( CurrentDate ) ) – Year ( DateBirth )

This returns an accurate whole number unless the person has not had their birthday in the current year.

An Accurate age calculation

To perform an accurate age calculation, we must include logic as to whether the person has had their birthday in the current year. In plain English, we would ask when their birthday is this year and compare that to the current date. This requires use of other date functions.

Their birthday in the current year is:

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

So, if this date is greater that the current date, they have not had their birthday – you need to subtract one (1) year from their age result above. Here is the full calculation using the Let function:

Let ([
  DOB = DateBirth;
  today = Get ( CurrentDate );
  birthday = Date(Month(DOB);Day(DOB);Year(today));
  notHadBirthday = (birthday > today)
  ];
  Year (today) - Year (DOB) - nothadBirthday
)

Remember to make this calculation unstored to ensure that it updates correctly each day.

Reasons not to use it

While the Year function is very useful, there are cases when you do not need to create a calculation.

Displaying the year of a date

If you need to display just the year for a particular date, that can most easily be done with date formatting. Select the field (or merge field text object) and set custom date formatting in the Inspector:

inspector custom date year

A field formatted in this way will display only the year. However, if the user clicks in the field, they will see the full date stored.

Finding records by year

You may be aware of the convention for finding a range of dates. When in Find mode, you enter a two dates separated by an ellipsis (…). For example, this

1/1/2017…31/12/2017

finds all dates in 2017. However, FileMaker allows a shortcut form when searching for all dates in a specific year – simply enter the four digit year and perform the find. FileMaker will construct the above date range for you and perform the search.

This shortcut is also available when searching for all dates in a specific month – searching for 12/2017 will find all dates in December 2017.

The shortcuts can also be used for range searches. Try searching for 2015…2017 and see what is returned.

Posted on

The A-Z of FileMaker: S is for Self

Self is a function which returns the content of the object to which it is applied. Rather than explicitly naming a field, using the Self function means that an expression can be easily applied to another context with the same result.

Conditional formatting

One area where you will see the Self function being used is in conditional formatting. Let’s say you have a number field, outstanding amount, that you would like to display red when the amount is more than $1000. You can select the field on the layout and choose Format > Conditional…. Then you set the condition as Value is…greater than…1000.

conditional format value is

Setting the condition is easy – FileMaker Pro provides a lot of comparison operations in plain English. However, if you want to see how it works, change the Value is to Formula is. This exposes the expression being constructed for you:

conditional formula is

The Self function is used to reference the field to which the conditional formatting has been applied.

The great value in working this way is that if you duplicate the field object and specify another field (say, invoice total) then the conditional formatting will apply to the new field value. No changes required. Magic!

Field options

Another useful area for using the Self function is in field options – specifically in calculated auto-entry and calculated validation.

For example, if you have a field for State in an address, you may want to ensure that only uppercase letters are accepted. So you can use a auto-entered calculated value that replaces existing values:

Upper(Filter(Lower(Self);"abcdefghijklmnopqrstuvwxyz"))

Working from inside out, this expression starts with the lowercase version of any value in the field (using Self). It then filters it to just letters of the alphabet. Then it returns it as uppercase.

So if a user enters ”  n. s .w” into the State field, it will be corrected to “NSW”. And the beauty of this is that the expression can be copied and pasted into the auto-entered calculation for any field and it will just work.

Tip

Create a custom function called CleanUpperChar (text) which uses the same expression but with a text parameter instead of Self. Then when using the custom function, you enter CleanUpperChar (Self).

Where else can Self be used?

Self can also be used in Tooltips. This can be useful to be able to display the full contents of a field when the field object is too small.

field self tooltip

You can also construct a conditional tooltip based on the value of the field. If this mirrored the conditional formatting set, the tooltip could explain what the formatting means. For example, a red figure displays the tooltip “overdue”, while an orange figure displays “pending”, and a green figure displays “paid”.

Self can be used in Placeholder text but I cannot find any practical examples of that use. Self returns the content of the field but placeholder text only displays when the field is empty! If you have a use case, please let me know.

Where is Self not accepted?

Before you get too excited and want to use the Self function in everything to abstract your code, there are many areas where you cannot use it. FileMaker Pro will tell you where these are:

One example of this is when using Replace Field Contents with a calculated result. It seems to make sense that you could apply the above expression to a field and quickly clean up all existing content. But you cannot use the Self function. Fortunately, you can still do it – you just need to explicitly name the field.

Self cannot be used in an If statement when scripting. Again, it seems like it would make sense to be able to run a script in the context of the current object. But no go!

Consider yourself to have been Self-actualised!

Posted on

The A-Z of FileMaker: I is for If statement

I is for If Statements

The word “if” indicates a condition and an action – in the event that the condition is met, the action will be performed. In real life, an example might be “If you eat your dinner, then you can have dessert”.

In the computer world, the condition is expressed in If statements as a Boolean test. We have previously explored Boolean values and expressions in another A-Z post.

FileMaker Pro uses if statements in both calculations and scripting. The structure of each varies slightly as we will explore below.

If function in a calculation

The If function is classified as a logical function. It has the following format:

If(test;result1{;result2})

If is the name of the function; test, result1 and result2 are parameters. The result2 parameter is optional and is shown in curly braces {} because it is optional.

When the test is evaluated, if the result is true the function returns result1; if the test is false the function returns result2. If the test is false and no result2 is specified, the function returns a null (empty) result.

Consider the following examples calculating a discount amount for VIP customers:

Example 1:  If ( IsEmpty (cust::VIP); ""; 0.2 )
Example 2:  If ( not IsEmpty (cust::VIP); 0.2; "")
Example 3:  If ( not IsEmpty (cust::VIP); 0.2 )

For VIP customers, the VIP field contains the string “VIP”. Each example uses the IsEmpty function to query the VIP field in the cust (customer) table.

Example 1 says that if the VIP field is empty, return a null result (“”); otherwise return 0.2 (20%).

Example 2 reverses the logic of the test – if the VIP field is NOT empty, return 0.2; otherwise return a null result.

Example 3 uses the not logic but does not explicitly state the false result so it will be null.

All three examples return the same result for any given data. Which is “best” is a matter of opinion. Some would say that #1 is easiest to read and understand – the not logic requires two steps to interpret. Others would prefer #2 over #3 for the explicit statement of the false result (even when it is null).

Did you know?

The FileMaker calculation engine also allows numeric results for the test where 0 (zero) is false and any non-zero numeric result is true.

Nested If functions

What if there were different levels of VIP customer – VIP1 and VIP2. How could you assign them different discount levels? You need to perform multiple tests with the following logic:

if the customer is VIP1 give them 10%
otherwise, if the customer is VIP2 give them 20%
otherwise, there is no discount

FileMaker Pro allows you to nest If statements so that result2 for the first If is another If statement.

If ( cust::VIP = "VIP1"; 
     0.1; 
     If ( cust::VIP = "VIP2"; 
          0.2; 
          "" ) 
    )

The parameters of each function above have been separated onto new lines to make it easier to read. However, even with one nested If, it is difficult to read and maintain. The Case function should be used instead.

Case function

The Case function simplifies the expression of progressive tests. The format is:

Case(test1;result1{;test2;result2;...;defaultResult})

The first test/result pair is required but there can be any number of following test/result pairs with an optional final default result (if all tests return false).

FileMaker Trivia

A calculation formula in FileMaker Pro allows a maximum of 30,000 characters. This places a technical limit on the number of tests in a Case statement.

The nested If statement above is more readable with a Case statement:

Case ( cust::VIP="VIP1"; 0.1; cust::VIP="VIP2"; 0.2; "")

This will also make it much easier to add new VIP levels in future.

The FileMaker calculation engine reads the expression and exits as soon as a test returns true. For efficiency, it is best to try to order tests with the most commonly expected results first.

Since most customers are not VIPs, the expression might be better written:

Case ( IsEmpty (cust:VIP); ""  ; 
       cust::VIP="VIP1";   0.1 ; 
       cust::VIP="VIP2";   0.2 ; 
       "")
What do you think?

It can be argued that there is no need to use If statements at all since any If statement can be written as a single test Case statement.

If script step

If statements are also used in scripting. The logic is the same as in calculations – perform a test, and act one way for a true result and another for false. The definition of ‘true’ is also the same.

Simple If script statement

Every If script step needs a matching End If step. The script shown above is the minimum structure required. There is no explicit action for when the test returns false. In that case, the script proceeds directly to the End If step and continues the script.

Else and Else If script steps

The Else and Else If script steps can be used within an If/End If structure. The screenshot below shows three possible structures for an If statement:

If statements in scripting

The Else script step is used in example 2 to provide a place for steps for when the test returns false. This is the standard If logic – “If the test is true then do this, else, do that”. When the If test returns false, the script skips to Else and then proceeds through the following script steps.

What do you think?

Advocates of explicit programming would argue that you should always include an Else step to be clear about what should happen when the test returns false. In this way, the developer is showing their intention for the code – even if they intend for nothing to happen.

There is no Case script step. Instead, you use Else If steps for progressive testing (as shown in example 3). At each test (If and Else If), when the test returns true the script performs the following script steps and then skips to End If. As for a Case statement in a calculation, it is most efficient to test for the most common results first.

What about this?

It is possible to write nested If statements with scripting (as below). But as with calculations, it is harder to read and harder to maintain. This was done before FileMaker introduced the Else If script step. You are well advised to avoid this type of scripting.

Nested if statements