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.
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?
1 of 1 people found this helpful
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.
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.
1 of 1 people found this helpful
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:
Did you make sure that the result type of the calculation field is set to 'Text'?
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!
Ah, yes it wasn't put to Text. I did that and it fixed the issue. I appreciate the help guys!