FM/SQL Portal Filter + Dynamic Sort

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)

Portal Filtering & SQL: Why?

In this article we are going to explain how to implement portal filtering in FileMaker portal using SQL techniques, because it is easy, powerful and extremely flexible!

I mean you have still to learn some SQL, which has a very different syntax than the regular FileMaker syntax, it has also a very different logic, moreover, as it is not FileMaker native language — you have to type it by yourself, and not make any mistake! Furthermore FileMaker provides you some dynamic parameters, which are inconvenient, because they need to be put in the function parameter in the correct order (like in FileMaker custom functions), but when you call them in your main SQL query you have to call them in the correct order (unlike in FileMaker custom functions) with always the same name: a question mark «?».

If FileMaker would like to confuse you on purpose, they do it right. The order catch is that many developers copy and paste the field table occurrence and the field name directly into their main query, so when you want to rename your field, you will break your SQL.

It is so easy to create a custom function, in order to get the field name or the table occurrence name dynamically and directly from your schema !

Portal Filtering & SQL: Constructing the Relationship

We are going to make a relationship…

• from «zSys» table (1 record only) to «Contact» table (where we store all the data)

and

• from a global text field «_Kg ContactS List» to the primary key field «__ Kp Contact ID».

Then somehow, we will do some SQL in order to get back in a return delimited list, all the key ID that match our filter criteria, and this is it!

Portal Filtering & SQL: Filter Source Fields

In the «zSys» table we have 10 global text fields with 3 repetitions:
[1] Repetition is where you type your filter string
[2] Repetition is a popup menu «Begin|Contain|End»
[3] Repetition is for dynamic sorting labeling purpose

We do use repeating field when it make sense to make an array in global field (in order to avoid to clutter the schema with 30 fields instead of 10 fields) but we don’t use repeating field as a substitute for related item in the schema!

Portal Filtering & SQL: Target Data Fields

Target data fields are in the «Contact» table, and we have 350,000 records to see how fast the SQL filter is.

Portal Filtering & SQL: Let’s do some SQL

If we have at least one filter source field not empty, then we want to pull out in «ZSYS~tog::_Kg ContactS List» all the «CONTACT~tog::__ Kp Contact ID» which will match our criteria.

Set Field [ ZSYS~tog::_Kg ContactS List ;
ExecuteSQL (
"SELECT " & SQL_F ( CONTACT~tog::__ Kp Contact ID ) &
" FROM " & SQL_T ( CONTACT~tog::__ Kp Contact ID ) & " AS Ct¶" &
"WHERE ¶" &

• SQL_F to take only what is after «::»
• SQL_T to take only what is before «::»
• SQL_F & SQL_F will protect the spaces inside Field Name «::»

For 9 source fields (out of 10) we want to search inside the target field, but with «Begin|Contain|End» popup the «%» could be at a different position
so the code would be too long to be readable, and as it is always the same stuff, it does make sense to detail it inside a custom function.

ExeSQL_ContactS_LIKE ( ZSYS~tog::Contact~Filter~Company~G [1]
; ZSYS~tog::Contact~Filter~Company~G [2]
; CONTACT~tog::Contact Detail Company ) &
".........."
ExeSQL_ContactS_LIKE ( ZSYS~tog::Contact~Filter~Email~G [1]
; ZSYS~tog::Contact~Filter~Email~G [2] ;
CONTACT~tog::Contact Detail EmailPersonal ) &

• ExeSQL_ContactS_LIKE will contain all the details
• The main query code remains easy to read
• When a custom function is designed for a particular solution only, we name it accordingly, ExeSQL_ContactS_LIKE, so we know when we import it into another solution, it has to be rechecked before reuse

Inside ExeSQL_ContactS_LIKE we tell all we need to tell:

ExeSQL_ContactS_LIKE ( myFiedSource ; myFieldBeginEnd ; myFieldTarget )
Case ( not IsEmpty ( myFiedSource ) ;
"LOWER ( Ct." & SQL_F ( myFieldTarget ) & " ) LIKE '" &
Case ( myFieldBeginEnd = "Contains" or myFieldBeginEnd = "Ends with" ; "%" ) &
Lower ( myFiedSource ) &
Case ( myFieldBeginEnd = "Begins with" or myFieldBeginEnd = "Contains" ; "%" )
& "' AND ¶" )

• if the source field is empty then we have to skip it
• we have to make a LIKE to search inside the pattern
• the target field should be the field name after «::» inside quotation mark
• but the source field should be the field value itself, inside single quote, to tell to SQL that it is a text value
• «%» SQL wildcard character substitute for zero or more characters should be
  after and/or before the pattern
• LIKE is case sensitive, so you should compare both the target & source field in lower case
• LOWER of the target field is the SQL Lower function but Lower of source field is the FileMaker Lower function

After ExeSQL_ContactS_LIKE a few more things:

Case ( not IsEmpty (ZSYS~tog::Contact~Filter~State~G ) ;
"Ct." & SQL_F ( CONTACT~tog::Contact Home State ) & " = '" & 
Upper ( ZSYS~tog::Contact~Filter~State~G ) & "' AND¶" ) &
"1 = 1"
; "" ; "" ) ]

• the search inside the State PopUp is an equal operator
• as we do generate a variable number of line terminated by an «AND» we can’t leave it orphan so to terminate “1 = 1” will always be true

FileMaker & SQL: How to Debug?

In order to make a proper query with FileMaker’s ExecuteSQL function, we need to produce dynamically some query that the FM SQL will understand in order to pull out some results. Should we use FileMaker syntax with some quotes to produce SQL syntax? It could be awfully confusing to do that!

Thanks to FM Data Viewer, it is possible as the building of our SQL Query is in progress, to view both the query & the result at once.

SELECT "__ Kp Contact ID" FROM "CONTACT~tog" AS Ct
WHERE
LOWER ( Ct."Contact Detail Company" ) LIKE '%am%' AND
LOWER ( Ct."Contact Home City" ) LIKE 'scot%' AND
Ct."Contact Home State" = 'AZ' AND
1 = 1

FileMaker & Filtering: Why not use the Standard Feature?

When we perform some portal filtering, we may wish to know how many records did we find? But with the standard FM feature it doesn’t work (FileMaker Pro 15 Help > Working with related tables and files > Filtering records in portals).

Contact Count (calculated number)

Count ( zsys_CONTACTbyFilterList::__ Kp Contact ID )

Moreover there is the tremendous flexibility of SQL: it is worth the extra time to learn as much SQL as you can, especially in reporting, because with SQL you can ask a question and get the answer: SeedCode.Com > FileMaker SQL : Why Use It ? (Context Independent)

FileMaker Dynamic Sorting: What is the Problem?

When you go in the Portal Setup Dialog Box, then you go on Sort portal records dialog box, with no [Specify…] button, to get you in the Specify Calculation dialog box where it would be nice to be able to perform some dynamic sorting like so…

SortPortal ( Field1 ; AscDesc1 {; Field2 ; AscDesc2 ... } )

As we have to wait for FileMaker 50, let’s go some way around extra coding!

FileMaker Dynamic Sorting: Sort Records Dialog Box

In the Sort Portal Records dialog, we are going to sort six fields (calculated text) from the «Contact» table in Asc | Desc | Asc | Desc | Asc | Desc order.

• the input will be $$Key_Field [1],[2],[3] & $$Key_Order [1],[2],[3] array global variable which will be modified when the user clicks on the column header label
• the output will be 1 if the condition is false OR the field to be sorted if the condition is true, but the field to be sorted will be output as text via the Sort_Field ( myField ) custom function with some extra care when the field type is Number, Date, Time or TimeStamp.

We are here in «CONTACT~tog» context, because «z_ Sort» Calculated Text Fields are in «CONTACT» Table

CONTACT~tog::z_ Sort 1 Asc  (calculated text)

Case (
not IsEmpty ( $$Key_Field [1] ) and $$Key_Order [1] = "Asc" ;
Sort_Field ( GetField ( $$Key_Field [1] ) ) ;
not IsEmpty ( $$Key_Field [1] ) and $$Key_Order [1] = "Des" ; 1 ; 
1 )

CONTACT~tog::z_ Sort 1 Des (calculated text)

Case (
 not IsEmpty ( $$Key_Field [1] ) and $$Key_Order [1] = "Asc" ; 1 ;
 not IsEmpty ( $$Key_Field [1] ) and $$Key_Order [1] = "Des" ;
 Sort_Field ( GetField ( $$Key_Field [1] ) ) ;
 1 )

CONTACT~tog::z_ Sort 2 Asc (calculated text)

Case (
 not IsEmpty ( $$Key_Field [2] ) and $$Key_Order [2] = "Asc" ;
 Sort_Field ( GetField ( $$Key_Field [2] ) ) ;
 not IsEmpty ( $$Key_Field [2] ) and $$Key_Order [2] = "Des" ; 1 ;
 1 )

CONTACT~tog::z_ Sort 2 Des (calculated text)

Case (
 not IsEmpty ( $$Key_Field [2] ) and $$Key_Order [2] = "Asc" ; 1 ;
 not IsEmpty ( $$Key_Field [2] ) and $$Key_Order [2] = "Des" ;
 Sort_Field ( GetField ( $$Key_Field [2] ) ) ;
 1 )

CONTACT~tog::z_ Sort 3 Asc (calculated text)

Case (
 not IsEmpty ( $$Key_Field [3] ) and $$Key_Order [3] = "Asc" ;
 Sort_Field ( GetField ( $$Key_Field [3] ) ) ;
 not IsEmpty ( $$Key_Field [3] ) and $$Key_Order [3] = "Des" ; 1 ;
 1 )

CONTACT~tog::z_ Sort 3 Des (calculated text)

Case (
 not IsEmpty ( $$Key_Field [3] ) and $$Key_Order [3] = "Asc" ; 1 ;
 not IsEmpty ( $$Key_Field [3] ) and $$Key_Order [3] = "Des" ;
 Sort_Field ( GetField ( $$Key_Field [3] ) ) ;
 1 )

Sort_Field ( myField ) (custom function)

Case ( Field_Type ( myField ) = "Text" ; myField ;
 Field_Type ( myField ) = "Number" ;
 Right ( "000000000000000" & ( Round ( myField ; 2 ) * 100 ) ; 15 ) ;
 Field_Type ( myField ) = "Date" or Field_Type ( myField ) = "Time"
 or Field_Type ( myField ) = "TimeStamp" ;
 GetAsNumber ( myField ) )

Field_Type ( myField ) (custom function)

MiddleWords (
 FieldType ( Get ( FileName ) ; GetFieldName ( myField ) )
 ; 2 ; 1 )

FileMaker Dynamic Sorting: Clickable Column Header

The column header label will be a global text field [3] and we will alter these fields via script:

ZSYS~tog::Contact~Filter~Company~G[3]
ZSYS~tog::Contact~Filter~First~G[3]
ZSYS~tog::Contact~Filter~Last~G[3]
ZSYS~tog::Contact~Filter~Address~G[3]
ZSYS~tog::Contact~Filter~State~G[3]
ZSYS~tog::Contact~Filter~Zip~G[3]
ZSYS~tog::Contact~Filter~Phone~G[3]
ZSYS~tog::Contact~Filter~Fax~G[3]
ZSYS~tog::Contact~Filter~Email~G[3]
ZSYS~tog::Contact~Filter~City~G[3]

Each label will also be a button, launching the same script, but with a different parameter (the name of the target field), but as we will need this name from 2 different contexts (ZSYS~tog & zsys_CONTACTbyFilterList), we will pass 2 parameters on 2 lines: same field, different context.

Perform Script [ "Contact > Contact > Portal > Sort > Execute";
Parameter: GetFieldName ( CONTACT~tog::Contact Detail Company ) & "¶" &
GetFieldName ( zsys_CONTACTbyFilterList::Contact Detail Company ) ]

When we pass some field as script parameter we don’t want the field value, but we do want the field name so we use «getfieldname ( field )»

FileMaker Dynamic Sorting: Script Alter Array Global Variable

When the user first click on a label we want to write down what field to sort.

$$Key_Field[1] ( name of the Field from CONTACT~tog context ) +
$$Key_Field~Label[1] ( name of the Field from zsys_CONTACTbyFilterList context ) +
$$Key_Order[1] by default Asc but Desc if it already Asc ( Click twice on the same Label )

When the user second click on a different label we want to write down what field to sort.

$$Key_Field[2] ( name of the Field from CONTACT~tog context ) +
$$Key_Field~Label[2] ( name of the Field from zsys_CONTACTbyFilterList context ) +
$$Key_Order[2] by default Asc but Desc if it already Asc ( Click twice on the same Label )

When the user third click on a different label we want to write down what field to sort.

$$Key_Field[3] ( name of the Field from CONTACT~tog context ) +
$$Key_Field~Label[3] ( name of the Field from zsys_CONTACTbyFilterList context ) +
$$Key_Order[3] by default Asc but Desc if it already Asc ( Click twice on the same Label )

FileMaker Dynamic Sorting: Script Alter Labels

• When $$Key_Field~Label [1] is zsys_CONTACTbyFilterList::Contact Detail Company
  then it has to be styled underline, with a ▲ if sort order is ascending with a ▼ if sort order is descending
• When $$Key_Field~Label [2] is zsys_CONTACTbyFilterList::Contact Detail Company
  then it has to be styled double underline, with a ▲ if sort order is ascending with a ▼ if sort order is descending
• When $$Key_Field~Label [3] is zsys_CONTACTbyFilterList::Contact Detail Company
  then it has to be styled italic, with a ▲ if sort order is ascending with a ▼ if sort order is descending
• etc. . . . . . . . .

We are here in «zsys_CONTACTbyFilterList» context, because the labela are displayed on a «Z_SYS~tog» context layout.

• For ▲ & ▼ we use S_As & S_Ds custom function, in order to call the character we choose, but also the font & the size, then we can call ▲ & ▼ easily.
• You can make a custom function with no parameter, when you need to set up some constant and recall them easily.
• We could have done that with a global variable, $$S_As & $$S_Ds , but then we have to set it up in our startup script / OnFirstWindowOpen.
• When we develop a FileMaker solution, since we can’t embed fonts (as we can embed plug-ins), it is good practice to choose web safe fonts, which will display well on both the Mac & Windows (W3Schools.Com > CSS Web Safe Fonts).

Sort_Label ( myField ; myLabel )

Case ( 
 $$Key_Field~Label [1] = GetFieldName ( myField ) ; 
 TextStyleAdd ( myLabel ; Underline ) & " " & 
 Case ( $$Key_Order[1]="Asc" ; S_As ; $$Key_Order[1]="Des" ; S_Ds ) ;
 //-----------------------------------
 $$Key_Field~Label [2] = GetFieldName ( myField ) ; 
 TextStyleAdd ( myLabel ; DoubleUnderline ) & " " & 
 Case ( $$Key_Order[2]="Asc" ; S_As ; $$Key_Order[2]="Des"; S_Ds ) ;
 // -----------------------------------
 $$Key_Field~Label [3] = GetFieldName ( myField ) ; 
 TextStyleAdd ( myLabel ; Italic ) & " " & 
 Case ( $$Key_Order[3]="Asc" ; S_As ; $$Key_Order[3] = "Des"; S_Ds ) ;
 // ----------------------------------- 
 myLabel
 )// End Case $$Key_Field~Label [1],[2],[3]

S_As

TextSize ( TextFont ( Char ( 9650 ) ; "Lucida Console" ) ; 14 ) 
 /* ▲ 
  http://www.w3schools.com/charsets/tryit.asp?deci=9650 
 */

S_Ds

TextSize ( TextFont ( Char ( 9660 ) ; "Lucida Console" ) ; 14 ) 
 /* ▼ 
  http://www.w3schools.com/charsets/tryit.asp?deci=9660
 */

FileMaker Dynamic Sorting: Reset the Sort

When you want to reset the sort just press [Reset Sort] to clear all the array global variables and refresh the labels.

---

About the Author

• Hi, my name is Joel Englander.
• I’m a professional FileMaker developer.
• I like to develop easy to use dummies proof interface based on nested portal + buttons + script triggered, with a minimalist custom menu bar & a very clear code nomenclature.
• I did publish some Custom Function on Brian Dunning Custom Functions
• You can Find some PDF sample with screenshots on www.asymptotes.com
• As I know web technology: HTML + CSS + JavaScript + XML + SQL, I did recently learn PHP FileMaker, and I’m currently learning PHP MySQL.