7 Replies Latest reply on Mar 26, 2013 12:33 AM by trevor4370

    Looked-up Value



      Looked-up Value


           I am a noob and am having trouble with Looked-up values (or maybe I should be using something else).

           A simple example as follows:

      TABLE 1

           - id

           - Town

           - Postcode

      TABLE 2

           - Name

           - Address

           - Phone

           - Town

           - Postcode

           Relationship between ID's

           TABLE 1 has a list of towns and postcodes for each town. When I go to TABLE 2 and select a town from the Dropdown list (Manage Value List - Town in TABLE 2), I would like the Post Code for that town to be automatically displayed. I know this should be simple but I cannot get my head around it - I have spent many hours trying.

           I have tried

           - Creating a relationship between the Towns

           - Town in TABLE 2 - Looked-up Value > Starting with Table = TABLE 2, Lookup from related table = TABLE 1, COPY value from field = Post code.

           - Even tried Lookup on Town.

           Using Filemaker Pro 12 Advanced


        • 1. Re: Looked-up Value

               Can someone please point me in the right direction - where to look this up

          • 2. Re: Looked-up Value

                 Won't you have multiple postcodes for a given town? (That's how it works in the US, anyway)

                 Is is it also possible to have two different Towns with the same name? (I grew up in a small town, and happen to know that there are several towns across the country with the same name, but in different states.)

                 Whatever you desing here will have to handle such complications if they apply to your part of the world.

                 One approach is to use a conditional value list where selecting a town or a town and a state, results in a value list for the postcode field conditionally filtering down to just the postcodes for that one town.

            • 3. Re: Looked-up Value

                   This was only an example, I created this small database to workout how to do lookups. I was actually going to use what I learnt in a large database invoicing system.

              • 4. Re: Looked-up Value

                     Yet the same issues can apply in some circumstances.

                     Here's a thread that discusses two different approaches. Both can have their uses in the same Invoicing situation so you have to understand how they work and why one might be the better choice before you implement one method or the other:

                Auto Fill

                • 5. Re: Looked-up Value

                       Yes, Yes ,Yes - thank you very much for this. I now have my head around it. In my test the lookup was based on the town as a value list, but I neded to value list the ID (the relationship) and then look-up the other fileds I needed.

                       Thank you again

                  • 6. Re: Looked-up Value

                         For looking up data, sometimes basing the relationship on a name makes for nicer options that using the ID. But if you go that route extra care has to be taken to handle the possibility of duplicate values and name changes.

                         Here's a demo file with an auto-complete enabled value list of names that looks up an ID so as to link the current record to a related table by ID:

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


                    • 7. Re: Looked-up Value

                           Thank you again - this is very interesting. I was looking at a relational tutorial on YouTube along these lines, but little was explained. This seems to tie the lose ends together for me.

                           Thanks heaps for this