Notice
  • Please enter your DISQUS subdomain in order to use the 'Disqus Comments (for Joomla)' plugin. If you don't have a DISQUS account, register for one here

Core FileMaker Blog

Keep up to date with CoreSolutions

SQL Picker Window Module For FileMaker

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

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

Leave a comment

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved