I would like to have more than one customer per activity.

But your solution is so complex, that I am afraid to mess up the relationships.

Could you give a little wink about how this can be done?

Hi,

This really isn't that complicated, as you'll see that many aspects of the calendar already anticipate this.

Don't forget that you can already link some other people to the appointment, using both the User field and the Resource field in the calendar. But if you need more than one "contact" for an appointment, here are the basic steps...

By the way, this is the kind of thing we'd be happy to do for you, if you'd like to email us a copy of your file and some more details about how you'd like this to work, we could give you a fixed price for making this modification for you.

So, the basic idea here is that you'll create a new table, a "join" table, to go between the appointment and the contact. This new table just needs two real fields: a field to hold the appointment ID, and a field for the contact's ID.

The new table goes on your relationship graph between CalDailyAppointments (to which it is linked by the appointment ID) and CalDailyApptContacts, to which it is linked by the contact ID. Once in place between them you'll see that the appointment is still linked to the CalDailyApptContacts table occurrence-- there is just an intermediate table in between them.

(Note that while this new join table only needs two fields, you may also want to create fields here for anything special between the contact and the appointment, for example, if the contact has a "role" in the appointment, like mediator, or plantiff, that would go in the join table.)

Once the join table is in place, you have to modify the scripts in the calendar so that when we add a contact to an appointment we create a new record in the join table and add the contact's ID there, *in addition to* adding the contact's ID to the appointment record.

For example, when you're on an appointment and you click "Select Contact" you're running the script "Edit Appointment - Choose Contact". This script only has two steps. The first runs our main selection script- you barely have to change that step- you just want to change the script parameter so the selection routine now supports multiple selection. Change the script parameter to this:

Code: Select all

"Operation = Begin ; Action = Choose ; Multiple Selection = Yes ; ContactID = " & CalDailyApptContacts::ContactID_kprime

Now, continue editing the script script "Edit Appointment - Choose Contact" and after the SetField already there you have to add a few lines to make a new record in your join table. Add something like this (where GoToLayout is going to a layout based on your new join table):

Code: Select all

Set Variable [ $ApptID ; CalDailyAppointments::ApptID_kprime ]
GoToLayout [ ContactsJoin ]
New Record
Set Field [ JoinContactID ; Filters::FilterChooseContactIDGlob ]
Set Field [ JoinAppointmentID ; $ApptID ]
CommitRecords
GoToLayout [ Original Layout ]

That is basically it. You're making join records and you've linked them to contacts and appointments.

The rest of your work falls into two categories:

a) changing the other scripts that link contacts and appointments (for instance you can make a new appointment from a contact's record). You also have to take care of deletes, so that if you remove a contact from an appointment (delete the join record) you also remove that contact's ID from theApptContactOD_kf field in the appointments table.

b) and cosmetics: you have to make sure that everywhere you're showing information about an appointment's contact, you're now showing information about *all the contacts* on that appointment.

In some places this cosmetic work means placing the existing CalDailyApptContact fields inside a portal to the join table. This is what you'd do on the Edit Appointment Mini Window. In other places you may use the List() function to gather the names of related contacts into a single text string. Some people do this on the layout where we print appointments, for example, rather than printing a portal to the join table.

This cosmetic work takes the most time, and it can require some creativity to accomplish. For example, how does one show all the related contacts in the list view of appointments, or on the daily view of the calendar? Maybe one answer is to leave these views largely alone (as they are they will show the first contact on the appointment) and add a new field that says something link "& 4 more contacts", using a calc in the appointments table to figure out the "4 more".

I hope that helps get you started. Please don't hesitate to email me directly if you'd like us to do this for you.

Best,

John