February FileMaker Functions - GetValue
The GetValue function returns a specified value from a list of values. In FileMaker Pro, a value list is a return separated list of text. Each value may be a single word or multiple words.
Value lists exist in a lot of areas in FileMaker Pro. One of these is the contents of a field where values are selected from checkboxes. Each value checked is entered into the field in order as a new return separated value. So if you are checking off features of a property and select Pool, Carport and then Games Room, the field will contain a value list with three values:
Pool¶
Carport¶
Games Room
The List function also returns a value list. This may be a list of values from a related field. An example is a list of subjects for which a student has enrolments. Or a list of ingredients that a product contains.
Value lists are also returned by a lot of the Design functions. For example, LayoutNames( Get(FileName)) returns a list of all the names of the layouts in the current file. The GetValue function can be used to extract a layout name from the list.
-
GetValue( listOfValues; valueNumber )
- Input: text (listOfValues), number (valueNumber)
- Output: text
There are a family of functions that get values from a list. The others are LeftValues, MiddleValues and RightValues. While GetValue returns a single value only, the others can return one or more values but always with a trailing carriage return. This is important to know when dealing with the result of any of these functions.
Examples of Use
1. Looping through a list processing each value in turn
Let's say we want to process items in a value list and only keep those that match certain criteria. In this example, we have a product record and a related list of ingredients. Each ingredient record has a name and a field that lists the food additive number if it applies (if not, the field contains N for natural). We would like to return a list of all the food additives in a product. A sample record is:
PRODUCT::ProductName = TimTam
Related records - INGREDIENT::ingredient, INGREDIENT::additive
Sugar, N
Wheat Flour, N
Vegetable Oil, N
Milk Solids, N
Colour, E102
Colour, E110
Cocoa, N
Emulsifier, E322
So our script to process the current product record could be something like:
Set Variable [ $additive_list; Value:List (INGREDIENT::additive) ]
Set Variable [ $$list; Value:"" ]
Set Variable [ $counter; Value:1 ]
Loop
If [ GetValue ( $additive_list ; $counter ) ≠ "N" ]
Set Variable [ $$list; Value:List ($$list; GetValue ($additive_list ; $counter)) ]
Exit Loop If [ Let ($counter = $counter + 1; $counter > ValueCount ($additive_list)) ]
End If
End Loop
For the above sample record, this will return a list in the $$list variable that is:
E102
E110
E322
2. Performing recursion in a custom function
Custom functions allow recursion - where a function calls itself to work through a series. The GetValue function can be used where the series is a value list and each value is processed in turn.
So let's say we have a list of addresses and we want to return a list of the last word in each address. The list looks like this:
14 Trafalgar Street, Annandale, NSW
15 View Street, Bayview, NT
16 Green Street, Annerley, QLD
We will be extracting the last word of each address - the state abbreviation.
Our custom function, ListLastWord ( thelist ), is:
If ( ValueCount ( thelist ) > 0;
RightWords ( GetValue ( thelist ; 1 ); 1 ) & ¶ &
ListLastWord ( MiddleValues ( thelist ; 2 ; ValueCount ( thelist ) - 1 ) )
)
What this does is to extract the last word of the first value (using GetValue) and keep doing that with a reducing list (removing the first value each time) until there are no values left to process.
3. Retain the last value selected from checkboxes
There are times when you want a checkbox list to behave more like a radio button list - to allow for the selection of one and only one choice. This adds flexibility in being able to uncheck that choice if required (not intuitive with a radio button).
We can do this with a field option for an auto-entered calculation that updates the field. The calculation will be:
GetValue ( Self ; ValueCount ( Self ) )
This calculation will get the last value entered in the field. The Self function is being used to refer to the current field where the option is set. ValueCount (Self) returns the number of the last value.
