10 Replies Latest reply on Dec 23, 2010 2:56 PM by philmodjunk

    Making a second product list for price lookup

    projay

      Title

      Making a second product list for price lookup

      Post

      Hello everyone whats the best way second product list for a look up in a invoice type setup?

      Jay.

       

      purchase_order_relationship.jpg

        • 1. Re: Making a second product list for price lookup
          philmodjunk

          What would be the purpose of the 2nd Product list? (What problem will that solve for you?)

          Do you want a second table or just an additional table occurrence box in your graph that points to Products but allows you to define a different relationship?

          • 2. Re: Making a second product list for price lookup
            projay

            Well this is based on purchasing.  I have vendors with different pricelists.  So I would like to add a set of price list for 3 vendors that we regularly purchase from. So yes i would like a 2nd and even 3rd table.

            Jay.

            • 3. Re: Making a second product list for price lookup
              philmodjunk

              Yes, but that can all be kept in the same table with a vendor field used to identify the vendor. This makes for easy adjustments should you need to add or remove a vendor. With separate tables, you'd need auto-enter calculations to look up pricing from the selected vendor and adding a new vendor to your existing 3 requires adding a whole new table, plus updating these same calculations.

              Thus, a unified table gives you a simpler table structure that is also more flexible when it comes to making future adjustments.

              • 4. Re: Making a second product list for price lookup
                projay

                So a sample of the product list would be as:

                ProdItem_No     Prod_Desc     Cost         Vendor_Name

                Is this what your saying...please feel free to add anyother field I need.

                By the way thanks for the fast response.

                Jay.

                • 5. Re: Making a second product list for price lookup
                  philmodjunk

                  Instead of a Vendor Name, I'd use a VendorId number. A value list to a Table of Vendors can list a Vendor ID in column one and a vendor name in column 2. That way you select a vendor by name, but the system enters their ID for you.

                  Vendor names aren't always unique and sometimes change. That's why I'd use a number.

                  Your relationship would look like this:

                  LineItems::VendorID = Products::VendorID AND
                  LineItems::ProductID = Products::ProductID

                  • 6. Re: Making a second product list for price lookup
                    projay

                    Yes basically thats how have my tables setup.

                    The items below is for the fields in my product table:
                    ProdItem_No     Prod_Desc     Cost         Vendor_Name

                    Is this what you were meaning about adding vendor?

                    • 7. Re: Making a second product list for price lookup
                      philmodjunk

                      Yes, but the relationship from LineItems to Products in your screen shot doesn't match my last post. By including a match for the vendor field, you can match to specific records in Products by both the product number AND the specified vendor.

                      Note: you may find it useful to have a Products table where you have a single record for each product with ID, and description type info and then a ProductPricing table with vendor specific fields for Product ID, Vendor and Price. This approach makes it easier to maintain consistent descriptions and similar data for your products without having to search through and update mutliple records for the same product, but for each vendor. Your LineItems table would reference some info directly from the products table via a link to Product ID, while pricing and any other vendor specific info would be referenced from the ProductPricing table via a link that includes product and vendor ID's.

                      • 8. Re: Making a second product list for price lookup
                        projay

                        Well I think I am making a little head way here...check out original post for new diagram...

                        It soundslike I need one more table to finish this What will the relationship be for the last table
                        would be ProductPricing:

                        ProductId       VendorName    Price

                        Does this sound about right and this table would have a relationship to the products?

                        Jay.

                        • 9. Re: Making a second product list for price lookup
                          philmodjunk

                          Yes, Keep in mind that you are not limited to one box (called a table occurrence) per data source table. You can add more to get different relationships. Thus you can link Pricing to both line items and products and also have a link from line items directly to products.

                          • 10. Re: Making a second product list for price lookup
                            philmodjunk

                            BTW, I suggest defining a VendorID field in LineItems and setting it to auto-enter the Vendor ID from Purchase Order. This is need so that LineItem records can match to ProductPricing records by both VendorID and product id.