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.

Get Started with FileMaker Pro now offered online

uLearnIT is pleased to offer the Get Started with FileMaker Pro course online. Now you can attend FileMaker training from the comfort of your office desk.

This course is presented using a webinar platform. Unlike a video recording or traditional webinar, sessions are designed to be interactive, allowing you to ask questions, take quizzes and to participate in discussions.

The course consists of seven sessions, each two hours long. Each session is structured in a similar way beginning with an introduction and covering any questions from the last session. This is followed by content presentation for the session and demonstration of concepts and techniques. Your instructor will introduce the exercises for you to complete between sessions. Each session will end with questions from the group.

When does it start?

Session 1 is free and is scheduled four times on Monday, 14 May and Monday, 21 May 2018.

The rest of the schedule, sessions 2-7, start from Monday 4 June. All these sessions are scheduled at 10am and 2pm on Mondays and Tuesdays.

Find out more

Get all the details about the online course including the session schedule, content for each session and cost to enrol here.

Sign up now

Sign up for either the free session or for the complete course here.

Searching FileMaker – text search operators

In our first post of this series, we looked at the FileMaker search engine – how to access it, and how it operates. In this post, we will start looking at specific searches as they apply to different data types for which you might search.

The article got so big that we had to split it up! We will consider searches in text fields to start.

Data types

When creating fields in a table, you specify a name and a data type. FileMaker provides several data types for fields:

  • text
  • number
  • date
  • time
  • timestamp
  • container

In addition, calculation fields are defined with a data type for the result. If a calculation field is searched, it behaves in much the same way as a standard field for the specified data type.

Searching in text fields

Text fields are the most common field type in databases. In FileMaker solutions, text is the default data type allowing a ridiculous amount of data to be stored in one field in one record. Text fields can store any characters –alpha, numeric, symbols.

The default search in a text field is “words beginning with”. So a search in an address field for st will find all records containing any words beginning with “st” – St Agnes Road, Storey Lane, Franklin Street, etc. Note that the word can be anywhere in the field – it is not “data begins with”.

What is a word?

A word is a string of characters delimited by a word separator. What is considered to be a word separator? A FileMaker Knowledgebase article on the subject notes the following as recognised word separators:

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

The article notes exceptions when characters like the hyphen, colon, forward slash, a single quote and a period (.) are not treated as separators.

In most cases, text field searches are not case sensitive. An exception is where the default language of the field is set to Unicode (Field Options – Storage). A search for dar will return the same results as a search for DAR or a search for Dar. So be lazy in your searches and don’t bother with the uppercase.

Search operators

FileMaker provides a number of operators (symbols) to perform searches other than “words beginning with”.

The first to consider is the asterisk (*). This represents zero or more characters – any characters. Thus, a wide-ranging search is performed by wrapping a string in asterisks like *dar*. This will search for records where the field contains the string dar anywhere in a word – start, middle or end.

Search far and wide

Perhaps the most wide-ranging search you could perform is *d*a*d*. This would find any record where the field contains dad in that order but anywhere in a word in the field. So it would find dad, David, dreadful, underhanded, and advantaged. But not addition or biddable.

Find ‘something’

The asterisk is very useful when searching for records that have something (anything) in a field. For example, if you wanted to find all contacts with an email address, just type * into the email field and perform the find.

The second operator is the at symbol (@). This represents exactly one character. So a search for s@mon will find Simon and Symon, but not Salmon or Simone. Note that when the @ symbol is used, the search is no longer for “words beginning with”.

The third operator is equals (=). This searches for an exact word. So a search for =ann will find Ann but not Anne or Anna or Annabelle or Annette.

Find nothing

The equals sign is very useful when searching for records with empty fields. For example, enter = in the company field to find all contacts for whom no company is recorded.

Two words, Any order

To search for two exact words in any order, precede both words with =. So a search for =hotel =grand will find both the Grand Hotel and Hotel Grand (but not Grandfather Hotel).

The fourth operator is two equals symbols (==). This searches for exact content in the entire field. So a search for ==Melbourne would find records with only the word Melbourne in the field and not Melbourne City or Melbourne CBD.

The fifth operator is the double quotes (“”). This searches for a phrase beginning at the start of a word. So a search for “little street” will find all records with that exact string somewhere in the address field but will not find one with Little Bourke Street. A search for Little Street (without the quotes) would find that record since it is searching for any record containing those two words in any order.

Combine with * to extend

If you add in the asterisk and search for *”e street”, it would find all streets with names ending in e.  In other words, the search starts from anywhere in the field, not just the start of a word.

The sixth operator is the exclamation mark (!). This searches for duplicates of the entire field. So a search for ! would find two records with Swanston Street but would not consider Cnr of Bourke and Swanston Street to be a duplicate.

Search for an operator as a character

So what if you want to search for one of the recognised operators in a field? Maybe you are looking for records containing * or @ or !. The backslash character (\) is used to ‘escape’ the following character in a search.

For example, if you search for info@ulearnit.com.au, there would be no records returned even if there was one with that email address. The solution is to escape the @ symbol like this: info\@ulearnit.com.au.

If you need to search for a recognised operator, you can also use quotes. For example, search for “@” or “!”.

next Up

In the next post, we will look at number and date searches.

Searching FileMaker – the search engine

This is the first in a series of posts about searching in a FileMaker solution. FileMaker provides a built-in search engine. Like other search engines, there are rules for how it works and various codes for specific types of searches. As a FileMaker user, it is important to understand this to get the most out of your experience with any FileMaker solution.

Search Engine

A search engine is a tool that provides an interface where a user can construct a ‘request’ and perform a search.

Perhaps the best known and most used search engine is Google. The initial interface is amazingly simple – enter a word or words relating to what you want to know about then press return. Google returns a list of matching web pages with links to access each. The web pages are sorted by ‘relevance’ according to the Google search engine.

Google search

The beauty of this simple Google search is that the details of how the search is done are hidden from the user. Google also provides an Advanced Search page where the user has more control over the search. Even in the simple search bar, there are syntaxes available to limit searches.

In the end, most people using Google just type in a few words, press return and see what they get.

FileMaker Search Engine

Each FileMaker client has a search engine built-in. So whether you are using FileMaker Pro on a desktop, FileMaker Go on iOS, or FileMaker WebDirect in a browser, you can search.

When you search in a FileMaker solution, the search returns records that match your request. The records returned are known as a found set. We looked at the found set in detail in The A-Z of FileMaker: F.

The found set is displayed in the status toolbar as shown below.

found set
Found set of 11 records out of a total of 478.

Click the Show All button to return to display of the entire table.

Find Mode

A FileMaker client provides up to four working modes – Browse, Find, Layout and Preview. Most of the time, you work in Browse mode where you can view, create, edit and delete data. Find mode is where you have access to the search engine.

Switching to Find mode is easy – use the View menu command or the keyboard shortcut (Cmd/Ctrl-F).  When working in Find mode, the menus and the status toolbar change. 

Browse mode menus and status toolbar
Find mode menus
Find mode menus and status toolbar

In Find mode, you work with Find Requests rather than Records. The simplest search is a single request containing data in a single field.

Find request
Find Request for records containing ‘insurance’ in the Being For field

Use the Perform Find button (or press return) to perform the search. FileMaker will either return a found set of records in Browse mode, or an error message:

Unlike Google, in a standard FileMaker find request, you must be specific about which field you are searching. To search in multiple fields, you will usually use multiple Find Requests. We will cover that in a later post.

In the next post, we will look at how the FileMaker search engine processes requests in different field types (text, number, date, etc), and some special searches you can do for each field type.

Free Training for your buddy – Get Started with FileMaker Pro in Sydney

Have you ever been to a training course and thought it would be good to have a buddy to work with after the course? Someone who remembers the things you forgot (and vice versa). Someone who understands what you are trying to do and can help. As they say – two heads are better than one.

Special Offer

uLearnIT is making it easy to bring a buddy along to training. Book two places for the Get Started with FileMaker Pro course in Sydney in February, use the coupon code FEB18, and one place will be free.

But hurry, this offer is only valid until 14 February 2018 for the Get Started with FileMaker Pro course running in Sydney, 22-23 February.

This course is great for people with little or no experience with FileMaker or databases. It starts at the very beginning and builds your knowledge and skills to build a custom solution in FileMaker.

What You Will Learn

The FileMaker Platform – overview of the products and what they do
Building a Database – three different ways to start a custom database
Inside a Database – looking at tables, fields and relationships
Displaying Data with Layouts – creating layouts, using text and field objects
About Relationships – using keys to form relationships, viewing related data
More About Layout Objects – formatting objects, using other layout objects
Calculation and Summary Fields – creating and using
Scripting to Automate Processes – creating and running scripts
Exporting Data – methods to extract data from your file
Securing Your File – how to protect your data file
Sharing Your File – how to provide access to other users
Next Steps – where to from here

Book Now

Visit the course schedule page to book your place now.

Enquiries Welcome

If you have any questions about the course, please call on 0481 25 22 14 or email info@ulearnit.com.au.

Get Started in Sydney in February – Confirmed

Are you looking for FileMaker training to get started with your custom solution? The Get Started with FileMaker Pro course is confirmed for Sydney – 22-23 February.

This course is great for people with little or no experience with FileMaker or databases. It starts at the very beginning and builds your knowledge and skills to build a custom solution in FileMaker.

What You Will Learn

The FileMaker Platform – overview of the products and what they do
Building a Database – three different ways to start a custom database
Inside a Database – looking at tables, fields and relationships
Displaying Data with Layouts – creating layouts, using text and field objects
About Relationships – using keys to form relationships, viewing related data
More About Layout Objects – formatting objects, using other layout objects
Calculation and Summary Fields – creating and using
Scripting to Automate Processes – creating and running scripts
Exporting Data – methods to extract data from your file
Securing Your File – how to protect your data file
Sharing Your File – how to provide access to other users
Next Steps – where to from here

Book Now

Visit the course schedule page to book your place now.

Enquiries Welcome

If you have any questions about the course, please call on 0481 25 22 14 or email info@ulearnit.com.au.

Start 2018 with professional development

The start of a new year is a time for reflection and planning. If your plans include some professional development and training, then you should consider our scheduled FileMaker courses in February and March.

New Year 2018

Courses

The courses currently scheduled are Get Started with FileMaker Pro and Building Effective FileMaker Solutions. Visit our FileMaker Training page for course outlines and video introductions to the courses.

Locations

Courses are scheduled in Australia in Sydney, Melbourne and Brisbane, and in Hamilton, New Zealand. Check the course schedule page for course locations and to reserve your place.

Reserve Your Place Now

All courses are currently open to reserve your place. Every course requires a minimum of six (6) persons to run. When you reserve your place, you will pay a small fee of $90. This fee is non-refundable if you decide not to attend. However, if the course does not run due to low enrolments, the fee will be refunded in full.

When the course is confirmed, you will be asked to pay the balance of $850. The full course price is $990 but we will give you a $50 discount to thank you for reserving your place early.

Once any course has been confirmed, all enrolments will be for the full price of $990.

A decision on each course will be made at least 21 days prior to the course date.

Enquiries

If you have any questions about the courses or would like to know about discounts for multiple bookings, please contact us here.

We look forward to meeting you at a FileMaker course soon.

Brisbane training courses scheduled

We have been running a survey asking for expressions of interest in alternative locations for our FileMaker training courses. We currently offer FileMaker training in major centres where demand is highest. But we can travel anywhere there is demand.

If you would like to attend FileMaker training closer to you, please complete our online survey. There is no obligation to enrol. We will schedule courses in locations where there is a good expression of interest. You can still complete our survey here.

Brisbane Wins!

As a result of our current survey responses, we have scheduled new FileMaker training courses in Brisbane in late October. We are still negotiating a venue somewhere in the CBD area. Course descriptions will be updated as soon as this is confirmed.

Get Started with FileMaker Pro
Brisbane CBD location (to be advised)
Mon-Tue, 23-24 October 2017
Building Effective FileMaker Solutions
Brisbane CBD location (to be advised)
Wed-Thu, 25-26 October 2017

These two day courses are at an introductory and intermediate level respectively. Course details including outlines and videos can be found on the Training page.

Bookings are open now for both courses and can be made through the Training Schedule.

Kia Ora New Zealand!

G’day to everyone in New Zealand. We bring great news – FileMaker courses are coming to you. We are starting on the North Island but there have been a couple of requests for courses down south too. So we will have to see how to make that happen.

Get Started in Auckland

Our beginner course, Get Started with FileMaker Pro,  is running in downtown Auckland on 10-11 July 2017.

Auckland NZ

This is a great course to get a solid grounding in FileMaker development. We assume nothing about your knowledge of the FileMaker platform. You will leave the course with all the skills needed to tackle your first project, or to get that existing project flying.

Build Solutions in Tauranga

To learn more about FileMaker development, you will need to go further afield. But what a great place to visit – Tauranga. We will be running our intermediate course, Building Effective FileMaker Solutions, at the Oceanside Resort in Mount Maunganui on 13-14 July 2017.

Mt Maunganui NZ

This course takes your FileMaker development skills to the next level. We get to dive into topics like relational design, scripting, calculations and user interface. We look at essential knowledge for building great multi-user solutions for your workgroup.

Bookings are open now

Each course has strict limits on numbers. While we would love to sell out, we don’t want you to miss out. So jump in and book online now.

See all the course details on our FileMaker training schedule. Email us any queries you have or go ahead and book your place. We look forward to seeing you in New Zealand.

Start with FileMaker Starter Solutions

One of the most common ways to start a new FileMaker database is by using one of the starter solutions. There are two levels of starter solutions available – four simplified files and sixteen advanced solutions. Where do you get them and what is the difference?

The starter solution files that ship with FileMaker Pro can be accessed through File > Get Started…. Scrolling down, you will come to ‘Choose a Starter Solution’ and the four simplified files – Contacts, Inventory, Content Management and Tasks. Each of these files has a ‘Create from This Starter Solution’ button. Simply click to create the new file on your desktop.

Scroll further down to the ‘Explore the possibilities’ section and click the link to ‘See advanced solutions’.

One of the most commonly used starter solutions is Contacts – a solution designed to store details of people and the company they work for. This is available in both the simplified and advanced versions.

So what are the differences and which one should you use?

Layouts

Contacts simplified has seven layouts – a startup screen, and a form/list pair for each of desktop, tablet and phone devices. The layouts are reasonably simple constructions using the Tranquil themes. The tablet and phone layouts are sized correctly for proper display on these devices. For each pair of layouts (form and list), there are simple and intuitive navigation buttons to switch between form and list views.

Contacts advanced has eleven layouts including a form/list pair for web clients, one for labels and a contact list for printing. Most of the layouts have a more complex structure including objects formatted with conditional visibility, invisible slide panels with button navigation, and objects off the layout edge required for some scripted operations. This means that it will be much harder for a novice FileMaker developer to both understand how all the layout objects work and to be able to modify the structure to fit their needs.

Data Structure

Both files have a very simple data structure with just one table – Contacts. This means that each is effectively a flat file. If a FileMaker developer was to create a Contacts file from scratch, they might create it with more tables. For example, there might be a Company table to separately store company details and to be able to link multiple person records, and a Notes table for storing notes with timestamps and categories.

Although both files have a single table, the simplified file has 31 fields while the advanced file has 56 fields. The difference is in the number of calculation fields in the advanced file (with just two in the simplified file). The simplified file focusses on fields for data entry, while the advanced file does some clever data manipulation with various calculations. Again, this will be an area where a novice developer may have difficulty understanding how the calculations work and why they are there.

Scripts

Contacts advanced has more than 20 scripts, many of which are used with event triggers such as switching layouts and resizing screens. Contacts simplified has just four scripts – one to explain what scripts are, one when the file opens, one to sort the contact list, and one to deal with new records on mobile devices. For the novice developer, this means that Contacts simplified is more obvious with what is going on as you use it. In contrast, Contacts advanced may do a lot of things automatically which can be harder to work out how and why.

Recommendations

So which one is best for you? Simplified is best. Why?

If you are a novice FileMaker developer with a need for a well structured Contacts solution, then Contacts simplified will be a great starting point. You can explore the file and easily add and remove fields according to your needs. For example, you might want to remove the Fax field – who still has one of those?! The file is ready for an upgrade such as adding  new table and creating a relationship from Contacts when you learn how that is done. The Contacts table already has a primary key field, albeit a serial number, so that is a good start and best practice for all tables.

If you are a more advanced FileMaker developer, you may be tempted to jump in and use Contacts advanced. I would recommend not doing this. Instead, I would encourage you to get a copy of Contacts advanced and pull it apart to see exactly how it works. When you see features that you like, work out how they were built (which fields, layouts, scripts and trigger they use) and then build them into your file. So even you can start with Contacts simplified and gradually make it more complex to suit your requirements.

Mentoring Service

Did you know that uLearnIT offers FileMaker mentoring? We can help you online or in person to understand any area of FileMaker development and give you the tools to build your own solutions.

If you are starting from a FileMaker starter solution, we can help you to understand how it is built and how to modify it to suit your needs.

You can start with a half hour session to see how it works. If you are interested, send us a message through our Contact form.