Picker Window

SQL Picker Window

by Matthew Leering

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.

 

Picker Window

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

  1. Copy VirtualPickerWindow table into your solution
  2. Reset next serial number to 1 (applies to VirtualPickerWindow::zc__recordSerial)
  3. Copy all scripts to your solution
  4. Paste all VirutalPickerWindow layout objects to your solution (Your layout should be called “VirtualPickerWindow” –> you can use the one that FileMaker automatically created for you.  Just delete all layout objects from it, and paste all the layout objects from this module over)
  5. 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

 

2 responses to “SQL Picker Window”

  1. Bruce Robertson says:

    Any thoughts about modifying this example to allow picking from distinct values queried from a single field only? For instance, let’s say in this example you want a city picker that picks from the list of cities that already exist in the file. So the SQL statement would be SELECT DISTINCT City from People.

    • While I could see the usefulness in that Bruce, it’s fundamentally different than what this module is accomplishing currently.

      Right now, the “Initialize Picker Window” script analyzes your SQL statement, and injects each record’s primary key as the first field returned (kind of like hiding the ID field in a FileMaker value list). to accomplish what you’re asking, I could potentially check for keywords like “DISTINCT”, and if they exist, I could circumvent the injection of the primary keys into the query. Do you think that would accomplish what you’re going for?

Leave a Reply

Your email address will not be published. Required fields are marked *