Sure would Like to View my VISTA Data Outside of VISTA....

If you have ever mumbled that statement to yourself, your worries are now over.  With the release of VA Fileman's SQL Interface (SQLI) Patch (DI*21*38), your VISTA data are now accessible by any ODBC compliant M to SQL product.
(For an update, download the Feb. 1999 White Paper.)

At the San Francisco VA Medical Center we wanted to have our VISTA On-Line Telephone Directory data available on our medical center IntrAnet.  The next step was to find a vendor offering a product that could interface with the new Fileman SQLI Patch.   We discovered such a vendor, KB Systems, Inc.   They distribute a package called KB_SQL.

The KB_SQL Package includes three pieces that are needed in order to query your M data via ODBC. 

KB_SQL
SQLI2SQL (or FM2SQL)
KB_SQL ODBC Driver

The KB_SQL Package prepared our Fileman data to be accessed via ODBC.  The next step was to find a development tool to do the web based interface to KB_SQL.  We found that Microsoft's Visual InterDev (v1.0) satisfied our requirement.  We also found a book titled   "The VISUAL InterDev Handbook", by Brian Masso which was very helpful in navigating Visual InterDev.

Visual InterDev works with Active Server Pages in conjunction with standard HTML files.  Standard HTML form syntax is used and the FORM ACTION= command calls the Active Server Page to process the form data and make the SQL calls through the ODBC Driver to KB_SQL.  I will outline our Staff Directory Inquiry Application here, and in doing so, provide some helpful hints on working with Visual InterDev. Following is the main (index.htm) page:

The main page provides three ways to look up Staff Directory Information:

The first choice is to look up information by last name.  The form action call from the index.htm page calls the Active Server Page phone2.asp.   The phone2.asp does the initial lookup to VISTA and provides a choice list of returned values that match the users input  The phone2.asp SQL Command calls the ST_PHONE_NAME Stored Procedure. 

The user can then chose the employee for whom he/she is searching and then display their Staff Directory Information.  The phone2.asp calls staff.asp to show the individual directory information (all three options call staff.asp as the final output) The staff.asp SQL Command calls the ST_PHONE_IEN Stored Procedure:

 

The second way to look up information is by service.  The index.htm form action calls the phone3.asp.  The phone3.asp returns a list of staff within a particular service.  The user may then select the staff for whom information is needed. The phone3.asp SQL command calls the ST_PHONE_SERVICE Stored Procedure.

The third way to look up information is by the first letter of the persons last name.  The index.htm form action calls the phone.asp.   The phone.asp returns a list of staff whose last name begins with the selected letter.  The user mat then select the staff for whom information is needed.  The phone.asp SQL command calls the ST_PHONE_LETTER Stored Procedure.

The San Francisco VA Medical Center's IntrAnet Staff Directory application extracts data from our Class III On-Line Telephone Directory application.  However, it can easily be modified to extract data from a facility's New Person File.

We are also testing the ability to update a persons digital and voice pager data from the IntrAnet. This process includes verifying the users VISTA Access and Verify Codes.

For additional information you can contact me, Kevin Magee at kevin.magee@med.va.gov.  You can also visit the San Francisco VA Medical Center's IntrAnet page or our Internet page.