csv.getrows.if ( array ; matchColumn ; matchValue ; delmiter )

Return entire rows where value of matchColumn = matchValue

Average rating: 4.5 (34 votes) Log in to vote

Bruce Robertson - Show more from this author

Sample input:

csv.getrows.if( "a|b|c¶a|x|23¶b|2|4"; 1; "a"; "|")

Sample output:

a|b|c
a|x|23

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

/*
csv.getrows.if( array; matchColumn; matchValue; delmiter)

Based on csv.getValue concept by Nicolas Bourré
Return rows whose value in column matchColumn = matchValue
Bruce Robertson, Concise Design
v 1.0 1/25/2011

v 1.02 1/27/2011
Added option to handle matchValue as list

*/

Case( 
IsEmpty( array); "";
IsEmpty( matchValue); "";
matchValue = "*"; array;
 
ValueCount( matchValue) >1; 
Let([ 
mc = ValueCount( matchValue);
mv1 = GetValue( matchValue; 1);
mv2 = RightValues( matchValue ; mc-1 )];
List( 
csv.getrows.if( array; matchColumn; mv1; delimiter ) ;
csv.getrows.if( array; matchColumn; mv2; delimiter ) 
));

Let (
[
   vc= ValueCount( array); 
   r = GetValue (array; 1);
   more = RightValues( array; vc-1);
   matchValue = GetValue( matchValue; 1);
   rowAsList = Substitute( r; delimiter; ¶);
   thisValue = GetValue( rowAsList; matchColumn) ;
   isMatch = Case( 
   thisValue = matchValue ;1;
   Left(matchValue; 1) = "!"; thisValue <> Right(matchValue; Length(matchValue)-1);
   )

];


List(
GetValue( r; isMatch) ; 
csv.getrows.if( more; matchColumn; matchValue; delimiter ) 
)
))

Similar to the csv match functions. Takes delimited data and returns entire rows where match column holds matchvalue, again with option to get all or get rows not matching. Performance seems to be OK for row counts in the low thousands.
v 1.02 1/27/2011
Added option to handle matchValue as list

Comments

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

csv.getrows.if ( array ; matchColumn ; matchValue ; delmiter )

Comments

Support this website.

Support this website.