3 Replies Latest reply on Aug 18, 2010 9:24 AM by philmodjunk

    Finds and operators, problems

    ahutler

      Title

      Finds and operators, problems

      Post

      I am trying to do a find for 2 fields.

      The 1st field is called "Medium" and I am searching on the term ceramic.

      Within that search I want to find objects that don't have any dimension L x W x H greater than 20", so I go to the dimensions field and insert the operator "less than or equal to" and after that I type "20" and hit return.

      This is not working? :(

      What am I doing wrong?

        • 1. Re: Finds and operators, problems
          philmodjunk

          If I read your post correctly, you have a text field for dimensions with text such as 10 x 15 x 15

          You can't treat such a field as though it were a number field with a list of three numbers in it.

          You need to put the three dimensions in separate number fields and search them.

          I'd use such separate number fields for the dimensions and then use a calculation field or Merge fields to display the string of characters you've got in your dimensions feld on any layout where I need to see this. Since you may have lots of records with dimensions already entered in this field, you may want to use some Replace field contents operations to extract the numbers into such new dimensions fields.

          Length: Leftwords ( dimensions ; 1 )
          Width: Middlewords ( dimensions ; 3 ; 1 )
          Height: Rightwords ( dimensions ; 1 )

          • 2. Re: Finds and operators, problems
            ahutler

            Yes, we have a text field for dimensions, because we maintain English and Metric systems in this field i.e. "22 1/8 x 2 1/2 x 10 - 56.2 x 6.3 x 25.4cm". There are also instances when we have multiple parts included in that field. Example "part A torso" 20 x 10, part B head 8 x 8.

            I would be affraid that we would lose data if I did what I think you are suggesting.

            • 3. Re: Finds and operators, problems
              philmodjunk

              Nevertheless, you need to put the dimension data in separate number fields.

              Given that additional info, my original calculations are far too simplistic, but the need for number fields for your dimensions remains. That will also include converting expressions such as 2 1/2 into 2.5 so that the search operators will work correctly.

              You may need to write a sophisticated script that scans through your text and intelligently extracts/converts each value from this text--storing the values in either a set of fields or, given the flexible number of such values shown in your last example, a table of related records.

              You can keep your original text for reference and to resolve any issues where your script might fail to correctly parse the text or where there's been a data entry error--thus no loss of data even in cases where the extraction fails.

              I'd also pair a "units" field with each number to record whether it is Standard or Metric.