CCCalendarSE

Integration

Can I Use a Client's file I Already Have?

Sure. See the Clients section for step-by-step instructions.

How Can I Use My Own Appointments File in the Calendar?

Swapping Out the Appointments Table.

In many cases, it will be easier to simply modify the appointments table that comes with CC Calendar SE. However, if you have a complex appointments file that already has a lot of work in it, you may wish to point CC Calendar at that file instead. To do this you’ll accomplish a couple basic tasks. First, you’ll define a file reference for the file containing your appointments file and add the key calculation fields from our appointments table to the table in your file. Then, you’ll point CC Calendar SE at your file by editing the calendar's File References, and Relationships. Lastly, you’ll tidy up a few things by reviewing a few layouts and scripts in CC Calendar SE.

Please note that this is a more complicated modification than it was in CC Calendar Pro. This SE version introduces a number of additional fields and calculations to the Appointments table: items you'll have to add to your table to get it to work. While all these calcs use Let() statements to make them easy to modify, this is still a painstaking process. Many users will find it easier to build out from the appointments table in CC Calendar SE.

That said, experienced FileMaker developers will be able to do this pretty quickly by simply looking at the Relationship Graph for CCCalendar_SchedulingEdition.fp7 and the field definitions for the appointments table (CCCalAppts). Even so, the checklist below might be of help.

1. Backup your files. Also you'll be editing some complicated scripts when it comes to re-pointing the repeating events script. Before you get started we suggest you print all CCCalendar.fp7 scripts to a PDF that you can refer back to while you're working. Printing the Relationship Graph and Field Definitions as PDFs is also prudent. While you'll have the original files as backup, it is easier to refer to PDFs while you're working.

2. Adding a File Reference to Your Appointments File. The repeating events feature and the calendar's conflict notification requires a few items be added to your appointments file. Define a file reference for CCCalendar_SchedulingEdition.fp7 in your appointments file, calling it "CCCalendar". Then place three table occurrences on the relationship graph in your file (you don't need any relationships to these TOs yet, just place them on the graph).

From the table "Calendar" create a TO called "CalendarGlobals";
From the table "SettingsValues" create a TO called "SettingsValuesDaily";
From the table "Interface", create a TO called "Interface";
From your own Appointments Table (the one in your file) create a TO called "AppointmentsDailyConflicts".

The TOs "CalendarGlobals" and "Interface" will never have anything linked to them. The other two will get relationships assigned to them in the next section.

3. Prepare your Appointments Fields. You'll need to make sure that you appointments file has a few basic fields used to relate appointments to the calendar. Note that you're fields don't have to be named the same as ours, but it can helpful to use the same names as all our documentation (and the Q & A on our "support forums":http://www.seedcode.com/support ) refer to these field names. Even if you don't keep these names long term, using them for the duration of this modification seems like a good idea. You'll need at least the following fields in your appointments file:

CommonOne - This is just a calculation defined to return the number one.

ApptDateStart - the date your appointment begins.

ApptEndDate - the date your appointment ends.

ApptDateEndCalc - this calc is used to fill in an End Date for you appointments if one does not exist. If your appointments never last more than 1 day, you can edit this calc to simply use the StartDate of the appointment, or dispense with this field and use ApptDateStart in its place in calendar relationships. Note, the as the calc is presently written creates and end date for appointments that cross midnight. Again, if your appointments _never_ cross midnight, you can simplify or dispense with this calc and with ApptEndDate.

ApptTimeStart - The start time of your appointment.

ApptTimeEnd - The end time of your appointment.

ApptKeyDateRangeCalc - this is a complicated calc but with FM7’s great let function you can almost just paste this into your file. Simply replace the two fields in the let statement with your versions of “ApptDateStart” and “ApptDateEndCalc”. The only part of this calc that you’ll edit is the beginning...

Let ( [

Start = ApptDateStart ;
End = ApptDateEndCalc
];

-> The Following 5 fields have a similar structure, you'll edit the beginning of their Let() statements, replacing ApptDateStart, etc. with the corresponding values in your appointments table. These fields are used to display records on the scheduling views and to adjudicate appointment conflicts:

ApptKeyTimeRangeCalc
ApptKeyTimeRangeCalcEnd
ApptKeyTimeRangeCalcStart
ApptKeyTimeRangeConflictCalc1
ApptKeyTimeRangeConflictCalc2

ApptUser - To keep CC Calendar simple, we relate appointments to people based on the person's initials. Formatting your data entry for a users' initials as a checkbox easily lets you record multiple users per appointment, which CC Calendar supports.

ApptKeyUserCalc - This calculation simply adds a "-" to the end of your list of users' initials for a given appointment. Make sure that this and all "key" calcs return the type "Text" and not "Number".

ApptType - Like ApptUser, this is a filtering key used to break up appointments into different types or different "calendars."

ApptKeyTypeCalc - This calculation simply adds a "-" to the end of your list of Types for a given appointment. Though the interface of CC Calendar doesn't explicitly allow it, you could assign more than 1 type to an appointment, though the appointment would take the color of the first type assigned.

ApptResource - This is the field we use to link an appointment with a specific resource.

ApptKeyResourceCalc - This calculation simply adds a "-" to the end of your appointment's resource and allows appointments with no resource to still show up on the Day, Week, and Month views.

ApptKeyRepeatIDX - Used to link a repeating appointment to its parent.

ApptRepeatHighlightCalc - This calc displays the green highlight on a selected appointment in the small list of repeating events which displays after repeating events are created. This requires one of the file references in your appointments file and two Table Occurrences described in the previous step.

ApptKeyClientIDX - This is the field used to relate clients to appointments.

ApptDisplayOnScheduleText - This is a display calc the formats the information we want to see for as given appointment on the schedule views. This includes some related information from the clients' record including their phone number and email. If you don't have these structures in your appointments file you can use the ones in the client file in CC Calendar SE: just comment out the whole calc for now and return to it after step 4.1 where you can uncomment the calc and enter the relevant client information. (To "comment out" a calculation, simply enter / at the front of the calc and enter / at the end.)

ApptDisplayOnScheduleTrimmed - This is another one of those tough calcs where you only have to edit the beginning of the Let() statement. Please note though that this is a repeating calculation so be sure to define your calculation to have 4 repetitions.

ApptDisplayRecordCountCalc - A pretty straight forward calc used to help users move around a found set of appointments.

ApptDisplayRecordArrowCalc - Another calc for navigating around found sets; please note that this will require a File Reference in your appointments file to the Interface table in CCCalendar_SchedulingEdition.fp7 (You don't need to related the table to any of your tables, simply place an occurrence of the Interface table on your graph and name it "Interface".) Also note that this is a repeating calculation with 2 repetitions.

ApptDisplayConflictCalc - This calc displays the conflict indicators on the Daily view. Before defining this field, find the "SettingsValuesDaily" TO you added in the previous step and and relate it to your appointments table by CommonOne to CommonOne. You can use any instance of your appointments table, but be sure to evaluate this calculation from the context of the instance you used. Then you can paste in the definition for this ApptDisplayConflictCalc field.

4. Create a File Reference for Appointments. To point the calendar at your appointments table, you'll add a file reference for your appointments table in CC Calendar SE ; to keep it simple, name the file reference “Appointments” for now. Go to File / Define / File Reference. Click “New” and then click the “add file“ button to add a line for your appointments file. Note: Do not rename the table occurrences in CCCalendar_SchedulingEdition.fp7

5. Repoint Simple Relationships on the Graph in CCCalendar_SchedulingEdition.fp7. Select File / Define / Database from the File menu and select "Relationships" so that you can see the relationships graph. All of the Green table occurrences (TOs) in this graph refer to the Appointments table; currently each of these TOs points to the appointments table in CC Calendar SE. We'll need to change each of these to point to the appointments table in your file. When we do so, the relationships between these TOs will break and we'll need to fix them. Fortunately, this isn't as hard as it sounds.

Lets tackle the first one, the "Appointments" To in the "Table Listing" section of the graph. Double click on this TO and you'll see the "Specify Table" dialog window. Note that the TO is currently from the "CCCalAppts" table and that it is from the "Current File.." (You can see the file attribute in the pop up list to the right of the word "File:" near the top of this dialog.) It is important as we repoint these TOs that we don't rename them. This one is currently called "Appointments" and we'll want to paste that name back in there after we point this at another table. From the "File:" pop up, select your appointments file. You'll now see a list of the tables in your file. Select your appointments table and then change the name of the TO so it continues to read "Appointments." Click "OK" to close the dialog. You've just "repointed" a table occurrence.

You'll notice that your newly repointed TO is now no longer properly related to "AppointmentTypes". To fix this, double click on the relationship line connecting these TOs. Edit the right side of this relationship so that...

KeyTypeName = ApptKeyTypeCalc

...then click OK. You're going to follow this procedure for each Green TO on the graph. Each section of the graph has its own flavor and instructions for specific sections follow. At this point you should go through and do the "simple" TOs. If you've printed a PDF of the original Graph in CC Calendar SE, you can use that as your guide as you repoint and repair each of the following TOs (being careful not to rename them):

SettingsClientsSelectedAppointments
-> AppointmentsDailyRepeating

5.1 Re-Point Calendar Relationships. Select File / Define / Database from the File menu and select "Relationships" so that you can see the relationships graph. You'll want to edit all the relationships between the Calendar (in blue) and Appointments (in Green) file. There are a lot of these, but they all follow the same basic format. Lets look at the first one: check out the relationship between Calendar Daily (in blue) and Appointments Daily (in Green). This is in the "Daily Views" section. Double click on the "X" between these table occurrences to edit the relationship. You'll want to select each line where there is an "unknown" field and select the correct field in your appointments table. While your fields may have different names, the relationship definition should look like this:

CalDateKey0 = ApptKeyDateRangeCalc
AND CalLinkUserCalc = ApptKeyUserCalc
AND CalLinkUserHideCalc ≠ ApptKeyUserCalc
AND CalLinkTypeCalc = ApptKeyTypeCalc
AND CalLinkTypeHideCalc ≠ ApptKeyTypeCalc
AND CalLinkResourceCalc = ApptKeyResourceCalc
AND CalLinkResourceHideCalc ≠ ApptKeyResourceCalc
You'll need to do this for every green Table Occurrence in the graph. Fortunately, the left hand sides of these will all be fine, but you'll need to re-point the right sides. Be careful and take your time.

There are a TOs that are similar to these Calendar relationships but have slightly different relationship criteria; if you've printed a PDF of the calendar graph you can tackle these as well, using the relationship criteria in the graph for reference:

AppointmentsSearchResults
AppointmentsMiniCalendarDay1-7
AppointmentsMoWk1-7
AppointmentsYear1-7

There are to TOs used for displaying conflicts. These have an appointments TO on both sides of the relationship; repoint these and edit their relationships as follows:

AppointmentsDailyConflicts...

ApptKeyTimeRangeConflictCalc1 = ApptKeyTimeRangeConflictCalc2
AND ApptUser = ApptUser
AND ApptResource = ApptResource
AND PrimeApptIDX ≠ PrimeApptIDX

AppointmentsDailyConflictsInclusive...

ApptKeyTimeRangeConflictCalc1 = ApptKeyTimeRangeConflictCalc2
AND ApptUser = ApptUser
AND ApptResource = ApptResource

5.2 Hour Relationships. The hour relationships in the Daily and Weekly sections use Date/Time fields instead of Date fields, so hour relationships look like this:

HourKeyTimeRangeCalc0 = ApptKeyTimeRangeCalc
AND CalLinkUserCalc = ApptKeyUserCalc
AND CalLinkUserHideCalc ? ApptKeyUserCalc
AND CalLinkTypeCalc = ApptKeyTypeCalc
AND CalLinkTypeHideCalc ? ApptKeyTypeCalc
AND CalLinkResourceCalc = ApptKeyResourceCalc
AND CalLinkResourceHideCalc ? ApptKeyResourceCalc

The TOs using this kind of relationship are:

AppointmentsHoursVisibleDaily
AppointmentsHoursVisibleWeek0-7

5.3 Scheduling Relationships. The last (bottom) section of the relationship graph in CC Calendar SE concerns the scheduling layouts. Each of our 10 possible resource columns has three green TOs associated with it. All these relationships follow the structure in 5.3 above, except for the first line where we only use ApptKeyTimeRangeCalc for the middle (2nd) relationship in each 3 TO set. The first of the relationships uses ApptKeyTimeRangeCalcStart and the last (3rd) uses ApptKeyTimeRangeCalcEnd. You can tell which one to use by the name of the TO, as these names end in either Start, Body, or End and use the following fields in the first relationship criteria:

Start ApptKeyTimeRangeCalcStart
Body ApptKeyTimeRangeCalc
End ApptKeyTimeRangeCalcEnd

Repoint each of these as you did the hours section and take your time. Note that the middle TO in each section is related to an instance of SettingsApptTypes (in Brown). These relationships need to be fixed as well and each has the same definition:

ApprType = KeyTypeName

6. Edit the Layouts. Your fields likely don't line up with the fields in our appointments table, so once your new relationships resolve, you'll need to edit the calendar layouts to make sure the correct fields show up in the correct places. Be sure to edit fields on each of the following layouts: Daily (each of the Daily layouts), Week. Month 1 Appointments, Schedule (each of the Schedule layouts), Search Appointments, Mini Window Edit Appointment, Mini Window Edit Appointment Client Info, MiniWindow Show Repetitions, and Appointments Print.

Just a few notes about some of these layouts:

Appointments (Print): Check to make sure the subsummary part here calls ApptDateStart.

Daily View (and others): Whenever you come to a portal of Appointments, you'll want to check the portal's sort order in addition to checking the fields it contains. These sort fields get misaligned just as layout fields do during our repointing of the tables.

Month View: The month view layout has a number of container fields on top of the appointment fields you'll wish to edit. You'll want to unlock these objects and move them off to the side, making sure you can reposition them in exactly the same location when you're done. Everyone has their own trick for this, but do NOT move these objects backward or forward to accomplish this; it will be very difficult to get the month view to look right again. You might want to try this first on a backup copy of the layout. Once you get the hand of it you'll find you can edit this layout without incident, but take you time the first time out.

The Horizontal Schedule Layout. If you're not going to use the horizontal orientation, you may just want to skip this section; this is a very complicated layout as it has a large number of occurrences of the field "ApptDisplayOnScheduleTrimmed" (note that it uses repetition 4 of this field). To get a sense of how this is laid out, copy the entire first row and paste it somewhere else off to the right of the layout. Then begin dragging the top most objects away until you reveal the fields underneath. In order to get at the appointment field you need to become "ApptDisplayOnScheduleTrimmed" you'll need to unlock the row and move objects out of your way much as you did with the month view above. Again, you'll have to reposition these objects into their exact original location and you can NOT send these objects forward or back. You'll definitely want to complete a few rows in a backup copy before you try this in your real file.

7. Edit Scripts. While you don't need to create any scripts in your appointments file, you do need to edit a few scripts in CC Calendar SE that set specific fields in the appointments table. As with layouts, your fields may not line up exactly with the original fields. Check the following scripts in CC Calendar SE: these scripts are thoroughly commented to make this process easier. However there are too many references to appointments to reproduce them all here: below you'll see a list of the places to check in each script. Look at your PDFs of the original scripts to make sure you select the correct field:

Select Client: We set ApptKeyClientIDX in two places in this script.

NewClient: We set ApptKeyClientIDX in this script as well.

New Appointment: Check the set field statements. They should set your versions of ApptKeyDateStart, ApptUser, ApptType, ApptResource, and AptKeyClientIDX respectively. In the "Schedule Views" section we set the ApptResource and the ApptTimeStart; in the "Hours" section we set ApptTimeStart. The script should end with a GotoField step going to your ApptTimeStart field.

Edit Appointment: In the "Day" section we test for AppointmentsDailyConflicts::PrimeApptIDX, then again at the end of the "Week & Month" section. In the "Scheduling" section you'll fins a section prefaced with the comment "Use a different GTRR Based on which Column we've clicked on"; each of the If() statements there tests for PrimeApptIDX across one of the AppointmentsHoursSchedStart relationships: the ElseIf() statements test for the same field across one of the AppointmentsHoursSchedBody relationships.

Edit Appointment From Hours: This script contains a section very much like the "Use a different GTRR Based on which Column we've clicked on" section of the script above: If() statements check for versions of PrimeApptIDX across the AppointmentsHoursVisibleWeek relationships.

Edit Appointment - Nudge Date: Check the set fields to make sure we’re manipulating your appointments start and end dates.

Edit Appointment - Continue: After the comment "Create repetitions if necessary", check to make sure we're testing for AppointmentsDailyRepeating::PrimeApptIDX.

Edit Appointment - Dupe: After we duplicate the record we set a global field to equal AppointmentsDaily::PrimeApptIDX and then end the script by going to your ApptTitle field.

Edit Appointment - Delete: After the "If this is not the last repetition left" comment we test for the count of AppointmentsDailyRepeating::CommonOne.

Select Repeating Yes/No: We open the script with a count of AppointmentsDailyRepeating::PrimeApptIDX; we then set ApptKeyRepeatIDX to be PrimeApptIDX in the first SetField(). The last SetField() sets AppointmentsDaily::ApptKeyRepeatIDX to be "".

Select Repeating - Clear Values: The 6th SetField() here uses ApptDateStart.

Cancel Repeat Settings: This script clears AppointmentsDaily::ApptKeyRepeatIDX.

Create Repetitions: This is a tough script, but little of the difficulty involves creating appointment records so editing it is not too bad. Please print this script to a PDF from a clean copy of CC Calendar SE and search for the word "Appointment", you can then find each instance and make sure it uses the proper field from your new appointments table.

Show / Hide / Select Repetition: We open the script with a test of AppointmentsDailyRepeating::CommonOne and go on to use PrimeApptIDX in two SetField()s.

Apply Filters from Search: The second If() statement here tests PrimeApptIDX and the Sort Records step sorts by AppDateStart and ApptTimeStart, though you can change that to suit your needs.

Go to Appointment from Search: The first SetField() here uses ApptDateStart to move the calendar to the date of the appointment you clicked on.

Print Appointments: You'll just want to check the sort order in this script to make sure it includes ApptDateStart as we use that in a summary field.

Export Appointment Found Set as iCal: There is a SetField() inside the loop in this script; that SetField() contains several references to Appointments. Check that whole statement against your PDF.

8. Field Definitions. A few fields in CCCalendar_SchedulingEditioin.fp7 look for specific appointment attributes. All of these look for the Serial Number in the Appointments file: PrimeApptIDX. You'll need to review these definitions and make sure they're looking at the right field in Appointments:

In the Calendar Table, CalDisplayMoreAppointmentsCalc 1 -7 looks for AppointmentsMoWk1:: PrimeApptIDX

In Hours check HourDailyConflictCalc and HourSchedulingConflictCalc0-10 along with HourSchedulingMaskCalc01-10. In all of these Hours calcs you only have to edit the beginning of their Let() statements.

9. That's It!