1. Example: Simplifying Dynamic Portal Sorting

A highly requested feature in FileMaker is the ability to sort portals in layouts for various columns, both in ascending and descending directions. There are multiple solutions to achieve this, but here's an approach that's quick to implement without the need for complex relationship changes. It assumes you already have a relationship with the portal table to display data.

1. Example: Simplifying Dynamic Portal Sorting
   1. Description
   2. The Calculation
   3. Changing the Sort Column and Direction
   4. ACF Function: FormatNumber
   5. The Script for the Buttons
   6. Hiding the Arrows
   7. Download and Demo Material
2. Sorting multiple columns
   1. Structural changes
   2. Changes to the calculation
   3. Changes to the button script
   4. Changes to Hide arrow functions
   5. Download the Multi Sort Demo
3. Formatting other datatypes and values
   1. Date values
   2. Timestamp Values
   3. Month names
   4. Sorting on weekday names
   5. Sorting electronic component values
4. Conclution

1.1. Description ↑

This solution relies on two global fields in the portal table and one calculated field:

• SortField: A calculated field that returns text.
• SortColumn: A global field of number type, stored.
• SortDirection: A global field of number type, stored.

In your layout setup, choose the SortField as the sorting field in ascending order.

To implement this, you need two arrow icons and an invisible button for each column. The arrow icons are conditionally hidden, showing only the one that matches the current sort direction and column.

1.2. The Calculation ↑

In your calculation, create a mechanism to determine the field selected by the SortColumn. Typically, this involves a case statement for each column number, returning the value for the field displayed in the sorted column. For example:

case (
   MyTable::SortColumn = 1; MyTable::FirstName;
   MyTable::SortColumn = 2; MyTable::LastName;
   MyTable::SortColumn = 3; MyTable::Position;
   MyTable::SortColumn = 4; MyTable::Salary;
   MyTable::ID
)

However, when dealing with numeric columns, it's essential to address sorting discrepancies. Since the SortField is a text field, it doesn't sort numeric values correctly, especially when they have varying digit counts. To address this, you can use a calculation like this:

right("           " & Round(MyTable::Salary; 2); 10)

Replace the use of MyTable::Salary in the initial calculation with this fixed-length text string calculation.

1.3. Changing the Sort Column and Direction ↑

To change the sort column and direction, use the invisible buttons in the column headings. But what about the sort direction? This is where the ACF-Plugin can be valuable with two functions: - dsPD_Text2Hex: Converts a string to a hexadecimal representation. - dsPD_Text2HexInv: Produces the inverse hexadecimal representation.

For example, using these functions:

dsPD_Text2Hex("ABC") & ¶ & 
dsPD_Text2Hex("DEF") & ¶ &
dsPD_Text2HexInv("ABC") & ¶ & 
dsPD_Text2HexInv("DEF")

This will create the following result:

414243
444546
BEBDBC
BBBAB9

Sorting with either dsPD_Text2Hex or dsPD_Text2HexInv will give you the opposite sorting direction, maintaining an ascending sort in the portal. Since the SortField isn't intended for display in the portal, it doesn't matter whether it contains text or hexadecimal numbers. Adjust the calculation as follows:

Let ([
v = case ( MyTable::SortColumn = 1; MyTable::FirstName; 
           MyTable::SortColumn = 2; MyTable::LastName;
           MyTable::SortColumn = 3; MyTable::Position;
           MyTable::SortColumn = 4; right("           " & Round(MyTable::Salary; 2);10); 
           right("           " & MyTable::ID;10) ) 
];
if ( MyTable::SortDirection = 1; 
        dsPD_Text2Hex(v); 
        dsPD_Text2HexInv(v) ))

Since you've already used the ACF-Plugin for these functions, consider creating an ACF function for cleaner calculations.

1.4. ACF Function: FormatNumber ↑

Here's how you can create the ACF function FormatNumber:

function FormatNumber(float n, int len, int dec)
    functionID 3506;
    string f = "%" + format("%d.%df", len, dec);
    return format(f, n);
end

Lets test this function in the data-viewer:

":" & ACFU_FormatNumber(3.1415; 10; 2) & ":"

Give this result: 
:      3.14:

Using this, our calculation becomes this:

Let ([
v = case ( MyTable::SortColumn = 1; MyTable::FirstName; 
           MyTable::SortColumn = 2; MyTable::LastName;
           MyTable::SortColumn = 3; MyTable::Position;
           MyTable::SortColumn = 4; ACFU_FormatNumber (MyTable::Salary; 10; 2); 
           /* else */                ACFU_FormatNumber (MyTable::ID; 10; 0 ) );
v = if (v = "";" "; v ) // So the blank fields are not on top in the Descending direction.  
];
if ( MyTable::SortDirection = 1; 
    dsPD_Text2Hex(v); 
    dsPD_Text2HexInv(v) ))

Now, we can just alter the field sort-direction where "1" means ascending, and any other value will sort descending.

Since both The fields SortDirection and SortColumn are referenced in the calculation. Changing those will make FileMaker recalculate the values.

Note: If your column contains a very long text, then it´s advisable to use the Left function and only return the first 50 characters or something.

1.5. The Script for the Buttons ↑

A FileMaker script is essential to change the sort order since it involves multiple steps. Ensure that your portal has a name; for example, MyPortal. Here's a sample script:

Set Variable [$Column; Get(ScriptParameter)]

If [$Column = MyTable::SortColumn]
    # Change Sort Direction only:
    Set Field [MyTable::SortDirection; If (MyTable::SortDirection = 1; 0; 1)]
Else
    Set Field [MyTable::SortColumn; $Column]
    Set Field [MyTable::SortDirection; 1] // Ascending for a different column
End If

Commit Record/Request [With Dialogs: Off]
Refresh Portal ["MyPortal"]

Execute this script for each of the invisible column heading buttons by passing the column number as the script parameter.

1.6. Hiding the Arrows ↑

To hide the arrows for columns other than the one actively sorted, and to show the correct arrow for the sort direction, two additional ACF functions have been created:

function HideUpArrow ( int column, int SortColumn, int SortDirection )
    FunctionID 3506; 
    bool hide = true; 
    if ( column == SortColumn && SortDirection == 1) then
        hide = false;
    end if
    return hide; 
end

function HideDownArrow ( int column, int SortColumn, int SortDirection )
    FunctionID 3507; 
    bool hide = true; 
    if ( column == SortColumn && SortDirection != 1) then
        hide = false;
    end if
    return hide; 
end

Apply these functions in the "Hide Object When" field, which can be found in the rightmost tab when clicking on an arrow in layout mode, Just paste in this calculation for the UP-arrow in column 1:

ACFU_HideUpArrow(1; MyTable::SortColumn; MyTable::SortDirection)

And for the Down-arrow of column 1:

ACFU_HideDownArrow(1; MyTable::SortColumn; MyTable::SortDirection)

The formula can be copied to the other columns, by just changing the column number for that column. (2, 3, 4... )

This setup should provide a smooth sorting functionality for your portal. Good luck!

1.7. Download and Demo Material ↑

A Demo can be downloaded at our download site (link below the picture)

The Links

• Se our Download page
• Download This demo
• See the demo page
• Look at some instruction videos

2. Sorting multiple columns ↑

I have been asking if this solution also can sort multiple columns, and the answer is Yes, with a few slight modifications. I have made a separate demo for multicolumns so that we keep the single-column demo intact.

When clicking on a different column heading than the one actively sorted, holding the "ALT" key on the keyboard should add a column to the sort criteria.

2.1. Structural changes ↑

The SortColumn field should be changed to type TEXT, as we intend to have a comma-separated list of column numbers in it. One number for each column.

2.2. Changes to the calculation ↑

Moving the calculation into an ACF function is probably the most easy way to do it. The purpose here is to make a concatenated value for the SortField. Then you will need one such calculation function for each different table that is used for portal-sorting in your application.

Here is the multicolumn implementation for our MyTable portal:

/*
    Calculation to go into the MyTable::SortField. Use dsPD_Text2Hex, dsPD_text2HexInv in that 
    calculation as needed.
*/

function SortCalcMyTable ()
    FunctionID 3508; // Unique ID for this function. 
    array string aSort = explode ( ",", MyTable::SortColumn); 
    int no = sizeof ( aSort ), i;
    string k; 
    string val = ""; 
    for ( i=1, no)
        k = aSort[i]; 
        if ( k == "1" ) then
            val += MyTable::FirstName; 
        elseif ( k == "2" ) then
            val += MyTable::LastName;
        elseif ( k == "3" ) then
            val += MyTable::Position;
        elseif ( k == "4" ) then
            val += FormatNumber (MyTable::Salary, 10, 2);
        else
            val += FormatNumber (MyTable::ID, 6, 0 );
        end if
    end for 

    if ( val == "" ) then
        val = " "; 
    end if

    return val; 
end

Then the calculation in our SortField is changed to this:

Let ([
    v = ACFU_SortCalcMyTable 
];
if ( MyTable::SortDirection = 1; 
    dsPD_Text2Hex(v); 
    dsPD_Text2HexInv(v) ))

2.3. Changes to the button script ↑

The button script for the invisible buttons, change the script to look like this:

Set Variable [$Column; Get(ScriptParameter)]

 # Suppose there are not more than 9 columns, otherwise we can use the alphabet too. 
If [Position ( MyTable::SortColumn ; $Column ; 1 ; 1 )>0]
    If [Position ( MyTable::SortColumn ; "," ; 1 ; 1 )>0 and Get(ActiveModifierKeys)  = 8]
        # We are in Multi-sort mode, remove the column instead, and don't change direction
        Set Field [MyTable::SortColumn; Substitute ( MyTable::SortColumn ; "," & $column ; "" )]
        #  Or if it was the first in the list
        Set Field [MyTable::SortColumn; Substitute ( MyTable::SortColumn ; $column ; "" )]
    Else
    
        # Change Sort Direction only:
        Set Field [MyTable::SortDirection; If (MyTable::SortDirection = 1; 0; 1)]
    End If
Else
    # Get(ActiveModifierKeys) returns 8 for the alt key on Mac, can check this for Windows too. 
    if [Get(ActiveModifierKeys)  = 8 and MyTable::SortColumn  ≠ ""]
        Set Field [MyTable::SortColumn; MyTable::SortColumn & "," & $Column]
    Else
        Set Field [MyTable::SortColumn; $Column]
    End If
    Set Field [MyTable::SortDirection; 1] // Ascending for a different column
End If

Commit Record/Request [With Dialogs: Off]
Refresh Portal ["MyPortal"]

2.4. Changes to Hide arrow functions ↑

The Hide Arrows functions need also some change. We assume that we only use a single digit for the column numbers, or we can also use the alphabet. Here are the new versions of it:

function HideUpArrow ( string column, string SortColumn, int SortDirection )
    FunctionID 3506; 
    bool hide = true; 
    if ( pos (SortColumn, column )>=0 && SortDirection == 1) then
        hide = false;
    end if
    return hide; 
end

function HideDownArrow ( string column, string SortColumn, int SortDirection )
    FunctionID 3507; 
    bool hide = true; 
    if ( pos (SortColumn, column )>=0 && SortDirection != 1) then
        hide = false;
    end if
    return hide; 
end

That would be all, or the portal can now be sorted in any number of columns we want.

Here is what it looks like with sort on Last name, First name:

2.5. Download the Multi Sort Demo ↑

This MultiSort demo can be downloaded at this link:

• Download MultiColumn Sort Demo

3. Formatting other datatypes and values ↑

It might happen that your portal contains rows containing other values that should not be sorted purely alphabetically. For example, month names should be sorted according to the calendar. Day names should be sorted according to the position in the week. Scientific numbers as text like 6.02E-23 should be written as a float number with many digits to be able to sort them chronologically. Finally, electronic component markings like resistors or capacitors values often come as 10K or 2R2 for 10 000 ohms and 2.2 ohms respectively.

In the following sections, I will present ACF functions for formatting them with respect to sorting order so that the sort becomes correct.

3.1. Date values ↑

Date values should be sorted in Year-month-day format to be correctly sorted. Usually, the countries have different display formats for date, like day-month-year or month-day-year, that do not work well when it comes to sorting. Here is a function for date formatting that can be applied to our sorting solution:

/*
    For date-sorting, returns a 6-digit date in the format YYMMDD
*/
function FormatDate ( date d )
    FunctionID 3510; 
    return string (d, "%y%m%d"); 
end

3.2. Timestamp Values ↑

Here are two scenarios, one where we have a field of type timestamp, and the second where we have a text field with a timestamp value ( maybe imported from some external system ). Here is the first scenario:

/*
    For timestamp formatting where the field is of type TimeStamp
    returns a string in format YYYYMMDD HHMMSS
    Use lowercase "y" instead of uppercase "Y" to have 2 digit year. 
*/
function FormatTimeStamp ( timestamp ts )
    FunctionID 3511; 
    return string (ts, "%Y%m%d %H%M%S"); 
end

For string timestamps, there are also various formats, namely the SQL format can be sorted as text directly as it already has a year-month-day structure in it. If the format follows the date formatting in your country, we need to split apart the timestamp into date and time, and then merge them together again with the new format. The function below can be altered to accept a specific date format in your timestamp, commenting out the active d= assignment and uncommenting the other, where you specify the date format you have in your field.

/*
    For timestamp as a string in the format used to display timestamps in your country. 
*/
function FormatTimeStampAsString ( string s )
    timestamp ts;
    date d; 
    // Uncomment the version needed, and comment out the version not needed. 
    // d = date ( left(s,10), "%d/%m/%Y"); //  like 31/12/2022
    d = date ( left(s,10)); // Default format
    s = string ( d, "%Y%m%d") + substring ( s, 10); 
    ts = s; 
    return ts  ; 
end 

3.3. Month names ↑

We have made a couple of functions here, for converting the month names into a 2-digit sorting value. There is one function for each language for 9 different languages. They can of course be edited to the language version you have if it's not in the list. Just copy the function needed. They all use one function in common, namely Find_in_array that needs to be copied in addition. Remember to put them in order so that referenced functions are defined above the place where they are used (since the ACF compiler is a one-pass compiler).

Here is the Find\_In\_Array function:

/*
    Find_in_array - Find the index in an array for a given string.
    Common for all the month names and weekday names sort. 
    Must be defined above where it is used. 
*/
function Find_In_Array ( array string arr, string s )
    int l = sizeof (arr); 
    int i; 
    for (i=1, l)
        if ( arr[i] == s) then 
            return i; 
        end if
    end for
    return -1; 
end

And here comes the format month names functions (one for each language, copy the ones for the languages you need, or translate if it lack in the list.

/*
    Format English month names, as two-digit month numbers: 
*/
function FormatMonthNameEng (string s )
    FunctionID 3520; 
    array string names = { 'January', 'February', 'March', 'April', 'May', 'June', 
            'July', 'August', 'September', 'October', 'November', 'December' }; 
    int i = Find_In_array (names, s); 
    if ( i<0) then 
        i = 99; 
    end if
    return format ("&02d", i); 
end

/*
    Format Spanish month names, as two-digit month numbers: 
*/
function FormatMonthNameSpa (string s)
    FunctionID 3521;
    array string names = { 'enero', 'febrero', 'marzo', 'abril', 'mayo', 'junio', 
            'julio', 'agosto', 'septiembre', 'octubre', 'noviembre', 'diciembre' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

/*
    Format French month names, as two-digit month numbers: 
*/
function FormatMonthNameFra (string s)
    FunctionID 3522;
    array string names = { 'janvier', 'février', 'mars', 'avril', 'mai', 'juin', 
            'juillet', 'août', 'septembre', 'octobre', 'novembre', 'décembre' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

/*
    Format German month names, as two-digit month numbers: 
*/
function FormatMonthNameGer (string s)
    FunctionID 3523;
    array string names = { 'Januar', 'Februar', 'März', 'April', 'Mai', 'Juni', 
            'Juli', 'August', 'September', 'Oktober', 'November', 'Dezember' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end
/*
    Format Norwegian month names, as two-digit month numbers: 
*/
function FormatMonthNameNor (string s)
    FunctionID 3524;
    array string names = { 'januar', 'februar', 'mars', 'april', 'mai', 'juni',
            'juli', 'august', 'september', 'oktober', 'november', 'desember' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end
/*
    Format Sedish month names, as two-digit month numbers:
*/
function FormatMonthNameSwe (string s)
    FunctionID 3525;
    array string names = { 'januari', 'februari', 'mars', 'april', 'maj', 'juni',
            'juli', 'augusti', 'september', 'oktober', 'november', 'december' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end
/*
    Format Danish month names, as two-digit month numbers:
*/
function FormatMonthNameDan (string s)
    FunctionID 3526;
    array string names = { 'januar', 'februar', 'marts', 'april', 'maj', 'juni',
            'juli', 'august', 'september', 'oktober', 'november', 'december' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end
/*
    Format Polish month names, as two-digit month numbers:
*/
function FormatMonthNamePol (string s)
    FunctionID 3527;
    array string names = { 'styczeń', 'luty', 'marzec', 'kwiecień', 'maj', 'czerwiec',
            'lipiec', 'sierpień', 'wrzesień', 'październik', 'listopad', 'grudzień' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

/*
    Format Chinese month names, as two-digit month numbers ( are they similar in Japan?)
*/
function FormatMonthNameChi (string s)
    FunctionID 3528;
    array string names = { '一月', '二月', '三月', '四月', '五月', '六月',
            '七月', '八月', '九月', '十月', '十一月', '十二月' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

Thanks to chatGPT that helped me translate for all those languages.

3.4. Sorting on weekday names ↑

Here are the functions for weekday names. There are functions for the same 9 languages and also one for Japanese, just copy the ones that are relevant to you. Those also use the same function `Find_In_Array´ described in the section above.

/*
   Format week-day names as two-digit numbers for sorting in the portal. 
*/

// English
function FormatWeekDayNamesEng (string s)
    FunctionID 3529;
    array string names = { 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Spanish
function FormatWeekDayNamesSpa (string s)
    FunctionID 3530;
    array string names = { 'lunes', 'martes', 'miércoles', 'jueves', 'viernes', 'sábado', 'domingo' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// French
function FormatWeekDayNamesFra (string s)
    FunctionID 3531;
    array string names = { 'lundi', 'mardi', 'mercredi', 'jeudi', 'vendredi', 'samedi', 'dimanche' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// German
function FormatWeekDayNamesGer (string s)
    FunctionID 3532;
    array string names = { 'Montag', 'Dienstag', 'Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Norwegian
function FormatWeekDayNamesNor (string s)
    FunctionID 3533;
    array string names = { 'mandag', 'tirsdag', 'onsdag', 'torsdag', 'fredag', 'lørdag', 'søndag' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Swedish
function FormatWeekDayNamesSwe (string s)
    FunctionID 3534;
    array string names = { 'måndag', 'tisdag', 'onsdag', 'torsdag', 'fredag', 'lördag', 'söndag' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Danish
function FormatWeekDayNamesDan (string s)
    FunctionID 3535;
    array string names = { 'mandag', 'tirsdag', 'onsdag', 'torsdag', 'fredag', 'lørdag', 'søndag' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Polish
function FormatWeekDayNamesPol (string s)
    FunctionID 3536;
    array string names = { 'poniedziałek', 'wtorek', 'środa', 'czwartek', 'piątek', 'sobota', 'niedziela' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// Chinese
function FormatWeekDayNamesChi (string s)
    FunctionID 3537;
    array string names = { '星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

// japanese
function FormatWeekDayNamesJpn (string s)
    FunctionID 3538;
    array string names = { '月曜日', '火曜日', '水曜日', '木曜日', '金曜日', '土曜日', '日曜日' };
    int i = Find_In_array(names, s);
    if (i < 0) then
        i = 99;
    end if
    return format("&02d", i);
end

3.5. Sorting electronic component values ↑

As this is a special subject for many, some might need it for solutions that contain a bill of materials for electronic devices for example.

Electronic component marking for resistors, capacitors, and inductors follows a special pattern.

• Resistors marked 4K7, which means 4700 ohms, also sometimes marked as 4.7Kohm
• 4.7 uF, also sometimes just 4u7, means 4.7 * 10^-6, or 0.0000047
• 100pF is 100*10^-12 or 0.00000000001 F

To sort such columns logically can be done with this format function. It is fixed to 30 characters with, and 15 decimals - as we work with both very small numbers and big numbers at the same time.

/*
    Format Electronic Component Value
*/
function FormatElectronicComponentValue ( string s )
    functionID 3540; 
    double n;
    double factor; 
    string regex = "^([0-9,.]+)([RpnuµmkKM])?(\d*)([FfHhΩom])?";
    string ns, nf, nd, nu, f;
    if (regex_match ( regex, s) ) then
        ns = regex_replace ( regex, s, "$1"); 
        nf = regex_replace ( regex, s, "$2"); 
        nd = regex_replace ( regex, s, "$3"); 
        nu = regex_replace ( regex, s, "$4"); 
        if ( nf == "R" ) then
            factor = 1;
        elseif (nf == "p" ) then
            factor = 10^-12; 
        elseif (nf == "n" ) then
            factor = 10^-9; 
        elseif (nf == "u" || nf == "μ") then
            factor = 10^-6;
        elseif (nf == "m" && nu=="H") then
            factor = 10^-3; 
        elseif (nf == "m" || nf=="M" ) then
            factor = 10^6; 
        elseif (nf == "k" || nf == "K" ) then
            factor = 1000; 
        else
            factor = 1; 
        end if
        ns = ns + "." + nd; 
        n = double ( ns ) * factor;     
    else
        n = double ( s ); 
    end if 
     
    return format ( "%30.15f", n);
end

4. Conclution ↑

The solution is simple to implement. Now we have two versions, one for single-column sort, and another that is multi-column sort. We also have a library for special format functions to be able to sort on diverse values that one can have. The functions in Chapter 3 must be seen as a library, there is no need to have all those functions in most solutions. If it is actually to edit some of the ACF functions presented here, have a look at the videos I have made.

The ACF plugin is a versatile plugin that covers all those areas shown in the example section. It can be bought for as little as 79 USD on our webshop on the main site. This is a one-off price and no subscriptions.

I hope this can be used for some out there. Send me a message on the contact form on the main site, if you have any questions.

• Ole K Hornnes