Report forecast&real (in & out), based on dates

[roqueredonde]

Hello,

I'm new to FM, so please excuse my question if you think it's really stupid, or should be asked in a different forum.

I'm using FM 5.5.

I have a table which could be sumup to the following fields:

- name: name of a person

- book in date: date the person should arrive

- book out date: date the person should leave

- check in date: the actual date the person arrived

- check out date: the actual date the person left

I would like to build report on a date period (range from date1 to date2), which will return something like:

Date ....... Book IN . Book OUT . Check IN . Check OUT . Present forecast . Present real

01/01/2005 ..... 2 .......... 0 .................... 1 .................... 0 .................. 2 ....................... 1

02/01/2005 ..... 5 .......... 1 .................... 6 .................... 0 .................. 6 ....................... 7

03/01/2005 ..... 3 .......... 2 .................... 4 .................... 2 .................. 7 ....................... 9

04/01/2005 ..... 1 .......... 5 .................... 0 .................... 4 .................. 3 ....................... 10

up to date2...

This could be understood as a reservation system of a camp ground (let say unlimited space ...).

And I need to know :

- what were/is/will be the forecast of in and out

- what was/is the actual check in and out

- how filled up it was/is/will be

on a daily basis, for past dates, today, future dates ...

So I'm wondering if I could do that with a simple report, or

I hope I'm clear enough...

Thanks in advance for your suggestions.

Have a good day,

[roqueredonde]

Well I guess my question is either in the wrong forum, or too incomprehensible, or too complex ...

Can't one of the experts just tell me yes, or forget about it,

hello ... any help ...

thanks

[comment]

It can certainly be done - but it is not easy (it is somewhat easier in ver.7). Certainly not a project for a beginner, if you pardon my bluntness.

You will need (at least) 2 files: BOOKINGS and REPORT. For now let's say that the Report is generated as needed, then discarded. In the REPORT file you will have 2 global date fields, gStartDate and gEndDate and a Date field. There will be a script to generate a record for every date in this range.

In the BOOKINGS file, you will have the fields you mentioned, i.e.:

BookingID

Client (preferably ClientID, with link to a separate CLIENTS file)

BookIn (date)

BookOut (date)

CheckIn (date)

CheckOut (date)

Now you can create 4 relationships from REPORT to BOOKINGS:

BookedIn:

REPORTS::Date = BOOKINGS::BookIn

BookedOut:

REPORTS::Date = BOOKINGS::BookOut

CheckedIn:

REPORTS::Date = BOOKINGS::CheckIn

CheckedOut:

REPORTS::Date = BOOKINGS::CheckOut

Next, define 4 calculation fields (result is number) in REPORT:

cBookedIn = Count ( BookedIn::BookingID )

cBookedOut = Count ( BookedOut::BookingID )

cCheckedIn = Count ( CheckedIn::BookingID )

cCheckedOut = Count ( CheckedOut::BookingID )

This takes care of the first four columns in your report. Now we come to the real problem. In the BOOKING file, we need a field that will contain all the dates in the booked range, and another field that will contain all the dates in the actual (check in-out) range. Let me pause here and ask:

a) can we set a limit to the number of days allowed between checkin and checkout?

: are you still with me?

[comment]

2 notes, mainly for myself:

1. Can a single booking be for more than one person?

2. Instead of battling with date ranges, one could look at this as a bank account, where a check-in is a deposit, and a check-out is a withdrawal. So a running balance could provide the last two columns - provided the starting balance is known.

[roqueredonde]

re a): yes the range does not have to be infinite. So a limit is fine.

re :: yes. I did have the feeling another table was needed, but I could not see how to relate them.

thanks for trying to help me !

[roqueredonde]

the other answers ...

re 1) No the booking is for one person. ie one name <==> one seat

re 2) I like this idea of running balance. And I guess I shall be able to set a starting balance (or find a way to let the user provide it ?)

thanks again

[comment]

the range does not have to be infinite. So a limit is fine

Well, actually, the range CANNOT be infinite in any case. But if it can be limited to say 10 days, then it is a rather simple matter to generate 10 dates and cut-off the tail of the calculation based on the actual range. But if the range is allowed to be more than that, it's getting complex.

[roqueredonde]

the max is more around 30 days (a month to say)

[comment]

OK, in that case I would suggest going at it this way - see attached template.

bookingsummary.zip

[roqueredonde]

Well what could I say ? This seems to produce the result Thank you !

Now, for sure, it is going to take me sometime to understand all your tricks to be able to reuse your solution in my db. But, hey, whatelse could I ask ?!

Really smart !

Thanks again !

[comment]

Most of it should be self-explanatory. I will explain one thing, though:

The repeating calculation field cDatesR must be indexed, since it is used on the right-side of a relationship. Therefore it cannot depend on a global field. Since we need to refer to a constant stack of numbers (0-34) as the index in the calculation, we have to store this information repeatedly in every record.

The solution is to create a constant relationship (constant1 = constant1). This relationship is used for looking up the stack of numbers from a global repeating field into a 'regular' repeating field, when a new record is created. So we only have to fill the stack once, in the global repeater, before any records are created. After that, the information is automatically copied into any new record.