The COMPLEXTABLE function searches the specified complex table for a single record and returns a single field from that record. The parameters to this function include, at a minimum, the name of the complex table to be searched and the value to search for within the records. If only these two values are provided, the function will search the complex table using the table’s primary index, returning the field upon which the primary index has been defined from the record found.
As optional parameters to the function, the index to search upon and the field to return from the matching record can be specified by passing in the definition names of each. While optional, these parameters are provided in most use cases.
An additional variation on the parameters passed to the function is in the situation when a search index is specified, and that index is a child to another index within the table. In this scenario, additional search values must also be provided to the function. The requirement is for each index, starting with the one specified up to the top-level index (one that has no parent index) in the structure, there must also be a corresponding search value provided to the function.
As an example, assume a complex table with three indexes defined: A, B, and C. Index C is a child index to B, and B in turn is a child index to A, which is a top-level index in the table. When searching this table with the COMPLEXTABLE function, if index C is specified as the search index, search values for indexes A, B, and C must be provided. During the search, the function will begin by finding records that match on index A, then within that set those records that match index B, and finally within that sub-set the first record that matches index C.
| @COMPLEXTABLE (Table Name, Search Value, [Parent Search Value N,] [Search Index, Return Field]) | |
|---|---|
Table Name | Required string parameter; contains the name of the complex
table to be searched by the function. |
Search Value | Required string parameter; contains the value used to search
the complex table records. |
Parent Search Value N | String parameter(s); required and specified only when Search Index is
a child index. One Parent Search Value must then
be specified for each index above the Search Index in
the index hierarchy. |
Search Index | Optional string parameter; provides the name of the index within
the complex table used by the function to locate the desired record
based on the Search Value and possibly Parent
Search Values. If this parameter is not provided, the primary
index of the complex table is used by the function. |
Return Field | Optional string parameter; provides the name of the complex
table field whose value is returned from the record found by the
function. If this parameter is not specified, the field for which
the primary index of the complex table is defined is the default
field value returned. |