Brian Dunning's FileMaker Custom Functions

AddRemoveListItem ( theList ; value )

Add a value to or remove an values from a list of values

  Average rating: 4.2 (41 votes) Log in to vote

David Head   David Head
uLearnIT
http://www.ulearnit.com.au/

Share on Facebook Share on Twitter

  Sample input:
AddRemoveListItem ( "apple¶banana¶cherry" ; "banana" )
  Sample output:
"apple¶cherry"

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

This function will add an item to a list if it does not already exist; otherwise the item is removed from the list.

Update: the listminusitem was adjusted to account for instances where the value being removed is also the end of any value of the list. This now works by wrapping the list in double ¶, substituting out the value, and finally substituting out the double ¶ at the end. Also reversed the logic of the item test (novalue). Also now consistent about referring to "value" instead of "item" in all variables.

Further update: added substitution of three ¶ to ensure that when the very last value is removed, the list is null. Thanks to Andrew McCallum.

 

Comments

Andrew McCallum   Andrew McCallum, Sydney, Australia
Mar 8, 2009
Works exactly as expected and was extremely easy to implement. Thanks.
 
Bruce Robertson   Bruce Robertson
Mar 9, 2009
How about:

Let (
nope = IsEmpty ( FilterValues ( theList ; value ) ) ;

If ( nope ;
List ( theList; value );
Substitute( theList & ¶; value & ¶; "" )
)
)
 
Bernard Cheang   Bernard Cheang, Singapore
Mar 9, 2009
This function is does not work 100%. If you had "apple banana pineapple" and the item is "apple", you would end up with "banana pin".
 
David Head   David Head, uLearnIT
Mar 10, 2009
Hi Bruce
I had something like that early in testing. I tested yours but it leaves a trailing return every time you remove an item. That was the purpose of the second listminusitem setting. ;)

Hi Bernard
Good catch! I have updated the CF to trap for cases where the value being removed is the end of the last value. I have used <EOL> to signify end of list. If you were happy to limit the use of the CF to version 10+ then you could use some formal ASCII character with the Char function. But I think this should be sufficient for most data. Thanks for the feedback.
 
David Head   David Head, uLearnIT
Mar 10, 2009
Hmmm, further testing revealed the problem was not just if the value being tested was the end of the last value - it occurred if it was the end of ANY value.

I have updated the listminusitem variable expression to fix this issue. Seems to work consistently now.

Note that this CF does not return any trailing returns in the result.
 
Bernard Cheang   Bernard Cheang, Singapore
Mar 10, 2009
Good job, David. I was looking for a similar function. Didn't have time to fix it myself. Thanks.
 
Bruce Robertson   Bruce Robertson
Jun 17, 2010
This function still has a big problem if there are empty lines in the original:

AddRemoveListItem( "a¶¶7¶6¶¶B"; "a")
Result:
7
6B
 
Bruce Robertson   Bruce Robertson
Jun 17, 2010
This seems to solve the problem where empty lines in the original generate errors. Remove the empty lines first.

Let ([
novalue = IsEmpty ( FilterValues ( theList ; value ) );
newList = Substitute( theList; ["¶¶¶"; "¶"] ; ["¶¶"; "¶"] ) ;
listminusvalue = Substitute( "¶¶" & newList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
listplusvalue = List ( theList; value )
];
If ( novalue ; listplusvalue ; listminusvalue )
)
 
Bruce Robertson   Bruce Robertson
Jun 17, 2010
Above has some errors also when adding to the list. I think this does it.

Let ([
value= GetValue( value; 1);
novalue = IsEmpty ( FilterValues ( theList ; value ) );
newList = Substitute( theList; ["¶¶¶"; "¶"] ; ["¶¶"; "¶"] ) ;
listminusvalue = Substitute( "¶¶" & newList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
listplusvalue = Substitute( List ( newList ; value ); ["¶¶"; "¶"] )
];
If ( novalue ; listplusvalue ; listminusvalue )
)
 
Benoit Jolly Active Développement   Benoit Jolly Active Développement, Lyon France
Jun 7, 2013
This function does not work if you have a repeated value to be removed :
Example :

theList ="a¶b¶b¶b¶c";
value="b";

--> returns "a¶b¶c"

I suggest you use the Extraordinary function by Agnes Barouh : Filter list (http://www.briandunning.com/cf/771)

Or the following method :

// by Benoit Jolly - Active Développement
//
// RemoveValueFromList(listOfValues;value)
//
// Date : 07/06/2013
// Removes a value from a list EVEN if it is repeated multiple times
// No recursion is used
//
// The method is based on the method proposed by Debi Fuchs of Aptworks Consulting on http://www.briandunning.com/cf/897
//
//
// RemoveValueFromList("a¶b¶b¶b¶b¶b¶b¶b¶b¶c¶","b") => "a¶c¶"


Let([
s = "######*q12sd6fa6z5fe16a5z1ef6a51zv65a1zv651az6e51fra6z5e16a1faz*#######" //very unlikely string
];
If(

//Check if unlikely string rears its head
Position( listOfValues; s; 0; 1 );

//If so, simulate an out-of-range error.
Evaluate( "Factorial( -1 )" );

// Otherwise, proceed to get rid of nulls
Substitute(
s & "¶" & listOfValues & "¶" & s;
[ "¶"& value & "¶" ; ¶ & s & ¶ ];
[ "¶"& value & "¶"; ¶ & s & ¶ ];
[ s & ¶; "" ];
[ ¶ & s; "" ];
[ s; "" ]
)
)
)
 
Marty Enerson   Marty Enerson, Minneapolis/MN
Aug 9, 2016
Is there a way to keep a ¶ at the end of the list? I have no way of pulling individual rows from the list on the last row.

Currently, I am finding the ¶ at the end of each line, but the last line doesn't have a ¶ so I can't grab it.

this is what I am using to pull out individual lines

Left ( field ; Position ( field ; ¶ ; 1 ;1) )
 
Debi Fuchs   Debi Fuchs, Aptworks Consulting
Aug 23, 2018
I get odd results when I run this on repeating values, e.g. AddRemoveListItem ("¶9¶2¶4¶8¶8¶¶7¶¶8¶9¶¶"; "8") gives me "9¶2¶4¶879¶" so I posted a function RemoveValue at https://www.briandunning.com/cf/2168

"RemoveValue" produces the same results, I think, as the "RemoveValueFromList" function Benoit Jolly suggested in the comments above -- as both are offshoots of CullNulls (https://www.briandunning.com/cf/897).
 

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.