10 Replies Latest reply on Mar 4, 2011 10:53 AM by BradenDouglass

    Auto Generating ID's with an auto Reset

    BradenDouglass

      Title

      Auto Generating ID's with an auto Reset

      Post

      Ok all, I have never really given this a whirl before but thought I would throw together something quick to test the usability. Needless to say, I have hit a snag. I have a unique ID table set to a "lots" table allowing many lots to be attached to a specific ID. What I want to do is increment the lots compared to the ID only. As long as there were new record being created from the id, it would increment up by 1. Once we changed ID, it would reset to 1 and count upwards. Kinda like below. Thank you for the help and sorry for the odd title.

      id1 - lot1

      id1 - lot2

      id1 - lot3

      id2 - lot1

      id3 - lot1

      id1 - lot4

        • 1. Re: Auto Generating ID's with an auto Reset
          philmodjunk

          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.

          • 2. Re: Auto Generating ID's with an auto Reset
            BradenDouglass

            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.

            • 3. Re: Auto Generating ID's with an auto Reset
              philmodjunk

              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.

              • 4. Re: Auto Generating ID's with an auto Reset
                BradenDouglass

                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?

                • 5. Re: Auto Generating ID's with an auto Reset
                  philmodjunk

                  I've used a name consistent with the relationship I gave you:

                  With:

                  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.

                  • 6. Re: Auto Generating ID's with an auto Reset
                    BradenDouglass

                    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.

                    http://cl.ly/4yAM

                    • 7. Re: Auto Generating ID's with an auto Reset
                      philmodjunk

                      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.

                      • 8. Re: Auto Generating ID's with an auto Reset
                        BradenDouglass

                        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!

                        Pictures

                        • 9. Re: Auto Generating ID's with an auto Reset
                          philmodjunk

                          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

                          • 10. Re: Auto Generating ID's with an auto Reset
                            BradenDouglass

                            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.