I have a simple database with two tables one has product details and the other has retailer/gallery info. They are related by the retailer_id. The retailer table contains a field for the particular retailer's commission. I have created a query that combines the two tables such that the list price in the product table has the appropriate commission added to it and displays in the query as the retail price along with the retailer's name.
While this is all good in the query, I cannot figure out how to get it to display in a form so that the retailer name and commission show the related values; all records show only the first record's retailer info no matter what retailer_id is entered.
What I would like to do on the table is show or enter new product details such that when I choose a retailer_id the retail price and retailer name info is sourced from the retailer table.
Also, if I make my form from the query, the data is displayed correctly but I cannot add new records or change existing records.
It is an easy thing to do in filemaker but it doesn't seem so straight forward in Base.
Thanks in Advance,