5 Replies Latest reply on Dec 19, 2008 6:51 AM by gidgetgirl

    How do i...create unique numbers in a specific format?

    LightningAd

      Title

      How do i...create unique numbers in a specific format?

      Post

      I'm completely new to database creation, and i'm trying to develop a system to help me in my job and learning as i go! (Fool i hear you cry).

       

      Because i am trying to build a system to catalogue TV commercials, which are all identified with specific 15 digit alphanumeric codes, i need to figure out how to create these.

       

      the code is in the format  XYZ/ABCD000/999    where XYZ is the agency id code, ABCD is the product identifier, 000 is a 3 digit number  and 999 equates to the duration of the ad so a 20 sec ad equals 020, a 40 sec = 040.

       

      I need to be able to generate a new code for each ad, but as i have recently passed the 999 point on my clock numbers i need the system to be able to use digits 001-999 then start again, but to also compare the first 12 characters of the code to ensure that each one is unique.

       

      Please can someone point me in the direction i should be headed with this one?

      I know i have probably bitten off more than a newbie can safely chew, but heck...how else do you learn?

       

      thanks in advance 

       

      adam 

        • 1. Re: How do i...create unique numbers in a specific format?
          elevatorgirl
            

          ok, so you want to take three different numbers and make them one. First create four fields: agency id, product Id , duration.( all numeric, with ranges or value lists ) fourth(Catalogue id) is a calculation.

          So it should look something like this:Catalogue id=agency id;product Id;duration.

          then also check box unique value,under validation, the program will prompt an error message when you have entered one that is already in use. I am just not sure if you can use / I think it is an operator you may need a different symbol. Good luck

           

          everyone was a newbie at some point!

          and I am a big believer in jumpin in with both feet, it is so liberating. Go for it.

          • 2. Re: How do i...create unique numbers in a specific format?
            markstar
              

            Since your posting only describe the goal, and not how you have attempted to achieve the goal, I will make some assumptions about how you have set up your file based on how I would try it. This solution illustrates the idea of a self-generating Serial number.
             
            I would create three data entry text fields, the serial number field, and a calculation field that strings these four fields together adding the forward slash separators:

             

            Agency_ID = Text, always 3 characters

            Product_ID = Text, always 4 characters

            Duration = Text, always 3 characters

            Serial = Number

            Code = Calculation, Text [= Agency_ID & "/" & Product_ID & Serial & "/" & Duration]

             

            If the first 12 characters of the Code must be a unique string, then the 3 digit Serial number must be tied to the combination of the Agency_ID_Text and Product_ID_Text. For instance you might want the following Code values to be generated when the user enters the Agency_ID, Product_ID, and Duration:

             

            XXX/BBBB001/020
            XXX/BBBB002/020
            XXX/BBBB003/040

             

            YYY/BBBB001/020
            YYY/BBBB002/020
            YYY/BBBB003/040

             

            ZZZ/AAAA001/020
            ZZZ/BBBB001/020
            ZZZ/CCCC001/040

             

            Even though many of these ads are for the same product (BBBB), they each have a unique string preceeding the duration.

             

            Suppose your data table is named Ad_1. Set up a new occurence of your data table, Ad_2, and relate the two occurences so that Ad_1::Agency_ID = Ad_2::Agency_ID and Ad_1::Product_ID = Ad_2::Product_ID.

             

            Go back to the definition of the Serial field and specify an auto-enter calculation:
            = Case( IsEmpty( Ad_2::Serial) or Max( Ad_2::Serial) = 999; 1; Max( Ad_2::Serial) + 1)

            Uncheck the "Do not replace existing valu for field (if any)" box.

             

            Go back to the definition of the Code field and alter the calculation slightly:
            = Agency_ID & "/" & Product_ID & Right( "000" & Serial; 3) & "/" & Duration

             

            Now when you enter any Agency_ID and Product_ID combination, the next Serial for the combination is auto-entered and the correct Code is generated!

             

            Let me know if you have any questions or comments -- Mark

            • 3. Re: How do i...create unique numbers in a specific format?
              LightningAd
                

              thanks both for the replies, i'll try them out as soon as possible.

               

              One question Markstar - and forgive me if it sounds dumb......whilst i think your approach with the numbers is the way forward on this project can i just check that because the current clock numbers are less well ordered (ie. done using Excel and so the 3 digit number is always 1 higher than the previous, but there is no numerical procession for each product code...dchp027, scdo028,drwh029, etc), and as i will have to enter over a thousand existing codes, will the auto generate system be able to "fill in the blanks"? So it starts at the 001 and looks for the next free number/product code combination?

               

              thanks again

              Adam 

              • 4. Re: How do i...create unique numbers in a specific format?
                LightningAd
                  

                having sat down to try out the suggestions, i realise now that i can understand the reasons behind the calculations but find the terminology surrounding the rest of the description a tad confusing.

                 

                How do i set up a new occurence of a data table (and what does it actually mean)?

                 

                thanks

                adam 

                 

                 

                edit -

                 

                I have figured out what you meant...and created a test. It looks good and i think is almost what i need...just another question:-

                 

                I tried to enter a new serial manually (because i will need to do this with the pre-existing data) and that allowed me to jump ahead of the sequence. The next record after that one continues the numerical sequence from the preceeding record...is there anyway to get the sequence to go back to the earliest free spot in the sequence.

                 

                Example - I entered record 1-3 using auto, then entered a record with serial 7 manually. Next record automatically shows 8 but i would like it to go back to 4 and continue the seq until it has to jump over 7.  Do you know if this is possible?

                 

                thanks

                Adam 

                 

                • 5. Re: How do i...create unique numbers in a specific format?
                  gidgetgirl
                    

                  Hello Markstar,

                   

                  I was hoping you could help me with a similar problem.  

                   

                  I need help with my database for assigning serial job numbers in relational tables.

                  I have a Clients table that assigns a client code to each client.  (Example:  John M. Doe's code is JMD and Tom S. Thumb's code is TST.)

                  Then I have a Projects table that I keep track of jobs for ALL my clients.  Right now it is just generating serial numbers in order of jobs as I receive them, with a looked-up client code in front  (example: JMD-001, TST-002, RSQ-003, MGS-004).  I do not like this format.

                  Instead, from this point on, I would like to start organizing my projects to be ordered by a generated serial number for EACH client.

                  (Example:  John Doe's projects would be ordered  JMD-001, JMD-002, JMD-003....

                  and Tom Thumb's projects would be ordered TST-001, TST-002, TST-003....)

                  But I don't want to have a separate table for each client's projects.  Is it possible to have all the jobs together with unique codes in the same table?

                  --------------------------------------------------------------------------------------------------------------

                  Right now I have the following IDs, with Client Name defining the relationship.

                  Clients Table:
                  Client Name
                  Client code (a unique value I assign, a 3 letter value)


                  Projects Table:
                  Client Name
                  Client code (looked up value )
                  Serial
                  Project Number  (text; calculation; [=Client_Code &  "-" &  Serial])
                  --------------------------------------------------------------------

                  I am not sure where to go from here.  How would I set this up?

                  I appreciate the help! Thank you.