Brian Dunning's FileMaker Custom Functions

RemoveValue ( Value ; ValueList )

A recursive function that removes all matching values from the supplied list

  Average rating: 3.8 (44 votes) Log in to vote

Caleb Ruth   Caleb Ruth
Data Performance
http://www.datap.co

Share on Facebook Share on Twitter

  Sample input:
RemoveValue ( "rabbit" ; "mouse¶jackrabbit¶rabbit¶lynx" )
  Sample output:
mouse
jackrabbit
lynx

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

This function removes all matching values from a list. Unlike other functions it does not require any padding nor adds nor removes any extra paragraph chars to the resulting list (save one at the end). Recursion limits apply to the size of the list.

 

Comments

F. Osman Cabi   F. Osman Cabi, Turkey
Jun 9, 2010
There's no need for recursion for this function. This does it in a single line of code:

Substitute ( "mouse¶rabbit¶lynx¶snake" ; ["rabbit";""] ; ["¶¶";"¶"] )
 
Cynthia   Cynthia, USA
Jun 16, 2010
I like the non-recursive one line solution suggested in the comment above, but it may remove parts of a value, not the whole value.

Example: Substitute ( "mouse¶rabbit¶lynx¶snake¶jackrabbit" ; ["rabbit";""] ; ["¶¶";"¶"] ) results in
"mouse¶lynx¶snake¶jack"

Adding the usual "¶" at the beginning and end of the ValueList and at the beginning and end of Value helps, but there may still be leftover returns depending on what the original list looked like. Rather than reinventing the wheel for dealing with extraneous "¶", I used the excellent CullNulls CF to do the cleanup and ended up with the following:

CullNulls( Substitute ( ¶ & ValueList & ¶ ; ¶ & Value & ¶ ; ¶ ) )

Note that unlike the original function presented, using CullNulls will remove leading/trailing returns originally in ValueList, so if retaining leading/trailing returns is important, additional logic is needed.
 
Carter Brooks   Carter Brooks, San Francisco
Jan 16, 2012
Here's an edit to the code that removes the trailing return by using the List function instead of an '& "¶"' Also converted If statements to Case statements for brevity.


/*
RemoveValue custom function by Caleb Ruth, Data Performance LLC
Edited by Carter Brooks, developer.carterbrooks.com. Converted to use List function to remove trailing return

parameters:
Value
ValueList
*/


Let ( [
the_value_count = ValueCount ( ValueList ) ;
the_existing_value = GetValue ( ValueList ; 1 )
]
;

Case (
the_value_count > 0 ;

List(
Case (
not Exact ( Value ; the_existing_value ) ;
the_existing_value
)
;
RemoveValue ( Value ; RightValues ( ValueList ; the_value_count - 1 ) )
)
)

) //let
 
Daniel A. Shockley   Daniel A. Shockley, New York, NY
Jan 27, 2012
Here's a succinct non-rescursive function that neither adds nor removes leading or trailing line returns, only removes entire values (no partial line issues). It adds its own line returns to make the substitute work, and so knows it can remove them before returning the new list:


// ValueRemove ( valueList; itemValue )
// version 1.0, Daniel A. Shockley

Let(
cleanedListPadded = Substitute( "¶" & valueList & "¶"; "¶" & itemValue & "¶"; "¶")
; Middle( cleanedListPadded; 2; Length( cleanedListPadded ) - 2 )
)
 
Justin Pakes   Justin Pakes, Virginia, USA
Jul 17, 2012
I liked Daniel's version, but found it still fails if valueList starts with itemValue twice
E.g.
ValueRemove ( "a¶a¶b¶c¶a" ; "a" )
returns
"a¶b¶c"

It then fails to catch one of those two.
 
eeGuler   eeGuler, Istanbul
Jan 6, 2013
I guess this one is better.

// RemoveValue ( valueList ; theValue )
// version 1.0, eeGuler

Let ( result = Substitute ( Let ( firstStep = Substitute ( valueList ; ¶ & theValue ; "" ) ; Substitute ( firstStep ; theValue & ¶ ; "" ) ) ; theValue ; "" ) ; result )
 
rob 'jesus Land Tidd' lewis   rob 'jesus Land Tidd' lewis, wawaka Indiana USA
Feb 7, 2013
yes !! but how do you remove values such as 2-5 or 1-7 or 5-10 ?
 
Jack James   Jack James, London
Apr 22, 2013
How about just:

Let (
a = Substitute ( valueList & ¶ ; theValue & ¶ ; "" ) ;
Left ( a ; Length ( a - 1 ) )
)
 
Jack James   Jack James, London
Apr 22, 2013
Sorry put a bracket in the wrong place, should be:

Let (
a = Substitute ( valueList & ¶ ; theValue & ¶ ; "" ) ;
Left ( a ; Length ( a ) - 1 )
)
 
Mattias Thorslund   Mattias Thorslund, Sweden
Sep 8, 2016
custom function: RemoveValue
parameters: values, value

Let(
text = Substitute ( ¶ & values & ¶ ; ¶ & value & ¶ ; ¶ );
Middle ( text ; 2 ; Length( text )-2 )
)
 
RealGrouchy   RealGrouchy, Ottawa
Nov 7, 2016
I didn't see the hidden comments on this page with additional suggestions until after I wrote up this variant.

This one is ExclueValue*s* because you can specify multiple items to be excluded. I needed this in order to compare two checklist sets against each other to find which one had been added/removed. It also has checks the parameters for trailing line breaks.

- RG>

// Custom function: ExcludeValues ( ValueList ; ValuesToExclude )
// 2016-11-07 - RG>
// Based on discussions at https://www.briandunning.com/filemaker-custom-functions/detail.php?fn_id=1175&comments=all
// e.g. ExcludeValues ( "a¶b¶c¶c¶d" ; "a¶c" ) will return "b¶d"

Let (
[
ValuesToExclude = ValuesToExclude & If ( Right ( ValuesToExclude ; 1 ) = ¶ ; "" ; ¶ ) ;
ValueListPlusBreak = ValueList & If ( Right ( ValueList ; 1 ) = ¶ ; "" ; ¶ ) ;
FirstValue = Left ( ValuesToExclude ; Position ( ValuesToExclude ; ¶ ; 1 ; 1 ) ) ;
RemainingValuesPlusBreak = Right ( ValuesToExclude ; Length ( ValuesToExclude ) - Length ( FirstValue ) ) ;
RemainingValues = Left ( RemainingValuesPlusBreak ; Length ( RemainingValuesPlusBreak ) - If ( Right ( RemainingValuesPlusBreak ; 1 ) = ¶ ; 1 ; 0 ) ) ;
FilterOutFirstValue = Substitute ( ValueListPlusBreak ; FirstValue ; "" ) ;
Result = Left ( FilterOutFirstValue ; Length ( FilterOutFirstValue ) - If ( Right ( FilterOutFirstValue ; 1 ) = ¶ ; 1 ; 0 ) )
]
;

If ( Length (RemainingValues ) = 0 ; Result ; ExcludeValues ( Result ; RemainingValues ) )

) //let
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.