XTREE enhancement: Automatically fill in a target cell based on the contents of a source cell using a lookup table. This is similar to the combo/list/substitution capability defined with Lest and CellListX and enabled by cformat "l" or "ll", except that the description appears in a separate column from the code—i.e., there is no need for a combo/dropdown box. Also, the list of code/description pairs is file-based rather than embedded in the coldef.

The feature is intended for data-entry cases where the user is entering codes for which you want to provide immediate visual confirmation 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 or complex database lookups, where only an exit to the application would make sense. Between those two extremes, say lookup tables between 50 and 50 thousand entries, providing the table to XTREE once and letting it do the lookups internally will be much faster and more convenient; this is especially true 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 general ledger transactions, you might use this technique to echo the account description in a non-editable column when the account code is entered.

Setup requires two new 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 from FNFILEAGE.BSI in SOSLIB[907,10] to determine when your FileListX file becomes out-of-date relative to the data file it was extracted from. 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 lists 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 target column, which is where the description will be echoed.

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. Chances are that the application already knows the descriptions associated with the codes being loaded into the tree, so it is probably more efficient for it to specify them rather than force XTREE to look each one up.

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.