Database Reference
In-Depth Information
function like this, though, you're in trouble. If
RemoveDuplicates()
calls
RemoveDuplic-
ates()
, which in turn calls
RemoveDuplicates()
(ad infinitum), then you have a problem:
This function just keeps going forever.
WARNING
When you work on recursive functions, you inevitably create such
loops
accidentally. When you do,
FileMaker thinks for several seconds and then gives up and returns
invalid
(a question mark in the
field). If FileMaker seems to be hung, wait a few seconds; recursion is limited to 10,000 cycles, and
FileMaker gives up eventually.
To avoid ending up in a loop, you need to figure out when to
stop
calling
RemoveDuplic-
ates()
. Think about what happens after this function calls itself several times. Each time it's
called with a slightly smaller list than the time before (because the first item—along with any
copies of it—have been removed). Eventually it's going to get called with just one item (or
sometimes zero items). When that happens, you no longer need the services of
Re-
moveDuplicates()
. Instead, you can just return that last word by itself since it obviously
has no duplicates. You use an
If()
function to help the recursion figure out when to stop.
Make sure your list has a blank line at the end. See the box below for the reason why. The fi-
nal function looks like this (with comments added):
// Start the result with the first item in the list
LeftValues ( values ; 1 ) &
// If there are items remaining in list...
If ( ValueCount ( values ) > 0;
// ...then remove duplicates from the remaining items
RemoveDuplicates ( Substitute ( values ; LeftValues ( values ; 1); "") );
// ...otherwise we're done
""
)
Now you just have to create the
RemoveDuplicates()
custom function.
RemoveDuplic-
ates()
needs one parameter, which is the values from which you're sifting duplicates. A de-
scriptive name, like “theList” (you can't use “list” because there's already a function with
that name), helps you remember what this parameter does. Finally, create a calculation field
using your new custom function, and create a reference to the field containing the list of du-
plicated values.
