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…
- 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
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.
10 thoughts on “FM 16: Pseudo Indexing”
This looks nice, Kevin! Would you be able to cite some examples where this might be the preferred approach over using something like a dynamic filtered value list or maybe even Magic Value List?
It’s another way to skin the proverbial cat… found-set aware… doesn’t require value lists or relationships… the results can be sorted in ascending or descending order.
What a shock!
A mac screenshot here! ;)
There have been quite a few of them over the last year or two.
But a Fabrice sighting is a much rarer occurrence. Nice to see you my friend.
This fits in nicely with the “Windowing in FM 16” article, as well!
I like the “found-set aware… doesn’t require value lists or relationships” for this technique!
I appreciate the kind words Beverly!
Kevin – Great concept and solution. Learned a lot and it has the squirrels running.
I had a problem with the script when adding the process to my database: it only displayed the data from the first column chosen. Turns out the dev::zz_g_pseudo_index field needed to be on the calling layout. I didn’t see this mentioned in your description. If you have a better solution, please share it.
In my experience dev::zz_g_pseudo_index does not need to be on the calling layout; unless I’m mistaken, my demo works fine without that field being on the calling layout.
Thanks Kevin. I’m sure placing the field on the calling layout solved my issue. I will dig a bit deeper.