All News

Let’s remind 2 tips that are as old as the world in FileMaker, but that we still use every day:

Sort an external table on one or more fields of other occurrences

Let's take a simple example, your database has 3 tables, schools, classes and students. At the relational level, students belong to a school and a class, and classes belong to a school. Which gives us the following relationships graph:

Graphe de liens FMP

On a model based on the table occurrence A01Schools we display all the students in our school record, we use an external table based on the occurrence A01_Students:

The elements are not very well organized and we would like to sort the students by class and then by their name. Problem: the name filed of the class which we want to use for sorting is not present in our Students table but in the Classes table. What This prevents us from selecting this item for sorting:

There is a very simple solution: All you need to do is to modify the table occurrence of the external table by the linked occurrence which contains the field on which you want to apply a sort, add this item to the sort of the external table then put the correct occurrence of table on the external table:

photo 5  photo 6  photo 7

Then we add the other filed on which we want to sort and here is the result:

photo 8  photo 9

Be careful though, this trick is very practical but it can nevertheless have a drawback. Sorting on a related field requires more resources from FileMaker, which can slow down sorting when sorting a large amount number of records… So, use it sparingly!

Format Datas with List() function

A problem we've all been faced with, formatting data when at least one element of the calculation is empty. We will base our example on formatting a postal address.

Here is what the basic calculation would be:

Street_1 & "¶" &
Street_2 & "¶" &
Street_3 & "¶" &
ZipCode & "-" & City & "¶" &

The problem that very often arises is that the fields Street2 and Street3 are very often empty. We can therefore end up with this type of result:

17 rue Porte de Montpellier

34150 - Aniane


17 rue Porte de Montpellier

Appartement 3
34150 - Aniane

A first solution would be to test the fields content in all headings to cover all cases:

Case (not empty (Street1); Street1 & "¶"; "") &
Case (not empty (Street2); Street2 & "¶"; "") &
Case (not empty (Street3); Street3 & "¶"; "") &
Case (not empty (ZipCode); ZipCode; "") &
Case (not isempty (ZipCode) AND not isempty (City); "-"; "") &
Case (not empty (City); City; "") &
Case (not isempty (ZipCode) OR not isempty (City); "¶"; "") &
Case (not empty (Country); Country; "")

Without debate this kind of calculation seems grotesque for a simple address.

This is where the List () function comes in. This function literally creates a list of items separated by a line break based on the presence of data in its parameters.

Our very simplified calculation would start by taking this form:

List (Street1 ; Street2 ; Street_3 ; ZipCode "-" City ; Country)

Now we just have to deal with the case ZipCode "-" City, which is nothing more or less than a list of ZipCode and City separated by "-" rather than by what we get with a Substitute:

Substitute (List (ZipCode ; City) ; "¶"; "-")

Here is our final result:

List (
Substitute (List (ZipCode; City); "¶"; "-");

Submit to FacebookSubmit to TwitterSubmit to LinkedIn

A question? A project to study?

Contact us

Please complete the form.
See our Contact page to contact us directly. 

Logo Claris FileMaker Pro

Logo Claris Partner

Logo Claris Marketplace

Copyright ©2020-2023 IU-DATA | All rights reserved | Legal Notices | Privacy Policy | Terms and Conditions | Designed by inPhobulle

By continuing to browse this site, you accept the use of cookies on this site for the purpose of producing visit statistics.