Pulling through volume Price

[garyaj]

Hi

This may be an obvious question but what I am trying to do is up a level from my current skills and is driving me mad!

I have a database called PRODUCTS. This contains fields for a description etc. Within this is a portal window built with a relationship, using PRODUCTID, that shows records detailing volume breaks for a product and the relevant pricing. For example, up to 10 units cost £5 each, between 11 and 20 cost £4. each and so on.

I have another database called CUSTOMER that features a portal with a relationship to another file using customer ID. In the portal there is a drop-down value list from which a PRODUCTID is selected. This then autofills the product description etc.

Also in the portal row is a field for quantity and price. What I am trying to achieve is to get the price field autofilled based on the volume selected.

All help appreciated.

[comment]

See if this helps:

http://www.fmforums.com/forum/showpost.php?post/294096/

[garyaj]

Hi

Thanks for this. I think i have done this. I have separate records for each product, within a PRICELIST file, where the break point is volume.

Where I am struggling is on the lookup. For example, product A has a price of £1 for all quantities up to and including 10 and £2 for aboce this.

My relationship is definitely built around PRODUCTID but it is only returning the first number in the table.

[comment]

I'd suggest you move your quantity breaks to use the lower limit, i.e. product A has a price of £1 for quantities from 0 and up, £2 for quantities from 11 and up, and so on (shouldn't it get cheaper as the quantity goes up?). It can work in the other direction too, but then you always have *some* upper limit, and no defined price for quantities above that.

Once you have that, define your relationship as:

SaleItems::ProductID = PriceList::ProductID

AND

SaleItems::Quantity ≥ PriceList::FromQuantity

with PriceList records sorted by FromQuantity, descending.

Finally, define the Price field in SaleItems to lookup the value from the Price field in PriceList.

[garyaj]

Thanks. I will try this and let you know.