Multiple Customer Pricing Structures
I'm trying to create a calculation that returns an individual price from a separate price list table based upon the customer.
So, I have a Product table which contains a list of all our items with unique product codes.
I also have companies table and a separate price list table. The price list table fields look up the company name and product code from the corresponding tables and you can then enter the unique price for the individual customer.
On another separate Job table you enter all the product items etc. What I'm trying to achieve is based upon the company name in the job you enter the price which pops up is for the individual customer which is in the price list table.
I think I'm pretty close with:
If ( Company = Price Lists::Company and Price List::Item Code = ID_Products ; Price List::Sale Price )
This actually works up until I try to enter another price for a different customer for a product where the value ID_Products is the same.
So in price lists I might have:
Customer 1, Product 1, Price
Customer 2, Product 1, Price
Customer 3, product 2, Price
The above calc works for Customer 1 but when I try Customer 2 for the same product I get a black field returned. I pretty sure I know it's to do with the ID_Products being the same but can't work out how to get round it.
I hope this makes sense.
Many thanks in advance for any help.