How to index translations

A common requirement of a B-tree is to index one file by a value translated from another file. For example, imagine a sales order file with customer numbers in attribute two. The customer numbers are identifiers for items in a separate customer master file called CM that keeps customer names in attribute five. How can we define a B-tree named SOBYCUST that keeps the sales order file sorted by customer name?

The solution is to simply put CASE ROOT = "SOBYCUST" ; KEY = OCONV(ITEM<2>, "TCM;C;;5"):nul:ID in the BTPKEY subroutine. That translates the customer number from attribute two of sales order items into the customer name from attribute five of items in the CM file. If sales order numbers are numeric, use something like (ID "R#10") instead of just ID.

The ";C;" code in the translate conversion string indicates the original value in attribute two should be retained as the result of the translation if no item is found in the customer file. That allows a browser or any other program to do a "reverse translation" and find a sales order for a given customer name: just create a dummy sales order ITEM consisting only of a customer name in attribute two, then call BTPFIND (the version that uses BPTSEQ as described in Branches #5) using the SOBYCUST B-tree. BTPFIND will return the customer's first sales order number in the ID parameter.

Note that if a customer's name (attribute five) is changed in the CM file, then that customer's sales orders have to be deleted from and reinserted into the SOBYCUST B-tree.