Advanced Calculations - FileMaker Pro 13: The Missing Manual - page 846

Step 1: Solve the First Case
Rather than think about how to solve the entire problem, just figure out how to deal with the
first line in the list. If you have a field called Values, for example, and you want to make sure
the first line appears only once in the list, you can use this calculation ( Figure 16-4 ) :
LeftValues ( values ; 1 ) & Substitute ( values ; LeftValues ( values ; 1 ) ;
"" )
Suppose the Values field contains the following items with a blank line at the end:
Red
Green
Orange
Red
Orange
Yellow
The Substitute() part of this expression does the lion's share of the work, so start with that
to figure out how the formula works. The Substitute() function sees that “Red” is the first
item in the Values field and takes it out of the field everywhere it occurs. If Substitute()
were the whole shooting match, then “Red” would disappear entirely from the Values field.
But the LeftValues(values; 1) piece of the expression also notices that “Red” is the first
item in the Values field, and it puts “Red” back at the top of the list. When both are put to-
gether (using the & sign), the result is the first item in the list, then the rest of the list with
Red removed. Here's the result you see if you make a calculation field with the formula on
the previous page:
Red
Green
Orange
Orange
Yellow