AnsweredAssumed Answered

Circular Lookup

Question asked by AndrewFoo on Aug 9, 2013
Latest reply on Aug 9, 2013 by philmodjunk


Circular Lookup


     I currently have a table (Location) that has the following fields: Location ID, Location Name, and Supervisor (Supervisor is not relevant to my question).

     I have a related table titled "Work History" (Parent) which also has a "location ID and Location name" section. The location field in work history performs a look up based on what the location ID is and grabs the location name from the "Location Table".

     I have it set-up like this because if a location's name were to change then changing all the records is a simple re-lookup.

     The problem I'm running into is with usability. As you can imagine, having to remember each location's ID can be annoying when you're entering a ton of records in. 

     My question is, is there a way to type in the location name (in the work history table) and have it look up the location ID? (Essentially the reverse of what is happening right now) then if any changes were made to the location name (location table)  the new look-up would be able to change the location name (work history table).

     Current lookup:

     Location ID --> Location name

     Ideal lookup:

     Location ID --> Location name


     Location name --> Location ID