6 Replies Latest reply on Jan 24, 2011 7:03 AM by ninja

    How to Sort Correctly Using a Simple Dashed Format, ie 1-1

    janman

      Title

      How to Sort Correctly Using a Simple Dashed Format, ie 1-1

      Post

      Hello experts ... I'm not a pro but I have FM Pro Advanced v10.  Just trying to do a simple sort.  I've searched through all the forums and tried a few things but still doesn't work properly.  I have item numbers that go like this ... 1-1, 1-2, 1-3 ... 1-10, 1-11 ... 2-1, 2-2, 2-3, etc.  Very simple.  When I run a Sort it turns out like 1-1, 1-10, 1-11, 1-2, etc.  I have the numbers in a TEXT field but have also tried a NUMBER setting.  I have also tried removing the dash and using a period.  Same thing.  Seems goofy that this program can't count from one to ten and put things in the right order.  Hope someone has a simple solution / explanation.  Many, many thanks!

        • 1. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
          ninja

          Howdy,

          FMP can sort from one to ten very well...it's just that that as text or number, that isn't what's in your field!

          Might I suggest that you break your code into two fields, one for the prefix number and one for the suffix.
          Then sort ascending by the prefix as primary sort, and sort ascending by the suffix as secondary sort.

          Perhaps the easiest way to do this is to only have the prefix and suffix open to editing, and have your "code" be a calculated field defined as:

          PrefixField & "-" & SuffixField

          • 2. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
            philmodjunk

            Neither sorting by number or text rules will work for this field as formatted.

            If you can add leading zeroes to your single digit numbers: 01-01, 01-10, text based sorting would work and you can use a text field.

            If you can't do that, split your values into two different number fields and sort on both of them.

            If you keep your current field, let's call it NumberText, unchanged, you can do this:

            cValue1
            Left ( NumberText ; Position ( NumberText ; "-" ; 1; 1) - 1 )

            cValue2
            Middle ( NumberText ; Position ( NumberText ; "-" ; 1; 1) + 1 ; 999 )

            Set both to return Number as their return type. Now you can sort first on cValue1, then on cValue2 in the same sort to get the correct order. Neither cValue1 nor cValue2 need be present on the layout where you sort your records.

            • 3. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
              janman

              Thank you BOTH very much for your suggestions.  I'll have to figure out how to try both solutions, where all those codes go, etc.  I haven't worked in FM for about a year so it'll take me awhile.  I did try that solution to just add a zero before the single digits and that worked but I'd rather not have the zero show.  I'll see if I can try the two number fields instead of one text field as I now have.  Thanks again !!!

              • 4. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
                philmodjunk

                Unless you have a compelling reason not to, use Ninja's approach to split the values into two number fields with a calculation that combines them to get your text field. In some cases, you can even use merge text on your layout and not have a calculation field at all:

                <<PrefixField>> - <<SuffixField>>

                • 5. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
                  janman

                  Hi Ninja and PhilModJunk ... can't thank you both enough for your help today!  I got it to work perfectly.  Here's what I did ... I split the values into two Number fields (ItemA and ItemB).  I then created a Calculation called ItemAB and just used ItemA & - ItemB.  I also set up the Primary and Secondary sorting as Ninja suggested.  And, there it was ... exactly as I had hoped.  I then looked back at Ninja's suggestion for the Calculation.  I added the quote marks and the extra ampersand.  That worked as well but now I'm wondering why you need the extra characters.  Mine worked without them.  You two are brilliant, extremely helpful and professional!  Thank you!!

                  • 6. Re: How to Sort Correctly Using a Simple Dashed Format, ie 1-1
                    ninja

                    Howdy,

                    The & simply means "and"

                    The quotes simply mean literally

                    So my expression says {whatever is in PrefixField} and a literal dash mark and {whatever is in SuffixField}.

                    Not having the quotes tells it {whatever is in PrefixField} and the negative value of {whatever is in SuffixField}.
                    While these may give the same result most times, it's safer to ask for what you really want...Safe is good.