Define an unstored calculation field, cAccountName as Get ( AccountName )
Use it in a relationship like this:
YourTable::cAccountName = Employees::Accountname
and then, from the context of YourTable, Employees::__pkEmployeeID would refer to the primary key for that employee.
This is called a conditional value list.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Regarding the first, if I want to use this lookup in different scripts that refer to different tables the I will create the cAccountName in each table and create a realationship in each table? I was hoping that there was a way to make a "global" lookup that could be used from any table.
On the second, thanks for the correct term. I wasn't searching on conditional.
You could define such relationships, but I'd just have a startup script use the single relationship to copy the ID into a global field or variable.
Great suggestion about including it in the startup script. I just hadn't thought about that.