9 Replies Latest reply on Oct 12, 2009 2:10 PM by philmodjunk

    Skipping an auto serial number

    mgxdigital

      Title

      Skipping an auto serial number

      Post

      Is there a way to where only if I create a new record will the my auto serial number work?

       

      I would like it NOT to create one if i duplicate a record.

       

      Is this possible?

        • 1. Re: Skipping an auto serial number
          philmodjunk
            

          There are several ways to script this:

           

          One example:

           

          Set Variable [$serial; Value: yourtable::yourserialField]

          New Record/Request

          Set Field [yourtable::yourserialField; $serial]

           

          Obviously, this is not a good idea if your serial number field is supposed to function as a Primary Key.

          • 2. Re: Skipping an auto serial number
            mgxdigital
              

            My serial number is used for a primary key.

            Is there a way to work around this?

            • 3. Re: Skipping an auto serial number
              mrvodka
                

              This defeats the purpose of a primary key...

               

              Please explain why you are trying to do this for duplicates...

              • 4. Re: Skipping an auto serial number
                mgxdigital
                  

                My serial number is used as a job number.

                 

                What i'm looking to do is have my regular job numbers and then have special numbers for partials.

                 

                So i have jobs 50, 51, 52 all ship out complete, then on job 53 i want to ship a partial (for partials i want my number to be the job number (53) hyphen 1, then hyphen 2 until the job is out of stock. Then on the next job i still want it to be 54...

                 

                I have the job records in a summary field and need to be able to show all shipped records, partials included on that page for billing, commission, inventory purposes.

                 

                Job Summary Page looks like:

                Status         Job #          Ordered Amount            Shipped Amount

                Shipped        50                      1000                           1000

                Shipped        51                      1000                           1000

                Shipped        52                      1000                           1000

                Shipped        53                      1000                            500

                Open            53-1                   500                               -

                Shipped        54                      1000                           1000

                 

                • 5. Re: Skipping an auto serial number
                  mrvodka
                    

                  Well the first problem is that you are using your primary key for something else than it is meant to be. A primary key should be meaningless.

                   

                  Second, perhaps it makes more sense to have a shipping table instead of trying to have it all done in the jobs table.

                  • 6. Re: Skipping an auto serial number
                    mgxdigital
                      

                    I see.

                    This whole database was set up before I got here and we're just trying to change things.

                    Is there a way to make a new primary key and apply it to old records? This way I could get my job number to not be the primary key.

                    • 7. Re: Skipping an auto serial number
                      ninja
                        

                      I agree with mr_vodka, (i often do...'cause he's right a lot)

                       

                      Script Help Needed

                      • 8. Re: Skipping an auto serial number
                        mgxdigital
                          

                        Yes, i get it.

                        Like I said, i'm coming into an already put together database with alot of mistakes.

                         

                        If i make a new primary key to connect my relationships how can i apply that to my current/past records?

                         

                        If i delete the primary key that they use now won't it mess something up?

                        • 9. Re: Skipping an auto serial number
                          philmodjunk
                            

                          Primary Keys are defined as primary keys because they link records in a parent table to one or more child records in related tables. That's why we're telling you not to duplicate a primary key field value because two parent records are then linking to the same set of related records and that will really cause you problems.

                           

                          If you want to define a new field as a primary key, that's pretty easy. Just define a new auto-entered serial number field. Then, show all records and click in this new field to use replace field contents to replace the contents of this field with a serial number. Be sure to select the "update serial number in entry options" check box so that your next new record gets the desired value. In child tables, you can also define a new field to link to your newly added primary key and use replace field contents and your current relationship to copy the new PK value into the child records.