Any company that maintains its corporate information in more than one database knows that giving access to everyone who needs it can quickly become a nightmare. Often there are few real options available; either everyone needs to be trained on every database application used in the company, or it's necessary to implement and maintain a complete custom software application that ties all the pieces together. The first solution introduces security problems (should your engineers really be able to edit customer billing records?) while the latter gives the company yet another unwieldy internal application with which to wrestle.
Fortunately, there are now two standard tools available that can combine to bridge the gap between corporate data and the audience that needs to see it--the BASIS ODBC Driver® 2.0 and the World Wide Web. This ODBC Driver provides an industry-standard database connection to your existing BBx® and PRO/5® data files. The World Wide Web brings together a number of world-standard interfaces and protocols that can make information available in human-readable format to anyone connected to the Internet. In combination, they form an amazing engine of data retrieval power.
BASIS is using this combination of tools to make detailed information about its customers available to our Technical Support analysts. The data is only accessible to BASIS employees, by way of the internal company network, but the techniques used to set up this powerful internal solution can be applied just as easily to any website, public or private.
This connection between data and the end user is made possible by the current generation of ODBC-to-Web connectivity tools. In our case, BASIS' Technical Services Supervisor, Nick Decker, developed a collection of search pages using Microsoft's Internet Information Server (IIS) webserver and Microsoft FrontPage 97 with the Internet Connector Wizard as the page development tools. However, there is a wide variety of other options available. Any web server that can connect to a Windows ODBC data source can be used with the BASIS ODBC Driver to provide similar access to data sources.
Setting Up For Access
To begin making data available online, the relevant data sources need to be accessible via ODBC. The BASIS ODBC Driver on the company's internal Technical Support web server has at least a half-dozen information sources available from a number of different machines within the company. None of these corporate databases were developed for the Technical Support web application; they were put together for their own specific projects and were later made available for standardized access through the BASIS ODBC Driver.
The two main databases that are accessible through the Technical Support web pages are the Technical Support Call Log database, which is maintained by the Call Log application used every day by BASIS' support staff, and a MAS90-based customer information database named 411. The Call Log database includes a large amount of information directly relevant to day-to-day Technical Support work, including a complete record of all contacts made between the customer and the BASIS support department. The 411 database, on the other hand, includes information on the various product licenses held by each customer, what products they are using on what operating system, and so forth.
By making this information available over the corporate intranet, BASIS has made it much easier to access. For example, managers do not need to use the highly-specialized Call Log application to check on the status of a Technical support issue--they can simply enter a customer name on a search page, select the appropriate incident from the list returned, and view the complete history of the customer's interactions with the support staff, right down to the amount of time spent on each call. From the other side of the fence, Technical Support now has quick and easy access to customer information, such as software revision levels and user license sizes, that can help them narrow down the problem when helping someone on the telephone.
Connecting to disparate information sources with IIS and FrontPage is simple and straightforward. These Microsoft products offer a number of Wizard-style tools that make issuing queries and returning results point-and-click simple.
The first step is to create the form that will initiate the search. It is possible to initiate an SQL search from a web page using only a handful of lines of embedded HTML code:
<form action="/cgi-bin/Detail_Cust.idc" method="GET"> <input type="text" size="35" name="CUST"> <input type="submit" value="Search"> </form>
This code displays a text entry box on the page with a
Search button next to it. When the button is pressed, the
user's web browser will take the text typed in the entry box, label
it CUST, and tell the web server hosting the page to search for that
text using an SQL query stored in the file
Before this request will actually do anything, however, it's
necessary to create the
From this information, the Wizard creates the
Datasource: 411 Template: Detail_Cust.htx SQLStatement: SELECTCUSTOMER.CUST_NUM, +CUSTOMER.NAME,CUSTOMER.ADDR_ONE, +CUSTOMER.ADDR_TWO,CUSTOMER.CITY, +CUSTOMER.STATE,CUSTOMER.ZIP_CODE, + +STR(CUSTOMER.PHONE_NUM,'(XXX) XXX-XXXX') + as CUSTOMER.PHONE_NUM, + +CUSTOMER.CONTACT +FROM CUST_NAME_SORT, CUSTOMER +WHERE (CUST_NAME_SORT.NAME LIKE '%CUST%%%') + AND (CUST_NAME_SORT.CUST_DIV=CUSTOMER.CUST_DIV) + AND (CUST_NAME_SORT.CUST_NUM=CUSTOMER.CUST_NUM)
When a search request comes in for an
Making Your Results Look Good
This is all very well and good, but how does the data get from the
query results onto a web page? That's why the
All of these options come together in this sample template, which is based on one of the templates used by our Technical Support department. (The HTML formatting tags have been removed to make it easier to see what the special template-related tags are doing.)
<%if CUST_NUM GT "0"%> <%begindetail%> Customer #: <%CUST_NUM%> Company: <%NAME%> Address: <%ADDR_ONE%> <%if ADDR_TWO GT "0"%> <%ADDR_TWO%> <%endif%> <%CITY%>, <%STATE%> <%ZIP_CODE%> Phone #: <%if PHONE_NUM GT "( ) -"%> <%PHONE_NUM%> <%else%> None Given <%endif%> <%enddetail%> <%else%> Sorry, no information matched your criteria. <%endif%>
The code between
It is a simple matter to extend the techniques described above to
almost any kind of data, from a corporate parts inventory to an
internal company telephone/email directory. The BASIS ODBC Driver has
the power to connect to your legacy data sources, and the Web has the
power to make those data available virtually anywhere. Together they
can make the power of your corporate information be felt
Copyright 1999, BASIS International Ltd. All rights reserved.