Instructions for adding related tables

Support, Questions and Suggestions for the FM7 Version of CC Calendar.
Posts: 7
Joined: Fri Dec 03, 2004 8:41 am
PostPosted: Fri Dec 03, 2004 8:49 am
I remember seeing instructions on how to incorporate tables into CC Calendar using the related items placeholders but can't seem to find them now. Can someone point me in the right direction? Thanks!
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sat Dec 04, 2004 5:20 am
There was a thread on this in the FM6 Calendar forum:

Swapping these out in FM7 is similar to the procedure used to swap out the appointments file (but simpler, since there is less scripting involved). I imagine you can incorporate new tables by following the spirit of these instructions for incorporating a new appointments table.

Let me know if you need something more specific.
John Sindelar
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Mon Dec 13, 2004 7:29 am
Here are some specific instructions for swapping out the Related Items A and B tables which come with CC Calendar.

These are placeholder files for tables you may already have. Replacing Related Items A and/or B with your own tables can let you show additional information in the calendar. You can use these to show holidays, vacation schedules, conference room bookings, etc.

You can even use Related Items A to show records from CC Gantt so that your project milestones appear in the calendar.

These instructions are based on the FileMaker 7 version of CC Calendar Pro; the procedure for the Full version of the calendar is almost identical. In this example we'll replace Related Items A with a table called "Milestones" in a file called "Data.fp7".

Here we go...

1. Backup your files. Really.

2. Getting Started. Begin work by going to the Daily tab in CC Calendar and clicking on the "collapsed" icon next to Related Items A so that you can see the portal or Related Items A. Next, go to the ScriptMaker and run the Show Status Area script. Then enter layout mode. You're now ready to get started.

In layout mode you'll see that the portal of Related Items A is showing records form the table occurrence called "RelatedItemsADaily". We'll want to find that table occurrence in the relationship graph of CCCalendar. From the FileMaker menu select File / Define Database and click on the Relationships tab. Gray horizontal dividers separate the graph into sections; scroll down to the "Daily Views" section and you'll see a purple table occurrence called RelatedItemsADaily close to the left hand side of the graph. Running your mouse over the shortcut arrow in the upper left section of the table occurrence (TO) shows that this TO is from the table "RelatedItemA" in the file "CCCalRelatedItemsAandB". We'll eventually be changing this to use the new table from your new Data.fp7 file.

3. Preparing Your File. If you double click on the relationship line between the TO RelatedItemsADaily and the CalendarDaily TO, you'll see that all this relationship needs is a field in RelatedItemsADaily called ItemAKeyDateRangeCalc. We'll need to make sure a field like that exists in your new table of Milestones. Close Define Database and using the Window command from the FileMaker menu, open the CCCalRelatedItemsAandB file. Define Fields in this file and you'll see the 4 fields you need: you need 4 because ItemAKeyDateRangeCalc references a couple other fields. You can name these whatever you like later, but for now, I'd suggest keeping the same field names we use in CCCalRelatedItemsAandB. This will make the documentation easier to follow. So, you'll need to find equivalents for the following fields in the Milestones table you'd like to use in place of RelatedItemsA:

ItemADateStart: A date field- just the date on which the appointment or event starts.
ItemADateEnd: A date field- just the date on which the appointment or event ends.
ItemADateEndCalc: To support events with just a single date we've created a calc that builds an end date as needed. This field is a calculation returning the type "date" defined as: If(not IsEmpty(ItemADateEnd);ItemADateEnd; ItemADateStart)

One thing to keep in mind is that as currently defined, things like deadlines should be entered as just start dates, as opposed to having just an end date. You can of course change the calcs to have this work the other way, but we though this was the most intuitive.

Finally, you'll want to create the calc field "ItemAKeyDateRangeCalc". This calc is pretty complicated, but you won't have to edit it at all if you've used the field names mentioned above. If you're using your own field names, you still only have to edit the Let portion of the calculation where its dependence on other fields is declared:
Code: Select all
Let ( [
Start = ItemADateStart ;
End = ItemADateEndCalc

Be sure that the calculation returns the type "text".

4. Creating File References. Return to CCCalendar.fp7 and select File / Define File References from the FileMaker menu. Click new and create a new File Reference for Data.fp7 (the file containing the table you wish to use instead of RelatedItemsA). Note: before you do this, please make sure that Data.fp7 and CCCalendar.fp7 are in the same relative position to each other as they will be when they are served. Ideally, the two files should be in the same folder. Once you've created the file reference you're ready to begin swapping out the files.

5. Swapping the Files. Navigate to the Relationship graph in CCCalendar again and double click on the RelatedItemsADaily TO. Change the "file" at the top of this Specify Table window to be the file reference you created for Data.fp7, next select your table (Milestones) from the list of tables in that file. Note that this will change the name of the table occurrence. Many developers refer to these TO names in their systems in order to determine the context in which a script is running. *In general* it is best not to rename table occurrences that you've inherited from an existing system, and we'd encourage you to maintain that practice here. Additionally, keeping this TO named RelatedItemsADaily will make it easier to follow this and other CC Calendar documentation and FAQs as they all refer to the TO names used in the shipping (unmodified) product. Once you've changed the base table for this TO, the relationship between it and CC Calendar will be broken.

6. Repoint the Relatationship. Now double click on the relationship between CalendarDaily and RelatedItemsADaily. The right hand side of this relationship will either say <fieldmissing> or have the name of some random field, depending on what kind of fields are in your Milestones table. Select the field ItemAKeyDateRangeCalc from the right side of the tables list (the list under RelatedItemsDaily) and click "Change". You've now repointed a relationship in FileMaker 7! Click OK and we'll investigate what this repointing may have broken.

7. Repointing Layouts. You'll want to navigate to a Day on the Daily view where you know you have an record in Milestones with that date as your ItemADateStart. Once there, you should see a white row for the record, along with a blue arrow to the left of it and a trash can icon to the right. If you don't see this and you know you're on a date with appointments starting then, recheck your work in steps 3-6 above. If you do see the item, go into layout mode and click on the field to the right of the blue arrow in the Related Items A portal (you may have to unlock the field from the FileMaker Arrange menu first). Change this field to be the title or description of the appointment in your Milestones table. If you don't know which field you'd like to use yet, just set it to ItemADateStart so that you can see the data come through. Return to browse mode and you should see your milestones data in the calendar. You'll need to make this change on every Daily layout in the calendar which shows a Related Items A Portal (there are 4 such layouts).

8. Repointing a Script. The blue arrow in the Related Items A portal runs a script to take you to that record in its native file. You can adjust that script to do whatever you'd like, but you'll need to change it from its current form because it points to the old related items A file you've just swapped out. Select ScriptMaker from the Scripts menu in CCCalendar and edit the Go to Related Items A script. There are 8 Go to Related Record steps in this script. All you need to edit in each of them is the layout (from Data.fp7) in which you'd like to view your related milestone. After you've selected a new layout in each case, you'll want to change the Perform Script line at the end of the script so that it calls some kind of "arrive" script in Data.fp7: a script that bring a window forward and arranges it as you'd like. This is the only script you have to edit.

9. Whew! At this point, you've successfully edited the relationship used on the Calendar's Daily tab. You now have to repeat steps 5, 6, and 7 for the relationships used on the week and month views. There are only 7 such relationships and the same 7 are used for the month and the week views. Some calendars use 42 relationships at the point, so this would be an appropriate point to be glad you're using CC Calendar. ;-)

10. Repointing the Week / Month TOs. If you scroll almost to the end of the Relationship graph in CCCalendar, you'll find a gray divider called "-- Related Items A and B on Month Weeek Views --" below that, are the 7 RelatedItemsA table occurrences (TOs) you'll want to edit. (These are named "RelatedItemsAMonthWeek1", "RelatedItemsAMonthWeek2", etc.) As in step 5, change the base table for each of these but leave the TO names alone (since the names change automatically, it is best to copy the name, make the base table change, and then paste the original name back in).

11. Repointing the Week / Month Relationships. As in step 6, double click on each of the relationships between these 7 TOs and CalendarMonthWeek, changing the "RelatedItemsAMonthWeek..." side to use the field ItemAKeyDateRangeCalc. Since double clicking on these can be a little awkward, consider slicing the TOs off to the right a little bit to get at their relationship lines; you can always line them back up later.

12. Repointing the Month and Week View Layouts. Start on the week view and adjust each days' field as you did in step 7. Test your work on a week where you know there should be an appointment for each day (you can easily do this by creating a milestone item starts on the first day of the week and ends on the last day). The month view is a little tougher because there are some transparent graphics over the Related Items A fields. Backup your files again at this point in case the layout work takes a second try. Navigate to the "Month 2 Related Items A" layout. Notice that if you click into one of the 7 portals in layout mode you actually select a big container field covering the whole day; you need to move these out of the way. Everyone has their own tricks for this, but I like to draw a rectangle in the upper left of the layout, flush with the top and left edge. Use the arrow keys to nudge it up in that corner and make sure it is flush. Then, while holding down the shift key, select this rectangle along with the 7 container fields (unlock these objects using the Arrange menu). Now drag these 8 objects off the calendar to the while part of the layout and out of your way. Keeping the container fields grouped with this rectangle will let you put them back in the exact place you took them from when you're done editing the fields beneath them. Repoint the 7 fields on this month layout as you did on the week view, then put the container fields back (I recommend relocking them).

13. Almost Done. You'll notice a red dot at the top right of some of the days on the month view. These dots show us if there are more than 6 appointments present for a day (more appointments than can show without scrolling). While the calculations that show these dots may be fine after repointing your relationships, you should check them to be sure. Enter Define Fields for CCCalendar and look at the fields for the Calendar table. You'll want to check the fields named "CalDisplayMoreRelatedItemsACalc1" through "CalDisplayMoreRelatedItemsACalc7". Each of these count a field in Related Items A (now your milestones table). You just want to make sure they are counting a filed that will be there for every record- a serial number, or the start date would do. If one of the 7 fields needs to be changed to count the right field, change all 7 calcs to use that field.

14. That's it!

Going Further...

There are a couple advanced things you might consider doing with your newly swapped out files.

a) For one, CC Calendar assumes that you already have a way to create your Related Items A records (now milestones) and that you just need a way to display them in the calendar. You could consider adding a New Record button to the Related Items A portal and pass the current date to your newly linked file, calling a create record script you may already have.

b) Some tables (like the Milestones table in CC Gantt) have more than 1 set of start and end dates per record and you may wish to show such a record in the calendar if any of these dates match. The trick here is to take the calculation used in ItemAKeyDateRangeCalc and paste it in after itself, now using your second set of dates in the let statement: be sure to separate the two halves of this calc with a carriage return.

c) You may want your Related Items a record to respect the calendar filters, providing you have fields like "type" and "use" in your file. You can do this by mimicing the structures used in the relationships between the calendar and the appointments table in CCCalAppts. While you can get the Related Items A portal to respect the filters, I would not recommend changing the mini-calendars so that their blue dots take Related Items A into account (similarly, I would not try and make the portal of times to the left of the daily screen look into the Related Items A table as well). Such changes would *really*slow down the calendar.
John Sindelar
Posts: 2
Joined: Wed Mar 21, 2007 3:39 am
PostPosted: Wed Mar 21, 2007 3:52 am
I'd appreciate some guidance in getting my related items file integrated. I've followed the instructions above until the end of step 7, with some adjustment for names that have changed in the meantime.

However, step 8 has me completely flummoxed. There is no blue arrow. I assume the whole width of the item is the button. When I go to ScriptMaker, there is no "Go to Related Items A" script. If I go to "Edit Other Item A" instead, I can change the first "Go to Related Records" item to my external file, but the subsequent ones don't offer the option of using an external table.

Am I trying to edit the correct script? Any help would be appreciated.

SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Wed Mar 21, 2007 5:12 am
Hi. That post was written for an older version of the calendar so you're doing great if you're applying that to a recent version of CC Calendar Pro.

Yes, you should be editing the script "Edit Other Item" and yes, the whole row is the button.

When using an external file for "Other Items" you really don't use manage window / cursor steps that follow the GoToRelatedRecords steps. Instead, you'll replace everything from "Perform Script [ Adjust Window ]" onward with a single step performing an "arrival" script in your file.

An arrival script will do these same things (adjust the window, put the user's cursor where it needs to be) but the script will run in your file. This will switch FileMaker's focus from the calendar window/file to your window file. Depending on how you want this to feel, you'll likely omit the "pause" step from your arrival script so users can work freely in your file once jumping over there from the calendar.

Hope that helps.
John Sindelar
Posts: 2
Joined: Wed Mar 21, 2007 3:39 am
PostPosted: Wed Mar 21, 2007 8:18 am
Thanks John.

I thought the calendar had been updated since the instructions, but when I got a whole page of stuff I didn't understand I thought it best to check before I broke something.

The Daily Item A links are all working now, thanks to your help. Just weeks and months to do now.

I bought Filemaker 8.5 just before the deadline for upgrades from 6, and the newer version of the calendar. I'm only just now installing them. Thanks to your help, things are going smoothly, despite the fact I haven't got to grips with tables and the like yet.

Linking to my external file is much easier in this version than in FM6, where I never quite got everything to work.

Thanks again.


Return to CC Calendar (FM7/8)

Who is online

Users browsing this forum: No registered users and 2 guests

Follow us: