The A-Z of FileMaker: N is for Navigation (part)

FileMaker layouts are composed of parts. Layouts, and therefore layout parts, are used to display data and other objects. Parts can be used to control how data is displayed.

Navigation parts were introduced in FileMaker Pro 14. There are two available for any layout – top and bottom. On screen, these parts display at the top and bottom of the window. They are called navigation parts because they will typically contain buttons and other objects that help the user to navigate the solution and identify the current location.

Adding parts to a layout

The easiest way to add parts to a layout is using Insert > Part… (when in Layout mode). Some parts may be greyed out to indicate that you already have one on the layout (and you can only have one).

Insert > Part…

Removing parts

If you need to remove a part from a layout, click the part tab at the left when in Layout mode. Then press delete on your keyboard. If the part contains objects you will be asked if you are sure (delete the part and all objects in it), otherwise the part will be deleted immediately.

Aren’t navigation parts just a header and footer?

At first glance, navigation parts appear to behave in the same way as header and footer parts. Fields placed in any of these parts will display data from the current record when in Browse mode.

When working in List or Table view, these parts will behave in a very similar way. The top navigation part appears at the top of a window and then the header part below. The bottom navigation part is at the bottom of the window with the footer above. None of the parts move as the user scrolls through a list of records.

Displaying parts in Table view

If you use Table view, you may not see navigation, header and footer parts. You can turn on display of any of these parts for any layout via Layouts > Layout Setup… Then in Views, click the Table View Properties button to access display for these four parts. Not easy to get to is it?

table view properties

When working in Form view and the body part is longer than the window, the header will disappear off the top of the screen when the user scrolls down. Similarly, the footer will only be visible when the user scrolls to the bottom of the body part. If the body part is shorter than the window, the footer will appear immediately after the body part (perhaps halfway up the window).

A difference to note is that the navigation parts will always fill across the entire window. In Form and List views, header and footer parts only extend to the implicit right edge of the layout (set in Layout mode). In Table view all parts fill across the entire window.

Forcing a header or footer to fill across the window

This can be done by setting any object in the part with a right anchor. Interestingly, if this is done in either the header or footer, it will affect both parts.

To print or not to print

A major difference between the part types is that navigation parts do not print – they are intended purely for on screen use. Of course, this also means that navigation parts do not display in Preview mode.

When printing headers and footers, they will appear at the top and bottom of each page. However, unlike in Browse mode, the header will display data from the first record on the page and the footer from the last record on the page.

There also exist title header and title footer parts. When printing, these parts will appear at the top and bottom of the first page (replacing the header and footer).

Zoom locked

Unlike all other parts, navigation parts will not zoom. With any change to the zoom level, navigation parts and their objects will remain at 100%.

Formatting part display

All parts can be styled through the Appearance tab of the Inspector. First click the part tab at the left of the part in Layout mode. Changes can be applied to the fill (none, solid, gradient or image), line and inner shadow.

part formatting

The default style for each part is set according to the theme chosen. All parts share the same set of custom styles. So it is possible to use styles to ensure that your top and bottom navigation parts are exactly the same.

Conclusion – when to use navigation parts

Use navigation parts when you need an on screen area that is fixed to the top or bottom of the window. Use them to present buttons for various actions and information that needs to be constantly available.

The difference between navigation parts and header/footer parts is most evident when working with a long body part and scrolling in Form view. In this instance, it may be useful to use a footer part to contain a button that can only be actioned when the screen has been scrolled to the bottom. For example, if you want to force a user to scroll through terms and conditions (and pretend they read them) to get to an Accept button.

Look at some of popular apps like Facebook and Instagram to see how they are using the app equivalent of fixed navigation parts and a header that scrolls off the top of the screen.

The A-Z of FileMaker: M is for Merge

There are a number of methods to display data on a FileMaker layout, the most common being using field objects. Often it is more convenient and flexible to use merge items within a text object.

What can be merged in text?

Text objects can contain three types of merge data:

  • fields
  • variables
  • symbols

All can be accessed via the Insert menu while working in Layout mode.

Insert menu merge

What do merge items look like?

When looking at a text object in Layout mode, merge fields and variables in the text appear within double chevrons <<like this>> and <<$likethis>>; merge symbols are enclosed in double curly braces {{like this}}.

merge items in layout mode

Merge fields

When inserting merge fields, you can specify any field that you could physically place on the layout (current table, related or global). Text objects containing merge fields can be placed as you would normal fields – on the layout, in a portal, in a popover, etc – and will display the same data as it would for a standard field object.

The exception for using merge fields is container fields. If these are merged, the text object will display the container data file name.

Concatenating data

Merge fields can be very useful for stringing together (concatenating) data. A common example is when displaying a person’s full name – you will want a single space between first and last names or perhaps a comma and space between last and first names. This can be done using a calculation field but merge fields for display may save creating a number of calculations.

concatenate with merge

Note also in the above that the merge fields have been formatted with styling (bold and uppercase) within the text object.

Merge variables

You can insert both local ($) and global ($$) variables in text objects. The value of a local variable will only be displayed during the script in which it exists.

To ensure correct display, you will need to use either the Refresh Window or Refresh Object (specifying the named text object) script step.

Tip: Displaying page count

As in the above screenshot, you may want to display the page count in the footer of a report – Page 3 of 10. While the page number is displayed with a symbol, the page count is determined dynamically for the report. When in Preview mode, you will go to the last page using the Go to Record/Request/Page [Last] script step, and set a variable to Get ( PageNumber ).

Idea: use Merge variables as Layout calculations

There have been various feature requests over the years for layout calculations. This feature would reduce the need for calculation fields in tables. Merge variables may satisfy that request in a number of cases. During a script, you can define a variable using any calculation. Then you can display the result of the calculation on a layout using a merge variable.

Merge symbols

Merge symbols use the Get functions available in the calculation engine (see the blog article The A-Z of FileMaker: G is for Get for more). If the function used is Get (AccountName), then the merge symbol is {{AccountName}}.

The merge symbol displays the result of the Get function in Browse, Find and Preview modes. It is evaluated when the text object is drawn on the layout and when it is refreshed.

Challenge

If the {{CurrentTime}} merge symbol is placed on a tab panel that is not the default front tab, when will the current time be evaluated?

a. when the layout first loads
b. when the tab panel is first selected
c. every time the tab panel is selected
d. when any tab panel is selected

(answer is at the end of the article but you would be wise to experiment for yourself to see whether your results agree)

The most commonly used merge symbols are available for immediate selection – date, time, user name, page number and record number. All others can be accessed through the Other Symbol… command and dialog.

Formatting merge data

Data displayed by merge fields, variables and symbols can be formatted for display by applying Data Formatting via the Inspector to the text object.

Only one format per data type (number, date, time) can be applied to a single text object. This means that it is not possible to have two numbers in a single text object display with different formatting.

CHALLENGe Answer

b. when the tab panel is first selected

After that event, it will not be re-evaluated except if the window or object is refreshed. If the window is refreshed and the tab panel is not active, then it behaves as it does when the layout is first loaded – it evaluates next time the tab panel is selected.

Why is this useful to know? It contributes to an understanding of how FileMaker draws layouts and layout objects, and when it evaluates calculations. Such understanding will help a developer to create efficient layouts and troubleshoot performance.

The A-Z of FileMaker: L is for Loop

L is for Loop

FileMaker scripting provides the features of any structured programming language. Conditional and looping structures are key features.

Conditional structures like the If / End If in FileMaker scripting allow for different subroutines to run on the evaluation of a condition. See our previous blog article on If statements.

A loop is a series of instructions that are continuously executed until a condition is reached. A simple example is working through a set of records performing certain operations until the last record is reached.

Loops in everyday life

We often perform repetitive processes. These can be considered to be loops with an exit condition.

peel potatoes loop

For example, if you had to peel a bag of potatoes, you would prepare by getting the equipment required and opening the bag. You pick up a potato, peel it, rinse it and place it in a bowl. Then you check for more potatoes. If there are more, you pick up the next one and repeat – peel, rinse, place. When all the potatoes are gone, you finish and clean up.

Loop steps in FileMaker scripting

The basic loop script steps in FileMaker scripting are Loop and End Loop. The script steps placed between (‘inside the loop’) form the looping operation. Any loop is expected to have two components:

  • procedures to be carried out each time through the loop
  • an exit condition which, when true, will exit the loop
Infinite loops are bad

A loop without a valid exit condition is called an infinite loop – it will continue without ever exiting. This is not something you want in a script.

In many cases, there are steps before the loop to set up the environment. For example, if the loop is going to work through a set of records, you may ensure it goes to the first record of the found set before entering the loop.

Example: Loop creating new records from a list

Sometimes you have a list of values that you want to turn into a list of records.

In this example, we have a list of children’s names in a global field. A scripted button will create records related to the current parent.

The screenshot above shows the Parent layout. In the Children section, the names are in a global text field. Child records will display in a portal at the right. The relationship is a simple one to many from Parent to Child.

Why would you be doing this?

Good question. Sometimes it is easier for users to enter a list of data than to create new records. This may be by pasting from another source. Then you provide an easy process to create the required records.

The looping script will work through the child values creating a new record for each. The challenge here is that we do not know ahead of time how many children will be in the list. So the script will count the names to know how many times to repeat the loop.

The pseudocode for the looping script is:

Store the parent ID and the child list
Count the child records required
Set loop counter
Go to the child table 
Enter the loop
Create new record for nth child in list
Exit loop if last child processed
Else increment counter
Return to the parent table

Here is the script:

looping script

How does it work?

The first four steps are setting variables to store required values:

  1. primary key of the parent record
  2. list of children from the global field
  3. count of the children
  4. loop counter initialised to 1

Then we go to the Child layout to create new records in the loop.

Inside the loop, the first step is to create a new record. Two Set Field steps are used to set the foreign key (ID parent) and the child’s name. The child’s name is  extracted from the child list using the GetValue function and the $counter to know which one to take.

GetValue ( $children ; $counter )

The loop will then exit if the $counter is greater than or equal to the $childcount i.e. if the required number of child records has been created. If the loop does not exit, it continues on to increment (add 1 to) the $counter and then loop back to the start where it creates another new record.

When the loop exits, the script returns to the original layout (where it started).

Q. Moving script steps around

Consider what might be the positive effects of moving the Exit Loop If step to line 7 – being the first step in the loop.

[see the end of the article for a suggested answer]

Anything else needed?

This script is bare bones. At a minimum, you will probably also check that the user is happy to proceed and error trap to stop the script if no child names exist. You may also choose to clear the global field if the loop ends successfully.

dialog check record loop create

dialog error loop no children

The above example shows the simple structure of a looping process with:

  • setup before the loop
  • doing things in the loop
  • checking the exit condition
  • returning to the start
Loop alternatives

There are many times that a loop will be the right thing to do. However, you need to be aware of alternative methods and possible speed implications of looping scripts.

For example, if you need to loop through a found set of records and set fields according to some conditions, then consider using the Replace Field Contents script step.

If a looping script ‘touches’ every record on the way through, that may slow the process. Consider the methods to avoid opening and closing records, and even jumping to key records in the set. A Freeze Window script step before the loop may result in speed improvements.

A. Moving script steps around

If you move the Exit Loop If step to the start of the loop, this will have the positive effect that a new record would not be created unless needed. Since the $counter has been set to 1 before entering the loop, if there are no values in the children list ($childcount = 0), the loop will exit immediately.

What do you use loops for?

Feel free to leave a comment below.

The A-Z of FileMaker: K is for Key

K is for Key

In a relational database like FileMaker Pro, key fields are used to establish relationships between tables. Depending on where you get your information, there are many types of keys. In this article, I will focus on just two – primary and foreign keys.

Primary keys defined

A primary key (PK) is a field that contains data unique to the record in that table. As such, it can be thought of as a record identifier. There are some required characteristics of a primary key:

  • never be empty
  • contain unique data (in the table)
  • never change

It is also most useful if it is not real or meaningful data. To understand this better, let’s consider some fields that seem to fit the requirements for a primary key but should not be used as such.

Avoid using these as primary keys
  • tax file number (TFN)
  • email address
  • student number
  • phone number

Although most people in Australia (and certainly those who are employees) will have a TFN, it can fail the PK test. First, if a person cannot provide their TFN for whatever reason, the field is left empty. Second, if the number is wrongly entered, it will need to be changed.

An email address could be shared by multiple people and may change.

A student number is generated by another database system. As for a TFN, it may not be available or may be incorrectly entered. Student numbers may be re-issued in some circumstances.

A phone number may be shared by multiple people and will likely change.

Good primary keys

The best primary keys are generated by the database system in which they are used.

The most basic is a simple serial number. In FileMaker Pro, the field option can be set to auto-enter a serial number on record creation. This will ensure sequential creation of unique serial numbers even in multi-user systems.

serial field option key field

Many database systems can generate a universally unique identifier (UUID). In FileMaker databases (as in many others), the UUID is a unique 16-byte (128-bit) string. In the standard representation, the UUID is 32 hexadecimal (base 16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12. An example of this is:

012F5382-37FF-413B-A93C-D957A5C6F451

The function used to generate this is Get ( UUID ). To create a primary key in this way, set the field options to auto-enter a calculation that uses the function. We covered this (Set a primary key) in a previous blog article The A-Z of FileMaker: G is for Get.

Foreign keys defined

A foreign key (FK) is a field that contains a unique identifier from a PK of another table. In this way, the record with the FK is linked (or related) to the record with the key data in the PK.

Consider a FileMaker solution for recording issues with equipment. There is a table for equipment with a PK field. There is a table for issues with a PK field. The issues table also has an equipment FK field.

When a new issue is created, the unique identifier of the equipment is copied into the equipment FK field. This serves to relate the issue record to a specific equipment record.

By definition, the values in a FK will not be unique. In the above example, all issue records for a specific equipment item will have the same data in the FK.

Usually the FK will not be empty. In the above example, if the FK is left empty, the issue is not attached to any equipment and would be considered invalid. In some cases, it may be valid to leave the FK empty.

Generally the FK will not change. If it does change, this means that the record is relating to a different record. In the above example, the FK may be changed to correct an error when the wrong equipment was identified.

Referential integrity

Referential integrity refers to a rule that every foreign key value must match a primary key value in an associated table. In other words, the FK value must exist in one record of the other table.

Naming Keys – What do you think?

Most developers have opinions and conventions for naming their keys. For what it is worth, I name all my PK fields with a simple name – ID. I find that the context of the field is usually self-evident. It also allows me to copy and paste a PK into a new table.

I name my FK fields simply too – IDtable. In the above example, the FK would be named IDequip or IDequipment.

Here is an ERD from a previous post on the Join Table.

Do you have a naming convention for keys and a reason for using it? Feel free to leave a comment below.

Other keys

Often when we import data from other systems, we may be provided with a key from that system. It may seem like we have a ready-to-use PK supplied for us. Beware! Do not use the PK from another system. However, it is useful to store it with our data. In reporting, we can then refer to records with our key or their key.

The A-Z of FileMaker: J is for Join Table

J is for Join Table

A join table is used to resolve a many to many relationship with a flexible structure for data storage.

The FileMaker Platform works around a relational database. The structure of a relational database includes tables and relationships between tables. There are three major types of relationships:

  • one to many
  • many to many
  • one to one

The most common relationship is one to many. This is where one record in table A may be related to many records in table B. Conversely, each record in table B is related to only one record in table A.

For example, consider customers and sales. One customer is (hopefully) related to many sales. Each sale is related to only one customer.

Other examples of one to many relations are:

  • one venue – many events (each event = one venue)
  • one employee – many payments (each payment = one employee)
  • one diver – many dives (each dive = one diver)
  • one student – many certifications (each certification = one student)

These are often represented with a crow’s foot notation where the crow’s foot is the ‘many’ end of the relation;

When working through the data structure for a database, we often come across many to many relationships. Some example are:

  • houses and owners – one house has many owners (over time); one owner owns many houses
  • students and classes – one student enrols in many classes; one class has many students enrolled
  • cars and mechanics – one car can be serviced by many mechanics; one mechanic services many cars
What is the problem with many to many relationships?

While it is possible to create a many to many relationship in FileMaker Pro and other database platforms, it is generally not done. When we find a many to many relationship, it presents a problem with the storage of data associated with the relation.

For example, with a relationship between houses and owners, where would you store data for the sale price and date purchased? If you put fields in the house table, then you will need to store a price and a date each time the house is sold; if you put them in the owner table, you need a price and a date each time they buy a house.

A join table is the solution

To correctly model a many to many relationship, we break it down using a join table. This is a table that represents the relationship between the tables. There is often an action word that represents the relation:

  • owners purchase houses; houses are purchased by owners
  • students enrol in classes; classes take enrolments by students
  • cars are serviced by mechanics; mechanics perform services on cars

In the case of house and owner, we add a table between called purchase. There is a relationship between owner and purchase (one owner makes many purchases; each purchase made by one owner). And there is a relationship between purchase and house (each purchase is for one house; one house can have many purchases).

Each purchase record relates to one house and one owner. The purchase record is the right place to store data such as sale price and date purchased. So this solves the data storage problem with one purchase record created every time an owner purchases a house.

Many to many resolved

Each table will have a primary key (named ID in each table below). The relationships are created to foreign keys (ID owner, ID house) in the join table.

When creating a purchase record, you need a process to select the owner and the house. The primary key (ID) of each is inserted into the foreign key fields of the purchase record to create the join.

Your turn

Draw the tables and relationships required for the other many to many examples above. Name the join table and the foreign keys required.

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

The A-Z of FileMaker: H is for Host

H is for host

With the FileMaker Platform, you can share data in a file over a network. Many users can access the same data set at the same time.  The shared file is open on a host device and available on the network. In the FileMaker world, the host is a computer (rather than a mobile device), but it may be a physical or virtual computer.

Another name for a host like this is a ‘server’. All servers are hosts, but not all hosts are servers. Only hosts that accept connections from other devices (clients) qualify as servers.

How to host a file

There are two FileMaker products that can host a FileMaker file – FileMaker Pro and FileMaker Server. You should consider FileMaker Pro as a host only for testing or for a limited timeframe. FileMaker Server is a robust and performant host recommended for all long term hosting.

FileMaker Pro as host

Hosting a file with FileMaker Pro is quite simple. Open the file and choose File > Sharing > Share with FileMaker Clients…. In the FileMaker Network Settings, turn Network Sharing on. Then choose a currently open file from the list and set network access to All users.

FileMaker Network Settings for host

FileMaker Pro is a limited host – it can accommodate up to five connected clients. Those clients can be either FileMaker Pro or FileMaker Go (no web clients). If the host needs to close the file for any reason, all clients must exit.

FileMaker Server as host

Install FileMaker Server software on a dedicated server machine. It is beyond the scope of this blog article to consider all the requirements for the server configuration. When the FileMaker Server has been installed and configured properly, upload files to the server from FileMaker Pro.

Open the file and choose File > Sharing > Upload to FileMaker Server…. In the resulting dialog, choose the FileMaker Server on the network and supply server admin credentials.

Did you know?

FileMaker Server can host up to 125 files simultaneously and easily accommodate 100 concurrent clients accessing this files.

Connecting to a hosted file

When you use FileMaker Pro, you can open a file stored on your local device as a single user. By definition, a hosted file is available on the network. To connect to a hosted file with FileMaker Pro, you choose File > Open Remote…, then choose a host and a file.

As with local files, you may need to provide account credentials to open the file. You are strongly advised to ensure that all hosted files are password protected. This will stop casual networkers having a look at the file. And obviously, you should use strong passwords.

Did you know?

Network traffic travels in and out of devices through numbered ports. The standard port for web traffic between your browser and the web server is port 80. Standard FileMaker traffic uses port 5003. When you are using FileMaker Server with SSL enabled, it also uses the standard SSL port 443.

Why use FileMaker Server as a host?

FileMaker Server provides many more important hosting features than does FileMaker Pro.

  • Scalability and Performance
  • Security – SSL connection, host AES-256 encrypted files, logging
  • Sharing – WebDirect and custom web publishing (PHP), OBDC/JDBC support
  • Maintenance – scheduled backups and procedures, web management console

For more details, refer to the FileMaker web page for FileMaker Server.

The A-Z of FileMaker: G is for Get

G is for Get (functions)

The FileMaker calculation engine provides almost 300 built-in functions. Plug-ins and custom functions provide even more. The largest group of built-in functions is the Get group. FileMaker Pro 15  includes 116 Get functions and more are added each version. In the calculation engine, you can type “get” and press the return key to see all the Get functions:

Get functions

Inline help shows what each function will return.

The basic structure of a Get function is:

Get ( InformationName )

where the InformationName is the information returned by the function. Get functions do not have any parameters – they query the system and return information.

When was that?

Perhaps the most commonly used Get function is the one that returns today’s date:

Get ( CurrentDate )

The “current” date is provided by the machine where the calculation is evaluated  – that may be a desktop client, mobile device or server. To be consistent in the date used for a hosted solution, it may be better to use:

Get ( CurrentHostTimestamp )

While this will return both the date and time from the host, it can be explicitly coerced to the date:

GetAsDate ( Get ( CurrentHostTimestamp ) )

Alternatively, if the calculation returns a date result or is inserted in a date type field, then FileMaker will coerce the date for you.

Note

The GetAsDate function used above is not classified as a Get function. There are 9 GetAsType functions that coerce data to a specific data type. There are 14 other functions that also start with Get such as GetNthRecord and GetLayoutObjectAttribute.

Did you know?

The Get ( CurrentDate ) function has been known by different names in previous versions of FileMaker Pro. Most recently, it was Status (Current Date) and before that it was Today.

Other Get functions that return date and/or time are:

  • CurrentTime
  • CurrentTimestamp
  • CurrentTimeUTCMilliseconds
Timing of evaluation

As with all calculations, you should know when the expression will be evaluated and re-evaluated if necessary.

When using the Get ( CurrentDate ) function, a developer will usually expect it to be using the current date. For example, this is a calculation for the ageing of invoices that returns the number of days since the invoice was issued:

If ( InvoiceDate < Get ( CurrentDate ) ; 
     Get ( CurrentDate ) - InvoiceDate ; 
     0 )

If the calculation field is set up with default storage (stored result), the expression will be evaluated when the invoice date is entered or modified. However, if the solution is closed and re-opened the next day, the expression will not be re-evaluated using the new current date. For an invoice issued on a particular date, the age of the invoice may stay at zero (0) days forever.

To ensure that the expression is re-evaluated as needed. the developer should set the calculation storage to unstored by checking the option for “Do not store calculation results — recalculate when needed”:

calculation storage options
Set Storage Options… in the calculation dialog box
Querying security

There are a number of Get functions that return information about the current security level of the user. These include the account name, privilege set name and list of extended privileges. You can use these functions to control scripted operations while using proper FileMaker security setup.

Extended privileges are great for assigning privileges to multiple privilege sets. For example, in a medical system, a custom extended privilege to allow prescribing of drugs may be assigned to a number of privilege sets (chief MO, doctor, head nurse, psychiatrist) but not to others (nurse, administrator).

The function Get ( AccountExtendedPrivileges ) returns a list of enabled extended privileges for the logged in account privilege set. The following expression will check for the existence of the extended privilege canPrescribe in that list:

Length (
  FilterValues ( Get ( AccountExtendedPrivileges ) ; 
                                     "canPrescribe" )
       ) > 0

The expression can be used in an If or Else If script step to approve the running of the script steps that follow. Using a custom extended privilege in this way allows for quick and simple assignment (or revocation) of privileges to multiple privilege sets.

Query the device and network

Some Get functions provide information about the device and the network being used:

  • Device
  • HostIPAddress and HostName
  • NetworkProtocol and NetworkType
  • ConnectionAttributes and ConnectionState
  • PersistentID, SystemIPAddress and SystemNICAddress

You can identify and log devices and clients with these functions, as well as checking that the network is appropriate for connection.

Other Get functions check the client and host software:

  • ApplicationVersion, HostApplicationVersion, SystemVersion
Query the state of the solution

A number of Get functions allow the developer to check what is going on in the solution and act appropriately.

  • FoundCount, RecordNumber
  • RecordOpenState
  • LayoutName
  • WindowInfo (there are 14 of these such as WindowName)

For example, Get (FoundCount) is commonly used in scripting to determine what to do next. Different actions may be appropriate depending on if there are zero, one or many records found.

If a developer uses a consistent layout naming convention, Get ( LayoutName ) can be used to determine the context and the next action. In a script:

If [ PatternCount ( Get ( LayoutName ) ; "customer" ) ]
     # do this for customers
Else If [PatternCount ( Get ( LayoutName ) ; "invoice" ) ]
     # do this for invoices
Set a primary key

It is common to use a universally unique identifier (UUID) as a primary key value rather than a serial number. This is made easy by the use of the Get (UUID) function. This returns a unique 16-byte string such as:

35D390F0-3602-4CE0-8014-F4990A845934
Developer Tip

When creating a primary key field, use the Get (UUID) function in an auto-enter calculation. Remember to turn OFF “Do not replace existing value of field (if any)” to prevent duplicate UUIDs.

Set and get file locations

Get functions can be used to set storage locations for files when sending to PDF or exporting records, and for retrieving files for email attachment and other processes.

  • DesktopPath, DocumentsPath, TemporaryPath
  • FilePath, FileMakerPath, PreferencesPath

For example, a developer could define the path and file name for a PDF file as:

Get ( TemporaryPath ) & 
Cust::custCode & "_" & Invoice::invNo & ".PDF"

If the developer sets this to a variable, that variable can be used for defining the file attachment in a following Send Mail script step. Since the PDF is saved in the temporary path location, it is cleaned out at the end of the session.

And so much more…

As stated above, there are over 100 Get functions. Clearly there is not room in one blog post to cover even half of them.

As a FileMaker developer, you would be wise to spend time familiarising yourself with many of them including understanding exactly what each returns in what circumstances. Get functions will often return variable results based on where they are evaluated (FileMaker Pro, FileMaker Server, FileMaker Go, FileMaker WebDirect).

Did you know?

uLearnIT runs FileMaker classes. You will learn about Calculation and Summary Fields in the Get Started with FileMaker Pro 15 course.  Learn more about FileMaker classes.

The A-Z of FileMaker: F

F is for find the found set

One of the advantages of using a database rather than a spreadsheet is the innate ability to find data. Sure, with a spreadsheet you can apply filters but it is not really the ‘wheelhouse’ of a spreadsheet.

When using FileMaker Pro, the status area shows how many records are in the table you are viewing on the current layout. In the top screenshot below, the current table has 102 records in total (and the current record is #98).

find results in found set
All records (102 total) and found set (62/102)

After a find request has been performed, there are 62 records in the found set. When you show all records, the found set is the entire table.

Why is the found set important?

Many operations in FileMaker apply to the found set of records (and therefore not to the ‘unfound set’). The following will apply only to the found set:

Replace Field Contents
Delete Found Records
Export Records
Constrain Found Set
Sort Records
Save Records as PDF or Excel
Print

It is commonplace to find a set of records before performing these operations. For example, if you wanted to print a daily invoicing report, you would first find invoice records from the day, then sort and print the found set.

Records not in the found set are effectively excluded or protected from bulk operations.

How is a found set managed?

As shown above, the most common way you will create a found set of records is to perform a find.
Continue reading “The A-Z of FileMaker: F”

The A–Z of FileMaker: E

E is for encryption

Encryption is the act of encoding data so that it cannot be understood by normal means. To be understood, it needs to be decrypted, and that can only be done by authorised parties.

Did you know?

Cryptography is “hidden or secret writing”; steganography is “covered or concealed writing”. The difference is privacy versus secrecy. Both date back to the classical Greeks with Spartans using the scytale transposition cipher, and Herodotus reported using tattooed messages on a slave’s head hidden by regrown hair.

Clearly, encryption is done to protect data and is often an important part of a security strategy. In the FileMaker Platform, there are many areas where data is encrypted – some of these are optional, others are the standard.

Challenge

Before reading any further, how many areas of FileMaker products can you name that use encryption? Consider all products in the FileMaker Platform.

Encrypted passwords

When you create a FileMaker security account, you specify an account name, password and select a privilege set. The password is encrypted and stored in the file. The encryption method used is a one-way hash, meaning that the stored encrypted password can never be decrypted.

So how does FileMaker Pro check that your password is correct when you log in?

The password you enter is encrypted by the FileMaker client using the same hash used when storing the account password. The your encrypted password is compared with the stored encrypted password, and if they match you are allowed access.

A one-way hash is also used for the FileMaker Server Admin Console password.

Did you know?

Before FileMaker Pro 7, passwords were stored in plain text. Hacking utilities existed that could easily extract passwords from files.

Database (file) encryption

Data stored in a standard FileMaker file is encoded but not encrypted. This means that if you can decode the file, the data is there in plain text. You do not need a password. Of course, you do need access to a physical copy of the file. Maybe you ‘found’ an old backup copy stored on a drive?

Starting with FileMaker 13, database files can be encrypted. This is referred to by FileMaker as Encryption at Rest ( EAR). Using the Developer Utilities in FileMaker Pro Advanced, a file or files can be selected and the option applied to Enable Database Encryption. To do this, you must supply a Shared ID, Full Access account name and password, and an Encryption Password.

The Shared ID is used to link multiple files in a solution.

It is strongly advised that the Encryption Password be complex, and as with other passwords, it is case sensitive. Write it down and store it securely! If you ever lose the Encryption Password for your file, no-one will never be able to access the file again (not even FileMaker Inc.).

When a file is encrypted, the user must enter the Encryption Password to open the file before entering their account credentials. The exception to this is when the file is hosted by FileMaker Server – the server can be authorised to host the file and store the Encryption Password so that users only need their account credentials to access the file. Importantly, closed files or backup copies made by the server are encrypted at rest. So the contents are secure even if someone can access a backup copy.

FileMaker uses AES-256 in CBC mode for file encryption. This is the encryption level required at the highest levels of government for top-secret data.

SSL for network communications

Data transferred from FileMaker Server to a FileMaker client over a network is not secured by default. It is possible to collect and decode packets of data sent. FileMaker Server allows the configuration of SSL with a custom certificate.

Did you know?

The standard FileMaker SSL certificate installed by default on FileMaker Server is intended for test purposes only. A custom SSL certificate is required for production use.

Enabling SSL on FileMaker Server will encrypt network traffic between FileMaker Server and:

  • FileMaker clients – FileMaker Pro, FileMaker Go, FileMaker WebDirect
  • remote Server Admin Console sessions

It will also encrypt:

  • files uploaded from FileMaker Pro to FileMaker Server
  • progressive download of interactive container data

It will not encrypt data between FileMaker Server and ODBC sources – this requires SSL configuration on the ODBC source. The same applies for communications withActive Directory or Open Directory for user authentication.

Did you know?

If you use FileMaker Pro as a host (for up to five guest clients), you cannot secure network communications between host and client.

Email notifications and directory services for FileMaker Server can also use SSL to encrypt communications.

Secure container storage

When you specify external storage for a container field, there is an option for secure storage. When this is selected, container data is stored in an obscured folder structure outside of the database file and each document is encrypted.

A typical secure storage folder structure is shown below. There are encrypted files named 2E85 and 112C. The file path is a UUID broken into parts.

The encryption method used for secure container storage depends on whether the file itself is encrypted. For encrypted files, secure container storage uses AES-256 in CBC mode; standard files use AES-128 in CBC mode.

The encryption key is held in the FileMaker file and is needed to decrypt the stored files. Without the FileMaker file, the stored files are not accessible.

FileMaker Go

FileMaker files stored on an iPad or iPhone device in FileMaker Go will be encrypted using the Apple iOS encryption as long as the device is passcode protected. There is nothing required to implement this encryption and the user does not require any encryption keys to access the files. This means that local files on a device are secure if the device is lost or stolen – the passcode is required to copy the files from the device.

Challenge Answer

Five – passwords, files, network traffic, container data and FileMaker Go files. Did you get all these encryption areas before you started reading? Are there any we have missed?

Did you know?

The author, David Head, has presented several FileMaker Security sessions at the annual FileMaker Developer Conference. Last year in Las Vegas, he presented a session titled Cryptography, SSL & the FileMaker Platform.