xPFilter

To give portal filtering on up to four words - in any order and to exclude records containing search words commencing - and to inc those commencing + and the OR search

Be the first to rate this function Log in to vote

Nick Lightbody - Show more from this author
Deskspace Systems Limited
http://deskspace.com

Sample input:

xPFilter ( ; )

Sample output:

[a binary flag - ie yes / no for each record to control which records are displayed in the portal]

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

To filter records for up to four words in any order or position within a record field

(NB: the spacing for this content is correct when viewed in an monospaced type face in a text editor)

xPFilter (Fn2search;Fnsearch)

The master function calls the appropriate sub function depending on how many words are input - hence there are 4 sub functions below - just to avoid doing more processing than necessary. This is called in the portal filter.
Although it is much slower than a scripted find it enables users to find records they otherwise miss with a normal string search - it is very popular with users.
I published a 3 word version on the FM Community site.

The input is the fieldname to search and name of the global field used for search in the Ui. The fieldname to search in this system is the autoentered summary field containing all the data on which we want to search.

/*
xPFilter v3
Nick Lightbody
December 2012 & October 2014
To give filtering on up to four words - in any order
and to exclude records containing search words commencing -
and to inc those commencing +
and the OR search if neither - or + suffices are used
input:
Fn2search
Fnsearch
--------------------------------------------*/

Let([
k=Fn2search; 
x=Substitute( FnSearch ; " "; ¶ );
$n=ValueCount(x)
];

Case ($n=0 ; 1 ;
      $n=1   ; xPFilter1w ( k ; x ) ;
      $n=2 	 ; xPFilter2w ( k ; x );   
      $n=3 	 ; xPFilter3w ( k ; x );
      $n=4  ;  xPFilter4w ( k ; x )
      )
)


xPFilter1w

/*
xPFilter v3
Nick Lightbody
December 2012 & October 2014
To give filtering on up to four words - in any order
and to exclude records containing search words commencing -
and to inc those commencing +
and the OR search
input:
Fn2search
Fnsearch
--------------------------------------------*/

Let([
k=Fn2search; 

// string of up to 4 words - converted to values
x=Substitute( FnSearch ; " "; ¶ );

// symbols used for exc and inc
exc="-";
inc="+";

// word 1
w1=xTrim(LeftValues(x;1));
L1=Length(w1)-1;
x1=Case(Left(w1;1)=exc ;Right(w1;L1);"");
y1=Case(Left(w1;1)=inc ;Right(w1;L1);"");
px1=Case(PatternCount(k;x1)>0;1;0);
pw1=Case(PatternCount(k;w1)>0;1;0);
py1=Case(PatternCount(k;y1)>0;1;0);
//summary output - exc or
p1=Case(px1;0 ;pw1 or py1;1;0);


// controllers
$n=ValueCount(x);
$nx=PatternCount(x;exc);
$ny=PatternCount(x;inc)


];


Case ($n=0 	; 1	
			;
      $n=1 	; Case	($nx=1 and px1; 0
					;$nx=$n; 1
					;$nx=0 and p1;1	
 					;0
					)

        	)

)


xPFilter2w

/*
xPFilter v3
Nick Lightbody
December 2012 & October 2014
To give filtering on up to four words - in any order
and to exclude records containing search words commencing -
and to inc those commencing +
and the OR search
input:
Fn2search
Fnsearch
--------------------------------------------*/

Let([
k=Fn2search; 

// string of up to 4 words - converted to values
x=Substitute( FnSearch ; " "; ¶ );

// symbols used for exc and inc
exc="-";
inc="+";

// word 1
w1=xTrim(LeftValues(x;1));
L1=Length(w1)-1;
x1=Case(Left(w1;1)=exc ;Right(w1;L1);"");
y1=Case(Left(w1;1)=inc ;Right(w1;L1);"");
px1=Case(PatternCount(k;x1)>0;1;0);
pw1=Case(PatternCount(k;w1)>0;1;0);
py1=Case(PatternCount(k;y1)>0;1;0);
//summary output - exc or
p1=Case(px1;0 ;pw1 or py1;1;0);

//word 2
w2=xTrim(MiddleValues(x;2;1));
L2=Length(w2)-1;
x2=Case(Left(w2;1)=exc ;Right(w2;L2);"");
y2=Case(Left(w2;1)=inc ;Right(w2;L2);"");
px2=Case(PatternCount(k;x2)>0;1;0);
pw2=Case(PatternCount(k;w2)>0;1;0);
py2=Case(PatternCount(k;y2)>0;1;0);
//summary output - exc o
p2=Case(px2;0 ;pw2 or py2;1;0);


// controllers
$n=ValueCount(x);
$nx=PatternCount(x;exc);
$ny=PatternCount(x;inc);

// No of includes in string
$ny12=Case($ny=1 or $ny=2;1;0);

// No of excludes in string
$nx12=Case($nx=1 or $nx=2;1;0);

// No of matching excludes
$px12=Case(px1 or px2;1;0)
];


Case ($n=0 	; 1	
			;
      $n=1 	; Case	($nx=1 and px1; 0
					;$nx=$n; 1
					;$nx=0 and p1;1	
 					;0
					)
			;
      $n=2 	; Case	($nx12 and $px12; 0	
                  	;$nx=$n; 1	
                  	;$nx=1; p1
                  	;$ny12 and p1 and py2;1
                  	;$ny12;0
                  	;pw1 or pw2;1
                  	;0                      
                 	)
        	)

)

xPFilter3w

/*
xPFilter v3
Nick Lightbody
December 2012 & October 2014
To give filtering on up to four words - in any order
and to exclude records containing search words commencing -
and to inc those commencing +
and the OR search
input:
Fn2search
Fnsearch
--------------------------------------------*/

Let([
k=Fn2search; 

// string of up to 4 words - converted to values
x=Substitute( FnSearch ; " "; ¶ );

// symbols used for exc and inc
exc="-";
inc="+";

// word 1
w1=xTrim(LeftValues(x;1));
L1=Length(w1)-1;
x1=Case(Left(w1;1)=exc ;Right(w1;L1);"");
y1=Case(Left(w1;1)=inc ;Right(w1;L1);"");
px1=Case(PatternCount(k;x1)>0;1;0);
pw1=Case(PatternCount(k;w1)>0;1;0);
py1=Case(PatternCount(k;y1)>0;1;0);
//summary output - exc or
p1=Case(px1;0 ;pw1 or py1;1;0);

//word 2
w2=xTrim(MiddleValues(x;2;1));
L2=Length(w2)-1;
x2=Case(Left(w2;1)=exc ;Right(w2;L2);"");
y2=Case(Left(w2;1)=inc ;Right(w2;L2);"");
px2=Case(PatternCount(k;x2)>0;1;0);
pw2=Case(PatternCount(k;w2)>0;1;0);
py2=Case(PatternCount(k;y2)>0;1;0);
//summary output - exc o
p2=Case(px2;0 ;pw2 or py2;1;0);

//word 3
w3=xTrim(MiddleValues(x;3;1));
L3=Length(w3)-1;
x3=Case(Left(w3;1)=exc ;Right(w3;L3);"");
y3=Case(Left(w3;1)=inc  ;Right(w3;L3);"");
px3=Case(PatternCount(k;x3)>0;1;0);
pw3=Case(PatternCount(k;w3)>0;1;0);
py3=Case(PatternCount(k;y3)>0;1;0);
//summary output - exc o
p3=Case(px3;0 ;pw3 or py3;1;0);

// controllers
$n=ValueCount(x);
$nx=PatternCount(x;exc);
$ny=PatternCount(x;inc);

// No of includes in string
$ny12=Case($ny=1 or $ny=2;1;0);
$ny123=Case($ny=1 or $ny=2 or $ny=3;1;0);

// No of excludes in string
$nx12=Case($nx=1 or $nx=2;1;0);
$nx123=Case($nx=1 or $nx=2 or $nx=3;1;0);

// No of matching excludes
$px12=Case(px1 or px2;1;0);
$px123=Case(px1 or px2 or px3;1;0)
];


Case ($n=0 	; 1	
			;
      $n=1 	; Case	($nx=1 and px1; 0
					;$nx=$n; 1
					;$nx=0 and p1;1	
 					;0
					)
			;
      $n=2 	; Case	($nx12 and $px12; 0	
                  	;$nx=$n; 1	
                  	;$nx=1; p1
                  	;$ny12 and p1 and py2;1
                  	;$ny12;0
                  	;pw1 or pw2;1
                  	;0                      
                 	)
           	;
      $n=3 	; Case($nx123 and $px123; 0
                  	;$nx=$n;1
                  	;$nx=2; p1
                  	;$nx=1 and $ny12 and p1 and py2;1
                  	;$nx=1 and $ny12;0
                  	;$ny123 and p1 and py2 and py3;1
                  	;$ny123;0
                  	;pw1 or pw2 or pw3;1 
                  	;0
                 	)
        	)

)

xPFilter4w

/*
xPFilter v3
Nick Lightbody
December 2012 & October 2014
To give filtering on up to four words - in any order
and to exclude records containing search words commencing -
and to inc those commencing +
and the OR search
input:
Fn2search
Fnsearch
--------------------------------------------*/

Let([
k=Fn2search; 

// string of up to 4 words - converted to values
x=Substitute( FnSearch ; " "; ¶ );

// symbols used for exc and inc
exc="-";
inc="+";

// word 1
w1=xTrim(LeftValues(x;1));
L1=Length(w1)-1;
x1=Case(Left(w1;1)=exc ;Right(w1;L1);"");
y1=Case(Left(w1;1)=inc ;Right(w1;L1);"");
px1=Case(PatternCount(k;x1)>0;1;0);
pw1=Case(PatternCount(k;w1)>0;1;0);
py1=Case(PatternCount(k;y1)>0;1;0);
//summary output - exc or
p1=Case(px1;0 ;pw1 or py1;1;0);

//word 2
w2=xTrim(MiddleValues(x;2;1));
L2=Length(w2)-1;
x2=Case(Left(w2;1)=exc ;Right(w2;L2);"");
y2=Case(Left(w2;1)=inc ;Right(w2;L2);"");
px2=Case(PatternCount(k;x2)>0;1;0);
pw2=Case(PatternCount(k;w2)>0;1;0);
py2=Case(PatternCount(k;y2)>0;1;0);
//summary output - exc o
p2=Case(px2;0 ;pw2 or py2;1;0);

//word 3
w3=xTrim(MiddleValues(x;3;1));
L3=Length(w3)-1;
x3=Case(Left(w3;1)=exc ;Right(w3;L3);"");
y3=Case(Left(w3;1)=inc  ;Right(w3;L3);"");
px3=Case(PatternCount(k;x3)>0;1;0);
pw3=Case(PatternCount(k;w3)>0;1;0);
py3=Case(PatternCount(k;y3)>0;1;0);
//summary output - exc o
p3=Case(px3;0 ;pw3 or py3;1;0);

//word 4
w4=xTrim(RightValues(x;1));
L4=Length(w4)-1;
x4=Case(Left(w4;1)=exc ;Right(w4;L4);"");
y4=Case(Left(w4;1)=inc ;Right(w4;L4);"");
px4=Case(PatternCount(k;x4)>0;1;0);
pw4=Case(PatternCount(k;w4)>0;1;0);
py4=Case(PatternCount(k;y4)>0;1;0);
//summary output - exc o
p4=Case(px4;0 ;pw4 or py4;1;0);

// controllers
$n=ValueCount(x);
$nx=PatternCount(x;exc);
$ny=PatternCount(x;inc);

// No of includes in string
$ny12=Case($ny=1 or $ny=2;1;0);
$ny123=Case($ny=1 or $ny=2 or $ny=3;1;0);
$ny1234=Case($ny=1 or $ny=2 or $ny=3 or $ny=4;1;0);

// No of excludes in string
$nx12=Case($nx=1 or $nx=2;1;0);
$nx123=Case($nx=1 or $nx=2 or $nx=3;1;0);
$nx1234=Case($nx=1 or $nx=2 or $nx=3 or $nx=4;1;0);

// No of matching excludes
$px12=Case(px1 or px2;1;0);
$px123=Case(px1 or px2 or px3;1;0);
$px1234=Case(px1 or px2 or px3 or px4;1;0)
];


Case ($n=0 	; 1	
			;
      $n=1 	; Case	($nx=1 and px1; 0
					;$nx=$n; 1
					;$nx=0 and p1;1	
 					;0
					)
			;
      $n=2 	; Case	($nx12 and $px12; 0	
                  	;$nx=$n; 1	
                  	;$nx=1; p1
                  	;$ny12 and p1 and py2;1
                  	;$ny12;0
                  	;pw1 or pw2;1
                  	;0                      
                 	)
           	;
      $n=3 	; Case($nx123 and $px123; 0
                  	;$nx=$n;1
                  	;$nx=2; p1
                  	;$nx=1 and $ny12 and p1 and py2;1
                  	;$nx=1 and $ny12;0
                  	;$ny123 and p1 and py2 and py3;1
                  	;$ny123;0
                  	;pw1 or pw2 or pw3;1 
                  	;0
                 	)
           ;
      $n=4 ; Case	($nx1234 and $px1234; 0	
                  	;$nx=$n; 1
                  	;$nx=3; p1	
                  	;$nx=2 and $ny12 and p1 and py2;1
                  	;$nx=2 and $ny12;0
         		  	;$nx=1 and $ny123 and p1 and py2 and py3;1
                  	;$nx=1 and $ny123;0
          		  	;$ny1234 and p1 and py2 and py3 and py4;1
                  	;$ny1234;0
                  	;pw1 or pw2 or pw3 or pw4;1
                  	;0
                 	)
        	)

)

[ends]

To filter records for up to four words in any order or position within a record field

(NB: the spacing for this content is correct when viewed in an monospaced type face in a text editor)

xPFilter (Fn2search;Fnsearch)

The master function calls the appropriate sub function depending on how many words are input - hence there are 4 sub functions below - just to avoid doing more processing than necessary. This is called in the portal filter.
Although it is much slower than a scripted find it enables users to find records they otherwise miss with a normal string search - it is very popular with users.
I published a 3 word version on the FM Community site.

The input is the fieldname to search and name of the global field used for search in the Ui. The fieldname to search in this system is the autoentered summary field containing all the data on which we want to search.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

xPFilter

Comments

Support this website.

Support this website.