リストから検索値を含む行を取得。
Retrieve rows containing the search value from a list.

 FileMaker 18以降かな


2022/07/25 修正

function(関数):
Sample input(入力例):
Sample output(出力例):
formula(式):

Let([ 
	~str = ¶ & textToFilter & ¶ 
	;~val = searchValue
	;~pCount = PatternCount ( ~str ; ~val ) 
	;~p = Position ( ~str  ; ~val ; 1 ; ~pCount ) 
	;~e = Position ( ~str  ; ¶ ; ~p ; 1 ) 
	;~p = Position ( ~str  ; ~val ; 1 ; 1 ) 
	;~s = Position (Left ( ~str ; ~p ) ; ¶ ; ~p ; -1) + 1 
	;~str = Middle ( ~str ; ~s ; ~e - ~s+1 ) 
]; 
While (  
    [  
	~count = ValueCount ( ~str )  
	;~n = 0 
	;~x = 1000 
	;~max = Ceiling ( ~count/~x ) 
	;~result = "" 
    ] ;  
    ~n    <    ~max ;  
    [ 
	~str1 = MiddleValues (  ~str ;  (~x*~n)+1 ; ~x )
	;~str1 = Replace ( ~str1  ; Length ( ~str1 ) ; 1 ; "" )
	;~result = List( ~result ;  
		Let([ 
			_val = ~val
			;_str = ~str1 & If ( PatternCount ( ~str1 ; ¶ ) = 0 ; ¶ )
			;_char182 = "{c;h:a;r:1;8:2}" 
			;_str =Substitute ( _str ; Char ( 182 ) ; _char182 ) 
			;_str = Quote ( _str ) 
			;_str = Middle ( _str ; 2 ; Length ( _str )-2) 
			;_formula =  
			"LET(_val=\"" & _val & "\";" & 
			"¶list(¶LET(_str=\""  
			& 
			Substitute ( _str ; Char ( 182 ) ;  
			"\";if(PatternCount(_str; _val );_str));¶LET(_str=\""  
			) 
			&  
			"\";if(PatternCount(_str ; _val );_str))" 
			& "¶)¶)"
			;_result = Substitute ( Evaluate ( _formula )  ; _char182 ; Char ( 182 )) 
		]; 
			_result
		)

) 
	;~n = ~n+1 
    ] ; 
~result
) 
)