There are a number of threads where this issue is discussed here, but they might be difficult to find as I can't think of an obvious key word under which you might search for them.
Will this be in a system where you have more than one user creating these records at the same time?
In a single user environment, this is fairly easy to set up. In a multi-user situation, it can be tricky to avoid the situation where two users create a new Lot record for the same ID at nearly the same time and get duplicate lot numbers.
Same problem I was actually having searching out the answer. Anyway, yep, it will be in a multi user environment. I was thinking of getting around it by creating some kind of a portal where they can add a few basic items (description, number, etc) and then click a button and jump to the much more robust, full layout. It complicates items but the users see what the numbers are in the portal and can simply key in a numerical value. Any ideas? Thank you.
You may find that it never happens. It would require two or more users to be adding lot records with the same ID at the same time. How likely that is depends on your business procedures.
I'd set up this relationship:
MainTable::ID = LotIDManager::ID
In LogIDManger, define this calculation field, cMaxLotNumb: max ( MainTable::LotNumb )
Then, you should be able to get this auto-enter expression to work: LotIDManager::cMaxLotNumb + 1.
As a guard against getting duplicate lots for the same ID, I'd define a text field with this auto-entered expression:
ID & "." & LotNumb
With Unique Value specified as a validation rule. That way you'll at least get a warning that a duplicate was created and you can either run a script to try to assign a new lot number or you can just delete the record and start over.
Note: You'll need to create a new record in LotIDManager each time you create a record with a new ID in MainTable. It's also possible, if you are careful, to set this up with LotIDManger a new table occurrence of MainTable so that you don't have to do this, but then care must be taken to make sure the calculation field and auto-enter calculations evaluate correctly.
Hmm, very cool Phil, I just have one question? What is the table in the max calculation (Maintable::lotnumb)? Is this just a simple placeholder just to count off from?
I've used a name consistent with the relationship I gave you:
MainTable::ID = LotIDManager::ID
You would use this expression exactly as written. when you subsitute your table occurrence name for MainTable in this relationship, you update this calculation to match.
Phil you wrote: "LogIDManger, define this calculation field, cMaxLotNumb: max ( MainTable::LotNumb )"
I understand the calculation right up to the LotNumb in the parens. Currently I have the relationship set up and under MainTable (table) I only have the error checking field and a blank "LotNumb" field (along with it's auto increment serial number). Under the lotIDManager table I have the lotIDMax that is calculated like Max(MainTable::lotNumb) along with the lotIDmanager::ID being updated with cMaxLotNumb +1 calculation. There is obviously something wrong, and I can't decipher it from the original post. Here are some pictures to help out, my brain is cooked and can't come up with a better way to explain it.
Looks like we have things inverted here.
Take the Orders table, (remove the _fk_orderID field here, it can't exist in this table as you have the primary key: _pk_OrderID defined here) and define the, cMaxLotNumb calculation field in it.
You'll have this relationship:
Order::__pk_OrderID = Lot::_fk_OrderID
and cMaxLotNumb will be defined in Order as Max ( Lot::LotNumb ) (LotNumb is not the same field as _pk_LotId)
Lot::LotNumber would then be defined to auto-enter Order::cMaxLotNumb + 1.
Hmm, Phil, I followed this out (attached pictures) as well as included some simple error checking that you mentioned in your previous post but I am not even getting the error checking to kick in. I can select the specific order number in the lot's layout but it doesn't update anything. Thank you for all your help!
I had to go back and check an older file here. Last time I helped someone set something like this up we ran into some field update issues that had to be set up just right in order for this to work.
I then made a test file and had to have these specific settings in place:
On the auto-enter calculation, clear the "do not evaluate if all referenced fields are empty" And also clear "Do not replace existing value option".
Finally, since I was using a portal to create the related Lot records, I had to add an OnObjectExit trigger to commit the record or I can create a new record in the portal without it getting the next serial value like it should.
Here's a demo file you can play with: http://www.4shared.com/file/hB9Oy8r-/SubSerialNumberTest_III.html
Thank you Phil, I had to play with what you gave me for a bit to make things work with my DB. Eventually I had to tell it to Always validate and everything works as great as possible. Thank you again.