9 Replies Latest reply on Jul 17, 2014 9:31 AM by philmodjunk

    Custom serial number creation

    AlbertoPilato

      Title

      Custom serial number creation

      Post

           Hello,

           I need to add every new record a serial number made as following

           89-XXXXX.000.00

           Where "89-" and ".000.00" must be constant and "XXXXX" should start from "00001" and increasing by 1 each new record. (must keep zeros in front of the number to always have 5 digits)

           How do I do it?

        • 1. Re: Custom serial number creation
          philmodjunk

               First, please do not use this serial number as your primary key to link tables of records in relationships.

               Then, use two fields. Define the first field as a simple auto-entered serial number.

               Then define the second as a text field with this auto-enter calculation:

               "89-" & Right ("00000" & SerialNumberField ; 5 ) & " 000.00"

          • 2. Re: Custom serial number creation
            AlbertoPilato

                 And what if I want to use the same code for child records in a related table show as a portal in the main layout using the following schema 

                 Parent : 89-XXXXX.000.00

                 Child : 89-XXXXX.YYY.00

                 How do I make it?

            • 3. Re: Custom serial number creation
              philmodjunk

                   Use a relationship based on a better primary key to access the data in this code from the parent record. While I put the data in quotes as a literal string, this data can also be in a field in the parent record so that it can be used by both the parent record and the child to produce the needed identifiers. The serial number field I described in my first post should be all you need use as a primary key.

              • 4. Re: Custom serial number creation
                AlbertoPilato

                     Sorry, but I can't get it working...

                     In the child table I do not have any field containing the XXXXX string. How do I get this string from the parent record?

                     I made the YYY string generation with increments of 10 from 010 to 990, but how do I concatenate it with the parent when generating a new record within the portal in the parent layout?

                     Of course I'm not using these fields as a primary key, I only need them as a reference internal to the company.

                     Sorry but I'm quite a n00b :(

                • 5. Re: Custom serial number creation
                  AlbertoPilato

                       I'm currently using Left(ParentTable::ParentCode ; 9) & Right("000" & 3digits; 3) & ".00" as calculation, but only the YYY.00 is saved into the field...

                  Left(ParentTable::ParentCode ; 9) is used to get "89-XXXXX." from the "89-XXXXX.000.00" parent field

                  3digits is an auto number field starting with 10 and increasing by 10

                       I then try to concatenate the result but as I said only the Right("000" & 3digits; 3) $ ".00" part of the calculation is working.

                  • 6. Re: Custom serial number creation
                    AlbertoPilato

                         This is an example of what I'd like to obtain

                    Progetto No is the code (not primary key) of the parent record

                    Codice is the code (not primary key) of child records

                    • 7. Re: Custom serial number creation
                      philmodjunk

                           Just checking a few details of concern before I post a more detailed response:

                           YYY increments by 10 with each new child record?

                           And the series of values for YYY start with 10 for each set of child records linked to a different parent?

                           And there will never  be more than 99 child records linked to any one parent? (This is a built in limitation of your format here.)

                           

                                I'm currently using Left(ParentTable::ParentCode ; 9) & Right("000" & 3digits; 3) & ".00" as calculation, but only the YYY.00 is saved into the field...

                           Sounds like you need to select "Text" instead of "number" as the result type or the relationship to the Parent table is not valid. But this does not assign a differen 3Digits value to each child record in the sequence that I am guessing is required here without some additional design work with the 3digits field.

                      • 8. Re: Custom serial number creation
                        AlbertoPilato

                             Yes, the series of values for YYY start with 10 for each set of child records linked to a different parent.

                        I'm aware there are only 99 combinations available.

                        You are right, 3digits assigns a different value to each child record, my calculation is wrong...

                             How do I do it then? Can you please help?

                        • 9. Re: Custom serial number creation
                          philmodjunk

                               First, the relationship between parent and child should start with this pattern:

                               Parent----<Child
                               Parent::__pkParentID = Child::_fkParentID

                               __pkParentID is the same field that I referred to as "serialNumberField" in my original post.

                               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                               Then we'll add a new Tutorial: What are Table Occurrences? of Child and link it like this:

                               Child-----<Child|SameParent

                               Child::_fkParentID = Child|SameParent::_fkParentID   (Child|SameParent is simply the name I've chosen for the second occurrence of Child.)

                               A field in Child name ChildSeq should be a number field with this auto-enter calculation:

                               Max ( Child|SameParent:ChildSeq ) + 1

                               Be sure to clear both the "do not evaluate if all referenced fields are empty" and "Do not replace existing value" check boxes.

                               Then the ChildIdentifier field can use this calculation:

                               Left(ParentTable::ParentCode ; 9)  & Right ( "000" & ChildSeq ; 3 ) & ".00"

                               I'd also define ChildIdentifier as a text field with this as its auto-enter calculation and set a "unique values" validation on it.

                               One of the reasons you don't want to use such identifiers as a primary key is that with bad split second timing, two users can create a new child record for the same parent and get identical identifiers. The validation option protects you against that unlikely event.