Globally looking up a field in a script and selected items in a value list
In my database I have a table that contains employee names, IDs and fields indicating if they work in a certain department and/or are active.
There are two things I am having trouble with.
First, the user's login name is the same as the on of the fields in the employee table. In certain scripts I need the employee number of the logged in employee. Is there a way to form a globally accessable relationship that would search on the login name and return that person's id? Essentially I want to use Get(AccountName) to locate the users record in the employee table and then get that record's ID field.
Second, is there a way to create a value list using a selection criteria field in the employee list table. For example, one field is active sales reps. Is there a way to specify a value list that returns the employee name but only if the active sales rep field equals "yes"?