For the weekly and monthly views I'd like to changed the display of the items to look like this:

July 2
"(Start) Write bug report"

July 7
"(Due) Write bug report"

Rather than this:

July 2
"Write bug report - Start: July 2, 2003 Due: July 7, 2003"

July 7
"Write bug report - Start: July 2, 2003 Due: July 7, 2003"

Perhaps you can guide me though the complications involved in this? I guess the relationship for the portal will have to change. Once you go through the current relationship you lose any information about the current day.

The new field would have to be a calculation based on the current day, so the new relationship for the portal would need to be a self join??

Any help would be appreciated.

Hi Ross,

If I understand you correctly, this is not just a formatting question (to change the formatting you could just edit the ItemADisplayCalc or ItemBDisplayCalc and leave it at that). I believe that you wish to show the start information if the date is displaying when it matches against the start date and show the end date when the date is matching against the end date.

Unfortunately, there is no way to do this directly using the same layout and the same field.

There are two ways to go about this... in the first you'd split the calendar into two layouts, one whose relationships only matched on start dates and one that only matched on end dates. Then, in each layout you could use a slightly different version of the ItemADisplayCalc field.

The other approach would have you split your record in two, so that for a given item you'd had one record for the start date and one record for the end date. Each record could be taught to know if it was a start or end record and then you'd be able to format a single calendar layout as you describe. This is certainly the more flexible method, but causes you to actually change the way the data is set up.

Let me know how this strikes you and I can help you understand how the relationships would have to change.

Best,

John

Hi John,

John Sindelar wrote:in the first you'd split the calendar into two layouts, one whose relationships only matched on start dates and one that only matched on end dates. Then, in each layout you could use a slightly different version of the ItemADisplayCalc field.

The other approach would have you split your record in two, so that for a given item you'd had one record for the start date and one record for the end date. Each record could be taught to know if it was a start or end record and then you'd be able to format a single calendar layout as you describe. This is certainly the more flexible method, but causes you to actually change the way the data is set up.

I understand how the first method would work, but I would like to try the second approach if possible. I don't mind making changes.

So, you are saying that I could change the records of the related items table so there is one record for the start date and one record for the end date? I see how this could work. This may make management of the other table tricky. Can I create this view for the calendar while maintaining the one record per task view for the user? I'm thinking this may be possible.

Is there any other solution to this problem?

Thanks for the help.

This is turning into a cool thread; thanks for asking about this.

So, you are saying that I could change the records of the related items table so there is one record for the start date and one record for the end date?

Yes.

Can I create this view for the calendar while maintaining the one record per task view for the user? I'm thinking this may be possible.

I would think so. You could have an arrangement like this...

Projects ----< Tasks -----< Milestones

Here, the "task" is what your users would be interacting with. The Milestones database would contain two records for every Task. Using tow separate relationships from Tasks to Milestones, (relationships with Allow-Creation turned on) you could even make it "look" like users were entering the start and end dates "in" the tasks database. The milestones database is the one you'd show in the calendar, calculating a description of the task into the file for display purposes.

(I don't know is you have a Project's file, but I always think of Tasks as children of *something*.)

You can even use this kind of structure to record multiple "versions" of the start and end dates to keep track of revisions to your schedule. In such a situation you'd need to use some scripting to make sure that only the latest of the start and end dates for a given task showed up in the calendar.

Is there any other solution to this problem?

I don't think so. Blowing the start and end dates out to their own file is as granular a structure as I can envision here and it is this kind of granularity that opens up possibilities.

HTH.

It works! Thanks for the help.

I am taking your word that this was the only way to go, I need to get this thing out the door.

My original "Related Items" table was simply a copy of your sample:

Table: ProjectSchedule
Fields: TaskId, ProjectNumber, Description, Status, StartDate, DueDate
Calc: CalendarDisplayCalc, CalendarKeyDateCalc

As you mentioned I moved the dates to their own table:

Table: ProjectSchedule
Fields: TaskId, ProjectNumber, Description, Status
Calc: StartDateKey, DueDateKey

Table: ProjectScheduleDates
Fields: TaskId, DateType, Date
Calc: DateKey, CalendarDisplayCalc, CalendarKeyDateCalc

The DateType is a text field set to "S" or "D" to indicate a start or due date. The DateKey is a calculated text field that concatinates the DateType and TaskId fields.

The StartDateKey and DueDateKey produce text fields that concatinate an "S" or "D" with the TaskId.

A relationship StartDate was defined that maps ProjectSchedule::StartDateKey to ProjectScheduleDates::DateKey.

A relationship DueDate was defined that maps ProjectSchedule::DueDateKey to ProjectScheduleDates::DateKey.

These relationships are set to delete related, so when you delete a ProjectSchedule record the ProjectScheduleDates records are automatically deleted. Very nice.

However I did need to write my own "New Record" script in ProjectSchedule to create my two date records in ProjectScheduleDates. I don't think you can avoid this (at least as easily as it was to write my New Record script).

The TaskId is used in ProjectScheduleDates to reference information in ProjectSchedule. To get the task decription for example.

After all this, the portal records of table ProjectScheduleDates have the information to distinguish between a start and due date. Writing the context sensitive CalendarDisplayCalc is trivial work. I used two special characters at the beginning of the task description to efficiently indicated a start or due task.

I also ended up replacing the daily DisplayCalc and KeyDateCalc with the one from the ProjectScheduleDates table. I was originally going to keep the daily display unchanged but encountered problems trying to create a calculation that could be indexed. So I took the easy way out and my daily view is the same as the weekly one now.

I hope this is of assistance to some other CCCal developer.