Xlate function
Description
Returns data from the specified element in a table that has not been opened. Typically used in symbolic fields to bring back column data from a related table.
Syntax
status = Xlate(tablename, key, field, control [ ,expression])
Parameters
The Xlate function accepts arguments for the following parameters.
| Parameter | Description |
|---|---|
| Tablename | The name of the table from which the record is to be read. tablename must yield the literal name of a table. It is not a table variable (the result of an Open statement). The table must be currently attached. |
| Key | The key or keys of the row(s) containing the column values to be returned. Can be multiple keys. Separate multiple keys with a system delimiter. Each key will return the value of that row in the specified column. These values will be separated by whatever system delimiter you use to separate the keys during the call to Xlate. Any values (@VM-delimited) or subvalues (@SVM-delimited) will be delimited with their own system delimiters. |
| Field | The number of the field or name of the field in quotes (example: "COMPANY") to be extracted. If a null is specified, the entire record is returned. If a zero is specified, the key is returned. |
| Control | Must be either of the literal values X or C. These keywords indicate what the function should return if the record does not exist. An X indicates that a null should be returned, while a C will cause Xlate to return the key value. |
| Expression | Indicates the number of times the system delimiters will be converted to the next lower delimiter. The default is 1. The column returned from the row can contain system delimiters. If the column is a multi-valued string (@VM-delimited), it becomes a subvalue string (@SVM-delimited) after one conversion. This feature becomes important when key yields a multi-valued string. After the conversion, each value contains a string of subvalues. A text mark (ASCII character 251) is the lowest delimiter. |
Let's presume you pass three keys to Xlate, and you specify the contents of the code column. The value of key could be:
key1:@FM:key2:@FM:key3
where you use @FM to delimit the keys. If the code column contains multi-values (as code in sample_invoices does), then the results could be:
mv1-1:@VM:mv1-2:@FM:mv2-1:@VM:mv2-2:@FM:mv3-1:@VM:mv3-2
@FM separates values from each column.
If the keys had been passed using any other system delimiter than @FM, the column values would have been returned separated by that delimiter (including @VM).
In the case of @VM, all returned values would be then delimited by @VM, and you could no longer tell which values belonged to which field.
See Also
Example
/* The following stored procedure function returns multi-value data from the two rows specified in the Xlate call. In its code column, sample_invoices has multi-value data, so this is returned in RetVal. */ Compile Function test_Xlate (void) tablename = "SAMPLE_INVOICES" ;* previously unopened table keys = 8:@FM:11 ;* two rows to access field = "CODE" ;* column to access control = "X" ;* return null, if not found RetVal = Xlate(tablename, keys, field, control) Return RetVal
* Given an orders table with a column named CUST_ID, and a customers table with a column COMPANY_NAME
* look up the customer name from the CUSTOMERS table, create a formula for a calculated column to return the company name for the order
@ans = Xlate("CUSTOMERS", {CUST_ID},"COMPANY_NAME","X")
* Look up the value of a column in a table
id = "29138"
* return the value of the LNAME field
lname = Xlate("PERSON", id, "LNAME", "X")
* return the value of the first field
f1 = Xlate("PERSON", id, 1, "X")
* return and entire record
rec = Xlate("PERSON", id, "", "X")
* Pass a multivalued list of keys, the answer is delimited by the delimiter of the keys. For example, @vm delimited keys return @vm delimited results
* If the column contains multivalues, for example a list of phones, the answers will contain @vms between the multivalues. If the keys and the multivalues are both @VM delimited you blur the association between key and result.
* Use the fifth parameter to 'demote' the @vms to @svms to keep the multivalues separate
keys = "414294,487836,E1215752,378152,414304,3936,30138,H1205861"
Convert "," To @vm In keys
* Look up the lnames for the keys. LNAME is single values
* Because the keys are vm delimited, the answer is vm delimited
lnames = Xlate("PERSON", keys, "LNAME", "X")
* Look up the products these persons have ordered. Product is multivalued, so the answer for each id is vm delimited
* In this example, we don't care that you cannot determine which phone goes with which key. You can use rti_dedupe_Array to make a distinct list
order_ids = Xlate("PERSON", keys, "ORDERS", "X")
products = Xlate("ORDERS", order_Ids, "PRODUCTS", "X")
unique_products = rti_dedupe_array(products, @vm)
* Use the 5th parameter to "demote" the results to retain the association.
* In this case, use a "1" so that the @vms become @svms. The phones remain associated with each person id
phones_demoted = Xlate("PERSON", keys, "PHONE", "X", 1 )
* Note: a negative 5th parameter will "promote" the results. In this case the @vms become @FMS
phones_promoted = Xlate("PERSON", keys, "PHONE", "X", -1 )
* Read a block of records. Use @fm delimited keys, demote once.This yields @fm per row @vm per column, @svm between multivalues
* You can then process the block however you please
Convert @vm To @fm In keys
records = Xlate("PERSON", keys, "", "X", 1)