Searching FileMaker – numbers and dates

In our last post of this series, we looked at how text searches work with the FileMaker search engine. In this post, we will consider searches in fields defined as number or date.

Number fields

Fields defined to store number data behave differently to standard text fields. The first difference is in how the data is indexed. Indexing is used for searching data.

Imagine you enter the values 1, 1A, A1 and A into a number field across four records, and the same into a text field. The field index for each is as follows:

number vs field index

In the text field, each value is different. In the number field, the first three values are indexed as 1; the last value (A) is seen as an invalid value (?).

If you search in each field for A, the text field will return two records (A1 and A) while the number field will return one record (A).

Sorting by the number field results in the order 1, 1A, A1, A; while the text field returns 1, 1A, A, A1.

Although it is possible to enter non-numeric characters into a number field, it is not recommended. A field validation exists to enforce a strict data type for number fields being Numeric only.

Number searches

In the following examples, we will use a set of records numbered 1 to 1000.

When searching for numbers, standard searches return only the complete number. A search for 20 would return just one record. The same search in a text field would return 11 records – 20, 201, 202, … 208, 209.

To search for a range of values in a number field, use the ellipsis () operator. A search for 110…119 will return 10 records. The search is inclusive – all values from 110 through to 119. If they existed, this range search result would include decimal values such as 112.34 but not 119.01.

Interesting fact

The ellipsis can be entered as the actual symbol (on macOS option-; or selected from the operators list in Find mode). Or it can be entered as three periods. And FTW – it can also be entered as two periods! Still works.

To search for all values above or below a certain value, use the comparison operators:  >  <  ≥  ≤  For our sample records above, a search for ≥900 will return 101 records. Again, these symbols can be selected from the operators list in Find mode or they can be typed on macOS – option-> will give ≥.

Interesting fact

The ≤ and ≥ symbols can be typed as two characters as they are said. So type >= for “greater than or equal to”; type <= for “less than or equal to”.

FileMaker does permit wildcards in number searches. One wildcard specific to numbers is # which represents one missing digit. So a search for 9#0 returns 10 records – 900, 910, 920, … 980, 990. A search for ## will find all two digit numbers.

The asterisk * is also supported as for text fields. A search for 90* will return 11 records – 90, 900, 901, … 908, 909. A search for *99 returns 10 records – 99, 199, 299, … 899, 999. And *99* returns 19 records (try it out).

Date fields

In the following examples, we will use a set of 1000 records containing all the dates from 1/1/2016 through 26/9/2018.

Dates stored in date fields are handled as integers (whole numbers) representing the number of days since the beginning of FileMaker time. The beginning of FileMaker time is 1 January 0001. So the date, 2/5/2018 (2 May 2018) converts to 736816 (days since the start of time).

Doomsday

FileMaker also defines the end of time – it is 31/12/4000. Beware!

Therefore, when sorting by a date field, the records are effectively sorted in numerical order which will match chronological order.

A standard date search requires a valid date. An invalid date results in a search error:

invalid date search

As for number fields, searches in date fields can use the range and comparison operators. A search for 1/1/2017…31/3/2017 will return 90 records from January, February and March of 2017. A search for ≤31/12/2016 will return 366 records (2016 was a leap year).

FileMaker permits the use of the asterisk * wildcard in date searches. A search for */3/2017 will return 31 records (all the days in March 2017). A search for */3/* will return 93 records (all days in any March or any year). A search for */*/2017 will return 365 records (all days in 2017).

Rather than using the wildcard, you can also use a short version of the date for the search. For the above examples, you could search for either 3/2017 or 2017. If you search for 3, this will return all records in March in the current year.

Short dates can also be used in range searches. For example, 1/2017…3/2017 find all dates Jan-Mar 2017; and 2017…2018 finds all dates in 2017 and 2018.

How does it do it?

To understand how these abbreviated date searches are working, do the search and then Cmd/Ctrl-R (Records > Modify Last Find). This will show how a valid date has been constructed using wildcards. Try it out!

FileMaker will also allow searches by day of the week. Use either the short (Wed) or long (Wednesday) form of the day. A search for Wed will find all dates that fall on a Wednesday. And the search can be further narrowed by adding other criteria. Try these:

  • wed 2017 – all the Wednesdays in 2017
  • wed 3/2017 – all the Wednesdays in March 2017
  • wed 3…4/2017 – all the Wednesdays in March and April 2017
  • wed */3/* – all the Wednesdays in any March

And finally, the day search supports range searches such as mon…fri 3/2017 to find all weekdays in March 2017.

Searching for records containing today’s date (as defined by the client operating system) is easy with the // operator (two forward slashes).

next Up

In the next post, we will look at searches in other field types not covered so far.

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.