Tip: Linking Appointments to Contacts / Clients

Support, Questions and Suggestions for the FM7 Version of CC Calendar.
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Feb 24, 2005 6:41 pm
Overview

You can easily modify the calendar to link calendar appointments with contacts or clients in your own database. In order to demonstrate this, the following example links CC Calendar with the free "Contact Manager" solution from FileMaker's web site.

This example makes the modifications in the Pro Calendar, though these instructions apply to any calendar version. (This wouldn't apply to the Scheduling Edition, where appointments are already linked to Clients.)

The Modification.

You'll want to begin by downloading the Contact Manager from FMI's web site or you can follow along with your own file. FMI's solution comes without any records, so you'll want to add a few sample names to it before you get started.

1. Backup Your Files. This modification is a piece of cake, but why tempt fate.

2. Getting Started. Begin by placing your contacts or client's file in the same folder as CCCalendar.fp7 Open both CCCalendar and your contatcts or clients file (Contact Management.fp7 in this example).

3. Define a File Reference. In CCCalendar select Define / File References from the FileMaker File Menu. Click "New" in the lower left and then click "add file" in the top right of the next screen. You'll be prompted to selet a file, pick Contact Management.fp7. Your file reference should now read "file:Contact Management.fp7" Click "OK" in the lower right.

4. Adding a Link in the Appointments Table. We need to create a place to record who each appointment is for; we do this by storing the contact's or client's ID Number in the appointment. Select File / Define / Database from the FileMaker File Menu and click on the "tables" tab. Double click on the CCCalAppts table- this is the table used to store appointments in CCCalendar. Enter a new field name at the bottom of this screen; call the field "ApptKeyContactIDX" and make its type a number. That's it. Click Create and OK.

5. Adding A Table Occurrence. Select File / Define / Database from the FileMaker File Menu and click on the "Relationships" tab in the resulting window. Here you can see the relationship graph for CC Calendar. Click the "New Table Occurrence" button in the lower left of this screen- this button is not labeled, it just has a little box with a green plus on it- it is the left most button in the bottom row of buttons. In the Specify Table window which comes up, switch the "File" selection away from "Current File..." to "Contact Management", the name of the file reference you created in step 2 above. Select the table which holds you contacts' or clients' info. In our example the Contact Management file has only one table, called "Contact Management". Selecting a table will put a litte box for it (a Table Occurrence) on your graph.

6. Adding a relationship. Our goal is to be able to see our clients' information when we are on the daily view AND when we edit appointments from any other view. Fortunately, the appointments on the Daily View and the Mini Window layout in which we edit appointments are based on the same Table Occurrence (TO) in the relationship graph in CCCalendar. So, that is the only TO we need to relate things to.

To start the relationship, draw a line from any field in the AppointmentsDaily TO to your new Contact Management TO. That will establish a quick relationship between the two TOs. Now double click on the line between these TOs to edit the relationship. On the Appointments Daily side, select the field "ApptKeyContactIDX". On the Contact Management side, select the field "Contact ID". Click change to save the changes and OK to leave the dialog. Now all we have to do is add the contact's information to layouts. Click OK to leave the Define Database window.

Image

7. Editing the Daily View Layout. Enter layout mode on the daily view and make some room on the layout by shortening the length of the appointment title field. You'll likely need to unlock this layout object first (select "unlock" from FileMaker's Arrange menu). Next, copy this Appt Title field and paste it to the left of the appointment label field. Position it within the portal row at the same height as the other text fields around it. Double click on the field and change the table occurrence name from "Appointments Daily" to "Contact Management". Once you do, you'll be able to see all the fields from your contact management table; select "Full Name". Now enter browse mode. You won't see anyone's name there yet as we haven't added an interface for assigning a contact to an appointment... we'll do that in the next and final step.

Image

8. Add the Contact to the Edit Appointment Mini Window. Go into layout mode and navigate to the Mini Window Edit Appointment layout. (You'll need to have the status area showing to do this- you can open it by running the "Show Status Area" script step.) We're going to add a place to add a contact to an appointment; to make room for this, shorten the description field and move it down. We'll place our new contact field between the description and the appointment's title. Copy the end time and paste it back in as the beginning of our contact field. Double click on the new field and switch it to the ApptKeyContactIDX field. Highlight the field and select Field Format from the FileMaker format menu; switch the format from Edit Box to Pop-up List. We'll define a pop-up list to present us a list of possible contacts: select "Define Value List..." from the Display Values From menu. Create a new value list called contacts: select "Use Values from Field" and format the value list as shown below...

Image

This value list will insert the selected contact's ID into our ApptKeyContactIDX. Now we just need to place a field above this to show the contact's name. Copy the ApptKeyContactIDX field and paste it back on top of itself; double click on the field and in the Specify Field dialog select the Contact Management table occurrence; select the "Full Name" field. You'll want this field to be for show only- actually editing the contact for this appointment will be done in the field below this one, so highlight the full name field again and using the Format / Field Behavior menu, set the field so that it can not be entered in Browse mode or Fine mode. Finally, make this top field the same color as the background of the layout so that you can't see the ID field below it.

Image

(Be sure to change the tab order of the layout so it behaves the way you'd like.)

Notice that using the same steps with which we added the contact's full name, we could add *any* contact information to the layout. Now that we have a relationship to the contact's table we can add anything we need from that file (such as phone numbers or email addresses).

That's it!
Last edited by John Sindelar on Fri Jun 20, 2008 3:45 pm, edited 3 times in total.
John Sindelar
SeedCode
PostPosted: Sat Mar 12, 2005 5:41 pm
When I got to step 7 and was to go to "Arrange" and "unlock" everything was grayed and unlock was not available. I tried going ahead anyway and did not find "Appt Title field" in the layout mode nor did I find "appointment label field".
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Mar 13, 2005 5:22 am
Pam wrote:When I got to step 7 and was to go to "Arrange" and "unlock" everything was grayed and unlock was not available.


In that case either the selected objects were already unlocked (perhaps from previous tinkering with the file) or you didn't have any objets selected when you went to "Arrange".

Pam wrote:I tried going ahead anyway and did not find "Appt Title field" in the layout mode nor did I find "appointment label field".


The field "ApptTitle" is indicated below by the left hand red arrow. What I (misleadingly) referred to as the "label" field is really called "ApptType" and is shown by the right hand arrow below. The whole point of naming these fields is just to suggest an appropriate field to cop, paste, and reuse as your client's name field. You can really put the field anywhere you want in the portal row.

Hope that helps.

Image
John Sindelar
SeedCode
PostPosted: Mon Mar 21, 2005 5:37 am
When I got to the point where I was to name the field "number" my ignorance and a tiny bit of reading got in my way. I read some where that the number field was strictly that. I want to be able to connect to Last name, First name and Case number. The receptionist will not have the case number available when making the appoiintment. Should we then use "text" instead of number?
We also ran into a problem with "strict following" of the instructions given when we tried to find the edit window.
I am aware that it is very difficult when one knows a field to be able to put on the hat of a novice and list all of the steps that a novice would need to complete the task.
We got to the point of having the database and calendar connected, but apparently something we did about copying and pasting was not done right.
I will say that my having worked through the steps you sent before showing them to someone else to do was very helpful even though she brings some college education to the project and I only know how to follow directions when it comes to making computers work. In fact that gave her a bit of trouble because she kept wanting to figure out "why" you had set something up a certain way and etc. We were working within a limited time frame and had several goals to accomplish so I taught her about Gestalt theory from counseling. One deals with what is presenting and does the best one can with the client in the hour given. Maybe you will some day evolve to knowing part of "why", but that is not to be your goal. Goal is to move client forward not backward.
I cannot give you the specific steps where we had problems in a concrete manner at this point because I must do some other things. Just thought I should send you a "Thank you" for your help to let you know we could not be where we are right now without you.
WE WILL REACH OUR GOAL!!!!!!!
THANKS,
Pam
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Mon Mar 21, 2005 6:20 am
Pam wrote:When I got to the point where I was to name the field "number" my ignorance and a tiny bit of reading got in my way. I read some where that the number field was strictly that. I want to be able to connect to Last name, First name and Case number. The receptionist will not have the case number available when making the appoiintment. Should we then use "text" instead of number?


I don't think so. At the end of the day you want to have the appointment linked to the contact by a unique ID in the contact's file. (Sounds like this unique ID is your Case Number).

The example presented here assumed that you have an existing record for your client/contact before you begin creating an appointment for them.

If that is not the case and you're creating new clients/contacts in the calendar you'll want to either a) set up your relationship from Appointment Daily to Contact Management to "Allow Creation of Records..." on the contact's side; or b) write a script you can call from the calendar that will create a new case and bring the newly created case number into the current appointment.

In either case it may be better for you to then separate the contact's First and Last name on the Edit Appointment mini window (as when creating new cases you'll be entering the contact's name from this window). You may want to make these fields separate from the ApptKeyContactIDX field so that you can see all 3 fields on the Edit Appointment mini window (ApptKeyContactIDX, along with the First and Last name fields from Contact Management).
John Sindelar
SeedCode
PostPosted: Mon Mar 21, 2005 7:03 am
There is an existing client record plus a "scanned" client information form which the client fills out. I tried, actually we just brainstormed, the idea of having the client fill out an electronic form on a tablet. Technology was not ready for that so we are using a paper form. Not all of their info is needed routinely so it was our decision that it did not need to be cluttering the drive on which the database is stored. It will ultimately be linked as a scanned image and has been named with a modification of the client (owner of animal) case number. Currently if they have to find it, they just search for the number. As I said, VERY RARELY, needed. There will eventually, if the veterinarian is willing, have a scanned image of the current animal file which differs from the client file.
I will forward your suggestion to another to review. Then she and I will brainstorm it from our differing areas of expertise.
I will also get a concrete explanation of the problem she had and send it fyi.
Thanks,
Pam
PostPosted: Wed Mar 23, 2005 5:06 pm
I don't think creating a new record from the appointment is the way to go in this situation. I think creating the new record in the database after they arrive for appointment and client information form is ready is the time to do that. Requested input from someone else has not arrived. Thought I would have it by now. Don't and don't have time to go back over the part they did and figure out and redo right now so things are on hold and probably are not going to be ready for staff to play/practice while professional is gone to Boston first of April.
PostPosted: Thu Mar 24, 2005 11:20 am
Here is the report I got. I remembered that the instructions you gave here did not say how to "get to" the edit window, but I did not do the work from that point.
Pam,
Sorry. I was trying to make sure of the things we had issues with. For some reason when I opened my copy up it did not have the changes that we walked through and made to the calendar. Not sure why, unless I just saved the original one. Anyway here are some of the problems that I remembered.

-The Edit Mini Window was not working properly. When we finished the steps of connecting it to the "contact manager", it was only showing a number in the box. The step that we were getting confused on is when we were to copy the new box we inserted then pasted directly over top of it.
It was then only showing that box when we went back into browse mode, with a single number in it.
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Mar 24, 2005 12:17 pm
Pam wrote:When we finished the steps of connecting it to the "contact manager", it was only showing a number in the box. The step that we were getting confused on is when we were to copy the new box we inserted then pasted directly over top of it.
It was then only showing that box when we went back into browse mode, with a single number in it.


I imagine that this number is the ID of the contact. You'll want to cover this with the related contact's name from the contact's file.
John Sindelar
SeedCode
PostPosted: Thu Mar 24, 2005 2:25 pm
I think what you said was done or at least attempted and did not work. Therein lies the problem.
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Mar 27, 2005 4:40 am
We've been speaking about this off the forum and I understand you're going to contract with some outside resources to help complete your modifications.

All the best,

John
John Sindelar
SeedCode
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Tue May 10, 2005 4:33 am
The enhancement described in this post (linking appointments to clients / contacts) is now included in the new Scheduling Edition of CC Calendar Pro.

The Scheduling Edition also comes with detailed instructions for swapping out the calendar's built-in clients' table for one you may already have.

You can see a screen shot of the client-linking interface here.

Hope thats good news!
John Sindelar
SeedCode
Posts: 2
Joined: Thu Jan 05, 2006 5:11 pm
Location: Michigan
PostPosted: Thu Jan 05, 2006 5:19 pm
Greetings,

I'm a new user of CC Pro SE as well as Filemaker. I don't have the Dev edition as yet and am having trouble understanding your instructions for the ClientTypeAheadKeyCalc field. Specifically you use say etc when describing the "old fashioned way" of scripting. Any scripting for me would be new so can you elaborate here, I don't understand where I'm to continue with on this field, you appear to be incrementing Companyname" x) not sure where I'm to end, is this to some reasonable lenght for company names? Also you make reference to Clients Last.

Left ( LastName ; 1 ) starts immediately after the last one for Company and is there any delimiter required between the CompanyName list and the Lastname List? Thanks in advance for kicking me back in the right direction!
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Jan 05, 2006 6:41 pm
jhankins wrote: and am having trouble understanding your instructions for the ClientTypeAheadKeyCalc field.


Good question. You're asking about our instructions for swapping out the calendar's client table for one you already have.

The ClientTypeAheadKeyCalc field we use as a key is really just a bunch of partial name keys separated by carriage returns. FileMaker will see each return separated line in a text field as a separate possible relationship match. So, if we have a field with the following contents...

S
Sm
Smi
Smit
Smith

... that would be a relationship match against any of those values typed into the other side of the relationship. This is how we do "type-ahead" filtering. As you begin typing the client's last name, the number of possible matches narrows. Of course, using native FileMaker you have to exit the field as you type in order for the relationship to resolve; that is, you have to type Sm and then tab out of the field to see the clients whose last name begins with Sm. Check out this link for a wicked way to do this without tabbing out of the field.

So, in order to get a field to look like this, we use Left like this...

Left ( CompanyName ; 1 ) & ¶ &
Left ( CompanyName ; 2 ) & ¶ &
Left ( CompanyName ; 3 ) & ¶ &
Left ( CompanyName ; 4 ) & ¶ &
Left ( CompanyName ; 5 ) & ¶ &
Left ( CompanyName ; 6 ) & ¶ &
"-"

We said "etc." in our instructions because you might want to support 10 letters, or more.

A couple additions to this are specific to CC Calendar: be sure to end with a "-" and be sure to include both the company name and the contact's last name. Here is a full blown calc you can just copy and paste if you want...

Code: Select all
Let ( [

LastName = YourLastNameField ;
CoName = YourCompanyNameField

// You do not have to edit below this line.

] ;

Left ( LastName ;1 )  & ¶ &
Left ( LastName ;2 )  & ¶ &
Left ( LastName ;3 )  & ¶ &
Left ( LastName ;4 )  & ¶ &
Left ( LastName ;5 )  & ¶ &
Left ( LastName ;6 )  & ¶ &
Left ( LastName ;7 )  & ¶ &
Left ( LastName ;7 )  & ¶ &
Left ( LastName ;9 )  & ¶ &
Left ( LastName ;10 )  & ¶ &
LastName & ¶ &
Left ( CoName ;1 )  & ¶ &
Left ( CoName ;2 )  & ¶ &
Left ( CoName ;3 )  & ¶ &
Left ( CoName ;4 )  & ¶ &
Left ( CoName ;5 )  & ¶ &
Left ( CoName ;6 )  & ¶ &
Left ( CoName ;7 )  & ¶ &
Left ( CoName ;7 )  & ¶ &
Left ( CoName ;9 )  & ¶ &
Left ( CoName ;10 )  & ¶ &
CoName & ¶ &
"-"

)

Using "Let" this way means you only have to replace "YourLastNameField" and "YourCompanyNameField" once with your actual fields before you can use the calc. Just be sure your calc is defined to return the type "text".

Hope that helps.
John Sindelar
SeedCode
Posts: 2
Joined: Thu Jan 05, 2006 5:11 pm
Location: Michigan
PostPosted: Fri Jan 06, 2006 10:36 am
Thanks, John. I'll give this a try.
Next

Return to CC Calendar (FM7/8)

Who is online

Users browsing this forum: No registered users and 2 guests

cron
(855) SEEDCODE
[email protected]
Follow us: