4 Replies Latest reply on Dec 16, 2011 12:23 PM by Charlie999

    Auto enter number question.

    Charlie999

      Title

      Auto enter number question.

      Post

      Hi

      I'm using a solution at the moment to allocate riders to a database for a charity bike ride.  Basically i import from an excel document the riders applications.  Then I manually allocate them the next riders number.  This sounds simple until riders nearer the day drop out. I then remove their rider number and allocate it to the next one to enter.

      In simple terms lets say I have allocated numbers 1 to 250 then riders 12, 15 & 230 drop out when I get the 251st person to enter I would give them number 12 and so on.

      If i got auto enter serial number it would add 1 each time but any ideas how I could get it to fill in the gaps?

        • 1. Re: Auto enter number question.
          philmodjunk

          First question: Do you really need to? What problems will the gaps cause for you? (Sometimes folks just want to avoid gaps to keep things "neat".)

          How soon do you need the numbers to be assigned? After the last application is processed or sooner?

          If you can wait until you have all the applications entered, you can use a separate, auto-entered serial number for any related tables in your database, but keep a "RiderNumber" field empty. Then you can use Replace Field Contents to enter a 'gap free' serial number into this field for all records after the last application is processed. This would work for putting numbers on the actual riders on the day of the race if it's really a problem to have gaps in the number series.

          • 2. Re: Auto enter number question.
            Charlie999

            Phil

            Thanks for the reply.  I suppose I just want things to be neat that's all.

            What I normally do is allocate a riders number when people enter over the preceding months before the ride and post them on our web site.  When people drop out I normally jot down the missing number and then use them up as best as I can with new riders.

            Another reason as well I suppose is that I'm new to FileMaker and am keen to learn.  I purchased my copy about 12 months ago but thought it was far to complicated and stuck to Bento.  I have since however started watching online training videos and am really getting into it.

            I was wondering if I could get an auto enter function or calculation to maybe attempt to give each new rider the number 1 and then if that fails because its been used and then increment by 1 until it finds a gap?  I have set the field to be unique so far.

            • 3. Re: Auto enter number question.
              philmodjunk

              I was wondering if I could get an auto enter function or calculation to maybe attempt to give each new rider the number 1 and then if that fails because its been used and then increment by 1 until it finds a gap?  I have set the field to be unique so far.

              It would take a script and such a system would be pretty inefficient and not a good idea if you ever hosted the file over a network so that more than one person was entering data at the same time.

              I know there are reasons for elminating gaps in serial number sequences. This is sometimes an audit requirement, say with numbered invoices. The number may present on a physical object such as a pre printed invoice form or the racing placards attached to some entrants in races (and where a gap in the series might be an issue when keeping track of who has finished and who is still on the course.)

              Just "keeping them neat" doesn't seem to be much reason to go to the extra effort.

              That said, if I really wanted to minimize the gaps in the series, I'd set up a system where a blank record with the unused number is kept in the table after an entrant drops out. When registering a new racer, you'd click a button that uses a script to perform a find for all such empty records. If one or more is found, sort them and pop up the blank record with the smallest number. If none are found, the script creates a new record.

              This won't remove 100% of the gaps as emergencies could drop a rider out even minutes before the event starts, but it will elminate most in the days leading up to the event for as long as additional entrants are yet to be registered.

              (As I said before, the other options is not to assign a number until the day of the race. If the number is to be used on such placards as I mentioned earlier, that might work for you as long as a different serial number field is used for linking records in relationships.)

              • 4. Re: Auto enter number question.
                Charlie999

                Phil

                Thats good food for thought thank you.

                As usual from this forum good fast advice. Thank you.