IF this is for a find, wouldn't it be far simpler to use the original list of criteria in Omit requests instead of manipulating a list of value like this?
To use substitute to remove a value from a return separated list of values, I use this expression:
Let ( TheList = Substitute ( ¶ & YourTable::ListField & ¶ ; ¶ & "valueToRemoveHere" & ¶ ; ¶ ) ;
Middle ( TheList ; 2 ; Length ( TheList ) - 2 )
Could you clarify what you mean in your first question, about 'original list of criteria in Omit requests'? Yes, it is for a Find. I have a list of Checked items, but to Omit the other items I need to remove the checked off ones from the full list of values.
I will test your Substitute statement. I found a similar looking one at Brian Dunning's Custom Function repository. It looks like:
novalue = IsEmpty ( FilterValues ( theList ; value ) );
listminusvalue = Substitute( "¶¶" & theList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
listplusvalue = List ( theList; value )
If ( novalue ; listplusvalue ; listminusvalue )
I didn't need the adding-in part, so removed those bits of code.
Mine does the job more simply and doesn't require a custom function to make it work.
Say you have records with the values Apple, Orange, Kiwi, Strawberry in a field named "Fruit".
You have a check box list of these 4 values and you want to find all records that DO NOT have one of the selected values:
To keep the script as simple as possible, I will assume that the check box field has global storage specified.
Enter Find Mode ---> clear the pause check box
Set Variable [$K ; value: 1]
Set Field [Table::Fruit ; GetValue ( Globals::CheckboxField ; $K ) ]
Set Variable [$K ; value: $K + 1]
Exit Loop If [ valueCount ( Globals::CheckboxField ) < $K ]
Set Error capture [on]
In find mode, Omit Records does the same thing as clicking the Omit button when you put together a manual find.
Yeah, I wasn't using it as a custom function, just copying the code. I just wanted to include the whole thing for reference.
Yes, this method would be simpler, but it doesn't quite address the problem. Due to the presence of some other ranges being included in the search (which are built as individual records, not a range operator in a single find record) I decided to go with Omitting the INVERSE of the checked items so that I didn't have to create several hundred records to cover each possible combination of things. Hence I was trying to remove the checked items from thelist of all possible items, thus leaving the inverse of the checked list.
I know, I come up with the weirdest things. :)