14 Replies Latest reply on Feb 2, 2012 6:13 AM by RayCologon

    Adding a Unique ID by combining other Unique ID's

    pademo57

      Hi There,

      Our company has a unique company ID for each employee. We have 7 locations across the country each using the same training system.

       

      An employee starts in a classroom then moves on to work a particular job in the factory. Once completed he returns to the classroom on a new course and repeats the cycle.

       

      Head Office wants to have all the training info sent to them on a weekly basis. So currently I have:

       

      Employee Table

      EmpID (Primary Key)

      EmpNo (Assigned by Company)

       

      Course Table

      CourID (Primary Key)

       

      Since each Employee can take more than one Course and each Course can have more than one Employee to get around the many-to-many relationship:

       

      EmpL_Cour Table

      EmpLCourID (Primary Key)

      EmpID

      CourID

       

      The EmpL_CourID is used each day by the instructor to write up how the Employee is doing on an iPad in the Daily Assesment Table

       

      My problem is, how do I make a unique ID using the Employee Number (EmpNo) and have it increment? This way, no two employees will have the same Assessment number no matter where they work across the country.

       

      For example: Employee Number: 123456-AssessNo01, 123456-AssessNo02, 123456-AssessNo03, etc.

       

      I can see in Filemaker Pro Advanced, I can put in a Calculated value or a Looked Up value, but how do I get them to increment?

       

      Or is there a better way of doing this?

        • 1. Re: Adding a Unique ID by combining other Unique ID's
          Mike_Mitchell

          Hi, pademo57.

           

          The SerialIncrement() function can do this directly. But if you're truly concerned about making the IDs unique, might I suggest making a truly unique ID via some other method - such as a base36 UUID strategy. You can simulate this by concatenating timestamp, MAC address, and record ID.

           

          (At the risk of restarting the great unique ID flame war.)        

           

          HTH

           

          Mike

          • 2. Re: Adding a Unique ID by combining other Unique ID's
            comment

            pademo57 wrote:

             

            My problem is, how do I make a unique ID using the Employee Number (EmpNo) and have it increment?

             

            I am not sure I understand what your concern is. Doesn't the Assesments table have its own auto-entered serial number ID field?

            • 3. Re: Adding a Unique ID by combining other Unique ID's
              RayCologon

              pademo57 wrote:

              I can see in Filemaker Pro Advanced, I can put in a Calculated value or a Looked Up value, but how do I get them to increment?

               

              Hi pademo57,

               

              If you can be confident that there will never be two Cour records for the same employee being created simultanously, then a calculation to determine the unique ID might be a viable option - but otherwise, there will be a risk that two simultanously created records will end up with the same calculated ID.

               

              In that situation, you'd be better to have the numbers be unique but non-sequential per employee (eg by using auto-entered serial numbers etc) or to use a script to compute and allocate the numbers, and have the script manage an allocation 'queue' to ensure that simultaneous allocations don't occur.

               

              Having said all that, if you decide it *is* appropriate in your circumstances to calculate the serials, here's one way. You will need a self-join relationship that connects the EmpL_Cour table to itself matching as an equi-join on EmpID, then you can use a calculation formula such as:

               

              SerialIncrement(EmpID & "-AssessNo00"; Count(SelfJoin::EmpID) + 1)

               

              ...where 'SelfJoin' is the name of the related TO of EmpL_Cour mentioned above.

               

              See the attached simple example of the above calculation in action.

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------

              • 4. Re: Adding a Unique ID by combining other Unique ID's
                pademo57

                Thanks to everyone for your suggestions.  To be more clear:

                 

                Originally, the program was sent to each location with the understanding that these files would never be pooled together.  I know, I know, what was I thinking?

                 

                So my thought was instead of re-inventing the wheel, I could use the unique Employee Number and combine it with the Assessment Number.

                 

                So in my Assessment Table, my primary key - AssessID would contain the EmpID + AssessXX. But I can't figure out if I should use a Calculated value or a Looked Up value?  And, if so, how do I get it to auto-increment?

                 

                Or is there a better way?

                 

                Am I making this clear as mud???

                • 5. Re: Adding a Unique ID by combining other Unique ID's
                  comment

                  I am still not following you. Why can't you import the assessments into a common table and renumber them during the import?

                  • 6. Re: Adding a Unique ID by combining other Unique ID's
                    2ninerniner2

                    pademo57 wrote:

                     

                    Originally, the program was sent to each location with the understanding that these files would never be pooled together.  I know, I know, what was I thinking?

                     

                    ...

                     

                    Or is there a better way?

                     

                    One way would be to either host the database with a FileMaker hosting provider or set up FileMaker Server and host it from within the company. This way, the training data would be up to date the moment it's entered

                    • 7. Re: Adding a Unique ID by combining other Unique ID's
                      pademo57

                      To Michael Horak:

                      Because the business I am in is 24 hours. In other words, these reports come in at any hour and some have 20 or 30 reports at a time and I didn't want to have to put in a new ID every time a report is submitted.  But maybe I am looking at this from the wrong perspective.  Is it possible to have these reports downloaded to one computer and have Filemaker add a field that would autoincrement?

                       

                      To 2ninerniner2:

                      Unfortunately, the company has not seen the light of getting Filemaker Server.  So you can see how this problem is a sticky one.

                      • 8. Re: Adding a Unique ID by combining other Unique ID's
                        comment

                        pademo57 wrote:

                         

                        these reports come in at any hour

                         

                        What exactly does "come in" mean? Don't you import them into a single table?

                         

                         

                        BTW, I am still confused regarding what are you trying to prevent by this elaborate exercise. Suppose one instructor wrote up an assessment of Employee #123 in Course #45. And suppose another instructor wrote up an assessment of Employee #678 in Course #90. And suppose that both of these happen to be tagged as AssessmentID #1234. That's not something that you should wish for, but where's the harm? Do these assessments have child records related to them by AssessmentID?

                        • 9. Re: Adding a Unique ID by combining other Unique ID's
                          pademo57

                          Michael:

                          The simple answer to your question is: YES.

                           

                          I was trying to keep this simple but to understand the system I will give a more in depth answer:

                           

                          Currently the Assessment Officer uses his iPad to measure the student's ability against a training profile.  The training profile is a PDF stored on the iPad.  The Assessment officer uses the assessment table which has a portal to a Session.  On the Session Portal an Officer can pick a section from the PDF and copies it into the Session.

                           

                          So each day a  student will have an Assessment record which is made up of one of more Sessions. If there is a below standard session, when the record is dumped from the iPad on to the Windows computer at the factory location, it sends an email to Head Office.

                           

                          So Employee #123 at Location #3 on Course #45 had an Assessment Record of #2345. It tells Head Office to look up Assessment Record #2345.

                          However, because all the locations are sending emails, we've had the instance where two Assessment Records had #2345 but from different Employees and different Locations.

                           

                          All I was trying to do was see if there was a simple way to combine an Employee number and an Assessment Number that would automatically increment and have that as the primary ID.

                           

                          But I guess an even simpler solution is to have it send an email with the Employee number and the Assessment Number as well.

                          • 10. Re: Adding a Unique ID by combining other Unique ID's
                            comment

                            Couldn't you have them auto-enter a unique LocationID? Or, if you know which location the report you are importing  came from, select it during the import. It seems to me a combination of Location and AssessmentID would be unique overall, would it not?

                             

                            If it can be assumed that the same employee will not be assessed at two locations, you could also combine the EmployeeID and the AssessmentID. But in such case, why would you need a calculation to increment anything? Let the AssessmentID be an auto-entered serial number, the EmployeeID whatever it is, and use a calculation field =

                             

                            AssessmentID & "|" & EmployeeID

                             

                            as the unique identifier.

                            • 11. Re: Adding a Unique ID by combining other Unique ID's
                              RayCologon

                              pademo57 wrote:

                              So in my Assessment Table, my primary key - AssessID would contain the EmpID + AssessXX. But I can't figure out if I should use a Calculated value or a Looked Up value?  And, if so, how do I get it to auto-increment?

                               

                              Umm... You don't seem to have read post #4.

                               

                              Regards,

                              Ray

                              ------------------------------------------------

                              R J Cologon, Ph.D.

                              FileMaker Certified Developer

                              Author, FileMaker Pro 10 Bible

                              NightWing Enterprises, Melbourne, Australia

                              http://www.nightwingenterprises.com

                              ------------------------------------------------

                              • 12. Re: Adding a Unique ID by combining other Unique ID's
                                pademo57

                                To Ray:

                                Actually I did read Post #4, but when I opened the file and try to look at the fields I get a pop-up error stating "The action cannot be performed because the file is not modifiable".

                                 

                                To Michael and Ray:

                                In File | Manage | Database

                                Using Table Assessment

                                On the Field Options under the Auto-Enter tab I clicked the Serial number, Generate on Creation. Under "next value" I put ASID001, increment by 1.

                                Which works fine.

                                 

                                Now the part I can't get to work is to INCLUDE or COMBINE the EmpID in the Serial number.  Where do I include this?  In the Calculated Value? In the Looked-Up Value?

                                 

                                I'm sorry to be so pedantic but this is getting to be very frustrating.  I can combine fields in MS Access (which I have used for over 10 years) but I can't seem to figure out how to do this in FMPro.

                                • 13. Re: Adding a Unique ID by combining other Unique ID's
                                  comment

                                  pademo57 wrote:

                                   

                                  Now the part I can't get to work is to INCLUDE or COMBINE the EmpID in the Serial number.

                                   

                                  I thought that's exactly what I explained in post #10: create a new field (in the same table) of type Calculation and enter the formula =

                                   

                                  AssessmentID & "|" & EmployeeID
                                  

                                   

                                  where AssessmentID is the field that auto-enter a serial number. Set the result type to Text.

                                  .

                                  • 14. Re: Adding a Unique ID by combining other Unique ID's
                                    RayCologon

                                    pademo57 wrote:

                                     

                                    To Ray:

                                    Actually I did read Post #4, but when I opened the file and try to look at the fields I get a pop-up error stating "The action cannot be performed because the file is not modifiable".

                                     

                                    Hi pademo57,

                                     

                                    When you open a file direct from a .zip archive, it will open as read only and you'll get the "...file is not modifiable" error you mentioned.

                                     

                                    You need to unzip the file (drag it from the archive to a local drive) and open it from there. ;)

                                     

                                    Regards,

                                    Ray

                                    ------------------------------------------------

                                    R J Cologon, Ph.D.

                                    FileMaker Certified Developer

                                    Author, FileMaker Pro 10 Bible

                                    NightWing Enterprises, Melbourne, Australia

                                    http://www.nightwingenterprises.com

                                    ------------------------------------------------