Custom Price base on Customer

[mastang13]

OK I'm stump. This one is making my hair chest fall
I have to modify or create a new invoice db that can provide custom line item prices based on customer.
So I have a typical db structure Customer, Invoice, LineItem and Inventory db's all with the proper links and portals, but here is my issue.
This is a table representing items broken down by Item# and different size (A,B,C and D). Item numbers are a range for example 1000 thrugh 1999 is a range of item numbers each with different description.

Customer A
Item# A B C D
1000-1999 $2.00 $3.00 $1.50 $2.50
2000-2999 $5.00 $8.00 $2.00 $3.50
3000-3999 $6.00 $7.00 $4.00 $4.00

So when I create an Invoice for customer A and input the number 1001 in the Item# and size A I would whant the price field to be $2.00 populated automatically. For the 2nd item if I would to put item number 2500 in the item# field with size D I would want the price field to be automatically populated with $3.50 and so on. This table represents the prices for customer A but the price will vary depanding on customer.
So customer B may be
Item# A B C D
1000-1999 $1.75 $2.50 $1.00 $3.00
2000-2999 $4.00 $9.00 $3.00 $4.50
3000-3999 $6.50 $8.00 $4.50 $5.00

I just can't put it together. I've done simple invoice were the price is the same through out but this one is tough.
Any help would be appreciated!

 

[JeanW]

First you have to determine the future way to go:
will it be always a productprice per client or can it be a price per product.

If it is per client you could work with a percentage of the base productprice.

If you want to work with an amount, you have to determine the 'rules' per client.
Something like, 1 year client = ( - 10 $ ), 2 years - 12 $.

You can go from simple to very complex.
If you put the priceconvention in a separate table:
CustomerID ; ProductID ; price (which can be a calculation based upon your requirements)

Your orderline could be a calculation:
case(priceConv = Yes; priceConv; basePrice)

 

[mastang13]

I believe I found the solution to my problem using a few GetRepetition and few lookups did the job.

thanks