7 Replies Latest reply on Aug 13, 2014 10:15 PM by philmodjunk

    Creating a field that autogenerates by 1 with some text in front of it

    terrypink

      Title

      Creating a field that autogenerates by 1 with some text in front of it

      Post

           How would you create field that has some text in front of it, and then autogenerates based on the last record that had such a field?

            

           It would be similar to the autogenerated serial number, but would only occur if the user pressed a button to create such a number?

        • 1. Re: Creating a field that autogenerates by 1 with some text in front of it
          philmodjunk

               I can think of several approaches, but which to describe here depends on details not yet known by me.

               Is the text preceding the number value always the same for all records in your table or do you have different groups of records that have a different text "prefix" in this field?

               If you have different prefixes, does the sequence number that follows it form a different sequence for each prefix?

               Are you planning on using this field as a match field in relationships to other tables?

          • 2. Re: Creating a field that autogenerates by 1 with some text in front of it
            terrypink

                 Thanks as always, Phil, for your kindness in helping novices.

                 No, the text that preceeds the number value is always "BMR", but if possibly to have some trailing zeros because the numbers should be 001-100.

                 No, there are no different prefixes. 

                 No, this field would only occur in one table.

                  

                  

            • 3. Re: Creating a field that autogenerates by 1 with some text in front of it
              philmodjunk

                   That narrows this down to two key issues: the leading text--which is simple and the need to generate this code "on demand" by clicking a button rather than automatically assigning a value to every new record.

                   There's a trick I recommend for "on demand" serial numbers that is, as far as I know, totally "bullet proof" against getting duplicates as it uses an auto-entered serial number in a creative fashion to produce the desired result.

                   Let's call your current table "Main". Let's assume that it already has a primary key field, __pkMainID that auto-enters a serial number or Get ( UUID ) text. To get an "on demand" serial number, do this:

                   Add another table, named "SerialNumbers" with two fields:

                   _fkMainID
                   Sequence (number)

                   Define Sequence as an auto-entered serial number in field options.
                   Enable "allow creation of records via this relationship" for the SerialNumbers table in this relationship.

                   Now this script step:

                   Set Field [SerialNumbers::_fkMainID ; Main::__pkMaindID ]

                   will create a new record in SerialNumbers, but only if a related record does not already exist. Creating this new record generates a new unique serial number. Unlike some other approaches, you can have a bunch of users all performing this script at the same time and still not get any cases where a duplicate value is generated.

                   Then the calculation for the ID field that you want would be:

                   "BMR" & Right ( "000" & SerialNumbers::Sequence ; 3 )

                   An unstored calculation field in Main can produce this or the script above could be modified to be:

                   Set Field [SerialNumbers::_fkMainID ; Main::__pkMaindID ]
                   Commit Records
                   Set Field [Main::IDfield ; "BMR" & Right ( "000" & SerialNumbers::Sequence ; 3 ) ]

              • 4. Re: Creating a field that autogenerates by 1 with some text in front of it
                terrypink

                     You're the best, Phil!

                      

                     Thank you so much!

                • 5. Re: Creating a field that autogenerates by 1 with some text in front of it
                  terrypink

                       One last thing, kind of on a different topic, but can I use this "Main::IDfield" field (which isn't the primary key, but just a field) to link to other tables?  Meaning, can I use a foreign key from a different table and link it to the Main::IDfield?  Or does the foreign key always have to link to the Main table's primary key?

                  • 6. Re: Creating a field that autogenerates by 1 with some text in front of it
                    RickWhitelaw

                         Phil, I don't see how your approach would work.  The table "Serial Numbers" would have no way of finding Main::pk unless the script was run from the Main table and New Record preceded your script steps with the script, of course, entering the Main::pk as the FK in the second table.. Maybe I'm missing something obvious. EDIT: Or does the script reference the Serial Numbers::fk from the context of the "Main" table?

                    • 7. Re: Creating a field that autogenerates by 1 with some text in front of it
                      philmodjunk

                           The key detail is the "allow creation of records via this relationship" setting.

                           This script is performed from a layout based on the Main table.

                           Set Field [ RelatedTable::Field ; some value]

                           will then create a new record in the related table, but only if a related table does not yet exist for the current record in Main. Thus this creates one new serial number record on demand, but only one for any given record in Main.

                           Basically this works like entering data into a single row portal with "allow creation..." enabled.