10 Replies Latest reply on Apr 18, 2017 8:36 AM by philmodjunk

    Create a Unique ID for each new request

    rporter

      We currently have a SQL database request system that I has been asked to try and recreate in FM.  Each new request generates an ID # such as 17-10292.  Can something like this be done in FM? 

       

      Or, would I be better off trying to figure out how to connect the SQL database to FM.

       

      Either way I would like to see if it is possible and not use the SQL DB any more as it is on old Win8Servers that will no longer be supported by Oracle soon.

        • 1. Re: Create a Unique ID for each new request
          philmodjunk

          FileMaker solutions can generate serial numbers and UUIDs that make good options for primary keys.

           

          A unique ID of the format shown (You don't explain what the "rules" are here for this ID) is also possible to use to "label" records, but not a good option for use a a primary key--the unique identifier used to link a single record to possibly many records in another table.

           

          So the short answer is "yes".

           

          But the long answer is: there could be issues to work out so that it's not used as a primary key, meets the needed format requirements (if any) and is still a unique value.

          • 2. Re: Create a Unique ID for each new request
            paulsbird

            The short answer is yes.

            A couple of questions

            1. Are the first two digits the year?
            2. If the answer to the above is "Yes" , does the sequential 5 digit number restart at the new calendar year?
            3. do you pad the last 5 digits with 0's?

             

            I setup a script for creating Land Use cases in which the case number gets a prefix such as ZV, the Year, and then a sequential number. The final result looks like this ZV2016-004 (Storing it like this makes it easier to extract the last sequential number.)

             

            When you create a new record calculate the last 2 digits of the year

             

            Calculate the next sequential number by using  a sequential number field OR doing a find on the last one entered and adding one. There are a number of different ways to accomplish this.

            pad zero's if needed

            Then, concatenate the fields or variables to produce the ID.

             

            edit - phil posted as I was writing. I do not use that string for the primary key.

            • 3. Re: Create a Unique ID for each new request
              beverly

              If you can, get a screenshot of that column in the SQL. It is likely an auto-enter, unique and may or may not be a Primary Key.

               

              Knowing how it's set in the SQL db can go a long way to know how to recreate in FM.

               

              You may need assistance to that granularity (poking into the guts of the SQL db), if you are not a SQL dba. But if you've been asked to do this, tell them you need a SQL dump of the tables. The dump would be even better than a screen shot.

               

              You don't need the data at this time, just the structure.

              You cannot import this, but it will give you valuable information, including dependencies and those all-important requirements for the columns (fields).

              beverly

              SQL dba & FM dba

              • 4. Re: Create a Unique ID for each new request
                rporter

                Yes, the first two digits are the year.  Then each year the ID resets to match the year and the other 5 digits generate sequentially from 00000.

                • 5. Re: Create a Unique ID for each new request
                  beverly

                  Then hopefully it is not a Primary Key! Imagine if there are records from 1917 and 2017! While the computer was not around in 1917, there can be records input from old paper archives.

                  Again, your SQL dump may clarify the setting for the column.

                   

                  beverly

                  • 6. Re: Create a Unique ID for each new request
                    rporter

                    Our DBA finally got back to me.  The request ID is a primary key!

                    • 7. Re: Create a Unique ID for each new request
                      beverly

                      did your DBA give you a SQL dump or tell you how the Primary key is created in the SQL?

                      • 8. Re: Create a Unique ID for each new request
                        philmodjunk

                        The request ID is a primary key!

                         

                        But not a good primary key, not in Filemaker, not in other SQL based DB systems.

                         

                        Encoding meaning (such as the year) and formats where the serial part of the value has to reset to 1 with each new year set's up scenarios where you might, in the future, be required to change the value or format used. That then causes problems for related data linked via that key.

                         

                        But you don't have to use it as a primary key in your part of the solution. You can keep it as a "label" field in the table and use it to find records, etc, but use your own internally generated primary key for any relationships that you may need to set up in your part of the system. This insulates you from changes to the format or value of the key "breaking" relationships to other tables.

                        • 9. Re: Create a Unique ID for each new request
                          beverly

                          good step, Phil! I often must import from Excel or other sources where the primary key needs a 'reset'. I have the auto-enter serial or UUID field in FM, AND pull the 'old' key into a normal field (text or number). A temporary relationship is set among all the tables as needed, based on the old database and old keys. Then the NEW primary key (auto-enter) is pulled into any foreign keys as needed in the related table(s) through that temporary relationship.

                           

                          It's works, but only needs to happen with old being brought into new. The old relationship is changed to the new one (new fields), or new relationships are created and keep the old for addtional imports.

                          But once IN filemaker the old data with new keys is used for any design in Filemaker (no need to go back).

                           

                          beverly

                          • 10. Re: Create a Unique ID for each new request
                            philmodjunk

                            Exactly!

                             

                            And the fact that the ID comes from a source external to FileMaker is sufficient reason in my book to employ this method that treats the externally generated ID as a label field and not an actual primary key. That eliminates any chance, however small, that the system from which you get this data might either fail to function correctly and produce a duplicate value or change the content/format on you and thus create issues best avoided.

                             

                            In my book, the ideal primary key is:

                             

                            a) Internally generated

                            b) Always unique

                            c) Never, ever modified once assigned to the newly created record

                            d) implemented in as simple and "bullet proof" a manner as possible

                            e) Never includes any "encoded meaning"