8 Replies Latest reply on Nov 7, 2016 10:30 AM by HashirRaja

    Looking up employee name from ID number

    HashirRaja

      Hi, I have an inventory database that manages our tablet inventory.

       

      When a tablet is damaged, I create a new record of the damage. In that record, I have the following info. ex. tablet no. damage date and most importantly the employee ID.

       

      For the employee ID and names, I have a separate employee table that has just two fields. Employee ID and employee name.

       

      Now, when I create a damage record, I have to put down which employee it was by using the employee ID as it is unique.

       

      My question is, how should I display the corresponding name to that ID number within that damage record because we cannot tell easily which employee it was with just the ID.

       

      Should I put the "employee name field" from the "employee table" into the damaged record and have it just show related data? Or should I make the ID field a calculated field where once you enter an ID, it calculates the name from the employee table? That way the ID number won't show anymore as it will be replaced by the related employee name. My only concern with that is, there are some employees with duplicate names, so will this completely remove the ID number that I put in earlier?

       

      Do I need separate ID and name fields within the damage tablet table or is ID enough as I'm relating it to the employee ID field and then display the name with the field from employee table?

       

      Explaining this through writing is pretty tough! hehe.

        • 1. Re: Looking up employee name from ID number
          erolst

          Your damage table should only have an employeeID field. Once you've inserted a valid ID, you can simply display the employee's name (or an other attributes) via a relationship where

           

          Damage::employeeID = Employee::ID

           

          i.e. put a related field like Employee::cNameFull onto the layout where you edit the Damage record.

           

          If you wonder how to select the correct ID: the simplest way is to create a value list where 1. field Employee::ID, 2. field: Employee::name, show only second field. Format the Damage::employeeID field as a popup with that value list. (Here you can even use the ID field itself to display the name, e.g. you don't need to place the related name field onto the layout.)

           

          A more versatile (but slightly more complex to set up) method is a Cartesian relationship to show all Employee records in a portal, then use a script to select an employee and insert their ID.

          • 2. Re: Looking up employee name from ID number
            HashirRaja

            Hey erolst!

             

            Thanks for the reply. This is great.

             

            I was headed in that direction. I'm a little confused about your following statement in BOLD:

             

            "If you wonder how to select the correct ID: the simplest way is to create a value list where 1. field Employee::ID, 2. field: Employee::name, show only second field. Format the Damage::employeeID field as a popup with that value list. (Here you can even use the ID field itself to display the name, e.g. you don't need to place the related name field onto the layout.)"

             

            How will the ID field display the name if the ID number is in there? Does this have to do with a popup button feature?

            • 3. Re: Looking up employee name from ID number
              erolst

              Remember when you defined the value list and checked the option "Display from second field only"?

               

              That is what happens here: the popup 'looks up' the second values that correspond to its values from the first field, displays those, and that's what you select and what is displayed.

               

              Note that this feature does not work with a drop-down list; since you are also able to directly type into the field, rather than only choose a value, FileMaker needs to display the actual field value, i.e. the ID.

               

              One last thing: a value list by definition only shows unique values; that means that for employees with the same name, only the first entry will be shown (even though they all have a unique ID). If that is an issue for you, try to devise a full calculated name that you make unique (e.g. by adding the ID in parens) and use that as second field in the value list, or (as hinted at in the other post) use another means of making a selection.

              1 of 1 people found this helpful
              • 4. Re: Looking up employee name from ID number
                HashirRaja

                Hmm, thanks for the reply!

                 

                I tried what you said and it works like a charm. I noticed however that there are some employees with the same name, as you mentioned.

                 

                So I made a new calculation field that puts the name and ID together. So it is shown like this: John Smith 01123

                 

                Now, I made the value list and put the ID field in the first one, and put the calculation field in the 2nd one and clicked on the show second values only. Now I'm facing the problem where its not sorted. The sorting option is not available when I click show second value only.

                The reason I like this is because I am able to type a letter and it jumps to that part of the list.

                 

                I tried the sort by English all the way on the bottom but that did not work either.

                • 5. Re: Looking up employee name from ID number
                  philmodjunk

                  I suggest using the name to look up the ID and link your damage record to an employee by that looked up employee ID. That looks backwards, but it works and can be easily set up to manage employees with the same name as well as making it possible to set up an auto-complete value list.

                   

                  See "auto-complete value lists II" example in this file:

                   

                  Adventures In FileMaking #2 - Enhanced Value Selection

                  1 of 1 people found this helpful
                  • 6. Re: Looking up employee name from ID number
                    erolst

                    Did you make sure that the result type of the calculation field is set to 'Text'?

                    • 7. Re: Looking up employee name from ID number
                      HashirRaja

                      That is an amazing file that you linked. It has a TON of information! I will be going through it in more details. Thanks a lot!

                      • 8. Re: Looking up employee name from ID number
                        HashirRaja

                        Ah, yes it wasn't put to Text. I did that and it fixed the issue. I appreciate the help guys!