KTS Systems, 1997 - 1998
Toronto, Ontario
www.kts.com -> www.SageNonProfit.com

KTS was a software products company with several products and 3 divisions. I was with the Adtraq software division. This software was used to manage advertising agency information needs. The Adtraq software had been around for about 20 years at the time when I had joined the company. It was written in PickBasic and ran on pc's/mini's using the Pick Operating System.

Technologies Used:

Visual Basic 5
SQL
SQL Server 6.0
Access 97

This division had been trying to develop Windows programs to interface/complement their core product over a few years. To this end, myself and 2 others were hired to carry on this effort. It looked like a good opportunity for me because I would be able to use some of the Pick programming knowledge I had learned when I had worked for TriplePC. It was a similar situation as with TriplePC but here they already were doing Windows programming using Visual Basic 5.

Print Rates Application (Visual Basic 5/Access 97)

The first project I worked on was an application to allow the data entry of advertising rates for print media across Canada: magazines, newspapers, newsletters, and journals. Once these rates were entered, another application being developed within the company would use these rates. So all I had to do was worry about the data entry.

This was a completely new application. Because this application was to be used only within KTS itself and it had to be done as quickly as possible, I chose to use Access 97. (Actually, this is one application where it should have gone straight onto the Net to allow publishers across Canada to enter their own rates when they wanted - I had mentioned this to them at this time but they could not see the wisdom of doing this.)

These advertising rates are published in the CARD (Canadian Advertising Rates Directory) catalog. This is what I used to determine how the data entry of these rates would be done and what structure the tables would take. The main tables:

Basic Publication Information
Region (for Magazines, linked to Basic Publication Information Table)
Colors (for Magazines, linked to Region Table)
Magazine Rates (linked to Colors Table)
Newspaper Rates (linked to Basic Publication Information Table)
Circulation Figures (linked to Basic Publication Information Table)

There were lookup tables for:

Languages
Regions of Canada
Colors
Publication Schedule(daily, weekly, biweekly, monthly, bimonthly, quarterly)
Category
Days of the Week

There were 2 main forms, the first was to enter/select the magazine wanted and from there a person could call up a Rates form. On the Rates form there were several data entry areas (subforms) being made visible/invisible depending on the type of publication being entered. There was a form for each of the lookup tables to allow information to be added or changed as needed. There was a form to allow copying magazine rates from one color to another within a publication. This helped eliminate repetitive data entry. Finally, I did the testing and debugging of the app before passing it on to another programmer for final testing.

Client Profitability Project (VB5/SQL/Access 97/SQL 6.0)

Purpose: To take a mass of accounting information and determine if a client was profitable to an advertising agency.

There were 3 goals of this project:

to examine a mass of accounting information
determine whether a client was indeed profitable to the advertising agency
allow the entry of budget numbers

This application was to look and work like a spreadsheet.

This was part of a 3-tier setup:

• the client pc module where requests for, display and entry of information took place(VB5 application) (the requests were made through remote automation of an ActiveX server component)
• the module where requests for and updating of information took place from the backend database(ActiveX exe server code component developed using VB5 and ran on the server, this was developed by another programmer with input from me as to the type of data requests were to be made and received)
• the database itself: storing queries and data (Access 97 and SQL Server 6.0 tables)

There was just one form in the client pc module. On this form there were:

• 6 combobox controls
• 1 Sheridan Tab control with 6 tabs and each tab had a vsflexarray grid control (ver 2.0)
• 1 Sheridan Tab control with 5 tabs holding controls for setting selection and display criteria

In the accounting information examination mode, the 6 combo boxes contained lists from which one or more selections from each could be made. These combo box selections together with settings from the one Sheridan tab control were passed to the middle module and arrays of data were returned.

There were 5 separate arrays returned, one each for:

Income
Billing
Expenses
Direct Hours
Direct Labour

Each array corresponded to a grid on the other Sheridan Tab control. Each array was put into its corresponding grid with subtotals and totals being calculated. Once this was done, the contents of the 6th grid was calculated based on the other 5 and this was to show the profitability of each client being displayed. The arrays covered 12 months of the year with numbers for budgeting and actual/projected.

In the budgeting mode, the combo boxes allowed the selection of various types of information to be placed onto the grids. Budget numbers could then be entered into individual cells. These grids had to function like spreadsheets to automatically do subtotals and totals across each row and down each column. Or, if appropriate, divide totals entered across each row and down each column. The idea was to have each grid in balance.

Once the budgeting was done, then all the information in each grid had to be sent back to the tables and saved for future use.

A separate table was added to the server to keep track of each user's settings and point of working. Win32 API calls were used to elicit who was logged in. This allowed the user to use any pc where the program was installed to log in and do their work.

Exporting of the arrays to text files was set up allow the use of the information elsewhere, ie: a real spreadsheet.

Win32 API calls were used to set up a dialog to select a directory anywhere on the network to which to export the files.

I used VB5 to create the client application.

Because this was a new application, real data did not exist. I created test data and used Access 97 SQL queries and tables to store it. The queries were needed to carry out the tests: select, update, delete, and append. Parameters were included where necessary. I transformed the resultsets into the arrays that were to be passed back to the main client module.

Arrays were used because resultsets could not be passed back from server objects.

I also checked that the same tables and queries could be set up under SQL Server 6.0. As the server side module could handle the information requests, the my test functions/queries were phased out.

I created the resource file to keep track of all the captions to be used in the application. I used Access 97 tables to store the different language versions and to generate the necessary resource file when necessary.