LookupX and FileListX
Added August 2018
This feature, which involves both the LookupX and FileListX Advanced Coldef Options, automatically fills in a target cell based on the contents of a source cell, using an external lookup table. Conceptually it is similar to the combo/list/substitution capability—see ListX and CellListX, cformat l and ll—except that the description returned from the lookup operation appears in a separate column from the initial code. In both cases, XTREE is able to supply the description for the code without having to exit to the application, but with LookupX, there is no need for a drop-down box, and the lookup table is supplied via an external file rather than embedded in the coldef.
The feature is intended for data-entry cases where the user is entering codes that you want to provide immediate visual confirmation of, by looking them up in a table and displaying the corresponding description. It fills the gap between small lists (where a combo box might make more sense) and very large, complex or rapidly changing database lookups (where only an exit to the application would make sense). Between those two extremes (say, lookup tables between 50 and 50K entries that are reasonably stable), providing the table to XTREE once and letting it do the lookups internally will be much faster and more convenient (especially in the ATE case where the turnaround time to exit and re-enter might be a significant fraction of a second, enough to slow the data-entry operation).
For example, in a tree used to key in GL transactions, you might use this technique to echo the account description in a non-editable column when the account code is entered. Or, as in the example below, LookupX is being used to display the product description as the code is entered.
LookupX requires the coordination of two Advanced Coldef options:
FileListX=idx,fspec
LookupX=idx,targetcol
FileListX defines the table and associates it with an single character index code in the range of A-Z or a-z. Note that the index shares the same space with the ListX and CellListX lists, so must be unique relative to them. In fact, combo and list substitution columns, i.e. cformat l or ll, may refer to lists defined by FileListX just as they can for CellListX.
The fspec can be an AMOS or native spec (from the client perspective). In the case of ATE, the search path will automatically include the %ate%\cache and %ate%\permcache directories, and it is up the application to transfer the file from the server if necessary.
Tip: you may want to use the Fn'FileAgeDiff() function in FNFILEAGE.BSI in SOSLIB:[907,11] to determine when your FileListX file becomes out-of-date relative to the data file it was extracted from. And for ATE, the ATEAPX.SBX routine will be handy for syncing the file to the client.
The file should be formatted as follows:
code,description
code,description
etc.
As with the List{X} and CellList{X} lists, if the first character of the first code is neither an alphabetic character or a numeric digit, it is assumed to be the delimiter; otherwise the delimiter is assumed to be the comma. However, unlike the other list types, here the descriptions may may be quoted if they contain the delimiter. (So standard two-column CSV files should be acceptable.)
To associate the list with a source and target column, add the LookupX= clause to the coldef entry for the source column—i.e. the column containing the code, specifying the index used in the FileListX definition, and the targetcol (target column where the description will be echoed).
As an example, the relevant parts of the coldef for the sample tree illustrated above would look something like this:
coldef$ += "0~0~x~H~FileListX=A,prod.csv~~"
coldef$ += .offsiz$(ST_XTR_ARY.code) + "~Code::Total~#En~LookupX=A,3~~"
coldef$ += .offsiz$(ST_XTR_ARY.qty) + "~Boxes::=SUM~|#En~~"
coldef$ += .offsiz$(ST_XTR_ARY.descr) + "~Prod Descr~S~~"
Note that the FileListX is associated with a "zero" column, while the LookupX is associated with the real column where the code is entered. The two arguments to LookupX link it to the FileListX index (A) and to the target column where the description will be displayed (3).
The lookup and fill operation is performed automatically whenever the source column is edited and changed. If there is no match for the code, the description will be blank. When loading data into a tree, the lookup will be performed only if the target column is blank. The theory here being that if the application is supplying descriptions, they are probably already correct and thus it would be a waste of time to look them all up again.
Note that if you want the looked-up descriptions to be returned to the app with the answer array, use the "U" code for the description column.
History
2018 March, A-Shell 6..5.1631.0: Added to A-Shell
