Example 5 - Parameters from table

Task:

Depending on various status information, such as a geo-fence ID GEO_FENCE_ID, a line identification in public transport LINE, the current month or other values, several variable parameters, e.g. a waiting time, a preferred direction or a switch-on command, are to be read from a table for subsequent calculations.

Solution:

1. Create the table

If the table is not too large, it can be made available directly as a text resource in the math module. Alternatively, it can be provided from (compressed) files or base64 sources using the decoder chains.

The following table is created under the resource tag "Table":

GF_ID ; LINE ; T_Wait ; Pref_Dir ; Cmd_On
----- ; ---- ; ------ ; -------- ; ------
12; S8; 20.5; left; true
14; S8; 45.0; left; false
28; S8; 12.0; right; true
*; S8; 18.0; right; true

12; U9; 18.5; right; true
26; U9; 5.2; right; true
37; U9; 9.2; right; true
42; U9; 5.2; left; false

100; *; 100.0; none; false;

2. Implementation in the formula text

_rowIdx = selectRow(GEO_FENCE_ID, LINE, {
         table: 'Table' // mandatory
       , keys: ['GF_ID', 'LINE'] // mandatory
       , colSep: ';' // optional
       , anyKey: '*' // optional
       });
_TWait = getField(_rowidx, {table: 'Table', field: 'T_Wait', notFound: 10.0});
_PrefDir = getField(_rowidx, {table: 'Table', field: 'Pref_Dir', notFound: 'none'});
_CmdOn = getField(_rowIdx, {table: 'Table', field: 'Cmd_On'});

Functions used

Explanation:

The table is read line by line. Empty lines or those that begin with 3 identical "line" characters (here e.g. '---') are skipped. The lines are cut apart at the defined separator (see colSep), freed from superfluous spaces at the front and back and interpreted as a variant. The first line provides the names for the columns (= fields). All other lines contain the data elements for comparison or reading.

This preparation only happens once with a selectRow() function in the formula text, after which the table is available to all other functions under the given name. The specification for the column separator colSep: ';' is optional. By default, a single comma, semicolon, tabulator or '|' is accepted as a regular expression. The specification of anyKey is also optional and denotes a table entry (text) that matches each key value offered.

With selectRow(), a suitable row is now searched for in the table and its index is returned as the result value. For this purpose, key values, here e.g. GEO_FENCE_ID as <int> and LINE as <str>, are searched for in the fields GF_ID and LINE defined using keys. As the search is performed line by line from top to bottom when the key values are changed, the entries with anyKey must always be sorted at the end of a corresponding data block. In the example, the search with "S8" and geo-fence ID 14 has the hit before any geo-fence ID and this geo-fence ID in turn has priority over the entry with geo-fence ID 100 and any line. This prioritization can be influenced by sorting the lines accordingly.

The getField() function is used to read matching entries from the designated column (field) from the same table. If the index _rowIdx < 0, no matching row was previously found and the variable specified with notFound is passed on to the variable.