1 Reply Latest reply on Aug 9, 2013 12:33 PM by philmodjunk

    Circular Lookup

    AndrewFoo

      Title

      Circular Lookup

      Post

           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

           AND

           Location name --> Location ID

            

        • 1. Re: Circular Lookup
          philmodjunk

               Location----<Work History

               

                    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.

               In that case, I suggest that you SHOULDN'T be using a looked up value setting. Your Work HIstory layouts can refer directly to Location Name from Location and you can discard the location name field from Work History. Then, any change to location name automatically updates the name shown on your Work History layout.

               

                    As you can imagine, having to remember each location's ID can be annoying when you're entering a ton of records in.

               And why on earth do you need to remember ANY lcoation IDs? There's a location name associated with each ID's. There are a number of different ways to set things up so that you only need refer to locations by name and yet your records remain linked by ID.

               The simplest approach (not necessarily the best) is to define a value list where the ID number from Location is specified for field 1 and the location name is specified for field 2. WHen the drop down list or pop up menu deploys, you can select by name, but the value list enters the ID number.

               For longer sets of values, it can make sense to use a method such as is found in this Demo file:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7