This module provides a FileMaker Pro developer with the layout structure, schema and scripts needed to make a generic but portable picker window. I use this module as a way to assign a value to foreign key fields in my systems. This module makes use of a virtual list table with calculated repeating fields, Execute SQL, and a popup modal window. The popup window will be sortable and searchable, and as long as you’re comfortable with FileMaker’s ExecuteSQL function, then you’ll find this a breeze to set up and use.
SQL Picker Window Screen Capture
Features
Smart scripting that will enhance the SQL that you pass ( will automatically add the “ID” field, and will automatically sort records if you don’t specify a sort order)
UI provided for sorting of records displayed
UI provided for QuickFinding of records displayed
SQL Picker Window is displayed in a modal window
Easy to integrate, and very flexible
How To Install
Copy VirtualPickerWindow table into your solution
Reset next serial number to 1 (applies to VirtualPickerWindow::zc__recordSerial)
Copy all scripts to your solution
Paste all VirutalPickerWindow layout objects to your solution (Your layout should be called “VirtualPickerWindow” –> you can use the one that FileMaker automatically created for yout. Just delete all layout objects from it, and paste all the layout objects from this module over)
Add an OnObjectEnter script trigger to one of your foreign key fields ( Script = “Initialize Picker Window”, Parameter = SQL Select statement that would return all of the fields that you want to display in the window)
Side Notes
It’s entirely possible that you may need customize some of the scripts in this module to make it work with your solution. This module will require you to make use of a very strict naming convention for your primary keys. It’s currently setup to expect that all of your primary keys are named “ID”
Since this module makes use of virtual lists, it means that the fields displayed in the picker window will actually be unstored calcs. Be aware of this, because it means that should you choose to display a large volume of records in it, then the QuickFind will probably perform slowly.
Download SQL Picker Window
Comments