WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
The following is Chapter 4, "Whole Solutions," from The Microsoft Outlook Ideas Book by Barbara March, published by Packt Publishing.
So far we have explored unconventional uses of Outlook's individual components and we have utilized the business example of "Our Company" to demonstrate:
- Staff records: Recording and evaluating staff leave
- A vehicle pool: Recording and monitoring vehicle details, servicing, etc. and linking to users
- Suppliers: Organizing their records into an invaluable business directory
- Taxi booking records that could be applied to other services
In this way, we have been developing an Outlook solution for Our Company, but this solution is not quite complete, there is more that we can do for Our Company.
In this chapter, we will complete the solution for Our Company, integrating various Outlook and Office components, and will also explore another 'whole solution', a School-based example that we haven't yet introduced.
Our Company Solution
We will create a scenario involving both the Outlook and Office components.
Meeting Room Management
Meeting room bookings are commonly managed using a separate calendar, and these reservations may be for external organizations as well as for internal groups. The calendar in which these bookings are recorded will resemble an appointments calendar except that the items will refer to the rooms in which the meetings are being held rather than the meetings themselves. When more than one meeting is taking place in different rooms and at the same time, concurrent entries are inevitable and acceptable. A simplified and small portion of such a Meeting Rooms Calendar might look something like the following:
This view provides a clear image of what is happening in the meeting rooms over a short period of time. However, a schedule of meeting room activity over a greater period of time and with more detail about who booked the room, the revenue due from external bookings, etc. would be very useful for analysis and planning purposes.
Outlook can produce such a view of a Meeting Rooms Calendar that will
- Display a schedule of all current and future room booking details
- Distinguish between internal and external reservations
- Automatically calculate the cost to the external hirer
Before building the set of meeting room contacts, we first need to create the new Contacts folder. This is done in the usual way by right-clicking in the Outlook folder list and choosing New Folder and naming the folder Meeting Rooms.
Creating Contacts records for hirers needs no special instruction and the only field that needs to be completed on the Contact form for the rooms is the Full Name field; the details of the rooms will be recorded in the view and not in the Contact record.
We now have individual Contacts for each of the meeting rooms and Contacts for the hirers, all of which we can link to the bookings in the Meeting Rooms Calendar. We can now create the view of the Meeting Rooms Contacts folder that will display the details of the rooms.
Creating a Meeting Rooms View
- Create a new table-type view from the Define Views | New option and name the view Meeting Rooms.
- In the View Summary dialog box, click Fields and select the field Full Name, and click OK.
- We will create the following manual fields:
- Rename the Full Name field to Meeting Room, from the Format Columns dialog box.
|Capacity||Number||Raw||To enter the number of people the room can accommodate|
|Rate per hr||Currency||2 decimal places||To record the cost per hour to hire each room|
|Facilities||Text||Text||To enter the equipment etc. available in each room|
All four fields require you to enter manually the details about the rooms i.e. the name or number of the room, how many people it can accommodate, the facilities that it has e.g. projector, flip charts, whiteboard, teleconference facilities, etc., and the hourly rate for external hire.
The following screenshot shows Meeting Rooms view:
Creating the Meeting Room Calendar View
The steps to create this view begin with linking the bookings to the Contacts forms of the internal or external hirers and to the Contacts forms of the rooms. This will provide a bookings history by room and by person or company.
This view can also be adapted to display reservations by room, by date, by occupant, or by internal or external booking just by changing the way that the items are grouped. Finally, we will see how the calendar appointment form can be redesigned to integrate with Microsoft Word to print an invoice for the room hire and how Outlook can produce a monthly statement for the hirer.
- Create two new Categories for the room bookings named Internal and External.
- Enter room bookings into the Calendar in the normal Day/Week/Month view and assign either the Internal or External category depending on whether it is an internal or external hirer.
- Create a new table-type view from the Define Views | New option and name the view Meeting room calendar.
- In the View Summary dialog box, click Fields, and select the fields: Location, Subject, Contacts, Start, End, and Duration, and click OK.
- We will create three new manual fields that will be used to build one Address field and these fields will also be used later in the development of the form and the printing of the invoice:
- To restrict the bookings in the view to current and future, add a filtering criterion. From the Filter | Advanced tab create the following filter:
- To distinguish internal from external room bookings, apply the following Automatic Formatting rule:
- Exit the View Summary and from the Format Columns dialog box modify the following fields as shown:
|Add Line 1||Text||Text||To enter the first address line of the hirer|
|Add Line 2||Text||Text||To enter the second address line of the hirer|
|Add Line 3||Text||Text||To enter the third address line of the hirer|
|Name||Type||In the Formula Field window|
|Total Fee||Formula||IIf([Categories]="External",Format([Room Chrge]*[Duration]/60,"Currency"),IIf([Categories]= "Internal","Internal"))|
|Address||Combination||[Add Line 1] [Add Line 2] [Add Line 3]|
|Start||On or after||today|
|Formatting Rule Name||Field||Condition||Value||Font Format|
|Field name||New name||Format|
What Just Happened?
We have created the basic view as described and the fields will operate as follows:
- The Start Time field repeats the Start field but formats the contents to show only the hours and minutes, ("h:mm"). Outlook does not allow you to place a field in the header area more than once but you achieve this by placing the field that you wish to repeat within a custom field.
- The Room Chrge field will automatically return the room fee depending on the room number entered in the Location field. The Format function ensures that the result is formatted as currency; so £10.00 for Room 1, £30.00 for Room 2 and £15.00 for Room 3. If no room number is entered in the Location field, this field will return "Rate?", indicating that a rate cannot be determined unless a room number is entered.
- The formula in the Total Fee field is dependent upon the creation of the two new categories, Internal and External, and meeting room bookings in the calendar being assigned accordingly. The formula says that if the item is an external room booking (i.e. the calendar item has been assigned the External category) the charge should be calculated as the Room Chrge multiplied by the Duration, divided by 60. It is necessary to divide the Duration by 60 because the Duration field actually holds time values in minutes even though it may display in hours. This part of the calculation is enclosed with the Format(.............."Currency") function so that the result will be in currency format. If the calendar item is assigned to the Internal category the field returns the word Internal. This assumes that no charges are levied for internal bookings.
- The three manual address line fields have been concatenated into the single Address field to save space in the view and can be removed from the view by dragging them off the field header bar. However, three separate address line fields are required so that they can appear on separate lines in the merge to the Word invoice template described later.