6 Replies Latest reply on Jul 30, 2014 10:25 AM by EwanMacGregor

    Linked tables

    EwanMacGregor

      Title

      Linked tables

      Post

           I've set up a table for the products we sell. Their description, size, colour cost etc. I am trying to set up a look up relationship for the category that I want the product to be filed under. I am trying to set up a lookup relationship between the product field and the category field but however I try to do it I can't seem to get it to work.

           In the product table I'ce set up a field called category. It's a text field and I've set it up to lookup the Category in the Category table. I've joined the category fields in each table but this doesn't work. When I've done this the category field in the product table won't allow the cursor to work in this field.

           Has anyone any suggestions what I have done wrong?

        • 1. Re: Linked tables
          philmodjunk

               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.

          • 2. Re: Linked tables
            EwanMacGregor

                 Hi,

                 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

            • 3. Re: Linked tables
              philmodjunk

                   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.

                    

              • 4. Re: Linked tables
                EwanMacGregor

                     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.

                • 5. Re: Linked tables
                  philmodjunk

                       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.

                  • 6. Re: Linked tables
                    EwanMacGregor

                         Thanks for being so helpful. I'm going to give it a try shortly.