I just purchased SeedCode Calendar Pro after tinkering for several weeks with one of your previous free versions. I am mightily impressed with the design and functionality of your cool products! Unfortunately, being a relative novice, I have quickly run into what I think is a fundamental design problem.

I am trying to adapt Calendar Pro for use in my substance abuse/psychiatric clinic. Here are my objectives:

#1: In the calendar views: I want to be able to schedule 5 types of encounters for any given contact (patient): appointments, urine screens, incoming phone calls, outgoing phone calls (from myself to the patient), and "other."

#2: In the contacts (patients) view: I want to see a chronological list of the patient's 5 types of encounters, each in a different portal.

#3: In a third type of view, I'd like to be able to view global lists of appointments, urine screens, etc. (in other words, portals that contain lists of all of my patients' appointments, urine screens, etc.)

My plan at this point is to use the Projects table(s), and the relationship(s) between Contacts and Projects, to achieve these objectives. More specifically, I plan to do the following:

1) To achieve objective #3, I would get rid of the current "Projects" view, and create 5 separate views, on 5 separate tabs, one for each of the encounter "types."

2) I would then create one "project" for each type of encounter (i.e. 5 projects, one entitled "appointments", one entitled "urine screens" etc.)

3) Each of the 5 new tabs will then show a portal that contains a complete list of appointments (tab 1) or urine screens (tab 2) or calls, etc.

Does this make sense to you, design-wise? Or is there a more elegant way to achieve my objectives?

Hi. Thanks for the cool post. I think there is a simpler way to get this done: one that doesn't involve projects at all.

To recap your objectives...

#1: In the calendar views: I want to be able to schedule 5 types of encounters for any given contact (patient): appointments, urine screens, incoming phone calls, outgoing phone calls (from myself to the patient), and "other."

The calendar does this out of the box if you create one color coded "type" for each of these types of encounters.

#2: In the contacts (patients) view: I want to see a chronological list of the patient's 5 types of encounters, each in a different portal.

This is the tricky one we'll get to below.

#3: In a third type of view, I'd like to be able to view global lists of appointments, urine screens, etc. (in other words, portals that contain lists of all of my patients' appointments, urine screens, etc.)

Isn't this the calendar itself? The daily view would contain all your appointments (filterable by type) for that day, and the calendar's list view would show you all appointments period.

So, getting back to number 2, if you want to see each of your 5 types in 5 different portals you'll create a special relationship for each type. Create a new field in the contacts table called "Common_urinescreens". Make this a calculated field, returning the type "text" and equal to "urine screens".

Then, click define database and look at the contacts section of the graph. Make another instance of the ContactActivity table occurrence calld "ContactActivityUrineScreen". You'll related this to Contacts exactly the same way ContactActivity is related to contacts except that you'll remove everything after the first relationship matcch (the one from ContactID_kprime to ApptContactID_kf). After that you'll add a relationship from your new field "Common_urinescreens" to "AptyType_kf".

What that does is create a relationship that is only true for that contacts' items of the type "urine screen". Now you can place a portal based on this relationship on the contacts layout and you'll see just that contact's urine screens. You'll do this for each of your 5 special types, creating Common... fields and relationships for each. You can then make individual sub tabs for these on the contacts layout (replacing the "blank" tab and adding a few more).

Going Further.

If you look to the right of the ContactActivity table occurrence on the graph you'll see a lot of related TOs to show things like the activity's project name and type color. From your post it sounds like you may not need to see these attributes for appointments in these 5 type-specific portals, but if you do you'll create copies of these TOs to the right of each of your 5 type-specific TOs.

When it comes to creating new appointments I'd use the existing new appointment script. You could also modify "New Appointment From External" to take "type" as parameter and then call that with a different type parameter from each of the 5 portals.

John:

Thanks for your very rapid reply to this post! Before I tackle objective #2, I have a quick question about your comment regarding objective #3.

You write: "Isn't this the calendar itself? The daily view would contain all your appointments (filterable by type) for that day, and the calendar's list view would show you all appointments period."

But this seems to contradict the documentation: the document entitled List and Table View contains the following statement:

Note that clicking on this [list icon] will bring up a found set matching the view you were looking at: if you click on the list icon from the day view you’ll arrive at a found set matching the records for that day and respecting any filters that might be in place. Clicking on the list icon from the month view would bring up appointments starting that month.

So I'm still not sure how to create (or access) a calendar view that shows all of my appointments. Can you comment?

jimrecht wrote:So I'm still not sure how to create (or access) a calendar view that shows all of my appointments. Can you comment?

Sure. Those list and table behaviors are just the starting found sets. Once there you can perform a "show all records" to show all your appointments.

Right! Yikes, now that I've read over the documentation again, I see that you had already made that clear. Sorry for my oversight. Thanks again for your help with this!

No problem at all!

Best,

John

I have two more questions on this topic of creating new appointments, so I will separate them into two posts. Here is the first:

Depending on the type of activity, ("appointment", "incoming call", "outgoing call," "urine screen,"), I will subsequently enter data into a different range of fields. Certain fields will be unique to a given type, and others will contain data regardless of activity type. For example, I need a field such as "UrineScreenCocaine" for each instance of a "urine screen" activity", and only for that type of activity. But I will be entering data into fields such as "ApptDateStart" for all activity types.

My question is: should I keep all of these "Contact Activity" fields in one table (the ContactActivity table)? Or would it make more sense to create separate tables for each set of "type specific" fields? Or doesn't it matter?

OK, now it's on to question #2 and another post.

Yours,
Jim R.

Here's my second question:

Since, as I stated in my previous post, I will be entering data in a unique set of fields, depending on the activity type, it seems to me that I ought to have a type-specific form for each of these ("appointment", "incoming call", outgoing call", "urine screen", "other"). My preference would be to use a single layout, one that contains a tab control with a tab for each type. That way, the user will click on the appropriate tab, thus setting the event type (using the script described in the post entitled "Selecting tab in tab control sets Type: can it be done?"), and going to the relevant set of fields to enter data.

But here's the problem: some types (and hence, using this design strategy, some layout tabs) will contain only two or three fields, while others will contain more than a dozen. For example, "outgoing call" requires only Date, Time, User, and Description. But "appointment" includes those 4 fields, as well as fields like CurrentSymptoms, RecentTherapy, MedChanges, FamilyInput, CurrentStressors, etc., etc. -- you get the picture. From a layout design perspective, it looks awkward.

The only alternative I can think of is this: use a unique layout for each type of activity. But if I do that, I'm not sure how I would get to the appropriate layout from the calendar's "New Appointment" button. I imagine I could tweek either the "New Appointment" script or the "New Appointment from External" script so that it first presented a dialog box (for example, a row of checkboxes labeled "Please select type of Activity"), then opened the layout corresponding to the user's selection. But that seems awkward too.

Do you have any suggestions?

jimrecht wrote:My question is: should I keep all of these "Contact Activity" fields in one table (the ContactActivity table)? Or would it make more sense to create separate tables for each set of "type specific" fields? Or doesn't it matter?

I'm a fan of keeping all these fields in one table. In terms of database performance it is no problem to have unused fields per record.

However, if some of these tests happen multiple times per appointment--for instance if you had two tests from the same urine sample, each with different results--it would make sense to move that kind of data into a separate table (thus making it easier to make summary reports based on test results).

In general, if the item occurs once per appointment it should be in the appointments table. If it occurs multiple times (several results from the same tests, selling multiple products per visit, multiple pieces of equipment being rented, etc.) then that would more properly be a child of the appointment.

Good question.

Excellent. Very helpful. Thanks!

jimrecht wrote:Here's my second question:
<snip>
The only alternative I can think of is this: use a unique layout for each type of activity. But if I do that, I'm not sure how I would get to the appropriate layout from the calendar's "New Appointment" button. I imagine I could tweek either the "New Appointment" script or the "New Appointment from External" script so that it first presented a dialog box (for example, a row of checkboxes labeled "Please select type of Activity"), then opened the layout corresponding to the user's selection. But that seems awkward too.

Do you have any suggestions?

If I understand your problem it is that some of these tabs would have just a few fields while others would have tons of fields--perhaps even having sub tabs within them.

My first reaction is so what? =) White space is a good thing. (In my experience it won't be white space for long.)

I think that is far preferable than having one layout for each type of activity. But there are legitimate trade offs here so I'll try and describe the trade offs as I see them.

Multiple Tabs on One Layout:

Pros. The big advantage here is that items which are repeated across all the record types (across all the tabs) are only drawn once on the layout, meaning these elements are easier to modify and maintain. You also automatically get a consistent look and window appearance across all the tabs letting users know they are working on various aspects of the same record. It is also REALLY easy to add new tabs, change their order, etc.

Cons. You can't easily have different window sizes for different tabs and all tabs need to be based on the same table occurrence. (This last point shouldn't be an issue in your case.)

Multiple Layouts:

Pros. The big advantage here is that FileMaker security is designed around the layout, not the tab. So if you want to really prohibit someone from working with a layout/tab you can do so right in the access privileges. With tabs you have to script your way into preventing them from reaching the tab, which is nowhere near as reliable. Go to Layout is also a little easier than "go to tab" since it's built into GTRR, etc.

Cons. Adding new layouts while maintaining a consistent look can be a pain, especially if you're making it "look" like a tabbed layouts. Re-ordering these faux tabs is almost as bad. In addition, your common elements are drawn on each layout meaning that formatting changes have to be applied over and over again to each layout. This, in general, invites entropy.

This is very helpful. And thanks for the bonus link to a very informative software-related article!

Now, I'd like to have the date of the most recent urine screen displayed in red, if that date is more than 30 days prior to today's date. I tried entering the following calculation in the "ApptDateStart_k"
field in the Appointments table, but it didn't work:

If ( Get ( CurrentDate ) > ApptDateStart_k + 30 ; TextColor ( ApptDateStart_k ; RGB ( 255 ; 0 ; 0 ) ) )

And actually, this isn't really what I want, since I am only interested in changing the format of dates/times of urine screens, not all appointments.

Any thoughts?

You're on the right track, you just don't want to do this with ApptDateStart_k itself since it isn't a text field. Create a new calc for this that returns the type "text" and tests for the type as well, something like:


If ( Get ( CurrentDate ) > ApptDateStart_k + 30 and ApptType = "Urine Screen"; TextColor ( ApptDateStart_k ; RGB ( 255 ; 0 ; 0 ) ) ; ApptDateStart_k )

Thanks much, John. Just to be sure I follow you: should I create a new field that has the characteristics you describe?