a) Create a new file from scratch, blank (not using a template or anything). Define the following fields:
Attending Text, Repeating field with 20 repetitions
ViewKey Calculation, of type Text, = Extend(Date)&Attending -- Repeating field, 20 reps
UnivKey Calculation, of type Number, = 1
b) Create a second new file from scratch, also blank from scratch. Define the following fields:
NameView Global, of type Text
UnivKey Calculation, of type Number, = 1
Date1st Calculation, of type Text = TextToDate(Month&"/"&1&"/"&Year)&NameView
Date2nd Calculation, of type Text = TextToDate(Month&"/"&2&"/"&Year)&NameView
Date31st Calculation, of type Text = TextToDate(Month&"/"&31&"/"&Year)&NameView
g.SetYear Global, Number
g.SetMonth Global, Number
Now preload this file with some data. Go to the ScripMaker and create and run the following script:
Set Field [g.SetYear,1999]
Set Field [g.SetMonth, 0]
Exit Loop If [g.SetYear>2100]
Set Field [g.SetYear, g.SetYear + 1]
Set Field [g.SetMonth, g.SetMonth + 1]
Exit Loop If [g.SetMonth>12]
Set Field [Year, g.SetYear]
Set Field [Month, g.SetMonth]
Now open Define Relationships and define 31 relationships, one for each of the 31 possible days in the month –
Second File First File
create also the following keys to allow administrators to view all folks' appointments for a given date:
One more thing. Go back into your first file and create a relationship to the second file from UnivKey to UnivKey. Then change the field definition of Attending. Click the Options button and set it to auto-enter the value of NameView from the second file by selecting the UnivKey relationship from the dropdown list of relationships and picking that field.
Now create a GUI and you’ve got a solution:
In the second file, the one in which you’ve just created all those relationships, go into Layout Mode and use the portal tool to create 31 portals, each to a different date relationship. Put the ones that go to MakeKey in the other file all one the same layout. When you’re done, drag fields onto those portals – one for Time, one for Description. At the top of the page put the Year and Month fields, and put the global field NameView there with a drop-down value list (Format: Field); define a value list of all the employees using Define Value List from this same dialog *.
Now you’ve got a calendar with each month on a separate record for all the months between January 1 2000 to December 31 2100. You can click into any day of the month and enter an appointment for the person selected in NameView. This calendar is muli-user and can be used by a dozen or more people simultaneously if you turn on sharing (File: Sharing), or by a couple hundred if you put it on a dedicated server and serve it up using FileMaker Server.
Make a similar layout except using the other 31 relationships, the ones that tie to ViewKey instead of MakeKey, and you’ve got an administrative view of everyone’s appointments all in one peek.
- Now that you’ve gotten this far, let’s replace that static value list of employees with a third file called Employees. Define fields for first name, last name, full name (calc, text, = first name&" "&last name), position, date of hire, salary, and so forth, and include a field for email address. Go back to the second file and change the value list definition to reference the new Employees file and display all values for full name. Define also a relationship from NameKey in the second file to Full Name in Employees; define new global field, g.EmailMsg (text) in field definitions and then you can define a script in the ScriptMaker:
Send Mail [ To: Employees::Full Name, Subject: new appointment, Message: “Please be advised that you've been scheduled for a new appointment...”,]
Now put a button in each portal and have the button perform the script and after entering an appointment you can have FileMaker auto-launch your email program and send out an alert email.
And you can have a script that goes into your first file every morning and looks for appointments for that date and sends out email to everyone attending.
Play with it
By February you'll be running your entire business with this thing :)