Calendar/Schedule Database. Will 7 ease my pain?

[RWX]

I'm currently exploring FM7 and must say that I'm *VERY* impressed. I definitely plan on migrating my current project to 7 in the near future. What I'm wondering now is if FM7 will help me with a piece of my current project. I need to create a rather simple schedule database. I don't need group capabilities, nor do I need the ability for one user to schedule another user's appointments. I need a basic time-grid in week view showing that user's appointments.

Am I still stuck writing hundreds of relationships to populate the time-grid (time-grid in one file or table, appointments in another), or does FM7 introduce some new tools/tricks that might make this process easier and more efficient?

Also, is it no longer possible to copy/duplicate a relationship in FM7?

Thanks everyone!

-Rob

Version: v7.x

Platform: Mac OS X Panther

[BruceJ]

1. If you're going to use FM7, don't do the work in FM6/5 and then convert, start out in 7 so you can take advantage of the features that will reduce your overhead.

2. No calandar features in FM7 like Mobile has (bummed). I can't think of any specific features I've found that would suddenly make doing that a whiz compared to previous versions. However, the way tables and realtionships are created may speed the process when combined with the ability to pull realtionships accross more than one file (that's awsome).

3. In my opinion, the variables you can add to realtionships is the biggest enhancement in this version. This saves so much from having to concate fields or having to create the same relationship in multiple files. Duplicatiing relationships isn't as necessary as it was in previous versions. However, I've discovered that you cann't duplicate tables either... this has led to me rethinking my designs and I now realize that my starter databases were full of all sorts of unnecessary overhead.

[Fenton]

Hundreds of relationships? I have pretty complete "time-grid" and "calendar" files, and I don't have all that many. For just a week view, you need 7 (duh).

But the individual user should be in a global field, concatenated with a fixed "day-of-week" (1-7). You certainly don't want to create a relationship for each user*7. Is that where the hundreds is coming from?

A Calendar month view needs up to 37 relationships in v.6, to use scripts with the possible day squares. In FileMaker 7 you just need 1, because you can pass a parameter to the script, using the calculation that is producing the day number.

My "time-grid" file has a record for each time slot of 1 day, depending on what span and interval you want. The is the ONLY real data in the file, easily replaced via a loop if you want different slots.

Everything else is globals and a few relationships back to the real time data file; global date, global people (pulled from a ValueListItems() from a People file). The actual "schedule" data file is a simple line items file, date, personID, time begin, time end. There is a multi-line calc producing the times used. It's a large calc, but it only produces a small amount of data.

Rather than using a multi-line calc for the times slots used, you could alternatively use another line items line items file, with 1 record/time slot per activity-person. Both methods are valid, the latter requires a little more scripting and control of editing.

[Ocean West]

Are you trying to create a gant chart?

Version: v7.x

Platform: Mac OS X Panther

[Joe King]

The FileMaker, Inc sales team has a real nice demo using a single relationship like mentioned above. I'm guessing that we attended similat events. I thought that demo would surface now that FMP 7 is shipping but I haven't seen it yet...

Anyone?

[RWX]

Thanks everyone for your advice and suggestions.

Fenton, of course I realize that I need 7 relationships for a week view :-). The hundreds # is coming from breaking down each day into 15 or 30 minute intervals, for example. Maybe I'm going about this all wrong, but if someone has an appointment scheduled for 3:15pm that lasts one hour, I want it to appear at that location on the grid. How else do I accomplish this other than creating a relationship for each block of time on the grid itself?

To answer your question, Stephen, no, I'm not trying to create a GANT chart. I just want to display appointments in a week view much like a standard scheduling application would. I realize that with just seven relationships, I can list the appointments for each day, but I want to take it a step farther and actually have the appointments appear in the proper location on the time-grid.

Another thought I had was to create the time-grid with a single repeating field for each day of the week. When viewing the calendar, the appointment data for that week would be loaded into the correct repeating field locations via a script, thus eliminating the need for all those relationships (that I may or may not need anyway...Fenton???). Sound thinking? Or madness?

Thanks again to everyone for all your help!!!

-Rob

[Fenton]

You going in the right direction with the repeating field, except it's not a repeating field.

The "time-grid" file is a separate file (or table :-) from the line item time data. You go there to "view" the grid. Any creation or editing is scripted, popping up a little window in the line items data file, pre-filled with at least the time.

The record-field data in the time-grid file is a time, like 7:15. The rest is globals

It's concatenated to get a unstored key: _gDate & " " & Time & " " & _gPersonID

_gDate and _gPersonID are changeable globals.

In the case of a Week view, you would have 7 _cgDate calcs, for days of week, based on any given _gDate.

We've got the calcs to produce a week of dates, just ask.

You can see how this would match a related entry in the line items file (table), but only once.

To match a range you need a range, which in my case was a big calc resulting in a small multi-line.

An alternative is to script the creation, in another file (table) of a separate record for each slot.

This alternative would be cleaner in 7, it would just be another table. You have to trap the user for creation and editing (I haven't heard how Loop/pause scripts work in 7), as you have to create and destroy (editing) several records.

The 1st multi-line method doesn't have to do this, but you'd still want to script the window, for conflict tests if nothing else.

Another difference between them is how multiple lines of text about an appt. can be displayed in the time-grid. The multi-line method requires a separate relationship for each line, overlaid. 3 is about the limit, as speed drops with each.

With the "separate record for each" method, you only need the 1 relationship to show lots of lines on the grid. But you have to script the creation/editing of those lines; each line is a separate record. This would require busting up text into fixed line widths of words, doable but... If you've got a lot to show, this is the method. It's also a faster display, less relationships.

Ex: Appt. from 7:15 to 8:00,

7:15

7:30

7:45

Notice no 8:00, as you don't highlight the end time, just the start time; because another appt. starting at 8:00 would not be in conflict. The calc end time is the end time - interval.

God knows if it can be done with <> relationship (TO) keys in 7

[RWX]

I think I follow what you're saying. However, why wouldn't you just create relationships for each spot on the time-grid? I realize this would mean writing 100+ relationships, but then the grid would populate automatically with no scripts and no creation/destruction of records. Just curious...

Thanks!

-Rob

[Fenton]

The time-grid does populate automatically. It has to, there's no record creation/deletion in that file, other than an initial Loop to create the time slots for 1 day.

It is simply a "mirror" display file, for the appointment line items, capable of showing "empty" time slots, because they are not empty in this file, they are a time.

It is the concatenation of this "regular" field containing a time entry with global fields holding the date and person which cause each row of a "day" column to auto-calculate differently.

It is unusual to mix mostly global fields with 1 changing regular time field, but time-grid files are specialized files. The "columns" are not portals. They don't need to be. The records are times for 1 day, the other data are related fields, using a different relationship for each "column" (7 for a week view).

All the scripting, record creation/destruction happens in the line item file(s), where Date, Time(s) and PersonID are regular files, also concatenated so they'll be the right-hand match of the time-grid calculation.

Here's a screenshot. It only shows a few days (I did 5). Notice I only colored the actual times. There are other ways to do this.

[Keefers]

Lets see. I believe we are thinking about this in the wrong way. Even pre 7 products should be able to do this through a concatenated join. I don't have time to validate this but I'll give you guys the idea and you can run with.

In the table that contains all of the appointments you need to create records that represent every time period in a day (Number of records = (24 * 4)). This table also contains all of your actual appointments. You'll need to protect these records from deletion by your users. The trick will be to figure out when not to display these 'spacer' records. In 7.0 you can use 1 relationship starting with the date of interest. Just specify the correct start and end rows in the Portal Setup Dialog. The left hand key should start from a specific date and it should show these extra records when there isn't data to show in that slot (in 7.0 can be a calculated global). That way you won't need to add scripts that update these 7 fields. The right hand key should specify the correct date for that record and probably some additional info. A sort may be required for the relationship to only display real data in the correct order OR you may have to serialize each slot by marking the first appointment in that slot. The relationship would then only display records of that date which are indexed as the first appointment in that slot.

Well, that is the idea. Have fun!!

[Fenton]

I believe where 7 really helps is not so much in the "time-grid" view of appointment slots, but in the monthly "calendar" view. And not so much to do with displaying data, as in scripting.

If you want a click on a "day" to cause something to happen in such a file, you've got to have a script for each possible "day" spot (like 37 in mine); or in v.7, 1 script, passing a different parameter for each spot. I've got a couple different actions, one for the general day, one for a click in a day's activities (in a small portal). That's 74 scripts in v.6, 2 in v.7.