The A–Z of FileMaker: C

C is for container

Container is a field type.

When you build a database, most of your fields will likely be text fields. Other standard data field types are number, date, time and timestamp. Although each of these have subtleties in how they store and present data, they are fairly obvious in their usage.

Why use container fields?

Container fields exist to store files such as a picture, a video or a PDF file. Such files are often referred to as binary files (as opposed to text files). Although some of their contents may be read as text, the file usually requires interpretation to ‘read’ the contents.

As opposed to storing data such as a text string or a number, container fields allow us to store documents or files. This can be extremely useful in creating a store of files that can be searched and accessed within a database structure. Many people will find use for a document management solution that stores PDF copies of files.

Inserting

There are a few different ways you can use to get a file into a container field and container fields have additional native functionality on iOS devices.

On the desktop, you can insert a picture into a container by:

  • drag and drop
  • Insert > Picture…
  • Insert > File…

Either of the first two methods will display the picture in the container field; the last method will display only the file icon and name. Your choice will depend on your intended use of the file – most users will be storing pictures for viewing while using the database.

On iPhone and iPad devices, you can insert images directly from the camera or from the Photo Library. Container fields also allow you to scan and capture a bar code with the camera, or to capture a signature as an image using sign-on-glass.

PDF files can be stored and displayed such that they are interactive – you can scroll through a multi-page PDF in a container field and use tools to zoom and print. To do this, the PDF must be drag-dropped into the container )or use Insert > PDF…), and the container must be formatted on the layout to be interactive (see later section on Formatting).

Storage

By default, files are copied into a container field and stored in the database file itself. This has two implications:

  • the original file can be moved, edited, renamed or deleted without affecting the stored copy
  • the database file will get larger, by approximately the size of the file inserted

When using the Insert menu to insert a picture, file, media or PDF, there is an option in the dialog to “Store only a reference to the file”. If you check this option, FileMaker will create a link to the original file in the container field. This will keep the database file size smaller. The link is used when the container field is displayed. If the file linked is missing, an error is shown in the container field – “The file cannot be found: <file name>”. This could happen if the file was moved or renamed.

Container fields have special field options under Storage.

You can change the option to store container data externally. This means that all files are stored outside of the database file in a folder structure created and managed by the FileMaker client. The folder structure is usually next to the database file in the computer filing system (there are special locations on FileMaker Server for hosted files).

For external storage, there is a further option for secure storage  or open storage. Secure storage creates a complex filing structure based on UUIDs and also encrypts the files. Files can only be decrypted by the FileMaker client and the FileMaker database. Open storage creates a logical filing structure of file/table/field with the raw files inside.

If any externally stored file is altered, the container file will display an error that the file has been tampered with (see above image). This is a security feature.

Formatting

Container fields on a layout can be formatted through Data Formatting on the Data tab of the Inspector (see below). These formatting options affect the size of the image displayed, alignment in the container, and whether the container field instance is interactive (important for PDF display and video playback).

Export

If the user has access to the field, they can export the contents of a container field using Edit > Export Field Contents….

Calculations

There are some calculations which are useful when looking at container field contents. Calculations can be used to get information about the file in a container field.

GetContainerAttribute ( Table::Container; "all" )

returns a return-separated list of a lot of useful attributes of an image such as file name, storage type and file size. The attributes are grouped into categories and have name pairs. Use parsing techniques to extract any attribute data you need. An example of what is returned is shown here:

[General] 
Filename: chart-1.png 
Storage Type: File Reference 
MD5: 00AE673591301E56D2EDFDFC53D21280 
File Size: 2355 
Internal Size: 74 
External Size: 2355 
External Files: 1

You can also query a single attribute, so:

GetContainerAttribute ( Table::Container; "filename" )

will return just the named attribute. The file name can also be extracted using:

GetAsText ( Table::Container )

Where the file is stored as a reference, the above expression will return the file name and the stored file path as a return separated list. For example:

file:landscape.png
filemac:/Macintosh HD/Users/johndoe/Desktop/landscape.png

Another GetContainerAttribute is filesize, the file size in bytes, which can also be returned using:

Length ( Table::Container)

For photos, you can access metadata if it is stored in the file. This may include latitude and longitude of the photo location, and make and model of the camera.

For signatures captured on an iOS device, use the signed attribute to get the timestamp when the signature was inserted.

The GetThumbnail function is very useful to return a down sampled version of an image stored in a container field (calculation result is container). The function format is:

GetThumbnail ( sourceField ; fitToWidth ; fitToHeight )

This example will return an image one third the size of the original:

GetThumbnail ( picField ; 
               GetWidth ( picField ) / 3 ; 
               GetHeight ( picField ) / 3 )
Want to know more?

uLearnIT runs FileMaker training classes in Australia and New Zealand. See the FileMaker Training page for more details.

The A–Z of FileMaker: B

B is for Boolean

A Boolean value is either true or false.

A Boolean expression is one that produces a Boolean value when evaluated.

FileMaker Pro uses Boolean expressions in a number of areas – calculations, scripts and object formatting to name a few. When creating an expression to be evaluated by the FileMaker calculation engine, you may use functions that either use or return a Boolean result.

If function

The If function has the format:

If ( test ; result1 ; result2 )

The test is a Boolean expression. If it evaluates to true then result1 is returned, otherwise result2 is returned.

If ( Year (dateBirth) >= 1995; "Gen Z"; "Other")

The above example of an If expression will return the string “Gen Z” if the year of the date of birth for the record is greater than or equal to 1995; in all other cases, it will return the string “Other”.

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

DID YOU KNOW?

FileMaker provides the Case function for evaluating a series of tests.

If script step

The If script step has the structure:

If [ test ]

   # do script steps here if the test is true

Else

   # do script steps here if the test is false

End If

As for the If function, the test in the If script step should return a Boolean or a numeric result. If the test returns no data (null) or does not resolve into a number, then it evaluates to false.

IsEmpty

The IsEmpty function has the format:

IsEmpty ( field )

It is a Boolean function returning 1 (true) if the specified field is empty; otherwise it returns 0 (false).

In addition to specifying a field name, you could also provide a text or numeric expression for evaluation.

Boolean Operators

The FileMaker calculation engine provides Boolean operators (and, or, xor, not) to construct more complex logic in expressions.

For example, the test in a If function:

If ( isEmpty ( discount ) and salescount > 10; 0.1 ; 0 )

For the test to return a true result (and therefore the function to return 0.1), both the expressions (separated by the and operator) must be true.

Other areas of use

You can also find Boolean expressions in:

  • other functions – e.g. Case, Choose
  • other script steps – e.g. Exit Loop If
  • conditional formatting and visibility of layout objects
  • security – record level access controlling view, edit and delete
  • custom menus – conditional installation
Did you know?

FileMaker reserves the words true and false. Each of these returns what it says. So what would the following inane expression return? This or That?

If ( true and not false ; "This" ; "That" )

The A–Z of FileMaker: A

A is for Auto-Enter

When using a data field (text, number, date, etc.), you can set field options. The first set of these are for auto-entry of values in the field. In most cases, the auto-entered data will be entered in the field when a new record is created. In some cases, the data can be updated when the record is modified.

Although the interface uses checkboxes for the choices, these are mostly exclusive (you can only choose one). And that makes sense.

Challenge: Find the non-exclusive choice and investigate how that behaves when two separate auto-entry options are set.

Creation

Data is set when the record is created. Choose from date, time, timestamp, name or account name (depending on field type).

The date and time are from the client’s clock. Name is the user name from the client’s preferences. Account name is the currently logged in account.

Modification

The data will be set when the record is created and updated each time data in the record is modified and committed. Choose content as for creation.

Serial Number

Sets a incremental serial number when the record is either created or committed. The next value is specified and an increment set (usually 1).

The last numeric ‘word’ of the next value is incremented. For example, for an increment of 1:

  • 1999 increments to 2000
  • A001999Z increments to A002000Z
  • A001-999Z increments to A001-1000Z
  • 100.9 increments to 100.10
Value from last visited record

Enters the data from the same field from the last record where any field was entered (but data not necessarily changed).

Data

A defined data string up to 255 characters. Often used to set a default value for a field for new records e.g. set the Status field to Draft for new projects. If a conditional string is required, use a Calculated value (below).

Calculated value

A value returned by evaluating a calculation expression at the time of record creation. When the option for “Do not replace existing value (if any)” is unchecked, the expression may be re-evaluated.

Looked-up value

A value looked up from another related table and copied into the field. This option has largely been superseded by the more flexible Calculated value (above). However, it does provide the additional option of copying the next smaller or larger value if there is no exact match.

Prohibit modification during data entry

If checked, will prevent a user from modifying any value in a field by keyboard entry. The auto-entered value could still be modified by a script step or when the record is modified.

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.

Come and learn FileMaker Pro 15

Well it has been a lot of work but enrolments are open for our first FileMaker courses for 2017. These will be the entry level FileMaker Pro 15 classes running in Sydney and Melbourne in March.

Get Started with FileMaker Pro 15 is an introduction to FileMaker Pro and creating custom database solutions. It covers everything from creating a new solution, setting up data structure, customising the appearance, securing and deploying your file and more.

The course is two full days and has been written from scratch to cover the most important skills of a beginning FileMaker Pro developer.

Check the course schedule now and book your place.