" FROM " & SQL_T ( CONTACT~tog::_ Kp Contact ID ) & " AS Ct¶" & "SELECT " & SQL_F ( CONTACT~tog::_ Kp Contact ID ) & 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. Portal Filtering & SQL: Let’s do some SQL 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: Target Data Fields 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! Repetition is for dynamic sorting labeling purpose Repetition is a popup menu «Begin|Contain|End» Repetition is where you type your filter string In the «zSys» table we have 10 global text fields with 3 repetitions: Portal Filtering & SQL: Filter Source Fields 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! from a global text field «_Kg ContactS List» to the primary key field «_ Kp Contact ID».from «zSys» table (1 record only) to «Contact» table (where we store all the data).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 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. If FileMaker would like to confuse you on purpose, they do it right. 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 «?». DYNAMIC PORTAL SORTING HOSTED FILES FILEMAKER PRO HOW TOIn 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! The example shown is one of the most common, where filtering is done on both a first and last name where either part contains a sequence of characters.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.ĭemo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed) This video showcases how to fully leverage the new filtered portal option within FileMaker 11. Enter a few pieces of critical know-how and you get the exact feature you were expecting with all the bells and whistles. Initially, there were some harsh critics, as the filter was a "display only" feature and did not adjust calculations which used the same relationship. However, with the release of FileMaker 11, the new filter portal option, presented as a check box on the portal settings dialog, offers a much easier method for filtering portals. When it comes to filtering portals, the previous method was to use a dedicated relationship in order to filter out related data - which is still valid in a variety of situations. Little by little, newer versions of FileMaker have been making it easier to accomplish tasks which typically required "workarounds" to provide certain features.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |