3 Replies Latest reply on Sep 12, 2014 1:30 PM by philmodjunk

    Serial number problem



      Serial number problem


      I have a contact database that creates a Vendor Number (serial ID) upon creation.  The format for that number is CP0001, CP0002, etc.  The database contains vendors and customers.  I have written a script to clear the number when a customer is chosen for the record and reset the serial ID.  The issue I am having is that instead of the script performing the MAX function it is reverting the next available serial number to CP0001.  I have set up a self-join relationship also and the issue persists.  Script is below.  Not sure what I am doing wrong.

      If [address::${Customer or Supplier} = “Customer”]

            Go to Field [address::Vendor#]

            Clear [Select; address::Vendor #]      

            Set Next Serial Value [address::Vendor #; Max (address 2::Vendor #; “CP”&Right (“0000” & address::Vendor # +1; 4))]

      End If

        • 1. Re: Serial number problem

          Sorry but serial IDs really should not ever be changed once assigned to a record if they, as appears to be the case here are used as primary keys to identify each record in the table and to link to other tables in relationships. Use a status field of some sort to distinguish between customers and vendors and keep the ID number unchanged.

          Max, when used with this syntax returns the value of Vendor# + 1, the value you just set to zero in the previous script step. You'd need a self join relationship to get the value of Vendor# across all records in your table.


          • 2. Re: Serial number problem

            After a bit of trial and error, as well as someone helping me think it though, I have come up with a solution for this.


            • 3. Re: Serial number problem

              And if you have more than one user using this at a time, you can get two records with the same serial number value.