AddOrRemoveValue ( listOfValues ; value )

Speedily (and with no recursion/cleanup) remove all instances of a value from a list, or add it, if it's not there.

Average rating: 5.0 (2 votes) Log in to vote

Debi Fuchs - Show more from this author
Aptworks Consulting
http://www.aptworks.com

Sample input:

AddOrRemoveValue("¶9¶2¶4¶8¶8¶¶7¶¶8¶9¶¶"; "8") -> "¶9¶2¶4¶¶7¶¶9¶¶"

Sample output:

"¶9¶2¶4¶¶7¶¶9¶¶"

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

/*
CUSTOM FUNCTION: AddOrRemoveValue(listOfValues; value)
by Debi Fuchs of Aptworks Consulting, debi@aptworks.com

Speedily (and with no recursion or cleanup of empty values) remove all instances of a value from a list, if the value exists in the list already, or add it, if not. (For list cleanup try custom function CullNulls).

e.g. AddOrRemoveValue("¶9¶2¶4¶8¶8¶¶7¶¶8¶9¶¶"; "8") -> "¶9¶2¶4¶¶7¶¶9¶¶"
e.g. AddOrRemoveValue("¶9¶2¶4¶8¶8¶¶7¶¶8¶9¶¶"; "1") -> "¶9¶2¶4¶¶7¶¶9¶¶1"

LAST MODIFIED: 28-AUG-2018 by Debi Fuchs of Aptworks Consulting
*/

Let(

  s = "##*aaa*##"; //very unlikely string

  Case(

    //Check if value itself contains a paragraph return
    Position( value; ¶; 0; 1 );

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

    //add the value if it does not exist in the list
    not(Position( ¶ & listOfValues & ¶; ¶ & value & ¶; 0; 1));
    listOfValues & Case(not(Right(listOfValues;1)=¶); ¶) & value;    

    //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;
      [ "¶"; s & ¶ & s ];
      [ s & value & s & ¶; "" ];
      [ ¶ & s & value & s; "" ];
      [ s & value & s; "" ];
      [ s; "" ]
    )
  )
)

Speedily (and with no recursion or cleanup of empty values) remove all instances of a value from a list, if the value exists in the list already, or add it, if not. (For list cleanup try custom function CullNulls).

Comments

		Debi Fuchs, Aptworks Consulting Aug 28, 2018
fixed a bug which caused the function not to work on single-value lists

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

AddOrRemoveValue ( listOfValues ; value )

Comments

Support this website.

Support this website.