3 Replies Latest reply: May 15, 2014 9:58 AM by Lynette _ RSS

    Operator "in."

    Lynette _

      Hi,

      Am using Pro5 and am trying to filter data using the operator "in" and am unsuccessful...

       

      e.g.

      I am trying to extract a list of companies having

      the "Lever" as part of their name.

      Using Company .in.("lever*) extracts all details as long as the name of the company starts with "Lever", but does not extract "Hindustan Lever" or "Unilever".

      If I use Company .in.("Lever") all records even companies other than from the Lever group are part of the filter...

       

      Am obviously doing something incorrectly...so please assist.

        • Operator "in."
          Tom Whiteside

          Hi, Lynette!

           

          Try:

           

          Company.In.("lever").Or.Company.In.("lever")

           

          (This covers Company beginning or ending with "lever.")

           

          If "lever" can be in ANY part of your company name, then try:

           

          If(Instr("lever", Company)>0, Company, "")

           

          (A non-zero value for the inequality means that the string "lever" has a starting position within the field Company - - and returns the Company name.)

           

          Let us know.

          • Operator "in."
            Grant Perkins

            Lynette,

             

            Tom's suggestion of the Instr function looks like the way to go with this one.

             

            However, if you were to identify that you had other undesired names with 'lever' somewhere in them appearing in the list, then the .IN. function might be preferable, though there may still be other ways to exclude the undesired names using and .AND. function with the Instr function (in 'not equal to' <name> mode). It all depends on the number of exceptions to be rejected!

             

            If I wanted to select ONLY 3 specific instances from, say, 10 possibles, then .IN. would probably be the better way.

             

            Company.In.("unilever", "hindustan lever", "lever brothers")

             

            for example to explicitly select the records.

             

            It may not be as elegant in some ways BUT it would ensure control over the selection which may not be quite the same by the Instr method. For example, if another name is added which has nothing to do with Lever Bros at all but still includes the string 'lever', it would pass the Instr formula test and may pass any exception 'instr' test meant to exclude unwanted records. Unless it was a exact duplicate (and that would be a very different problem to deal with!) it would not pass the .IN. selection test.

             

            The negative side of .IN. is that it IS PRECISE and so is less tolerant of data errors (typos). So if you are using it with a controlled field (e.g.  Customer Name derived from a master record not a keyboard entry) it SHOULD be quite safe. Unless someone changes the master record!

             

            If the field derives from keyboard entry the results may not be so complete. But that comment can apply to any selection method to varying degrees. The .IN. function would fail for a typo anywhere in the string. The Instr function, as described above, is seeking a match in a smaller part of the string and so is less error prone but not assuredly error free.

             

            I hope this adds some ideas that you can consider for this requirement.

             

            Grant

             

             

              Originally posted by Tom Whiteside:

            Hi, Lynette!

             

            Try:

             

            Company.In.("lever").Or.Company.In.("lever")

             

            (This covers Company beginning or ending with "lever.")

             

            If "lever" can be in ANY part of your company name, then try:

             

            If(Instr("lever", Company)>0, Company, "")

             

            (A non-zero value for the inequality means that the string "lever" has a starting position within the field Company - - and returns the Company name.)

             

            Let us know. /b[/quote]

             

            [size="1"][ June 14, 2003, 04:59 PM: Message edited by: Grant Perkins ][/size]

            • Operator "in."
              Lynette _

              Thanks Tom and Grant,

              The "Instr" function was the solution...

              Regards,