1 Reply Latest reply on Jan 3, 2013 7:13 AM by philmodjunk






           I have created in one file 3 layouts. Contract + Customer + Supplier

           1st  layout - Contract - contains the description of the contract + supplier + customer

           - I want to retrieve the supplier information out of the Supplier Layout (info is in the 2nd layout)

           - I want to retrieve the customer information (when the contract is sold) out of the Customer Layout. (info is in the 3rd layout)


           I have followed the information in "the missing manual" page 213 but without succes.

           What have I done wrong...

           Rgds Tom (The Netherlands)

        • 1. Re: Lookups

               Since I don't have a copy of that book "page 213" meand nothing to me.

               Be careful of the term "layout". You can have many layouts that all display data from the same underlying table.

               You can do with what you describe with two tables or three. Supplier anc customer info could be stored in the same table. If there is any chance that a supplier could also be a customer, putting both in the same table could make for much more efficient management of your data.

               Whether you have 3 tables or 2, your relationships should be:


               Customer::__pkCustomerID = Contract::_fkCustomerID
               Supplier::__pkSupplierID = Contract::_fkSupplierID

               See the first post of: Common Forum Relationship and Field Notations Explained if this notation is unfamiliar to you.

               With these relationships, you can format _fkCustomerID and _fkSupplierID on your Customer based layout as drop down lists or pop up menus for selecting a customer and a supplier. (The values lists can display ID's and Names, but enters the ID number when you make a selection.)

               There are then two basic options for "looking up" data from the supplier and customer tables. Both use a relationship such as the above, but one option physically copies information from the source table and the other simply displays the current value of the specified record in the source table. Both approaches have their uses so you'll need to decide which best fits your business needs.

               See this thread for a description of both methods: Auto Fill