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!!