Sorry in advance for the length of this...
Requirements:
Employee list to contain names and indication of availability status.
Mgmt will manually change the Availability flag as employees are/are not available.
Availability is indicated by Y or N.
Several Job lists will be using this information.
In Job list, in lookup field called Assigned, users should only be able to see and select an Available employee.
Employees can work on more than one job. Availability is determined by the Employee list.
Once an item is saved, the Assigned value should not change unless the user changes it. (Historical data)
2 Lists: Employee and Jobs
Employee: ENames, Avail Filtered on Avail=Y.
Jobs: Title, Comments, Assigned Assigned=lookup on ENames
OR
Employee: ENames, Avail, AName AName=EName where Avail=Y.
Jobs:Title, Comments, Assigned Assigned is a lookup on ANames
Both scenarios create an Assigned lookup field dropdown containing of only available employees.
Works great for initial item creation.
Here is the problem though... It overwrites the current Assigned value when an existing item is edited.
Example:
Existing Item
Job:ID=1 Assigned=MARY when initial item was created.
Change Mary's availability so that MARY is longer in the Job:Assigned lookup dropdown.
Employee:Avail=N where EName=MARY
View
Job:ID=1 Assigned=MARY
Edit (Do not make any change or change any field except Assigned.)
Job:ID=1 Assigned lookup dropdown shows NONE
Save
View
Job:ID=1 Assigned="" --> Now we no longer know who is assigned to the job.
Appears the trigger for a lookup field is editing the list form, not selecting the list lookup field. This means that even a workflow to write value to another field will also have the same issue.
Is it a bug, or is it like this on purpose? Any thoughts on a work around - preferably out of the box?
Thank you in advance!
