5 Replies Latest reply on Jul 28, 2011 2:24 PM by philmodjunk

    Creating Alpha Numeric Unique field for each client with different 3 word

    ShoaibAhmed

      Title

      Creating Alpha Numeric Unique field for each client with different 3 word

      Post

      HI All


      i desperately need help regarding to Build the data Base

      we have 5 clients and i have to create a Unique Alpha numeric Character as a unique ID for sorting and searching, its primary key.

      1- All the 5 client, i have to use 3 characters which is difference.
      2- All the numeric value would be fine by incremental 1 but if i am selecting the other client which got the 3 different word ( i.e -  coke for Coc, Nestle for Nes) how i have to define value so when i am on coke or nestle, it start from 1 and if we have in one client 50 record and other one got 90 record and some one come over and want to add new record its add 51 or 91. please help.



      thanks

        • 1. Re: Creating Alpha Numeric Unique field for each client with different 3 word
          philmodjunk

          Can you explain why you need such a complicated system?

          It would be simpler just to use an auto-entered serial number as the primary key. Such calculated primary keys can be tricky to implement in shared databases to ensure that two users don't generate identical values by doing this at the same time. If you do need such a value, it's best to use it as a "label" value in the parent record, but base your relationships on the auto-entered serial number.

          • 2. Re: Creating Alpha Numeric Unique field for each client with different 3 word
            ShoaibAhmed

            hi thanks

            But the client code we had it already and we have been using it from long time but it was on paper, for Example Coc001 or Nes001. we have 3 another client which got different code, and i want to create a database so by selecting the drop downmenu i can pick Coc and Nes and other, after selecting thees 3 word code other Number can come up as a unique autometically, but question is how to select when some one pick Coc and go to last unique number which was created last time.

            hoep this help

            • 3. Re: Creating Alpha Numeric Unique field for each client with different 3 word
              philmodjunk

              You can still use this system. Just don't use it to link your tables in relationships in your database. Add a field for this in your parent table and you can use it when performing finds and sorts and on reports, but any related tables would still link by the internally generated serial number in the parent table.

              You can define a related table, LabelCodes, to track the maxium value for each three letter code;

              In this table you have two fields:

              3Letter, cMaxValuePlusOne

              3Letter is a text field.

              cMaxValuePlusOne is a calculation:

              Max ( MainTable::IDCodeNumber ) + 1 and clear the "Do not evaluate if all referenced fields are empty" option for this calculation field.

              You set up this relationship:

              Maintable::3Letter = LableCodes::3Letter

              A script trigger on MainTable::3Letter can do this script:

              Set Error Capture [on]
              Loop
                 Set Field [MainTable::IDcodeNumber ; IDCode::cMaxValuePlusOne ]
                 Commit Record
                 Exit Loop If [get ( LastError ) = 0 // no duplicate value ]
                 Set Variable [$I ; Value: $I + 1 ]
                 If [$I > 100 //something's wrong 100 tries still is not producing a unique value ]
                    Show Custom Dialog ["Error creating unique IDcode"]
                    Exit Loop If [True]
                 End If
              End Loop

              Define a text field with this auto-entered calculation:

              3Letter & IDcodeNumber

              and set a unique values validation rule on it. This is the validation rule that will trigger an error if the script attempts to assign an existing combination of 3letter and IDcodeNumber--which can happen if the file is hosted over a network and two users attempt to generate such an identifier for the same 3letter value at exactly the same time.

              • 4. Re: Creating Alpha Numeric Unique field for each client with different 3 word
                ShoaibAhmed

                so it means there would be duplicate recore fond when some one do the search and they found multiple unique ID like, 001, 001 but practically differnce would be one is Coc001 AND other one is nex001.

                is this is the case how they would get the estimate or payrole from that where they would have 2 different files, one for payrol and other one is estimate.

                thanks

                can you explain steps.

                thanks in advance

                • 5. Re: Creating Alpha Numeric Unique field for each client with different 3 word
                  philmodjunk

                  What I have described should avoid any duplicates. Should two user actions attempt to generate the same code over the network, the script should catch and correct the error by assigning the next value in the series.

                  TO add leading zeroes, you'll need to modify the calculation slightly:

                  3Letter & Right ( "000" &  IDcodeNumber ; 3 )

                  Note that this limits you to 999 as the maximum number for any 3letter code. Once you reach that point, the script will display the custom dialog error message each time as it will be unable to generate a new unique value for that three letter combination.

                  Payroll and Estimates would be two related tables in your database. They can be in separate files, but there's no need to do so. Either way, you have two related tables with a relationship such as:

                  Customer::CustomerID = Payroll::CustomerID

                  Customer::CustomerID = Estimates::CustomerID

                  CustomerID is an auto-entered serial number. Using these table names as examples, you'd define the above method for generating your IDcode in Customer. Any time you want to show, find or sort records by this value in Payroll or Estimates, you can refer directly to the field as it is defined in the Customer table.