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 Tuesday, 15 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 [email protected] 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 [email protected], 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 [email protected].

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 [email protected].

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.

The A-Z of FileMaker: Z is for Undo

And here we are at the end of the A-Z of FileMaker. But undo doesn’t start with Z. Of course, Z is the ubiquitous application keyboard shortcut for the undo command. Use either Ctrl-Z for Windows or Cmd-Z for macOS.

Over the years of FileMaker Pro versions, there have been multiple steps of undo added as well as new areas where undo can be used.

The many steps of Undo (and Redo)

If the undo command is available to you, there are usually many undo steps available (sometimes called an undo stack). This means that if you do something wrong, followed by a series of other actions, you can use the undo command repeatedly to step back through the actions.

Let’s say you delete an object on a layout. Then follow with resizing a field, making a label bold, then adding another field. That’s four actions. If you decide that you should not have deleted the object, and it would be difficult to recreate, then you can call the undo command four times.

If you accidentally went one undo step too far, you can step forward again using the redo command – Ctrl-Shift-Z on Windows or Cmd-Shift-Z on macOS.

The Edit menu shows which step is next to undo or redo. If there is no undo or redo available, the command will be greyed out and say Can’t Undo or Can’t Redo.

undo redo commands

The persistence and therefore availability of the undo stack varies according to the situation.

Where can you undo? And how persistent is it?

There are many places in the current version of FileMaker Pro where you can undo your actions. This has been greatly improved over the years. Note that this list is of places where there is a specific undo command available. In other places, you may be able to Cancel a dialog to effectively cancel all changes made there.

Confession

The main time I use undo is around 3am and it usually tells me that it is time to stop and go to bed.

Layout mode

As mentioned above, you can undo actions in Layout mode. This has recently been given great functionality in that the undo stack is persistent across mode changes. This means that you can make some changes to your layout, check them in Browse and Preview mode, and then return to Layout mode to undo any changes you need.

The undo stack for a layout is lost when you switch layouts.

HOT Tip

If you want to edit a number of layouts and maintain their undo stacks, you can do that in different windows.

Browse mode

Although there is undo available in Browse mode, it is very limited and not often useful. The undo stack is maintained for changes made within a field. For example, you could add some text, format a word in bold, delete some text, then add some more. These steps can be undone.

But the undo stack is lost as soon as you leave the field. If you have not committed the record, changes to the record as a whole can be undone with the Records > Revert Record… command. But that will revert all changes to the record since it was last committed.

The one place where undo in a field is very useful is when you paste in formatted text. If you paste and then immediately undo, it will remove all the formatting from the text and take on the formatting set for the field.

Script Workspace

When creating and edits scripts, undo is available. The undo stack is available until the Script Workspace is closed. Even if you save a script, as long as you do not close the tab in the Script Workspace, the undo stack will be retained. You can even run the saved script (without closing the workspace) then go back and undo changes!

Relationship Graph

Changes to table occurrences and relationships in the FileMaker relationship graph (File > Manage > Database) can be undone. The undo stack is persistent until you close the Manage Database dialog, or if you make changes in either the Tables or Fields tabs.

Important times when Undo is NOT available – beware!!!

If you are about to do something in a FileMaker solution that is destructive with no undo available, you will generally be well warned.

The following actions have no undo available:

  • deleting a record or deleting a set of records
  • Records > Replace Field Contents…
  • File > Import Records…
  • deleting a field or a table (and the data contained therein)
  • performing a find – there is no way to revert to the previous found set

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.

The A-Z of FileMaker: X is for XY Coordinates

In mathematics, a flat plane or surface is defined by two axes X and Y. On a screen or piece of paper, the X axis goes across (remember an X is “a cross”); the Y axis goes up and down (remember a Y looks like a tree). The position of any point is defined by its position on the X and Y axes – the point coordinates.

Layout positions

Let’s consider a FileMaker layout.  Object coordinates are based on the definition of the origin point – being 0 on each axis. On a FileMaker layout, the origin is at the top left of the layout. Display rulers (View > Rulers) to confirm:

origin layout coordinates

The coordinates of every point on the layout are measured right of and down from the origin point.

Function Trivia

There is a function – FieldBounds ( fileName ; layoutName ; fieldName ) – which returns the location, in points, of each field boundary and the field’s rotation in degrees.

For example, the expression:

FieldBounds ( Get(FileName); Get(LayoutName); "task")

returns:

20 82 350 132 0

for a field named task on the current layout of the current file. The first four numbers are left, top, right and bottom coordinates in points.

Units of measurement

FileMaker Pro offers three units of measurement for position and size – inches (in), centimetres (cm), and points (pt). There are 72pt in one inch. It is easy to switch between these units by clicking on the unit symbol between the two rulers, or by clicking on the unit in the Position tab of the Inspector.

Layout Design Tip

When working with position and size, it is strongly recommended that you work in points. This provides whole numbers which are easier to work with – 20pt instead of 0.278in or 0.706cm. Some options (such as those in script steps) must be entered in points.

Inspector – Position tab

The first tab of the Inspector is the Position tab. The first section in that tab is the Position section.

inspector position tab

Objects

For any selected object, the Inspector shows the position of the left, top, right and bottom points. The coordinates of the top left corner of the selected object in the screenshot are ( 20, 82 ) – 20pt right of, and 82pt down from the origin.

The Inspector is very useful for both determining and adjusting the position of an object on a layout. Click in any of the position boxes, type a number and press tab accept the change and move the object. The same can also be done for size.

Tip

Often a text object containing merge fields is larger than what is needed to display the data on the layout. You can resize the text object with the Inspector Position so that it is the appropriate size for data display.

Where an object needs to be in exactly the same position on two or more layouts, using the Inspector to set and confirm the position is priceless.

Layout Parts

If you select a part label (see below where the Body part is selected), the Inspector displays the position of the bottom of the part and the height of the part. This can be very useful to make accurate adjustments to the size of any layout part.

layout part size

With no objects and no parts selected, the Inspector displays the width of the layout (to the explicit right edge). Again, this is useful for making accurate adjustments to the layout width.

Window position

XY coordinates also come into play when creating new windows. The options for the New Window script step provide settings for the position of the new window:

new window options

The position is defined (down) “from Top” and (across) “from Left”.

FileMaker Annoyance

Why would the order of Position be defined Y then X instead of X then Y?

Unlike object position, the origin point used for most new windows is the top left corner of the screen. The exception is for card windows where the origin is the top left corner of the layout of the parent window.

The Move/Resize Window script step allows you to move a window by defining the position relative to the origin used for the window type:

move resize window options

If you want to reposition a window relative to its current position, use the functions Get ( WindowLeft ) and Get ( WindowTop ). For example, to move the window down 100pt, use the expression:

Get ( WindowTop ) + 100
Strange window positioning

FileMaker Pro will allow you to position a window off the edge of the screen by entering a negative value for the left position. This has been used by FileMaker developers over many years to create utility windows that the user does not see. Often this is done for a cleaner user experience – whatever happens in that window is not seen by the user and is not rendered by FileMaker Pro for performance gains.

The A-Z of FileMaker: W is for Window

A window is a device used to display the user interface of an application. When a user opens a FileMaker solution, it will open a new window. Since FileMaker Pro 7, there has been support for multiple windows for a single file.

Opening  a new window

The simplest way to open a new window is to choose Window > New Window.  A new window created in this way is a duplicate of the current window – same size, same layout, same found set, same sort order. However, the windows are independent – each can display a different layout, found set and sort order.

A developer can also use the equivalent script step – New Window. This provides additional options such as a choice of four window styles (see below), setting the window name, changing the layout and setting the size/position. There are also options to show or hide various window features such as the Close button (see below).

new window options

A developer can also use the Go to Related Record script step to create a new window. Using the Show in new window option provides the same options as above.

go to related record options

FileMaker window styles

Scripted new windows provide a choice of four window styles.

Document

This is the standard modeless FileMaker window. It is used to provide a second view into the file such as displaying a different set of records (perhaps from a different table) so that the two sets can be compared side by side.

Floating

This is just like a document window except that it is always on top of all other windows. If multiple floating windows are created, any one can be selected to be in front, but all floating windows will always be in front of any document windows.

floating window arrangement

Quirky behaviour?

If a script creates multiple floating windows, any of those windows can be selected and brought to the front. However, if a new floating window is created from an existing floating window (using Window > New Window), then that floating window remains behind all others (but still in front of document windows). Weird eh?

Dialog

A dialog window is modal. Opening a dialog window will prevent access to all other windows for the current file. The dialog window must be closed to resume use of other windows. Dialog windows are therefore used in a similar way to custom dialogs but with more flexibility in the design (being based on a layout).

Card

Card windows were introduced in FileMaker Pro 16. They are created inside the parent window and are modal to it – the parent window cannot be used until the card window is closed. However, card windows allow the use of other windows.

Being a new window, card windows can display a completely different context (layout) to the parent window. This makes them particularly useful in interface design.

Each window can have only one card window open at any time. The card window can be positioned outside the bounds of the parent window.

Window options

When scripting creation of a new window, there are options for display:

window display options

The options available vary by window style.

  • Document and Floating Document: all except Dim parent window
  • Dialog: all except Minimize and Dim parent window
  • Card: only Close and Dim parent window

When creating a new window, any of the available options can be turned off. However, developers are well advised to carefully test their choices to ensure that the user can proceed. For example, if there is no Close button on a Dialog or Card window, the user may not be able to close the window at all and therefore be effectively locked in the solution.

Window script triggers

There are four file triggers available through File > File Options… that relate to windows. They are:

  • OnFirstWindowOpen
  • OnLastWindowClose
  • OnWindowOpen
  • OnWindowClose

The OnFirstWindowOpen and OnLastWindowClose triggers are activated when the file opens and closes respectively.

OnWindowOpen is activated when any new window is opened for the file. This includes the first window when the file opens. It occurs after the OnFirstWindowOpen trigger.

OnWindowClose is activated when any window is closed for the file. This includes the last window is closed. It occurs before the OnLastWindowClose trigger.