Demo file: FM-22-Pseudo-Indexing.zip (works w/ FM 16 or later)
When I wrote about Sort Values and Unique Values back in 2017, I made passing reference to an article I’d written a couple years previously on the topic of “pseudo indexing“, i.e., displaying a list of unique values for a specific field for a found set, rather than for all records.
Actually, I wish things were that simple. Full disclosure: there was the original FM 13 demo/article. Then when FM 16 was released, I did a second demo/article, “FM 16 Pseudo Indexing”. Recently I discovered a flaw in the approach I used in the FM 16 article, so I am pulling that demo and article off line in favor of this one.
Today we’re going to dust off the old pseudo indexing demo, and update it to take advantage of various FileMaker functions and innovations that were not available back in 2015. Why bother? Here’s why: the new implementation…
- is simpler (has fewer moving parts)
- scales better
- is less brittle (prone to breakage if schema is renamed)
- enables you to bring new fields on board faster
- can sort the pseudo index in ascending or descending order
To learn about benefits of using pseudo indexes, please see the original article. Today we’re going to focus on what’s changed with the new implementation.
In both demos, you click a button to invoke a script, with a different parameter attached to each button. In the original demo, you pass a value list name; in the new demo you pass a fully-qualified field name…
…which uses GetFieldName to keep things from breaking if the underlying field or TO are renamed. No need to define a new value list every time you need a new pseudo index.
Each data table (i.e., transactions and chart_of_accounts) contains these three highlighted fields.
The basic idea is…
- the parameter gets pushed into zz_g_field
- and evaluated by zz_flex_evaluate
- all values for the found set are rendered in a list by the summary field zz_s_list_evalulate
- those entries are de-duplicated via UniqueValues
- and sorted via SortValues
- pushed into dev::zz_g_pseudo_index
(Ray Cologon’s Trim4 strips off the unwanted trailing return) - and displayed in a “dialog” window based on this layout
(Note the icons to zoom in/out and to sort asc/desc)
If you’re wondering why zz_flex_evaluate references a global field instead of a variable, if you use a variable it appears to work at first, but after a while zz_s_list_evaluate stops updating. Using a global field instead of a variable prevents this from happening. Also, for the technique to work reliably, the global field must live in the local table. (For more information see Darren Terry’s Shaking The Dependency Tree.)
Note: instead of a fully-qualified field name, you can pass any well-formed string as a parameter, e.g.,
GetFieldName ( trans::acct_number ) & " & " - " & " & GetFieldName ( coa::acct_name )
…which produces this:
Here’s the full script.
Why use a dialog window instead of a card window? I briefly considered going with a card window, but I prefer being able to move and resize the pseudo dialog window, as well as being able to clearly see the contents of the background window.