4 Replies Latest reply: May 15, 2014 9:56 AM by Pat Donnelly RSS

    Using Calulated Field with .in. expresion

    Pat Donnelly

      I am trying to create a Calculated Field to use in an expression using the .in. Operator in a filter.

       

      Calculated Field name is Plant_Code_IN

       

      Style:     Runtime Parameter

      Value:     12,15

      Description:     Enter Plants like (11,12,56)

       

      I believe the Calculated Field MUST be of the type Character as 12,15 is not a valid numeral (US of course)

       

      The rub is if I create a filter like this:

       

      .In.()

       

      It only works when Plant_Code_IN has only one value as in 12.

       

      I have created a filter like these and they work correctly.

       

      .In.(12,15)

      .In.("12","15")

       

       

      I have tried all the variations I can think of on both sides of the .in. Operator.  I cannot get it to work with Plant_Code_IN having more than one value.

       

      I am convinced that filter is going to interpret Calculated Filed as a string and not a set of values.

       

      Can anyone get this to work???

       

      Thanks,

       

      Pat Donnelly

        • Using Calulated Field with .in. expresion
          Pat Donnelly

          By the way I am using Version 7.0 Pro

          • Using Calulated Field with .in. expresion
            Grant Perkins

            Pat,

             

            I can only get this to work with the first value in the runtime field (either numeric or character). There may be a more challenging formula that could get the .in. concept to work but it may be simpler to use the INSTR function. I am assuming that your PLANT Code is a single code at this point, not a selection of codes.

             

            (INSTR(,[RUNTIME PARAMETER FIELD]))>0

             

            should work as a filter to select records with a PLANT CODE which matches a value entered somewhere in the RUNTIME PARAMETER FIELD. However it would need to be used with care since the strings entered MAY be subject to problems of duplication.

             

            For example, if PLANT CODE = 10 it would match a RUNTIME entry of 10, 100, 101, 102, 1000, etc.

             

             

            Another approach would be to enable multiple Runtime fields and run a filter which looks for matches to any of them. Once again care and a full appreciation of the potential contents of the field would be desirable.

             

            Not quite what you are looking for but similar.

             

            Does this help?

             

             

            Grant

             

             

            Originally posted by Pat Donnelly:

            I am trying to create a Calculated Field to use in an expression using the .in. Operator in a filter.

             

            Calculated Field name is Plant_Code_IN

             

            Style:     Runtime Parameter

            Value:     12,15

            Description:     Enter Plants like (11,12,56)

             

            I believe the Calculated Field MUST be of the type Character as 12,15 is not a valid numeral (US of course)

             

            The rub is if I create a filter like this:

             

            .In.()

             

            It only works when Plant_Code_IN has only one value as in 12.

             

            I have created a filter like these and they work correctly.

             

            .In.(12,15)

            .In.("12","15")

             

             

            I have tried all the variations I can think of on both sides of the .in. Operator.  I cannot get it to work with Plant_Code_IN having more than one value.

             

            I am convinced that filter is going to interpret Calculated Filed as a string and not a set of values.

             

            Can anyone get this to work???

             

            Thanks,

             

            Pat Donnelly /b[/quote]

            • Using Calulated Field with .in. expresion
              Steve Caiels

              I think the expression would be parses as PLANT .IN.(“12,15”).  So you’ll only find a match if you have the identical “12/15” in the plant number field.

               

              But you can get around it using INSTR with a slight variation on Grants post by adding commas front and back.  That way ,10, won't get confused with ,100,

               

              First create a calculated field to add commas either side of your numeric plant number.

               

              ","trim(str(plantnum))","

               

              This will give ,12,  or ,15, etc

               

              Then create a calculated field to do the same for your character based runtime parameter.  So 12,15 ends up as ,12,15,.

               

              Then a filter of instr(,[char selection]) will do the trick.

               

              Or you could do all the conversion in the filter and go straight from a numeric plant number and character runtime parameter by using

              Instr(","trim(str(plantnum))",",","Selection",")

               

              Regards

              Steve

              • Using Calulated Field with .in. expresion
                Pat Donnelly

                Grant,

                 

                Thanks for your input.

                 

                Your work arounds are good.