FM 16: Pseudo Indexing

During our recent discussion of SortValues and UniqueValues, I made passing reference to an article I wrote a few years ago 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.

Well today we’re going to dust the pseudo indexing demo off, and update it to take advantage of the above-mentioned new-in-16 functions. Why bother? Here’s why: the new implementation…

1. is simpler (has fewer moving parts)
2. scales better
3. is less brittle (prone to breakage if schema is renamed)
4. enables you to bring new fields on board faster
5. can sort the pseudo index in ascending or descending order

Demo file: FM-16-Pseudo-Indexing.zip (requires FM 16)

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…

…and can use 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 the the two highlighted fields.

The basic idea is…

• the parameter gets pushed into dev::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. (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.