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.
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
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 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]
Set Field [MainTable::IDcodeNumber ; IDCode::cMaxValuePlusOne ]
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]
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.
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.
can you explain steps.
thanks in advance
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.