Originally, I was considering multiple tables but with Paul's help, I created one new table called ‘Product Category’ with two fields called ‘product_category_code’ and ‘product_category_name’.
I entered these categories:
I generated a unique serial for each. (1-5)
I created a ‘product_category_code’ field in my ‘Purchase Order’ table and placed the field on the purchase order layout as a drop down list showing values from ‘Product Category’ table.
I suppose your purchase order table is set up with the fields you need. You will also need a products table, and, since I suppose you want more than 1 line item per purchase order, you will need a LineItems table as well. I would probably set up the LineItems table as a portal on the Purchase Order layout. You will need the minimun amount of fields below:
foreign key (from Purchase Orders)
foreign key (from Products)
You will need to create three relationships. Purchase Orders:__kp_ID -< LineItems:__kf_PO_ID
and LineItems:_kf_Product_ID >- Products:__kp_ID and, based on addition TOs,
LineItems2:Category >-< Products2:Category
On your Purchase Order layout, create a portal based on the first relationship, with the portal showing records from the LineItems table. Add the LineItems:Category field (you can make this a drop down with a custom value list), and the LineItems:_kf_Product_ID field. Make this field a dropdown list and create a new value list with the first field based on Products2:__kp_ID. The crucial part here when creating this value list is to select "Include only related values starting from:" and choose the LineItems2 table.
This should give you the filtered relationship you need based on the category you chose in the first field in each portal record.
Sorry, I should have mentioned that I have the tables created. The purchase order works fine but we have quite a few supplies that we order and I think it wise to put them in categories.
Purchase Orders>Line Items>Products
Thank you for your reply. I'm a beginner struggling with the learning curve.
On my Purchase Order layout, I created the portal based on the first relationship, with the portal showing records from the LineItems table.
I added the LineItems:Category field and made it a drop down with a custom value list based on Products2:__kp_ID.
I added the LineItems:_kf_Product_ID field and made it a dropdown list.
I created a new value list with the first field (Category) based on Products2:__kp_ID. This value list is to select "Include only related values starting from:" and choose the LineItems2 table.
Is this correct?