Dynamic Lookup field overwrites existing data (Lookup field update triggered on form edit, not on Lookup field selection) RRS feed

  • Question

  • Sorry in advance for the length of this...

    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
        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.
       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

       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

       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!

    Friday, March 1, 2019 10:07 PM