8 Replies Latest reply on Mar 17, 2014 3:59 PM by MichaelShearer

    Calculating number of a fixed sort order

    BenBW

      Title

      Calculating number of a fixed sort order

      Post

      I need to create a field that represents the order of the entire database according to a particular sort -- regardless of how the records are actually sorted!

      In other words, if I sort the records another way, or have search results showing only a few records, I don't want the field showing the original order to change.

      To make matters worse, the sort order is by three fields: first "Type", which is sorted by the contents of a Value List; then "Title" and "Subtitle", which are both alphabetic. Records in each field are NOT unique, though no record has all three values the same.

      Any information gratefully received, even if it's just pointers.

        • 1. Re: Calculating number of a fixed sort order
          philmodjunk

          Can you give an example of how a field would "represent the order of the entire database"?

          I don't understand what it is that you want to display in that field, especially since is not to represent how the records are actually sorted. Keep in mind that the records in a given table can simultaneously be part of any number of different found sets and all of them can have a different sort order. The only other "order" that is possible is the original creation order of the records produced when you show all records or "Unsort" them.

          • 2. Re: Calculating number of a fixed sort order
            BenBW
            Can you give an example of how a field would "represent the order of the entire database"?

             

            The field represents the order of that record when sorted in a particular way. So that when the database is sorted, in its entirety, I get a field with records 1, 2, 3, 4, 5, ...... n.

            And I want the number in that field to stay the same when the database is reordered differently, or is displaying search results.

            • 3. Re: Calculating number of a fixed sort order
              philmodjunk

              Use this script to number all your records in a specified order:

              Show All Records
              Sort [Restore ; no dialog ] //specify that "particular way"
              Replace Field Contents [ No dialog  ; YourTable::OrderFIeld ; SerialNumbers ]

              Each time you add new records or change a field value that will sort the record into a new position in this specified sort order, you will then need to run this script again.

              • 4. Re: Calculating number of a fixed sort order
                BenBW

                Thanks, that's great.

                Though it would be great if I didn't have to run it every time I changed something. But it's much better than I had before!

                • 5. Re: Calculating number of a fixed sort order
                  philmodjunk

                  Only if you change something that affects the specified sort order.

                  • 6. Re: Calculating number of a fixed sort order
                    BenBW

                    I have been shown a solution that automatically updates if new records are added that affect the order.

                    1. Create a Calculation field that Lists the three sort fields together. (You may need to substitute the carriage returns for spaces.)

                    Substitute (List (Field1; Field2; Field3) ¶; " ")

                    2. Create a self-joined relationship that creates a ≥ connection between the calculation field and itself.

                    3. Create another calculation field that Counts the records in Table 2's calc field.

                     

                    In other words, this relates every record that is alphabetically greater than (i.e. EARLIER) or equal to the current record, and then counts them.

                    • 7. Re: Calculating number of a fixed sort order
                      philmodjunk

                      Definitely a clever approach. If you try to search or sort on this field, it won't be as fast as the straight number field as this is now an unstored calculation. That may not be a problem.

                      Likewise, be careful if Field1, Field2, etc are ever empty as

                      Apple [EmptyField2] Zebra

                      will be treated as equal to:

                      Apple Zebra [EmptyField3]

                      instead of the second example being greater than the first. This is because List will just omit empty fields. There are ways to adjust the calculation if that is a concern here.

                      • 8. Re: Calculating number of a fixed sort order
                        MichaelShearer

                             I remember in one of the old FM programs you could create a position field and put @@ in it and it would number the records from 1 - what ever. What happened to it?