This is one of a hopefully continuing series of periodic posts highlighting recently added routines that might be useful (as-is or as examples) that might otherwise go without notice buried in the treasure chest of the SOSLIB and EXLIB repositories.

In this case, the subject is XTREE-based filtering. There are several ways in which XTREE and data-filtering might go together:

1) Using XTREE as a means of displaying the result set. (This is an obvious use for XTREE and doesn't require any special features or examples.)

2) Using XTREE as a means of inputting the filter criteria (i.e. as a somewhat more user-friendly alternative to something like SQL). A new SOSLIB module, FNXFLTR.BSI contains a few handy functions for implementing such a dialog. One, Fn'XFLTR'Dlg() handles the UI, creating the dialog and prompting for the selection info. (You configure it by setting up an array describing the fields available for selection.) Another, Fn'XFLTR'Field() applies the captured selection criteria to a single field to determine if it satisfies the crieria. (The app loops through the available records, passing the relevant fields to this routine.) For the SQL environment, another routine, Fn'XFLTR'Gen'SQL'Query$(), can be used to convert the criteria entered by the user via Fn'XFLTR'Dlg() into an SQL query string.

3) Using the Coldef LoadOptions=NoRowIf! command (new as of 6.5.1627.0) to tell XTREE which records from the array to load into the tree, i.e. to convey the filtering results to XTREE without having to build a new array. An EXLIB sample program, TSTXFLTR2.BP , illustrates how it might be used.

Combining the above we get something like this:

I like it! As you know customers have asked many times for AutoColumn filters in Xtree, maybe this one way I can implement it in a round about way... (unless it’s built into Xtree itself!)

This is absolutely great, congratulations for the initiative Jack.

Are we near to be able to add a first row with combos containing the existing values for all columns defined to be auto-filtered?

The only difficulty to not do it right now is how to define "combos" for the first row while the next rows have "other style" :rolleyes:

Actually, although the fnxfltr.bsi SOSFUNC routines may appear to be an attempt to deal with the long-longed-for (is that redundant?) XTREE auto-filtering, that wasn't really the primary motivation.

Instead, the driving motivation was to create a more user-friendly approach to SQL queries. Aside from the aspect of making it possible for a user not fluent in SQL to be able to retrieve a desired set of records, the other aspect of the problem, one that does not lend itself to XTREE internal auto-filtering, is that the full database is likely way too large to load it all into the tree so that XTREE can do the filtering.

It's not just a memory constraint, but SQL databases are typically on another machine across the network, so the app has to pull all the selected records across the network (with a separate SQLOP_FETCH_ROW call for each). If the goal is to select, say, a thousand records for display from a table containing a million records, it would be ridiculous to first retrieve the million records and then pass them to XTREE for filtering. The only sensible approach would be to let the SQL database do the filtering, so that we only need to retrieve the reasonable-size subset of records.

Relying on XTREE for auto-filtering makes sense when the overhead to retrieve the entire set of records from storage and load into the tree is "reasonable". The definition of "reasonable" is obviously a bit fuzzy, but in all cases the threshold will be a lot lower for the ATE environment (where the data has to again travel across the network from the server to the client) than in the A-Shell/Windows environment (where it can go direct from application memory into XTREE memory).

Putting all of that aside, the combo problem you mention is a tough one and isn't likely to be solved any time soon. BUT, it probably doesn't matter because even the first-row-combo scheme would still require too much custom setup by the app, and wouldn't be sufficient for "proper" auto-filtering anyway. We need a variety of data-sensitive drop-downs, in some cases with checkboxes of ranges, or multi-select from a discrete list of possibilities; in other cases perhaps from-to fields, or maybe even a regex-type entry field.

What I'm imagining is that rather than having to populate all these complex drop-downs in the coldef, we instead try to make XTREE do as much of the work itself (essentially like Excel does). Maybe in some cases we can use a Coldef option to give it a clue as the style of filtering the programmer thinks is best for a particular column, but generally we don't want to have to do that. We'd rather have XTREE just figure it out on its own.

I started to work in that direction when I discovered the obstacle of the fact that XTREE does not support "hidden" columns.

But the new LoadOptions feature may just turn out to be a reasonable workaround for that problem.

I'll try to schedule some bandwidth to give more thought to this...