A relationship that links a Category field in one table to a Category field in Products should look like this when you double click the relationship line in Manage | Database | Relationships:
Table1::category = Products::Category
If you then place a portal to Products on the Table1 layout and select a category in Table1::category, this should then show all products with that category in the portal. And Go to related records from Table1 could bring up this set of products records as a found set on a Products layout.
Is that what you want to do?
There's a good chance that you need more than one relationship between table1 and products so you may need to create a new Tutorial: What are Table Occurrences? of products in order to get this to work without affecting other relationships.
Thanks for your quick reply.
What I'm trying to do is have a field that has say 10 category options that drop down when I enter a new product. I can then select the appropriate category and move on.
I had originally done this with a value field but when I went to print out a list I didn't want to use the alphabetical category sort. I therefore decided to add a number to the category name and then sort by the number
It is not clear to me what you are trying to do.
I can then select the appropriate category and move on.
That should be simply a matter of selecting a Category from the Products::Category field after formatting it to use a value list of your category values.
I therefore decided to add a number to the category name and then sort by the number
My best guess here is that you want to select a category from the list, but have this enter a number that you use for sorting.
If so, your relationship would be:
Categories::CategoryNumber = Products::CategoryNumber
You'd format Products::CategoryNumber with a "use values from a field" value list where CategoryNumber is selected for field 1 and CategoryName is specified for field 2. This allows you to select a category by name, but the value list enters the name.
And there are a number of different options for displaying the CategoryName field from Categories on your Products layout. Your field can be formatted as a pop up menu with the "show only values from second field" specified for the value list. Or you can add the Categories::CategoryName field to your Products layout and it will display the needed name.
Maybe I'm trying to do too much in one step. What I'd like to do first is enter a Category for a product in my Products table by creating a relationship between the Products table and the Category table that currently has 10 different categories in.
I've linked the tables by the category field.
I've then tried to set up a lookup relationship in the product table hoping that the drop down menu would show the possible category names so that one could be selected but I've stalled at this stage.
There was a typo in my last post:
This allows you to select a category by name, but the value list enters the number.
You've then posted:
What I'd like to do first is enter a Category for a product in my Products table by creating a relationship between the Products table and the Category table that currently has 10 different categories in.
My previous post describes exactly how to do both: You assign categories by entering the Category Number which you then use for sorting, but use the number based relationship to show the category name from the categories table whenever you need to show the name. This can be done simply by adding the category Name field from categories to your product layout.
Thanks for being so helpful. I'm going to give it a try shortly.