7 Replies Latest reply on Feb 23, 2012 9:50 AM by philmodjunk

    Unique ID database wide

    DavidJacobs

      Title

      Unique ID database wide

      Post

      I'd like to know if anyone has developed a way to create a unique Id for each record--through out the entire database, not just the record's current table?

      I'm working with someone who already has a database I'll be periodically importing too. He has a script he uses when a new record is created. A new serial number is generated( i think this is his serial number table), then the number is automatically inserted in new record. Perhaps there is a way I can have a script run everytime a record is created?

        • 1. Re: Unique ID database wide
          philmodjunk

          You can add a field to your table, usually a number field but it can be text. when you select feld options, you can use the auto-enter tabl to specify that a serial number be entered automatically each time a new record is created. No script need be used for this.

          "Database wide". What do you mean by that? A record exists within a specific table. If a field in that table--and the above serial number field is almost always the means used--uniquely identifying that record within that table is almost always sufficient. So I'm not quite sure what you mean by that phrase.

          • 2. Re: Unique ID database wide
            DavidJacobs

            "Database wide". What do you mean by that? A record exists within a specific table. If a field in that table--and the above serial number field is almost always the means used--uniquely identifying that record within that table is almost always sufficient. So I'm not quite sure what you mean by that phrase."

             

            I know we can use filemaker to generate a unique serial number for the record in the table. I am being told I need to have an id unique to the entire database (all tables). I'd been told there might be a way to go to another "serialnumber generator table" have that table create a new number then bring that number back to the current record or table, place that new number in the serial number location of the record. Then no matter what table I was in the serial number for that record would be unique. Perhaps this is overkill, but you can see how this might be useful when searching the database.

             

            The other database is not a filemaker database. I think he used Access, so I can't understand some of the terminology he uses in describing how he created this feature.

            I hope this is clear enough. I'm by no means a filemaker expert.

            • 3. Re: Unique ID database wide
              philmodjunk

              The only way that makes sense is if you have the same records stored in more than one table--not a design approach that I recommend for FileMaker or any other database--though union  and make table queries make it workable in SQL DBs where it's really not workable in FileMaker.

              The question that needs answer is "unique compared to what other data?"

              If I have a table of contacts, I can assign a serial number ot each contact and that uniquely identifies each contact. If I then have a table of Invoices to link to those contacts, giving contacts a serial number to "make it unique when compared to invoices" makes no sense at all as the invoice  table stores completely different data than contacts. If, however, I have a second table of contacts (say I have a table of clients and a table of vendors), the records are identical and I need an ID that will uniquely identify a contact record across a listing of both tables combined, then this is understandable, but not an optimum design for your data.

              The solution is to use a single data source table for both so that all contact records reside in the same table and thus use the same serial number field as the primary key. If there are distinct differences between Vendors and Clients, you can use a related table for documenting the differences--but this still leaves a single table to produce the unique ID's. (I'd do it this way, even in Access.)

              The other case I can think of where some extra special handling is requies ia when you have multiple copies of a database and need to merge the data from all such copies and need unique record ID's that do not match those of records created in a different copy of the table. If that's what is needed here, let me know as there are some very simple ways to handle that issue.

              • 4. Re: Unique ID database wide
                DavidJacobs

                Ok. I think trying to recreate the serial numbering approach which worked for this other database may not be necessary. Of course, in Filemaker each of  the records are defined by their serial number (and location within their own respective tables).

                Perhaps, you are right about the real issue coming around the corner is importing the records to his database. Since I'll be deploying multiple versions of the database, all generating their own records, there is the very real possiblility that they will create records which have the same ID number. This won't make any friends for me with the other database guy, since he'll have to go through and find any duplicate ids and figure out how to change them so they still work. (I reference the ids in other tables, so it isn't just a matter of changing the individual records ids.

                 

                If you had any suggestions, on this i'd appreciate it!

                 

                • 5. Re: Unique ID database wide
                  philmodjunk

                  In other database systems, it's possible to set up a UID a universal ID that is supposed to uniquely identify the record in all situations no matter what you do with the record. While useful, it's not an option in the form of a built in FileMaker feature though a check of custom functions will likely produce several that people have created that provide such a value for use as a Primary Key in your tables.

                  Here's a simpler approach that may also work:

                  Option 1:

                  Often, databases have a utility table where specific preferences and stuff are stored. Add a field to this table where you can enter different text for each copy of your file. Add a script to your file that runs each time the file is opened. (See file options). Set this script to copy the contents of this field into a global field or variable.

                  Add a new RecordID field (text) to your table that uses this auto-enter calculation:

                  $$GlobalVariable & SerialNumberField

                  or you can use:

                  Table::GlobalField & SerialNumberField.

                  This way, John can use his copy to produce records numbered ABC1, ABC2, ABC3 and George can use his copy to produce records numbered XYZ1, XYZ2, ...

                  • 6. Re: Unique ID database wide
                    DavidJacobs

                    Thanks, this actually sounds like a great idea. Only I'm thinking the serial numbers are "numbers". I'll see if I can talk him into this approach.

                    • 7. Re: Unique ID database wide
                      philmodjunk

                      You could produce numbers that look like this:

                      123.123456

                      Where the number in front of the decimal is unique to the file copy and the number after the decimal is your serial number value.