1 Reply Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    Best way to filter out rows in table based on list of numbers

    MonUserCJ _

      Hey all,

       

      I have a table that contains contact information and a list of IDs. I have a second list of IDs that I want to use to filter out rows from the table whose IDs are contained in the second list. So, for example, if the list has ID 2 listed, I would want the row in the table with ID 2 filtered out. This is a task I find myself needing to do somewhat often.

       

      What is the most straightforward way of doing this? I have tried defining it a filter with the "Not In" operator, but there is a limit to the number of values that can be placed in the filter definition box.

       

      The other alternative I've found is to create a two-column table, with the IDs in one column and a placeholder value in the other (maybe an empty string, or a letter, for example). Then, use the external lookup to match on the ID column, and import the placeholder column. This works decently, I suppose, but it requires that I create a placeholder column. Also, sometimes I have problems with external lookups.

       

      Is there a more straightforward way to do this in Monarch? I'd appreciate any advice anyone can give.

       

      Thanks.

        • Best way to filter out rows in table based on list of numbers
          Olly Bond

          Hello MonUserCJ,

           

          Both those approaches should work. Which one suits you best will depend on how often the list changes, and how automated the process should be, as well as the size of the list. An alternative to NotIn is to create a calculated field of Memo type, paste the list of IDs into this field, called , say, and then filter it with:

           

          Instr(;[List])>0

           

          A memo field can hold 32k of data, which should be enough. To prevent false matches, it's useful to put a separator between the values in the Memo field, using any character that doesn't appear in the ID field. # or / usually do the trick.

           

          Best wishes,

           

          Olly