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:
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:
Then we add the other filed on which we want to sort and here is the result:
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 & "¶" &
Country
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
France
Or
17 rue Porte de Montpellier
Appartement 3
34150 - Aniane
France
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 (
Street_1;
Street_2;
Street_3;
Substitute (List (ZipCode; City); "¶"; "-");
Country
)
