7 Replies Latest reply on Apr 23, 2013 4:34 PM by PhillipFountain

    Indexing Beyound 9

    PhillipFountain

      Title

      Indexing Beyound 9

      Post

           I'm building a table that needs to be sorted on the primary key field that is made up of an apha-numeric numbering system. The problem I'm having is when the numbering system gets to 10, the record is not maintained after the 9th record but moves it up to a position in the 1's. 

           The purpose of the first field is to maintain the sort order as a primary key, how do I structure the table to maintain the proper order?

           Thanks,

        • 1. Re: Indexing Beyound 9
          philmodjunk

               Option1: Sort on a number field rather than a text field. This is possible even though you indicate that the value is "alphanumeric". Calculation fields can separate the numeric and text portions into two fields one of type text and one of type number and you can then include both in the sort order.

               Option 2: format the numeric portion of your alphanumeric value so the the numeric portion has a fixed number of places by padding smaller numbers with leading zeroes. The resulting text will then sort alphabetically into the same order as a number.

               I recommend that you don't use this field as your primary key. Replace it with an auto-entered serial number in a field of type number. If you need this value to meet user needs/requirements keep it as a data field in the parent table, but don't use it as a match field in relationships.

          • 2. Re: Indexing Beyound 9
            PhillipFountain

                 Thanks, I played with it when I first got FileMaker and now after playing with it for awhile I see that auto-entered serial number will keep everything in order. A question I have is that the table will need to be edited each year with additions and deleted records. When changing records, will the auto-entered numbering system adjust the numbering process to account for placement and removal of records, or do I have to manually shift the numbers to account for the different record positions?

            • 3. Re: Indexing Beyound 9
              schamblee

                   Use your auto-entered serial number as the key field, then use a second field as your sort field. You could then modify this sort field.   If you try to modify a serial number field, you can loose records. (Have Orphan records) You would have to change all child related records.  Making changes to a  serial field is just not good design practice.

              • 4. Re: Indexing Beyound 9
                philmodjunk

                     And because this is an "internal use only" value, "gaps" due to deleting or reverting records should not be an issue. If you need to number records in "gap free" sequential order, give serious thought to not allowing the records to be deleted. Mark them as "void" or something instead of deleting to maintain a "gap free" series. There are alternatives, but that method is the simplest.

                • 5. Re: Indexing Beyound 9
                  PhillipFountain

                       What I'm working on is an aviation department safety management system audit protocol that is usually adjusted each year with changed text, new records, and numbered records that may be removed and re-assigned with a requirement at a latter time.

                       I'm working with the second recommendation which uses a primary key field with an auto-entered serial number and a second sort field that I will use to keep the records in the desired order. The only drawback that I see is that I will have to manually reset the numbering system of the sort field each year that the protocol is adjusted.

                       The protocol table will be read only and accessible only by me for updating each year. The editable fields that the auditors and flight department use will be their property that can be exported to the next years protocol.

                  • 6. Re: Indexing Beyound 9
                    philmodjunk

                         You can use Replace Field Contents with the Serial Number option to renumber a set of records sequentially.

                         Both Replace Field Contents and resetting a "next serial value" field option can be done with a script so that you do not need to do it by hand.

                         Just don't do this on a primary key field.wink

                    • 7. Re: Indexing Beyound 9
                      PhillipFountain

                           Thanks guys, good information. I will be having more questions as I get into it. These audits will be accomplished at corporate flight departments and I plan on having a laptop interfaced with an iPad with FileMaker Go. This will allow the auditor to rome the facility will doing the audit.