4 Replies Latest reply on Nov 16, 2012 7:51 AM by disabled_JustinClose

    Building an inverse list of items?


      Building an inverse list of items?


           I am trying to find an efficient way of building an inverse list from a list of options from a checklist.  I have a user interface where they are checking off/on various things.  It is part of building a Find.  To prevent various fields from conflicting with each other, or having to build huge lists of possible combinations for a positive-Find, I decided to construct things using a negative-Find (i.e. Omit records that were NOT checked).

           I have currently been doing a three step process: 

           * collect the list of things that were Checked.  (Simple enough:  the checked items are in a global field.)

           * create a Master list of all possible values (not too difficult:  using ValueListItems(file; listname))

           * Then remove items from the Master that are in the Checked list.  This is where it seems to be getting trickier.  I am currently using a Substitute () command, replacing the Checked string with an empty string. 

           The substitute() is mostly working, but I tend to end up with a variety of gaps in my list at times, because of CRs being at the end of a string or not. 

           So, I was wondering if someone had a better way of building this Inverse array?  An SQL statement perhaps, where I can exclude a multi-value list?  Some better way of removing items from the multi-value list?




        • 1. Re: Building an inverse list of items?

               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 )

          • 2. Re: Building an inverse list of items?

                 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:

                 Let ([
                 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.

                 -- J

            • 3. Re: Building an inverse list of items?

                   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]
                      Omit Records
                      Exit Loop If [ valueCount ( Globals::CheckboxField ) < $K ]
                      New Record/Request
                   End Loop
                   Set Error capture [on]
                   Perform Find[]

                   In find mode, Omit Records does the same thing as clicking the Omit button when you put together a manual find.

              • 4. Re: Building an inverse list of items?

                     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.  :)

                     -- J