4 Replies Latest reply on Dec 19, 2013 3:47 PM by sundog

    Need a dummies guide to lookups/relookups




      New to filemaker and I'm having problems with lookups and relookups. I have a database with a few tables. One is of clients and another is of dogs. They should have the same last name. I've linked the two together via the last name (in relationships) and in the dog table, I have set it to look up the related Client last name in the dog last name field. I tried to relookup and nothing happens.


      I've tried it in a small little database with 2 tables of mens names and womens names and tried to link their names together (with the lookups on each of the "imported" names from the other table). The same thing happened; ie. nothing; there was no updating for relookups. I don't know what I'm missing or what's not working or why. (all the tables have ID#'s and foreign key numbers (for the other talbes).




        • 1. Re: Need a dummies guide to lookups/relookups

          A lookup copies the data in table A::field A into table B::field B when there's a match in the key field between the two records. But it doesn't relookup a key field because there's no valid match between the records.


          If you want it to do that, you need to establish another relationship based on a different key field and use that relationship for the lookup.


          Did I understand your problem correctly?

          • 2. Re: Need a dummies guide to lookups/relookups

            I have a relationhsip ::Name to ::Name with a lookup between them; and I can't do a relookup on them to fill in the fields of the 2nd (dog) table.

            • 3. Re: Need a dummies guide to lookups/relookups

              Don't copy data unless you need to (e.g. prices for invoices).


              If you have a table Owners, and a table Dogs, and they are related by a key (which should be an auto-generated key of some kind), then on a layout you could display the dog's "full name" using a merge field like <<dogName Owners::nameLast>>, or just put the related name field itself on the layout, or refer to the owners last name in a calculation via that relationship.


              As Mike said: if you use the name itself to establish the relationship, and change that name, you invalidate the relationship.


              This is one of the reasons why you do NOT want to use names or other real-world attributes as match fields; use a serial key you define once and that has no inherent meaning except identifying a record uniquely. Then you can change any of the parent record's attributes without breaking any links to existing child records.

              • 4. Re: Need a dummies guide to lookups/relookups

                Thanks.  I think I have it now.  I put it as a calulated value of the client last name.... and some jerk (*blush*) put the key field as _clientIDfk but the data for the clients was in the _classIDfk.... (oops)