4 Replies Latest reply on May 9, 2013 6:15 AM by SteveMartino

    Sorting items in a report

    SteveMartino

      Title

      Sorting items in a report

      Post

           Hello Forum, and of course Mr. Phil.  I have a report in which I cannot figure out how to sort.  Please have a look

           To make sense of what your looking out, these are nozzles for heating oil burners.  I need it to look like this:

           Nozzle Brand (Delavan), then nozzle size.  Sorting the nozzle size is the problem. 

           The nozzle has 3 components--

           1.  Pattern (A, B, W)

           2.  GPH ( Gallons per hour, number before the "X".  Can range from .45 to 4.50)

           3.  Spray angle (number after the "X".  Can range from 30 to 90. 

           The Brand is in one field, and the Nozzle is in a separate field with all 3 components together.

           For Example:

           1.25 X 80B is a 1.25 gph, 80 degree spray angle, "B" pattern.

            

            

           They sort the way you see them, but I would like them to sort like this (if at all possible):  See second post for second screen shot.

           I don't know if I have to break the nozzle into 3 fields, (probably easiest) or if I can script to sort.  (beyond my abilities in my learn one, make one, use one database.

           Sorry for the long post..

           Thanks

           Steve

            

            

      ssnozzle.png

        • 1. Re: Sorting items in a report
          SteveMartino

               second screen shot

          • 2. Re: Sorting items in a report
            philmodjunk

                 You need to separate out just the data to be used in the sort into a separate field.

                 If you are just sorting by the number after the X and ignoring the letter, you could set up a calculation field:

                 GetAsNumber ( Rightwords (YourField ; 1 ) )

                 And then you can sort on this calculation field.

                 I'd also consider permanently separating these values into separate fields and using either a calculation field or merge fields to display them in a combined row like this.

            • 3. Re: Sorting items in a report
              SteveMartino

                   As always, thanks Phil for your response.  Was there something  in your post after the last line that didnt come thru? 

                   "...I'd also consider permanently separating these values into separate fields and using either a calculation field or merge fields to display them in a combined row like this..."

                   Yeah I think I have to separate the data.  I want to get it like the second screen shot.....brand, then last letter, with the 2 last numbers in acending order, based on the first numbers before the "X" in ascending order...if that makes sense.

                   Thanks again

                   Steve

              • 4. Re: Sorting items in a report
                SteveMartino

                     Hey Phil, I left out a critical and obvious piece of information.  The nozzles are in a value list (drop down in field), in the order I want them in.  So a simple sort by "Custom order based on value list" was what I needed.  Sorry I didnt mention that in the first place.

                     Thanks as always

                     Steve