price levels: calculation or lookup?

[fileman922]

hello , i have different customers with different prices for each product, about 10 price levels for each product , no more than that.  but sometimes not all products  applies for 10 different prices ,  so if i use calculation i would need 10 fields on products table and the invoice table  price i have to set up calculation to check the price level and then set the right price.

the other option is to have on my products table one portal that stores the price levels for each product and then on the invoice price set the field as lookup . 

now my question is , which method is more faster , stable , easy to maintain?  please let me know your thoughts  or how do you deal with different price levels? 

thank you very much 

[comment]

1 hour ago, fileman922 said:

i have different customers with different prices for each product, about 10 price levels for each product

I don't understand what that means.

[bruceR]

Lookup. My guess is that price levels are associated with quantities. But we don't know that for sure yet.

Fileman922: what are "price levels"?

 

[fileman922]

im sorry for my bad english 

 

price level is the category for each customer

 

I mean, i have many customers and they are in different categories . example :  “ retailres “ , “wholesale” , ” consumer”  etc.  10 categories in total and each category has different prices for the products . 

 

example : 

 

red shirt for wholesale cost 10 usd

red shirt for retailers cost 15 usd

red shirt for consumer cost 25 usd

etc

 

so i will need 10 fields on products table to store all the prices for each category . 

 

so the calculation i have is like this . 

 

 

Case(

customer_category = “wholesale“;

products::wholesales_price

)

+

Case(

customer_category = “retailer”;

products::retailer_price

)

 

so my question is ,,, is better to setup the prices this way or is better to create a portal in products and then check the price with a lookup? im looking for performance

Edited December 28, 2015 by Lee Smith
Made the Text readable

[comment]

27 minutes ago, fileman922 said:

example : 

 

red shirt for wholesale cost 10 usd

red shirt for retailers cost 15 usd

red shirt for consumer cost 25 usd

You should have a Prices table, where each price is an individual record. In your example, the table would look something like:

ProductID    Category    Price
123         Wholesale   10.00
123         Retailer    15.00
123         Consumer    25.00

Then lookup from this table using a relationship matching on ProductID and Category.

 

 

Edited December 28, 2015 by Lee Smith
Made the text by fileman

[fileman922]

thank you very much!!