Hi again..

I'm trying to add a field for each day.. that will show the name of a key employee that will be working at one of our 2 offices.

This sort of ties in with the way I'm trying to set up our Week and Month views(described in a previous post).

For Week and Month, I wanted to show how many appointments there were, of a given type; instead of showing the actual appointments.

The only way I could get it to work was to use the CalFilterResourceGlob to show only 1 type of appointment, do a count on that and store it in its own field, then set the filter to another type, do another count, and store it in another field, etc. So I ran into some trouble trying to keep these storage fields in the Appointments table.. relationship troubles. So I went and put the storage fields in the Calendar table.. and I thought it was all working ok.. but if i view a different month, these fields(appointment counts & employee for that day) don't change to reflect the new month.

Hope that makes sense.

I think my main problem here is the relationship between the Calendar table and Appointments. I'm pretty sure that I have to store those fields in the Appointments table, so that each day will have its own unique set of those fields. I tried using the ApptMoW# occurrences but couldn't get it working.

So for each day in Week view, I want to see(for each office) who's going to be at each office, and a count for each type of appointment that we have.

Month view will be similar.. except I added in a portal for Other Items in the bottom half of each day cell.

hope these work..

month view:


week view:


Looking through the relationships graph scares me..

Thanks for the screen shots! Nice looking mod! What follows is a long, rather theory-heavy discussion of this mod. For folks just casually interested in this please note that you do not have to get into this kind of thing to make the calendar work for you. =)

Overview.

The overall problem you're encountering is that FileMaker prefers to calculate this kind of thing in Preview mode using subsummary fields. When I say "prefers", I mean that given a bunch of appointments as you have in CC Calendar, you can display the counts you want in preview mode by only adding 1 summary field. Then you can count by type, by type-day, or by type-day-location just by changing summary parts and your sort order. To do the same thing in browse mode, you have to build structures and relationships for everything you want to count. To do this in month view, you have to build a lot of them.

In general, you have to have a relationship that is true for what you want to count, and then a Sum() field to count it. Can be kind of tricky, but certainly doable.

Employee for the Day (Coding).

This is the easier part. For this I'd suggest you create a special type of appointment that you'll use to indicate the key employee that will be working at one of our 2 offices. One way to do this is to add a field to the appointment table called something like "KeyEmployee". Entering this field you'd have a pull down menu offering "Office1" of "Office2". Then, you'd create a special relationship to the appointments table that was only true for the key employee for Office 1 or Office 2. This would mean adding two new constant keys to the calendar table, one hard coded to say "Office1" and another for "Office2".

Creating the table occurrences (TOs) to add this to the month and week views would then entail creating 7 new version of the "AppointmentsMoWk1-7" table occurrences for each office, 14 new TOs in all. Though instead of having all the relationship criteria the current ones do, you'd have only the first criteria we have now (the date matching criteria) and a match between this constant Office1 or 2 in the calendar table and the new "KeyEmployee" field in appointments. You can include the criteria for filtering by type if you want- if you needed to assign a key employee for each type.

Employee for the Day (Just Using Data in the Calendar As Is).

Thought I know this isn't what you asked about, there is a way to show this information without changing anything in the calendar. Simply create an appointment type called "Key Employee" on the Settings screen of the About tab and then filter the calendar to show just appointments of this type when you'd like to see who the key employee is for each day.

Appointment Counts by Type (Coding)

The principle here is to place the Sum() calculations (your counting fields) in the table that you'd like to summarize by. In this case, though we're counting appointments, we're counting them by type, so the calc needs to go in the Types table ( SettingsApptTypes ). That field will then count across a relationship to appointments. However, if the relationship is only based on appointment type you'll get a count of all the appointments for that type- all of them, not just the ones for a given day. So, you need to get a date criteria into that relationship and the way to do that is to create unstored calcs in SettingsApptTypes that replicate the date key calcs in the calendar table.

Just as we have 7 of these ( CalDateKeyCalc1-7 ) in the calendar table, you'd have 7 in SettingsApptTypes. Once that is in place, creating the TOs is pretty cool. In a sense this parallels the structure we use to show the vertical columns of hours on the week view. You'll create one relationship from CalendarMonthWeek to an instance of SettingsApptTypes. This is a Cartesian join (an "X") since you always want to see all the appointment types per day. Then from that one instance of SettingsApptTypes you'll create relationships out to 7 new instances of the appointments table. Each based on one of the 7 date criteria you're now calculating into SettingsApptTypes, along with a more natural relationship based on the type name. Creating 7 Sum() calcs in SettingsApptTypes, one for each of these relationships, lets you count the appointments for each day of the selected week.

Appointment Counts by Type (Just Using Data in the Calendar As Is).

Again, I know you want to do this for all appointments at once, but you could just create 7 Sum() calcs in the calendar table that count across each of the AppointmentsMoWk1-7 relationships we already have. This would then show the count of the filtered appointments. If filtered to show just one type, you'd have a count by that type, etc.

Appointment Counts by Type (The Bad News).

So, what we've described above regarding appointment counts will work on the week view. However, on the month view it would always show the counts for the selected week (the week of the currently selected day) not for each week of the month. The short reason for this is that you've only created 7 date calcs in SettingsApptTypes. You'll need 42 date calcs in SettingsApptTypes to do these counts on the month view and you'll need a new month layout with 42 portals instead of 7. We have done a few modifications like this for people who need to see Gantt charts on the calendar, for instance, It works, but it introduces a lot of redundant structures and, since you're counting 42 times the number of appointment types you have, it can get kind of slow. Multiply this by the number of calcs you'd need if you want to see both locations at once instead of filtering by location, and I'd suggest you find a way to review this kind of thing on a week view instead of by month, perhaps using the "As Is" strategy above for the month views.

Now, you don't actually *have* to have 42 portals. You can do it with 7, provided that you figure out how to move the "6 records" attribute of the calendar table into the SettingsApptTypes table. Thus you could create 6 records for each appointment type and add a record number attribute to the table. In this scenario you wouldn't have a Cartesian join (an "X") between CalendarMonthWeek to an instance of SettingsApptTypes, rather, you'd base this relationship on CalRecordNumber in CalendarMonthWeek and in this new record number attribute in SettingsApptTypes. You'd need to put some scripting in place to make sure that as users created and edited these SettingsApptTypes records all 6 of them would stay in synch with each other. (You'd also need a new way of validating for type name uniqueness.) While that is kind of a hack, it does make for a much simpler relationship graph.The date calcs you've piped into SettingsApptTypes would now, however, need to evaluate themselves based on the record number of their SettingsApptTypes record, meaning they would no longer be simple pipes from the calendar table but would need to be their own instances of the CCCalendarDate custom function we use to calculate these dates in the calendar table in the first place.

Again, I'd try to find a way to review this information on the week view instead of going through these acrobatics on the month view.

Thanks again for your post!

John... you just blew my mind

I'm gonna print that out.. and I'll be studying it for the next few days : P

Thanks so much for all the info you've provided so far.. you guys are awesome with your support.

I should also say that the CC Calendar works great 'out-of-the-box'. I just had different ideas for how we could make use of the Week/Month views

Thanks for the feedback; I really appreciate it.

Have a great weekend!

John