Date range and data look-up

[Iain Peace]

Hello all,

I am very new to filemaker and am enjoying struggling through my problems with the aid of forums and, I might add, a lot of luck, but I fear that I have come to a wall and am banging my head against it and am therefore looking for help.

I have a number of related databases in my design with the main one including three field - Arrival date, departure date and total price. I have another db which includes the price relative to a specific date range. For example 1/5/05 to 01/06/05 = 230

[comment]

The answer depends on how you want to deal with price changes occuring during the stay period.

If a guest is unaffected by price changing during his/hers stay, then it's not too complex - you simply lookup the price based on the arrival date. The PriceList table has a FromDate and ListPrice. The relationship from Invoice/InvoiceLineItem is ArrivalDate = FromDate. The Price field is defined as lookup of ListPrice, if no match - lookup next lower.

If you want to split the staying period into separate price ranges - that's another story.

[Iain Peace]

Thanks for your response. The price changing during his/her stay will not come into things at all.

I have got a price list table which incorporates a From and To date and a price field. Overall there are five date ranges and three price brackets. The price ranges reflect low season, mid season, high season, mid season again and finally low season again. Therefore the prices reflect low, mid and high season.

What I do not understand is how I can tell filemaker that should the arrival date fall within one of these season date ranges, then it should select the relevant price. I'm not sure if I've understood that what you are saying resolves my problem?

I truely hope you can help.

Thanks,

Iain.

[comment]

The PriceList should not be circular. There is inflation, etc., and you should keep a record of what your prices were at any given time.

So, at the beginning of each season you need to generate 3 new records in the PriceList table (one for each price bracket - which I understand to be a kind of product). The records, apart from a serial number, need only FromDate, Bracket, and ListPrice fields. The ToDate field is redundant, since a subsequent record with a later FromDate will override.

Now, since you have a price bracket, let's make the relationship from Invoice to PriceList:

Invoice::Bracket = PriceList::Bracket

AND

Invoice::ArrivalDate >= PriceList::FromDate

Define the relationship to sort on the PriceList side by FromDate, descending.

In Invoice, make the Price field a straight lookup from PriceList::ListPrice.

[comment]

I have given this some more thought. I was bothered by the need to generate new prices every season - even when there was no change in pricing compared to the same season last year.

The problem then is how to lookup the correct season in the cycle of seasons, when one of the seasons spills over the New Year boundary?

The attached file attempts to solve these problems, with minimum information. Each season has starting date only. Each price has 'effective from' date only. Still, selecting the product (that would be bracket in your case) and entering the arrival date picks up the correct price from the price list (hopefully!).

lookupSeasonalPrice.fp7.zip

[Iain Peace]

Thanks a million, your file is nearly exactly what I'm after. However, I have something new to throw into the equation which I forgot to mention earlier.

My price list also incorporates the number of people that are in the booking party.

I have modified your attachment to reflect the actual seasonal dates and the price list dependent on the number of people within a specific season. This file is attached. I don't think that this is going to cause a problem as, as you can see, the season and price list just extend to cover additional people. However, when I have tried to do a test record, I'm not quite getting the correct answer. My first thought is perhaps this is to do with U.S vs. European date formats? I'm not sure. Can you please help?

Many thanks,

Iain.

SeasonLookup.zip

[comment]

1. The seasons table should hold only seasons (with unique season code).

Any additional parameters, such as addidtional people, define the product - not the season. So that pricelist table is a sort of a matrix, multiplying products by seasons.

2. It seems that there is only one low season, from September till June. If so, there is no need to enter it twice (I have worked extra hard on this point).

3. The Invoice looks for a price that is relevant to the date of arrival. Therefore, the price list MUST contain a price that is effective from a date BEFORE the arrival. Otherwise there is no price.

This allows you to enter price changes in advance: for example if the price for '5 people in Mid season' is to be changed effective January 2007, you can enter this information today, and it will be ignored until 2007 comes. Conversely, if in 2007 you enter an arrival date in 2005 - you will get the old price.

I have fixed the file in accordance with the above. Note that the actual prices are incorrect now. I have also fixed a stupid bug in the season code calc.

Hopefully all should work now OK.

lookupSeasonalPrice.fp7.zip

[Iain Peace]

Perfection. Thanks a million. I now need to spend some time getting my head around the formula and logic!

Thanks again,

Iain.

[Iain Peace]

A stumbling block. Thinking that I have roughly understood the formula and logic - remember, I am EXTREMELY new to Filemaker - I have tried to incorporate your solution into my database. By incorporate, I mean I have basically lifted it into my database.

Anyway, for some reason which I am struggling to understand, it does not function the same way as in your solution. I have attached here my current db for you to be able to see for yourself. As I have said, I am new to this so my db is extremely crude in its functionality. Hence the reason why I am trying to improve things.

Just for information, I rent gites (holiday homes) in the south of France. This db is intended to modernise the paper processes of the previous management. At the same time, and I don't want to appear cheeky, but if you have any other ideas for streamlining my db, that would also be worth my investigating and learning, I would be greatly appreciative to hear them.

Many thanks for all your help.

Iain.

MyDatabase.zip

[comment]

I can't really spend the time now, so just a few notes based on a cursory glance.

1. Pay attention to details such as:

- the way a relationship is sorted;

- the comparison operators used in defining the relationship's criteria;

- types of fields, incl. calculation results.

2. In Seasons, SeasonContinues is a number, and the entry in Low season is 1. It is only formatted to DISPLAY as "Yes".

On a more general note:

You have a major task in front of you, even before you get to the price auto-calc. It is going to take some time. I would suggest you put this problem aside for a moment, and concentrate on the basics of your solution, mainly data structure. IMHO, you need to think very carefully how to approach this.

I cannot give you any specific advice on this, because the process requires familiarity with your workflow. Just as an example, if I were working on this, I would ask if a specific gite is always tied to a specific price bracket. If yes, then the "product" should really be a gite - not bracket. The bracket would in this case be a property of gite. And there are hundreds of similar questions.

I think you will need more tables to organize your data properly: Guests, Gites, Reservations etc... basically a table for every type of item you track. Then you create relationships between them. For example, a Guest record should contains nothing but the details of the guest: name address and such. Every time a guest makes a reservation, a new record is created in Reservations, with a link to the guest's data ( Reservations::GuestID = Guests::GuestID). That is the basic of relational data management.

Looking up the seasonal price could make a nice add-on - but you really need to get the basic system working first.

[Iain Peace]

Hello,

Since the last note I have tried to follow your advice and have attempted to re-structure my solution into something which I believe resembles the kind of structure you had mentioned. As a result, things definitely do look and work better, and seem to be more logical, both graphically and practically.

I have attached a copy for you to see exactly where I'm at. As you can see, I now have the fields in dedicated tables to reflect their function, and I am using portals to present the data as I wish to see it. You will also see that I have continued with trying to implement the auto-pricing solution which you provided, and it is here that I am experiencing a problem that I can't understand.

The problem is that regardless of what date I specify as the arrival date, I always get the same price lookup figure. It's as if it is ignoring the season table. I have tried to follow your solution to see if I can understand what I have missed, but for the life of me I can't seem to find what I am missing.

Can you please, please help me.

Just for information, I have obviously wiped my db clean as, unfortunately, I am using my solution whilst simultaneously developing it, and therefore certain things might seem somewhat bizarre, i.e. tab order. Please excuse these. Also, some of the fields within the portal have been moved around for presentational reasons. This is only within the reservations table though.

Many thanks,

Iain.

[Iain Peace]

Sorry I forgot the attachment. Here it is.

DB.zip

[Lee Smith]

I got an empty folder?

[Iain Peace]

Do you mean the zip file is enmpty or the database? I've tried downloading the zip and it seems ok to me. I don't know what to suggest. The database is intentionally empty. By all means, feel free to add a record.

[comment]

I am a bit confused - as I told you I would be.

It seems there can be only one item per Invoice. If this is true, you don't need the table that used to be LineItems and is now named AutoPricing.

Also, can you summarize, in 3-4 sentences, the pricing structure? Say I am customer interested in placing a reservation: what choices do I have and how do they affect the price?

[Iain Peace]

In actual fact there are three possible items per invoice. The main item being the gite rental and the other two being the payment for renting a television and the payment for using a washing machine. I haven't been too concerned about the later two at this point, more that I want to get the main rental cost side working first.

In summarizing the pricing structure, it is fairly simple. We rent dependent on how many people and at what time of the year. This breaks down the pricing into a 12 box table, as demonstrated in the attached file.

I hope this helps clarify the pricing and that you can offer me some further guidance.

Many thanks,

Iain.

Pricing.zip

[comment]

Perhaps we have started off with too much sophistication. I have made a much simpler model for you - see if this makes things easier.

Note that you need to have 5 seasons per product in the pricelist.

SeasonalPrice2.fp7.zip

[Iain Peace]

That works perfectly...well nearly. I do have a slight issue which just takes the edge off of it that I hope you can help me with. If a customer makes a reservation that falls over two season time periods, I currently only display a price based upon the arrival date.

For example, their arrival date is 25/06/05 and their departure date is 10/07/05. This is one week at the mid season rate and one week at the high season rate. Currently the solution is specifying a price based upon the arrival date from the mid season. Is there a way to resolve this?

Thanks again for all your help,

Iain.

[comment]

Oh, my... I really don't know what to say. Would you like to scroll back to the beginning of this thread, and read the first question I asked about this?

[Iain Peace]

Ahh, yes. Sorry. I think the combination of my thinking about what I actually want to do whilst learning filemaker functionality and trying to develop - and simultaneously work with - a functional solution has proved to be too much for me to do at once.

I take it then that my wanting to split the staying period into separate price ranges is a bit of a headache, and I know it is certainly out of my skill range!

If we've come to the end of the road, I want to thank you for your help, comment, you've taught me a lot of things which I hadn't really thought about.

Kind regards,

Iain.

[comment]

I am not sure how much of an headache it is, but it is certainly another ballgame, so what you have now is practically useless.

I don't have the time right now to give it more thought, but in any case there are crucial details missing: I see that a booking goes from Saturday to Saturday, but is that a rigid rule? If not, how is one charged? Also, the season can change in mid-week (can it?), how does one compute the charge for that week? And so on...

IOW, you need to provide enough information to enable us to figure the charge - using paper and pencil - in any foreseeable circumstances. Only then can the process of translating it into Filemakerese begin.

[fperezl]

Hi. I am new to filemaker. I have been able to design a database for my transportation business (freight).

I have different routes, client and rates. The last (rates) change every now and then depending on the date range. All of them have different rates depending on the origin and destination of the trip. However, my client my change the rate he pays and my database calculation will be affected. Question :

What is the best way to have the rates indexed or dependent upon a date range ( from july 10-through july 20) and that the machine looks up the rate based upon the range, and i am able to keep the history.

Sounds confusing ? well, well, it is not.. can anyone help me..thanks,