4 Replies Latest reply on Sep 22, 2014 10:17 AM by WBSimon

    Question about finding skips in numbering?



      Question about finding skips in numbering?


      I've "inherited" a database that's being used to keep track of books, magazines, etc. in a small private library. Each record was given a sequential number, automatically. Of course, various records were deleted over time, which has resulted in "skips" in the numbering system.

      The library recently purchased pre-printed labels to match the numbering system we are using. Whenever there is a "skip" in the numbering, we end up with a wasted label. Funding is tight, so we really don't want to throw away these unused labels.

      Is there any way to make FileMaker come up with a list of all the various unused numbers? (Besides going through the records one by one, and taking notes of any "skips", I mean!).

      Forgive me if the answer should be obvious. The last time I used FileMaker was many years ago (version 2, I think!). The gap between versions 2 and 12 (Pro) are significant, and while I've spent the past few months exploring all the new (or new to me) features, I suspect I still have quite a ways to go to catch up!

      I would greatly appreciate any advice! Thanks!

        • 1. Re: Question about finding skips in numbering?


          IF that field ISN'T used as primary key for relationship:

          1) sort the records by your actual "sequential number" field
          2) put the cursor into the field of the first record
          3) do a Records >> Replace Field Contents... >> Replace with serial numbers checking the box "Update serial numbers..."

          Note that this command isn't undoable, so do it on a copy of the DB.

          • 2. Re: Question about finding skips in numbering?

            Thanks for your response, raybaudi. Unfortunately, the number field IS used in relationships to other tables.

            I think I may have found a solution, however. I added a new text field to my existing table, and used Replace Field Contents so that it reads "continue" (I chose the word arbitrarily) for every existing record. I also set it so that the same word would be auto-entered into any new records.

            Then I created another table with just two fields:

            1. A number field that follows the same pattern as my existing table.

            2. A text field that uses the number field to "look up" the contents of my "continue" field from the first table.

            I linked the two tables, and then I started creating records in the new table. Eventually I intend to have approximately the same number of records in both tables. I only have a couple of hundred so far, but it seems to be working: I performed a Find (in the new table), setting it to Omit all the records that have "continue" in the text field. All that's left are the numbers that were "skipped" in the original table.

            I'll be the first to admit that it's an inelegant solution, but in some ways it's better than what I first envisioned. At least this way I can continue to track new skips, and also remove the old skips as they are used for new records. Much better than a static list that I would have to keep track of by hand. But I would still welcome any new ideas!

            • 3. Re: Question about finding skips in numbering?

              I would be very very careful here. In my book, the minor savings from using all your labels is not worth the risk to you data integrity due to trying to assign new records to past "skipped" ID numbers.

              What I would do is use two fields to keep the primary key value separate, hidden and thus can have as many "gaps" as possible. I'd then use a second field as the source of values for the labels you will print. You can then use Replace Field Contents with the serial number field to assign a gap free sequence to this new field for use in your labels and you can then experiment with design options to avoid future "gaps" without any risk to your relationships.

              • 4. Re: Question about finding skips in numbering?

                I have faced this in the past.

                1) Created another table (Table2) with all the serial numbers.

                2) Create a calculated field in each table such as zz_FlagX and set the calculation to equal "X".

                3) Link the two tables using zz_FlagX.

                4) Create a layout using the new table with the serial number and the linked field Table2::zz_FlagX.

                The missing serial numbers will show up as empty fields.