4 Replies Latest reply on Jul 7, 2012 8:13 AM by nella

    Product Category List

    nella

      Hi all,

      I am trying to create a product category list for a purchase order and could use some help.

      The products have been separated into 5 categories.

      1. General
      2. Laminates
      3. Pigment
      4. Maintenance
      5. Resin

      The goal is to

      • Create New Purchase Order
      • Choose Product Category from a drop list
      • Enter products from that category in the purchase order
        • 1. Re: Product Category List
          nella

          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:

          1. General
          2. Laminates
          3. Maintenance
          4. Pigment
          5. Resin

          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.

          Value List.jpg

          • 2. Re: Product Category List
            JeffPenner

            Hi Nella,

             

            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:

             

            Purchase Orders:

            key field

             

            Line Items:

            key field

            foreign key (from Purchase Orders)

            foreign key (from Products)

            category

             

            Products:

            key field

            category

             

             

            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.

             

            HTH!

             

            Jeff Penner

            • 3. Re: Product Category List
              nella

              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

              • 4. Re: Product Category List
                nella

                Hi Jeff,

                Thank you for your reply. I'm a beginner struggling with the learning curve.

                Relate.jpg

                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.

                Cat-List.jpg

                Is this correct?