As-you-type filtering in FileMaker (Improving the Invoices Starter Solution)

The Invoices starter solution is pretty good, but there are a few quick ways in which you might want to improve it ready to use it for your business.  An easier way of selecting products to enter into your invoice is one such improvement.

When you have an increasing catalog of say, 30+ products, it quickly becomes difficult to find an individual product using a standard drop-down list or popup menu.  To avoid this issue, we’re going to implement a quicker and easier way to search for and select products to insert into our invoices.

To achieve this, we’ll create an additional product list layout, designed to fit in a popup window, add a global field to use to perform the search, then create a script to either filter the list or select a product and insert it as an invoice line item (depending on the parameter passed to it).

The line items portal on the Record Detail-Invoices layout needs to be named, so we can reference it using the “Goto Object” script step.  I’ve called it InvoiceLineItems, but it can called whatever you like, but it’s best to name it something memorable.  You can name objects via the Position tab in the inspector, visible in Layout mode (you might need to click View->Inspector to se it).

First, we need to create a global field in the products table to act as the “search box”.  I’ve called it “z_Filter_g”.  Remember to define it to be global from the Storage tab of the Options dialog.  Then we need to create the filter popup layout, which looks like this:

In the image above, you’ll see the global field (z_Filter_g), with an OnObjectModify script trigger set to perform the “Filter Product” script with the “Filter” Parameter.  Of course, of you have FileMaker 11, you could use the Quick Find box instead of the global search field.

Next let’s write the script, that will need a Four way “If” statement to decide whether to “Open” (the popup window) or  ”Filter”, “Clear” (the search results) or “Select” (the product and insert it as an invoice line item)  The script looks like this:

Define the Clear and Select links to be buttons performing the “Filter Product” script, passing “Clear” and “Select” respectively as the Script Parameter.

On the Record-Detail-Invoices layout, disallow entry into the Product ID field in the Line Items Portal, and define that field as a button, performing the “Filter Products” script with “Open” as the parameter.

When this is all completed, clicking in the “Product ID” field in the Invoice will open the popup window.  You’ll then be able to select a product, and it will be inserted into the invoice.

In this particular example, the search works only for the “Product Name” field.  By creating an auto-enter calculated text field returning several fields separated by spaces or carriage returns, as defined below, we can search across multiple fields.  If you’re doing this, you’ll need to modify the Set Field script set in the “Filter” branch of the Filter Product script to “Set Field [Products::z_MultiFieldSearch_c; Products::z_Filter_g]”  Or, if you’re using FileMaker 11, you can remove the “Show/Hide Status Area” script step, and the “Filter” and “Clear” branches, and use the Quick Find box instead.

The attached files shows both options. Any questions, please ask away in the comments!

Invoices (Quick Find Line Item Search)

Invoices (Global Field Line Item Search)

For more info about the author and the FileMaker product line, or to discuss how I can help with your next project, please get in touch.

Copyright © 2010 – The Stuchbury Group – All Rights Reseved.

This document and all attached files are offered as is, without warranty expressed or implied. Whilst every care has been taken to ensure any attached files are robust and functioning as intended, the author and any contributors accept no responsibility for loss or damage of any kind. Before making any changes to your data you should ensure you have an up-to-date backup made. The author and any contributor to this document and all attached files, accept no responsibility for any loss or unintended change to any data whatsoever.  Guarantee of fitness for any purpose is expressly disclaimed.  If you are unsure of anything, don’t attempt it with live data.  You have been warned.