FileMaker - Improved PatternCount Searches

In FileMaker, you sometimes need to perform a PatternCount on a list (e.g. to check if a specified value appears in a specified list). The expected method would look something like this:

    PatternCount ( List ( RelatedIDField ) ; RequiredValue )

e.g.

    PatternCount ( List ( cListOfJobIDs ) ; “JOB11333” )

However this will easily fail if you are looking for explicit values since PatternCount only concerns itself with whether a supplied search value exists within another supplied value (e.g. a PatternCount for JOB11 will be found in JOB11333).

To get around this you can alter your code to this:

    PatternCount ( List ( RelatedIDField ) ; RequiredValue & “¶” )

e.g.

    PatternCount ( List ( cListOfJobIDs ) ; “JOB11333¶” )

This adds a terminator so we can include it as part of the search value but will fail if there is only one value in the supplied list, since it won’t end in a carriage return.

A neat solution is to wrap both the list and the search value in carriage returns thus:

    PatternCount ( "¶" & List ( RelatedIDField ) & "¶" ; "¶" & RequiredValue & "¶" )

e.g.

    PatternCount ( "¶" & List ( cListOfJobIDs ) & "¶" ; "¶JOB11333¶" )

Now it no longer matters if there is one or 100 values in the provided list.

If you notice anything wrong with this blog, think that you can do it better or just want to discuss it, then please get in touch.


Blog entry is closed for new comments.