Schedule summary?

[cknudsen01]

Hi everyone!

I've been struggling with a problem that I'm not sure can be solved given Filemaker's limitations. I've been working on a solution which includes a schedule database. I am trying to figure out the best method of summarizing a client's schedule.

Currently there are 3 related databases:

Main

Appointments

Calendar

The appointments database stores the dates as individual records. The calendar database displays these dates in a series of portals in a calendar format. (i.e. 28 different layouts depending on the month)

What I would like to be able to do is summarize a list of the related dates in a single calc. field in the main database. For example:

A client goes on vacation from May 15-20. The dates exist as individual records in the appointment database (May 15, 16, 17..etc). They display fine in the portals in the calendar database. How can I create a calc. field in the main database that could summarize these records?

A portal would be easy enough, but that would display the dates as individual records. Ideally it would be great if they could simply appear as "May 15 - 20".

If anyone has any ideas I would be grateful. I feel like my head is going to explode.

Cheers,

Christian Knudsen

Toronto, Ontario

[cjaeger]

if in the Main db you have something like an event ID, you can get the start & End dated with this calc:

duration display= Month(appointmens::date) & " " & Day (appointmens::date) & .. & " -" Month(Last(appointmens::date)) & Day(last((appointmens::date)) &...

Now add a couple of case statements to check wheter day, month, year are identical & adjust the disply accordingly:

Month(appointmens::date) & " " & & Day (appointmens::date) &

if(Year(Last(appointmens::date))!=Year(appointmens::date);Year(appointmens::date),"") & ....

Instead of just the related field and Last() you could use Min() and Max(), which would be independend of portal sort order.

[cknudsen01]

Thanks Christian!

It works! I'm going to try to put in a loop into the script so that I can display individual dates as well as a range.

Thanks a million, I couldn't get my head wrapped around that one.

Cheers,

Christian Knudsen