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.
Cool stuff, Kevin! Thank you for sharing this technique.
Thank you Steve. I appreciate you saying so.
Really nice, Kevin. Thanks!
Thanks Jonathan!
This is pretty sweet, Kevin. Thanks for this, and for the shout out.
Thank you Darren. Maybe we can get you back here for another guest article one of these days. (Jonathan, that goes for you too!)
Nice technique! Thank you for sharing this!
I haven’t made a generalized pseudo-index script like this, although I made a “Copy from Found Set” script, which does one of the things. However, that uses a loop over the records. Switching it to an evaluated calculation in a global field that can use FileMaker’s much faster Summary of Text field type should improve speed. Maybe I could have it attempt to use a Summary Text field and fall back on the loop method if it gets an error. Hmm…
It looks like if you want it to support any well-formed string as a parameter, anyone using it would need to be sure the first part of that calculation string is a field (to be able to get the $to). Alternatively, you could use some kind of multi-parameter-passing method, where you can specify the table occurrence more explicitly.
Kevin, have used this one for a long time. Might have come from TechTalk
//Eilert Sundt
//http://www.eilert.no – Note: domain seems to have changed hands
//set startchar to 1
//wrap in UniqueValues() if wanted
GetNthRecord ( fieldname ; startchar ) &
Case (
startchar < Get ( FoundCount ) ;
"¶" & FoundList ( fieldname ; startchar + 1 )
)
This is a very good and flexible technique, but it’s important to remember that using the Evaluate() function in field calculations poses a security risk, as it will execute with Full Access privileges.
Therefore, you will need to configure the security settings accordingly to prevent anyone from calling your script with incorrect parameters or modifying the global field used as a function parameter.
Excellent points and duly noted. Thank you Alex.
Nice technique. Thanks for sharing!
My first thought when seeing that you had updated this was that it would be using the new GetRecordIDsFromFoundSet function. So that set my mind wondering if there is a way to use that.
I came up with this.
Advantages:
– Only requires 1 field added to each table, which is a stored calc for the RecordID, and some people may already have that in their schema for other reasons.
– Doesn’t use Evaluate. I’m not opposed to it, but others brought up concerns. I think if it is a controlled system where users don’t have full access, it should be fine.
Disadvantages:
– This one uses ExecuteSQL, which might not be as performant(?). Haven’t tested it at scale.
– You can’t easily pass concatenations, like your example. Still possible, but you’d lose some of the modularity.
This is the basic calc to get the list of values from the found set:
Let ( [¢Table = SQLTable ( Meetings::Title ) ;
¢Field1 = SQLField ( Meetings::Title ) ;
¢Field2 = SQLField ( Meetings::RecordID ) ;
¢SQL =
"SELECT " & ¢Field1 &
" FROM " & ¢Table &
" WHERE " & ¢Field2 &
" IN (" & Substitute ( GetRecordIDsFromFoundSet ( 0 ) ; ¶ ; "," ) & ")"
] ;
ExecuteSQL (
¢SQL ;
"" ; ""
)
)
(We’ll see how much the comment mangles the calculation formatting.)
Note that SQLTable and SQLField are custom functions that take a FQFN and get the table or field name, quoting them to make them SQL safe.
From there, you would apply UniqueValues, SortValues, and trimming the same as you were already doing.
Like I said, I haven’t tested to see how it compares for speed. Just another option.