How can I show records from more than one table in the same calendar?
Overview.
Traditionally a portal can only show records from one table occurrence, though it can include fields from other table occurrences related to the one the portal is based on.
This calendar, however, can include events from completely unrelated tables--each of which becomes a "data source" for the calendar. In this way you might have one source be an events table holding things like followup calls, meetings, etc. And have a second source for your jobs table where you show job due dates on the calendar.
The calendar ships with an example second source, a To-Do List, to show you how this works. You can toggle this second source off and on by visiting the "Sources" tab in the calendar's left hand sidebar.
You can even have multiple sources from within the same table: if, for instance, you had a job due date in your jobs table and a followup date in your jobs table, you'd create those as two separate sources.
Note that having multiple sources can show up in the calendar's speed as there are more records to draw, but this FM12 calendar is much faster at drawing multiple sources than our previous versions have been.
How does the calendar decide which source to use for a new event?'
First, if there is only one source showing, when you create an event the calendar will create it in that source.
When multiple sources are showing we look to see if the "default sources for new events" is one of them. If it is, we create the event in that source. You can specify this default source toward the top of the script "Load Calendar Settings - On Startup --- Edit Configuration Here ---".
If this default source is not one of the ones showing, we create the event in the first source showing.
So that's what happens when you just ask to create an event without specifying anything further: that is, when you shift-click in the calendar or click the "+ new" button. The calendar does, however, have an API that will let you make your own "+" button to create events in any source you want. You can use this to make a "new todo" button, for example. You'd do this by calling the script "Create Edit Delete Event ( SourceNo )..."
Read the comments at the head of that script to learn more about how to use it to make new events.
Creating a Second FileMaker Source.
Each source in the calendar is represented by a layout. You'll see a couple of these layouts in the calendar's example file: they are names "Source No 1" and "Source No 2". Any new sources you create will need a layout named the same way.
Here is how you'd create a new FileMaker source...
1. Duplicate the "Source No 1" layout and rename it Source No 3 (or your next unused source number).
2. Enter layout mode, then layout setup and base this new layout on the table you'd like to show records from. In this example, we'll call this "Orders"
Note that you may want to make our Events table a child of your orders table so that you can see multiple events per order. Making "orders" a source in itself is best for showing a singular fact about each order (like a due date) in the calendar.
3. While still in layout mode, visit each of the configuration tabs and enter the details or map fields specific to your source. Enter browse mode when you're done and review each tab for errors. Remember how you had to add a couple fields to your events table when you first
integrated the calendar? You'll have to add the same fields to this table (to "Orders" in our example) in order to complete a few of the Required fields. These fields are:
zscEventSummaryCalc
zscTimestampEndCalcNum
zscTimestampStartCalcNum
You'll also want to add the following auto enter calc to your TimeStart field (if you don't have a time start field, create one). Same thing with TimeEnd--make one and use this auto-enter calc:
Case (
Self = Time ( 24 ; 0 ; 0 ) ; Time ( 24 ; 0 ; 1 ) ;
Self = Time ( 0 ; 0 ; 0 ) ; Time ( 0 ; 0 ; 1 ) ;
Self > Time ( 24 ; 0 ; 0 ) ; Self - Time ( 24 ; 0 ; 0 ) + 1 ;
Self )
Make sure you uncheck the "Do not replace existing value" checkbox. This Auto-Enter option is needed if you want events to be able to start or end at midnight.
If you want to show event colors in list view as we do in the Event List or Event Detail layouts, copy our field "z_ColorCalc" from Sample Events and add it to your "Orders" table mapping our field "status" to the equivalent field in your table: this is the field we used to color code events in the calendar. Note that the calendar shares the same list of statuses and resources across all sources.
4. Create an Event Detail layout for this source: this is the small layout you'll use to view the event when you click on it in the calendar. We recommend creating a small detail layout even if you want folks to jump to a layout you already have (like an orders layout) when clicking on an event. Having a small mini-window come up can help users quickly look at an event and then close that window without loosing their place in the calendar. Add a button to this layout to jump them to your main layout if they need more. Here's how:
jump to my event.
5. Edit the script "Load Calendar Settings - On Startup --- Edit Configuration Here ---" and find the comment "Which sources are active by default". If you want your source to be shown in the calendar at startup, add its number to the List () function after that comment. If you don't do anything here your source will show up as a possible source on the Sources tab, but its events won't show in the calendar unless a user elects to click on that source.
6. Now edit the script "Load Source Settings at Startup --- Describe Your Sources Here ---" where you'll see a block of a dozen lines for each source. Duplicate this block for your new source by copying and pasting an existing block back into the script: begin your block by testing for the source's layout name as we do, testing for "Source No 3". Then edit each of the lines in the block, taking the script comments as instructions.
That's it.
Source Limitations
The Gantt Chart can only depict records from the first source and unlike other views it won't show records from more than one source at the same time.