2 Replies Latest reply on Mar 31, 2011 4:19 PM by RafaelRomano

    Variations between pricelists, product codes and prices.



      Variations between pricelists, product codes and prices.


      Hi guys,

      I'm working on a table that should bring PRICES according to the CODES of the product and I also have 6 DIFERENT PRICELISTS for the same CODES (products), according to diferent costumers and the price variation hasn't a logic for discount, for ex.

      I have a file a database called PRODUCTS where the itens are all listed with their codes, descriptions and their 6 diferent prices; then I have the database SALES where I import each product detail from it's CODE and where I have a Field with names for each PRICELIST.

      I'm working on a Case description, but the problem is that in the end it doesn't link to the CODE anymore to set the right PRICELIST value based on the product.

      Case (

      FIELD pricelist name = "A" ; INVENTORY::PRICES A ;
      FIELD pricelist name = "B" ; INVENTORY::PRICES B ;
      FIELD pricelist name = "C" ; INVENTORY::PRICES C ;
      FIELD pricelist name = "D" ; INVENTORY::PRICES D ;
      FIELD pricelist name = "E" ; INVENTORY::PRICES E ;
      FIELD pricelist name = "F" ; INVENTORY::PRICES F ;
      "" )

      Would you have a better idea?



        • 1. Re: Variations between pricelists, product codes and prices.

          Typical Invoicing systems have this structure. Look Familiar?


          I'm guessing that LineItems in this example might be SALES in your database.

          You have two options here, you can use looked up values to copy all 6 of the prices into separate fields in the sales table and then your case function can be defined in the Sales table to select the appropriate price or you can modify the relationship to include a match by the second pair of fields so that the correct price is looked up when both a code and a pricelist name is entered/selected in your sales table.

          With this second option, modify your database design to look like this:


          LineItems::Code = PriceList::Code AND
          LineItems::pricelist name = PriceLists::pricelist name

          Products::Code = PriceList::Code

          • 2. Re: Variations between pricelists, product codes and prices.

            Perfect! That was very helpful, thx a lot