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.
Nice work, Joel! Thanks for putting it all together and sharing!
Joel, have you looked at this portal sorting method:
http://www.fitchandfitch.com/2011/05/filemaker-portal-sorting/
IMHO it still holds up as the most practical and least painful way to achieve dynamic portal sorting. It’s designed for a portal driven by an ID list, as in the SQL technique you describe here, but it uses the order of the ID list itself as the sort order.
Be sure to read the follow-up article as well. Happy new year!
Tom,
—> I did look it very carefully :
—> I wanted to have BOTH the ablities :
to sort on Three Keys AND
to Toggle Ascending & Descending sort !
—> Plus to provide some feedback Labelling :
to let the user know if he is on 1st Key OR 2nd Key OR 3th Key AND
to let him know if it is an Ascending OR a Descending sort !
—> MoreOver, all the attempts, that I did make,
to some portal dynamic sorting, based on some loop on the related FoundSet :
were too slow on a Large FoundSet.
—> So, in my Tech File I did provide 350 000 Related RecordS on Purpose :
to be sure to evaluate the time need to sort, relating the number of row on the portal.
And by default I bring back the user on the NO Sort Layout,
in order to be able to perform the Find quicker, before to perform the Sort.
Sincerely yours,
Joel
Thanks for sharing, I’m impress about this method , nice job
With the SQL search choices being “Begins With, Contains, and Ends With,” what would be the code for “Equals!” I would expect that would be the most used choice, but it was not included here. For example, if I want to find all Kens, I cannot do it without getting all Kendalls, Kendras and Kenyas, etc.
Ken, in SQL the “begins with” is LIKE ‘xyz%’, “contains” is LIKE ‘%xyz’, and ‘ends with” is LIKE ‘%xyz’. The equal sign (=) is an exact match (including case) in SQL. In FileMaker the searches do not require SQL and the Exact match is == symbol before the phrase to match.
Joel,
I really like the demo and have already found uses for the technique. One thing that I add/changed is the setup when all search ( global fields ) are empty. When implementing a search on a list view, my clients like to see all records when the search field(s) are empty. So the search script will do a show all records and exit.
With a portal this doesn’t work. So to accomplish this with your demo, I added a field _SearchID with the formula List( __ Kp Contact ID; “*” ) and make sure it is indexed.
On the relationship I modified the graph so the
ZSYS~tog::_Kg Contacts List = zys_COBTACTbyFilterList::_SearchID
Finally in the script if all of the search fields are empty, I set the field ZSYS~tog::_Kg Contacts List to “*”, commit record and exit script before doing any of the prep for or SQL work. Will do the sort if selected.
Bruce,
———————————————————————-
—> On my Sample File I did on purpose display NO Record,
when the All Filter Fields are empty,
because as I do have 350 000 Records :
I didn’t want to slow it down by displaying them all in the Portal.
—> But should I wanted to display them all (in that case) :
>Commit Records/Requests [ With dialog: On ]
>Go to Layout [ “CT Portal NoSort” (ZSYS~tog) ]
>If [ not IsEmpty ( ZSYS~tog::Contact~Filter~Company~G ) or …… or not IsEmpty ( ZSYS~tog::Contact~Filter~Email~G ) ]
>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 ¶” …… ]
>Else
>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¶” ; “” ; “” ) ]
>End If
>Perform Script [ “Contact > Portal > Sort > Reset” ]
—> Then it will display all the Contacts : when the All Filter Fields are empty !
—> But it will be slow on such a large found set !
———————————————————————-
—> A work arround would be :
>_Kc Search ID Calculation (Text) Indexed
>CONTACT~tog, = __Kp Contact ID & “¶” & “dummy_value”
>ZSYS~tog::_Kg ContactS List = zsys_CONTACTbyFilterList::_Kc Search ID
>Commit Records/Requests [ With dialog: On ]
>Go to Layout [ “CT Portal NoSort” (ZSYS~tog) ]
>If [ not IsEmpty ( ZSYS~tog::Contact~Filter~Company~G ) or ……. or not IsEmpty ( ZSYS~tog::Contact~Filter~Email~G ) ]
>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 ¶” & …… ]
>Else
>Set Field [ ZSYS~tog::_Kg ContactS List ; “dummy_value” ]
>End If
>Perform Script [ “Contact > Portal > Sort > Reset” ]
—> Then, it is fast, and you keep a single portal as well !
———————————————————————-
>With a portal this doesn’t work.
>So to accomplish this with your demo,
>I added a field _SearchID with the formula List ( __ Kp Contact ID ; “*” )
>and make sure it is indexed.
>
>On the relationship I modified the graph so the
>ZSYS~tog::_Kg Contacts List = zys_CONTACTbyFilterList::_SearchID
—> I’m not sure that I understand exactly :
do you have 2 portal : 1 to filter + 1 to display All Record ?
—> Could you send me you altered file to :
through a DropBox (or similar) and Zip it before ?
———————————————————————-
Sincerely yours, Joel
Joel, works perfect for me. Thank you for this post!
Thanks very much for this demo. I’m working on a project in which I need to build a WHERE statement. The techniques and custom functions in the demo were the inspiration I needed to come up with a solution.