11 Replies Latest reply on Dec 20, 2009 10:20 AM by comment_1

    Lookups problems making Access look easy!



      Lookups problems making Access look easy!


      I am trialing FileMaker to replace Access 2007, primarily because Access 2007 removed all the user access control features, which are needed as the database is operated off a single PC by many people.


      There are many things in FileMaker that are easier to do, but so far lookups have been a disaster.  In two days of fiddling I have yet to get one lookup to work.  The FileMaker documentation/help functions are absolutely useless.


      Does anybody know of a good tutorial somewhere on setting up lookups?  All I want is for set of tables (six of them) to retrieve names of places and people from three other tables.  This is not rocket science, but no matter how I set relationships nothing seems to work.


      If setting up lookups is so difficult, I wonder what horrors are in store in creating reports.



        • 1. Re: Lookups problems making Access look easy!

          Hello LeoB,


          I am also new to Filemaker Pro.  The first thing I had to do was get used to the terminology in Filemaker Pro.

          Lookups are called 'Value Lists' in Filemaker.  It is really pretty easy to do once you get the hang of it.


          Open your database and then from the Help menu there is a section on 'Value Lists'.


          If you are serious about Filemaker Pro I suggest you buy the book The Missing Manual.  

          There is a step by step tutorial on Value Lists in the book among many other goodies for newbies.


          Hope this helps you.



          • 2. Re: Lookups problems making Access look easy!
               I agree.  The Missing Manual is very good.  Not one to recommend books usually, but just spent 2 days in hospital and having time to kill finally got around to reading this manual.  The book I have is for File Maker 9, but 95% of it applies to version 10.  It is easy to read, but not condescending.  Clear examples and samples can be downloaded from their web site. 
            • 3. Re: Lookups problems making Access look easy!

              I hope you are out of the hospital and okay.  Have a great holiday season.



              • 4. Re: Lookups problems making Access look easy!

                "Lookups are called 'Value Lists' in Filemaker" . . .?


                This is not accurate. Although Value Lists with drop-down lists do amazing things, a "Lookup" is something different. An example would be looking up a current price from a "Prices" table. When you create an invoice, say, the Price field on your Invoice "Looks up" the current price based on a relationship and stores it in your current table. Whenever the current price changes, you change it in your "Prices" table, but it won't change the values of fields in the other table that have already looked up the price. The next time the lookup occurs, the current (new) price is looked up and stored. That way the history of the DB can remain accurate. Lookups are well worth exploring and are well explained in the documentation.

                A value List is meant to offer the user a choice. A lookup is automatic. Also the "Lookup" FUNCTION is interesting . . .



                • 5. Re: Lookups problems making Access look easy!

                  Thanks for pointing this out to me.  I just looked it up in the help.  It seems I learn something new every day about FMP.



                  • 6. Re: Lookups problems making Access look easy!



                    I have The Missing Manual and used that, too.  However, I needed it quickly so I bought the Kindle version from Amazon to view on my PC.  BIG MISTAKE!  The Kindle version is awkward to use and does not allow searches or notes.  Probably the hardcopy would be better, but we just got socked with 25" of snow so I won't be going out anywhere soon or getting any deliveries.


                    I went through all the steps in the Manual religiously, but still can't get the lookups to function.  No matter what I do, the input field in Browse mode says <Value List and even though I've deselected Value Lists, it still won't work.


                    It must have something to do with Relationships, which I still can't figure out as I've tried links between the specific fields, the ID fields, etc.


                    I even created a trial database and tried to set lookups -- no go.



                    • 7. Re: Lookups problems making Access look easy!

                      It's difficult to give specific advice without specific details. In general, a "set of tables" cannot lookup from "three other tables". A lookup is always defined for a specific field in a specific table, and it looks up data from another specific field in a related table (with some exceptions).




                      Table: Customers

                      • CustomerID

                      • Address


                      Table: Invoices

                      • InvoiceID

                      • CustomerID

                      • Address


                      If you define a relationship betwen these two tables as:


                      Customers::CustomerID = Invoices::CustomerID


                      then you can define the Address field in Invoices to lookup from Customers::Address.


                      Once you do that, any time you enter/modify the CustomerID value of an invoice, the related customer's address will be copied into the invoice's Address field.

                      • 8. Re: Lookups problems making Access look easy!



                        This is where I've tried all kinds of things.  Are the CustomerID fields you mentioned each with unique serial numbers?


                        For example, the primary table is labeled Audits and has an AuditID field with a serial number.  It has a field in it labeled Airport.  I want the Airport field to look up from a separate Airports table (which has an AirportsID field) to a field called ShortName.  The user can select ONLY an airport that's in the pop-up menu.  That same table should have similar pop-ups in other fields for AircraftOperator and Inspector.


                        Should I make the Airport field in the Audit table have a serial number?



                        • 9. Re: Lookups problems making Access look easy!

                          LeoB wrote:
                          Are the CustomerID fields you mentioned each with unique serial numbers?

                          No. The CustomerID field is unique only in the Customers table (where it serves as the primary key). So in this table, it is set to auto-enter a serial number.


                          A customer may have several invoices, and all of these would have the same CustomerID value. So in this table, the CustomerID is a foreign key, and it is defined as a regular number (or text) field, with nothing auto-entered.




                          Similarly, in your Audits table you need an AirportID field (foreign key to the Airports table). The user should select a value for this field from a value list using values from Airports::AirportID. Once you define a relationship matching:


                          Airports::AirportID = Audits::AirportID


                          you can define the Airport field in Audits to lookup from Airports::ShortName.

                          • 10. Re: Lookups problems making Access look easy!

                            OK -- finally figured out that I was supposed to be creating a Value List.  I was presuming Value Lists included only set options (like in Access) for that field and did not relate to other tables.



                            • 11. Re: Lookups problems making Access look easy!

                              Value Lists do include only set options - but not for any specific field. You define a value list first, then you can attach it to field/s.


                              But this has very little to do with lookups. A lookup will function the same way whether the field is popupated by choosing from a value list, or by entering the value manually.