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 - Show more from this author
uLearnIT http://www.ulearnit.com.au/ |
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, Sydney, Australia Mar 8, 2009 |
|
| Works exactly as expected and was extremely easy to implement. Thanks. | ||
 |
Bruce Robertson Mar 9, 2009 |
|
| How about: Let ( nope = IsEmpty ( FilterValues ( theList ; value ) ) ; If ( nope ; List ( theList; value ); Substitute( theList & ¶; value & ¶; "" ) ) ) |
||
|
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, 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, 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, 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 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 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 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, 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, 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, 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). |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.