Skip to content

Custom Function for Filemaker: Find Matches in 2 value lists

In a Filemaker database, I had to come up with a quick way to find out which values in array myitems also exist in array list1 and array list2. For this, I created the following function:

_FindMatchesInTwoValueLists(list1,list2,start,myitems)

This is the function – as you can see, it’s recursive. The variable “start” is usually 0 when the function is initially called. start will then be incremented until the end of the array is reached. In this example, array list2 should be the larger array – else you will have to adjust the function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

If (
  start > ValueCount (list2) ; myitems ;
   Let( [
      needle = LeftValues ( list1 ; 1) ;
      reallist2 = "|" & Substitute( list2 ; "¶" ; "||") & "|";
      myitems = Case ( 
              PatternCount(reallist2 ; "|" & Substitute( needle ; "¶" ; "") & "|") ≥ 1 ; 
                myitems & needle;
                   PatternCount(reallist2 ; "|" & Substitute( needle ; "¶" ; "") & "|") = 0 ; 
                     myitems)
          ];
	Case (
	   Length ( reallist2 )  > 1 ; _FindMatchesInTwoValueLists ( RightValues ( list1 ;  ValueCount( list1 ) - 1 ) ; list2 ; start + 1 ; myitems )
             )
        )
)

and the settings in the custom function editor:

filemaker-custom-function

Post a Comment

You must be logged in to post a comment.