4 Replies Latest reply on Mar 17, 2010 11:00 PM by comment_1

# Date calculations and finding a price

### Title

Date calculations and finding a price

### Post

Hi, I am working on the following and need some help.

I have a product in my database that has a price of lets say 10, but this price is only valid from 1Jan09 to 31Jul09 and the price for 1Aug09 to 31Dec09 is 12.

Product, valid day from 1Jan09 to 31Jul09, 10

Product, valid day from 1Aug09 to 31Dec09, 12

How can i make a function based on the sale date (variable) to select the correct price. So if i sell the product after 1Aug it will take the 12 as the price.

Cheers,

• ###### 1. Re: Date calculations and finding a price

You don't need a calculation for this, only a proper structure: a table of Products, and a table of Prices with fields for:

• ProductID

• FromDate

• Price

Then you can lookup the correct price from your sales table using a relationship:

Sales:: ProductID = Prices:: ProductID

AND

Sales:: Date ≥ Prices:: FromDate

with the Prices records being sorted by FromDate, descending.

• ###### 3. Re: Date calculations and finding a price

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.

many thanks

Arno

• ###### 4. Re: Date calculations and finding a price

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.