FileMaker Value List Functions

FileMaker provides several value list functions to help manage and manipulate value lists, but I’ve always found their behavior a little odd. Every time you use them, you get a little extra carriage return at the end of the list. I didn’t ask for it, but FileMaker thought I needed it. Fortunately, this is well documented in their functions reference.

Each returned value ends with a carriage return, allowing lists to be easily concatenated.

https://help.claris.com/en/pro-help/content/leftvalues.html

Let’s look at FileMaker’s value list functions, and then determine what this means and how to handle the behavior:

• LeftValues
• RightValues
• MiddleValues
• UniqueValues
• FilterValues
• SortValues

When using any of these functions, the result will have a carriage return after the last value. That allows you to concatenate the results of the function to another list. There are several other value list functions that don’t follow this behavior:

• GetValue
• JSONListValues
• JSONListKeys

These functions will return a value list without the carriage return at the end. The following calculation will return a list with the first three and the last three values from the variable $list. They can be concatenated like this because the functions return an ending carriage return.

LeftValues ( $list; 3 ) & RightValues ( $list; 3 )

However, we already have a function for concatenating values together; List ( ). The following calculation will also work, but doesn’t require the trailing carriage return at the end of the function results:

List (
  LeftValues ( $list; 3 );
  RightValues ( $list; 3 )
)

There are times when the ending carriage return can cause problems, that’s why it’s important to be aware of this behavior and how to account for it when necessary. For example, if you are comparing the 2nd value in one list to the last value in another list:

GetValue ( $list; 2 ) = RightValue ( $list2; 1 )

This will always return False, because the result on the left will not have an ending carriage return, and the result on the right will always have an ending carriage return. We can add a carriage return to the left and compare, or we can remove the carriage return on the right and compare. The first solution is the most straight forward, but there are times where you want to remove the trailing carriage return. For that, here is a calculation to remove the carriage return at the end of the value list.

Let ( ~list = $list;

  Case ( Right ( ~list; 1 ) = Char (13);
    Left ( ~list; Length ( ~list )-1 );
    ~list )
)

Although FileMaker recognizes several line break types for value lists, it only uses the carriage return as the ending line break in the result of its functions. This calculation assumes $list is the result of one of the value list functions above with the ending carriage return. It can also be adapted to other types of line breaks.

Sometimes behavior is good, sometimes behavior is bad, and sometimes behavior is just… different. With the right knowledge and tools, you can be prepared for anything.