2 Replies Latest reply on Jan 9, 2014 4:03 AM by gavjuan

    Modify auto-enter Serial Number using a script

    gavjuan

      Title

      Modify auto-enter Serial Number using a script

      Post

           I have a Quotations database with consecutive serial numbers and a portal that displays related items and costs - occasionally I need delete a record, I do this manually by deleting the Quotation record and associated portal records then re-setting the Serial Number one number back in the Database Manager so there are no holes in the record numbers. Is there a way to do this with a script that can re-set the number in the Manager? I can see a script line 'Open Manage Database' but no way of modifying the Serial Number - can this be done?

        • 1. Re: Modify auto-enter Serial Number using a script
          philmcgeehan

               I use two different fields, an auto-generated serial number as my Primary Key (which doesn't need to be consecutive, or even visible, as it's only used as a unique and consistent ID), and another field where I can calculate the next sequential number, which I use as the main quote number that everyone can see and use.

               I call them __pk_QuoteID and _QuoteNumber, respectively (the underscores just helps me find them quicker when the fields are sorted alphabetically).

               So I can delete quotes as I please, and when I create a new quote the next number is calculated by using a self join relationship:
                       __pk_QuoteID <──┤X├──> __pk_QuoteID
               and the calculation:
                       c_NextQuoteNumber (from Quotes) = Max ( QuotesSelfJoin::_QuoteNumber ) + 1

          • 2. Re: Modify auto-enter Serial Number using a script
            gavjuan

                 Hi Phil - thank you for your concise reply. I will give it go.