You don't need a calculation for this, only a proper structure: a table of Products, and a table of Prices with fields for:
Then you can lookup the correct price from your sales table using a relationship:
Sales:: ProductID = Prices:: ProductID
Sales:: Date ≥ Prices:: FromDate
with the Prices records being sorted by FromDate, descending.
Thanks for the reply!
Your solution worked fine, but just ran into a new problem.
I am building a database where you e.g. sell hotel rooms. And prices are only valid for a certain period. So if somebody books a room from 30 March to 3 April and the price changes per 1 April how can I pick up the two prices and calculate 1 day against the old price and the rest against the new price? without having to make two bookings, one for 30 March and from 1 April to 3 April.
I am using filemaker pro 10 on a iMac using MacOS 10.5.8 The database is used on a stand alone machine only, and my filemaker experience lvl is beginner to intermediate.
Well, you could lookup the price on departure date (using another relationhip) and if it's different from the price at arrival date, do a calculation. This is assuming there can be only one price change during a booking - otherwise it can get very complicated.