1 Reply Latest reply on Aug 11, 2009 8:38 AM by philmodjunk

    Relationships and Look-ups



      Relationships and Look-ups


      I have some challenges with correct look-ups.


      I have created a table "suppliers and products", and then created fields like: Supplier and Products (corresponding products available from that supplier), and filled out the details for all suppliers and products.


      I have then created another table "customers", and there created fields like: Customer Type, Customer Names, Customer Address, Phone number etc., and filled out the details for customers / records.


      Then a created a table "sales tracker" with fields like: Supplier, Products, Customer Type, Customer Names, Customer Address, Phone Number etc.


      What i would like to happen is that when creating a new record in the table/template "Sales Tracker" that one can choose the desired "Supplier", then under "Products" the available products from that Supplier, then choose "Customer Type" and under "Customer Names" coose the available customers under that Type of Customers upon which (after selecting the customer name) the Customer Address, Phone number etc gets looked up and filled in automatically.


      I have managed to get the Supplier => Products relationship / value list working, the Customer Type => Customer Name relationship / value list is also working. However the Customer Address, Phone number etc are always those of the 1st Customer details under the available Customer names. Say i choose the 2nd customer in the pop-up list, after i have chosen desired customer type, the address, contact numbers etc are those of the customer on top / 1st on the available pop-up customer names. If i change the customer type, the available customer name pop-up list changes correctly, however same again if i choose say customer 13 on that pop-up list, the address, contact numbers etc is looked up from the 1st customer on that pop-list.


      What am i doing wrong? can anyone help me?


      thanks very much!!


        • 1. Re: Relationships and Look-ups

          I recommend you take a fresh look at your table/field design.


          If you have data stored in one table such as "customers", you don't have to copy(lookup) the data from that table into a second table such as "Sales Tracker".


          You should have auto-entered serial number fields for each table so that you can set them up in the following relationships:


          Suppliers and Products::SupplierID = Sales Tracker::SupplierID

          Customers::CustomerID = Sales Tracker::CustomerID


          I think you have other relationships for your conditional value lists and these appear to be working for you so you shouldn't need to change them, just add the above relationships if you don't have them already. You don't want to use customer and supplier names for these relationships because people and businesses change their names from time to time and that would mess up your relationships if this should happen.


          If you want to add customer fields to a layout based on Sales Tracker, simply add the fields from the Customer table to your layout. In like manner, you can add fields from Suppliers and Products. In each case the relationship will control which record in the related table supplies the information you then see on the layout.


          See if you can get that working and then, if you're interested, we can look at whether you need an additional table to list each purchased item in a portal as part of an invoice based on your sales tracker table.