8 Replies Latest reply on Mar 11, 2013 6:34 AM by jbrown

    Employee ID Numbers


      Hey filemaker community.

      I'm reaching out to you to ask what do you do to create employee IDs. Currently at my system of three schools we have little to no HR system. for all 100 employees they have file folders and emails and excel documents to track information about the people who work there. Pretty sad state, to say the least.

      I'm working with the HR department (about half of 3 people doing that job) and putting it into the overall solution for the region that I have developed.

      My question is on Employee IDs. Right now we really don't have employee ID numbers. The payroll person (half of one of the people doing HR) has some in her payroll system, but i think she doesn't do much with them. It seems to make sense to create new ones for everyone that will be permanent.

      So questions:

      1. What is your typical length?

      2. Do you use employee info in the ID number (letters in name, birthdate, etc)?

      3. Do you let filemaker assign them or does the HR person enter them?

      4. Should ID numbers be dependent on the school that they are located? One school has a code of 426. Would it be wise to put that in the employee ID - like 426-4959?



      I suppose I could just make up all the rules for the Employee ID numbers: Here are some rules I think about. Do you have any others to add?

      1. An ID length of 4 digits, using only numbers. This gives me 9,999 possible numbers, well above the maximum of the number of employees our system could have.

      2. Completely unique for a person.

      3. Not dependent on the school (often teachers switch schools).

      4. I'd like FM to generate them. Is that possible to create a 4-digit uniqe ID Number. I know the Get(UUID) function can do it but that ID number is too long for this instance.


      .ANother question:

      Would it make sense to have this ID number be the login account number for the FileMaker database? Right now people log in like this "jbrown". The log in script detects which school they belong to and assigns global variables based on that, I realize 'jbrown' is not a good login account ID at all, hence the reason for this post.


      Thanks. I'd appreciate your help and opinion on this.



        • 1. Re: Employee ID Numbers

          We use the employee's initials and then a 3-digit number, for example:



          To generate the above from my name, (Brian Curran) we also use an auto-enter serial number for each employee record.

          Left ( NameFirst; 1) & Left ( NameLast; 1) & If ( StartingOrder <10 ; "00" ; "0" ) & StartingOrder


          I hope that helps...


          • 2. Re: Employee ID Numbers

            The debate between semantically meaningful ("intelligent") vs. meaningless ID numbering schemes is alive and well, and I doubt it will ever be settled. When I do build a semantic key, here are some things I try to stick to:


            1. There's a non-semantic numeric portion of the ID. This numeric portion should be unique by itself. Any other information is strictly informative. This enables you to break guideline 4 in service of guideline 3. It also means a user should successfully be able to do a find even if they only have the numeric component.

            2. Don't impose a limit on how big the non-semantic number can be. Plan for growth. You can use delimiters (hyphens are a favorite) or different character types (for example, perhaps all semantic content must use alphabetical characters) to decompose the key.

            3. Don't put any semantic information in the key unless it facilitates how you actually organize stuff.

            4. Resist including information that could plausibly change — name, initials, gender, date of hire (rehires), role (cross hires), etc.

            5. Do not use semantic user-facing keys for the underlying relational keys in your database tables. This may take some minor sleight of hand in the user interface.


            "jbrown" seems like a perfectly good login account name. Why don't you think so? If it works for an email address, it works for a login account.

            • 3. Re: Employee ID Numbers

              What if another jbrown gets hired at our school, or our region? My name is Jeremy Brown, a pretty damn common name. What is James brown gets hired there. Would i do jbrown2? For the log in? That's my only wonder.   I should talk to the IT guys and ask them how they'd handle this.


              I'd like the system to have the same log in as their email, which is currently what happens. Unfortunately right now that 'jbrown' log in is also the key for any data tables. When I create a record in the behavior table, the 'jbrown' shows up on the auto-create createdby field. It seems as if that should be 1. unique and 2. the same unique number as my account name and my name in the employee personell records.



              • 4. Re: Employee ID Numbers

                I agree that it's useful to document login account names and email addresses in association with employee personnel records, but I don't necessarily agree that the same unique ID should be used to identify the same entity for different purposes. (See guideline 5 that I mentioned above.) Including different pieces of information in a semantic key is useful to the extent that personnel records are organized according to the information included in the key, and just plain awkward otherwise. Would you prefer that my email address be jbante@org.com, or jtb198412fmit@org.com? On the other hand, if you don't really need the employee ID for HR purposes to be useful for organization, why not just use a serial number or the account name assigned by IT?


                As far as "jbrown2" is concerned, I have been involved with organizations that used that practice. It's not a pretty solution, but it's not a pretty problem. You and James Brown might instead be "jeremybrown" and "jamesbrown," respectively, but what do you resort to when there's another Jeremy Brown hired? I'll take "jbante2" over "jtb198412fmit" any day.

                • 5. Re: Employee ID Numbers

                  Hi Jeremy,


                  You have to distinguish between the real ID as used for relations and the "short" human usable access code like "jb001". These must be kept separately as they almost always can NOT be used for both purposes at the same time.


                  The use of IDs is described in detail at <http://fmdiff.com/fm/serialnumber.html>. As these IDs should never be visible to the user, you can generate the "short" version with any method you like, provided it remains unique for the entity.


                  Since there is no safe haven to store the highest used number - with or without the alphanumeric part - you have to provide this part yourself, taking into account that used "shorts" should not be reused.



                  • 6. Re: Employee ID Numbers

                    I would use the school number say 426 and just  a four digit auto serial text field so peole are 426-0001 426-0002 and so forth you can do  self join on the school number if you want it calc the four digit number sequentially within each school. As others have said use the number for tracking the employee but don't use it for your relationalshios or anythng like that you'll still need a real primary key for each record that you use for that purpose like EMP0001 or something like that.

                    • 7. Re: Employee ID Numbers

                      I use Get(UUID) as the primary key, but names similar to "jbrown" as the account login.  After logging in, the account name is used to determine appropriate global values - i.e. an exact match to the account name is found in the USERS table off screen (e.g. the same value will be located in a text field in the USER table, such as "AccName"), and global values are set based on the found record.


                      You can change the "AccName" field value down the track without breaking your other relationships.  The main criteria are to ensure that the "AccName" field value is defined to be unique and that a corresponding filemaker account name is created.  The "AccName" field is NOT used in relationships. 



                      • 8. Re: Employee ID Numbers

                        Thanks all for your advice. I need to make some changes in things. I think it can be easily done however.

                        Thanks for your help.