Making a second product list for price lookup
Hello everyone whats the best way second product list for a look up in a invoice type setup?
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?
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.
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.
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.
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 ANDLineItems::ProductID = Products::ProductID
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?
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.
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 tablewould be ProductPricing:
ProductId VendorName Price
Does this sound about right and this table would have a relationship to the products?
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.
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.
Retrieving data ...