Simple Pick Lists in FileMaker Pro

Download Demo File

Recently, I was explaining the Pick List technique for a friend and thought there might be a good example online, but couldn’t find one, so here is my version. This is a pretty simple technique and can be adapted to many different aspects of a solution. Whenever you want to get data from somewhere in a database and and there is a long list of items to choose from, this is a good approach.

Simple Data Structure

The beauty of pick lists is that they don’t require any complicated relationships to display the data. The example I am using is a simple invoicing solution, where you have lots of products to pick from and you want to make that accessible on an invoice. Below is the relationship graph of the Demo File. Contacts have Invoices, Invoices have Line Items and the Products table has no linkages.

Card Windows

This technique employs Card Windows, which were added in FileMaker 16, so you will need at least that version to use this demo file. You could do this same technique without using a Card Window, using a regular window and centring it in the screen. Using a Card Window has the benefit of giving the user a good sense of where they are in the database, because they can see the previous layout in the background greyed out. Adding a Cancel button allows the user to back out of selecting any products with no changes made to the invoice. With Card Windows, you get a linear workflow – add an item, then pick or cancel and end up back where you started. This approach can help keep it clear to the user what is going on, and where they are in the database system.

You can test the Pick List from the Invoices layout of the Demo File. Click on ‘Add Product’ button and up pops the pick list. 

The script is straightforward. The basic steps are:

• Open a New Window – in this case a Card Window that has been resized to fit nicely over the existing Invoice Layout

• Show All Records – You can have more sophisticated filtering if you need.

• Pause Indefinitely – It pauses in the middle and waits for user interaction. If you look at the ‘Add’ button on the pick list layout, you will see it just say ‘Continue Script’. When they click on an item in the Product List, the script resumes.

• When the script resumes, it sets two variables, the Product Name and the Product Price

• The Card Window gets closed

• Go the Portal Row (Last)

• Set two fields based on the two variables set above

You can of course add as many variables to this routine as you need, including something like Product ID, if you want to link back to all the products uses for a report.

Script Upgrades

If you have a lot of items in the product list, and you might want to filter them down into smaller batches for the user, the script could be more sophisticated, for instance:

If (get(script parameter) = “Plumbing”

Enter Find Mode - search on Plumbing category, etc.

Using this approach, you would simply create different buttons, each calling the same script, but with a different script parameter embedded in the button. Alternatively, you could add filter buttons at the top of the pick list. If the pick list was for a long list of customers, you might have an alphabetic list – A, B, C, D etc. If it is building supplies, there might be a button for Plumbing, Wood, Electrical etc.

The Pick List Layout. 

It is a good idea to create a separate layout for the Product Pick List. Keep a separate layout for data entry into the Product list and then customize the Pick List layout. In this case, the displayed fields are locked and there is a button overlaying the text. This prevents the user from accidentally clicking into the field. The plus sign icon is right aligned. The button script is a single ‘Resume Script’ step.

That’s it, hope you find this useful. Leave me a comment below if you think of anything you’d like to see added to the demo.