The GETVAL function allows formulas to extract data values from text strings—a handy feature for storing data in programs with no spare numeric or text storage locations. An example will best explain how it works:
Example: Suppose a Description column (field) in your accounting program contains the following text:
SillySpray child's lawn sprinkler [Wt=3.8;Cs=12]
The square brackets surround the text area from which GETVAL can extract data. Here, Wt identifies the per-unit weight of a SillySpray sprinkler (3.8 pounds), and Cs is the number of sprinklers per case (12).
If the above Description field text corresponds to cell D7 in FormCalc SST, this formula would extract the per-unit weight and the number of sprinklers per case, and multiply them to calculate the weight per case:
=GETVAL(D7,"Wt")*GETVAL(D7,"Cs")
What if the goal was to calculate a shipping weight for each line of an invoice? If cell D9 corresponds to a quantity of items, and D7 holds the same Description text described above, this formula would multiply the weight per item by the quantity of items ordered, thus calculating the shipping weight on that line of the invoice:
=GETVAL(D7,"Wt")*D9
 | You may store and retrieve numbers, or text, or both using this technique. |
GETVAL Data Storage Rules
For GETVAL to extract data from a text string, the data must be stored according to the following rules.
| [ ] | The data block must begin and end with square brackets. |
| name=value | Each data item within the block must consist of a data name and a data value, separated by an equal sign. Data names are not case sensitive. so these data names are all equivalent: Wt, wT, wt, WT |
| ; (semicolon) | If the data block contains more than one data item they must be separated by a semicolon character. |
| spaces | Extra spaces are between data items and delimiters are ignored. Text data items are not required to have surrounding quotation marks except when they contain spaces which you want to preserve. |
| block location | FormCalc SST searches for the data block from the right-hand end of the source text string. So square brackets may appear elsewhere in the source text so long as they are to the left of the data block. |
Examples:
[Name=John] ...no quotation marks needed
[Name="John Doe"] ...quotation marks required, because of space
[Wt=44;Len=55.75] ...typical
[ Wt=44; Len=55.75 ] ...extra spaces will be ignored
[Wt=34. 7] ...error! (space within a data value)