In this example we are going to explain,
how to make some portal Filtering in FileMaker Portal using SQL techniques,
because it is easy and 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 logical,
moreover as it is not FileMaker native language you have to type it by yourself,
and not to do any mistake!
Furthermore FileMaker provide you some dynamic parameters,
who 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 quotation mark «?». If FileMaker would like to confuse you on purpose : they do it right.
The order catch is that many developer 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 : How to make your 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 Key Primary ID Field «__ Kp Contact ID».
Then somehow, we will do some SQL in order to get back in a return delimed list,
all the key ID that match our filter criteria, and this is it !
Portal Filtering & SQL : Filter Source FieldS ?
In «zSys» Table we do have 10 Text Global Field with 3 Repetitions :
[1] Repetition is where you type your filter string
[2] Repetition is PopUp Menu «Begin|Contain|End»
[3] Repetition is for dynamic sorting labelling 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 !
We do have 350 000 records to see how fast the SQL filter is ?
Portal Filtering & SQL : lets do some SQL ?
If we do have at least one Filter Source FieldS 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 Sources Field (out of 10) we want to search inside the target field,
but with «Begin|Contain|End» PopUp the «%» could be at 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 will contain all the details
the main query code remains easy to read
When a custom function is design for a particular solution only, we name it accordingly,
ExeSQL_ContactS_LIKE, so we know when we import it in another solution,
it has to be recheck before reuse it !
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 seach 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 SQL Lower Function
but Lower of Source Field is FileMaker Lower Function
After ExeSQL_ContactS_LIKE a few more stuff :
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 a 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 allways be true
FileMaker & SQL : How to debug ?
In order to make a Proper SQL Query with FileMaker ExecuteSQL Function :
we need to produce dynamically some query that the FM SQL will understand
in order to pull out some results : we use the FileMaker syntax with some quote 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 to use Standard Feature ?
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 you should be able to perform some Dynamic Sorting !
As we have to wait for FileMaker 50, lets go some way around extra coding !
FileMaker Dynamic Sorting : Sort Records Dailog Box ?
In the Sort Portal Records Dialog, we are going to sort 6 Fields (Calcultated Text)
from «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 modify when the user will click on the Label on column header
↪ 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 a Text with Sort_Field ( myField ) Custom Function
with some extra care for Number Date Time TimeStamp Field Type
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 : User Click on Column Header ?
The Column Header Label will be Global Text Field [3]
we will alter those Fields with script
Each label will also be a button, they launch the same script, but with different parameter :
the name of the Target Field will be the parameter,
but as we will need this name from 2 different context (ZSYS~tog & zsys_CONTACTbyFilterList),
we will pass 2 parameter on 2 lines : same Field with different context.
When we pass some Field as Script Parameter we don't want the Field Value,
but we do want the Field Name so we do «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 DoubleUnderline,
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 Label are display 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 Custom Function with no Parameter, when you need to SetUp
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, as we can't embed font (as we can emded PlugIn)
it is good pratice to choose Web Safe Font, then will display well both and the Mac & Windows
W3Schools.Com > CSS Web Safe Fonts
When we want to reset the sort just press [Reset Sort]
we will clear all the Array Global Variable
then we refresh the Label !
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'm very experienced Remote Worker through
Recorded Phone Call Meeting with Simultaneous Screen Sharing.
Download the TechFile : FM SQLPortal Filter & DynSort.fmp12
