Many offices, particularly in military and government organizations, are required to have someone in charge during office hours. If the official manager is absent, that person delegates responsibility to someone else as "acting," but who?
It would be nice to have a Web page where everyone could quickly see who is here--today and who the delegated officials are--today? That's what this application does. A "Key Personnel Today" table shows who is acting in every official position and how to reach them. Here is a sample screen shot:
In the above table, the bold font shows people who are "acting" today for the absent official in the previous row. E-mail, telephone, and fax numbers are also provided here. This table presents, in one place, all the information needed to immediately contact officials in the entire organization.
ASP code behind the table takes care of a lot of the administrative tasks automatically. When an official returns to duty (on a previously specified date), the acting official's data is automatically deleted from the table.
The application gets the date information from a very simple form that the official manager fills in prior to an absence. It looks like this:
When the official manager fills in this form with an employee's name, the
ASP finds that employee in the general employee database and stores this employee's data in another database. This is the data that is viewed on the Key Personnel Today page. Note that there are two dates to be specified, the start and end dates of the anticipated absence. These are also stored in the database and are used to display the acting manager data only within the specified date range. When this form is filled in, the user has an opportunity to review the data, and if correct, it is then stored.
Note that in the Add form, any unique substring is sufficient to find the name of an acting employee; the whole name does not need to be spelled out.
Expired data is removed from the Key Personnel table, but they are not destroyed because sometimes it may be necessary to determine who was in charge on a particular day (e.g., to determine who may have approved a decision or signed a document.) So the expired data is sent to an archive, which also may be viewed if desired. A typical archive sample table is shown below:
Finally, functionality is provided for deleting records in case a manager's plans change and the names or dates of travel need to be revised. In this case, the old data in the active database could be deleted and new data added using the previously shown form. The delete page looks like this:
Here, the user simply checks any boxes desired to delete these records from the active database (the data displayed in "Key Personnel Today" when the dates are within range). Note that (as stated on the page) such deletions only have to be done when the previously entered data is incorrect; expired data will be removed from the display automatically.
Program Code
This application is implemented in two Microsoft Access databases and five Active Server Page (ASP) files written by the authors for practical use in a government facility. In designing such an application, our usual practice is to first create a "structured English" or "pseudocode" plan for the functionality. This can be done in Notepad or any other text editor, but we consider it helpful in order to clarify the processes that need to be developed. It also can provide brief but adequate documentation of the functional steps. So this application has the following pseudocode:
------ Add Acting Manager page ------
form
present entry fields for code, manager name, acting manager name,
start and end dates, and an optional comment.
click to preview form data
validate the form data with client-side JavaScript
post form
----- Preview page (part of Add page) ----
determine acting manager's code and name from posted data
open Employees database
select records that contain a match to the acting manager's name and code
if there is no match
ask user to re-enter name data
else if there is more than 1 match
show the user a table of the options available, ask for choice
put the chosen data in hidden form variables
else if there is exactly 1 match
get all of the acting manager data from the form
display a preview of the data in text format
put all the variables into hidden form elements
post the form data
close Employee database
----- Processing page ----
put all the Request.Form variables in an array
create an SQL statement for all the form data
open Acting database and insert form data into a new record
trap errors
close Acting database
---- Key Personnel Today page ---
get the current date
open the Acting database:
acting managers table
official managers table
archive table
loop on the rows of the official managers table
reset the cursor to beginning of recordset
read a row of the official managers table
find any matches of official manager and acting manager
if there is a match of code and official manager name
if current date is after end date
insert record into the archive table
delete record from the acting table
else
if current date is after start date
write a row of data in the acting table
end if
end if
end if
end loop
close databases
end
---- Delete Incorrect Data page ----
form
open the Acting database, acting managers table
display all records with checkbox
post form data
if posted form data contains checked row(s)
delete rows checked in the database
close database
end
---- Archive Table page ----
open the archive database
loop on all records
display rows in archive
close database
end
Now you have a rough outline of the logic for the whole application. The download includes a compressed set of the ASP pages, style sheets, this documentation, and two Access 97 databases (with fictitious names).
Using the App in Your Environment
In order to use the application as it stands, you will of course need to use your own employee and manager data. You can adapt the fields as needed; in the application most of these fields are not used. Only the organizational code and name fields are used for matching purposes. There also may be a need for additional care in processing names to handle accents that may appear (apostrophes, such as in O'Brien, are handled OK).
The file Data Source Names for all the databases use Object Linking and
Embedding Database Object (OLE DB) providers in Microsoft Data Access
Components (MDAC) 2.1, and they assume that the databases are located in the C:\Access folder. Here, these data source names (DSNs) are incorporated directly into the code for portability. It would probably be better to put them in your global.asa file as application variables. Then they could be easily edited or updated. Visual InterDev provides a convenient, graphical way to do this.
Security for the pages is not included in this example. You may wish to restrict access to the pages, although it is probably not necessary if the application is on an intranet.
This application underscores the importance of having a robust, general-purpose employee database upon which applications may draw. All organizations should use such a general-purpose database as a Lightweight Directory Access Protocol (LDAP) server or Microsoft Active Directory. This simple application can pay for itself in lost time as people try to find out "who's here today?"
About the Authors
Paul Arveson is a management consultant and Web analyst. Until recently he worked in Web development and strategic planning for the U.S. Navy, where he earned a Chief Information Officer (CIO) Certificate from the National
Defense University. He also has a recent MS in Computer Systems
Management, and has been working with ASP and Visual InterDev to develop business applications. He founded the Balanced Scorecard Institute, devoted to strategic management applications, which has a Web site at
http://www.balancedscorecard.org. He can be reached at
paul@balancedscorecard.org.
Alvin Lin is a student at the Thomas Jefferson High School for Science and
Technology in Alexandria, Virginia, where he has worked with a team to create advanced multimedia Web sites. He is also an accomplished pianist and violinist.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
