Request: In a law firm, the principal billing system is running on a UNIX-Server, access is provided through terminal services. The German offices need editable Word documents in the company layout with office-related account information. Data for single invoices arrive through FTP. Users should have the choice between different invoice layouts (short, long, with details, etc.)
Difficulties: The only possible interface are textfiles with the invoice data and timesheet details. These files have an old-fashioned printer report layout, they are not structured files (no XML). The files do not contain all necessary data, so that certain information must be pulled from the CRM database on the SQL-Server.
Solution: For performance reasons, the program is divided into a DLL (Visual Basic) and a VBA solution saved in the invoice template file. The selected textfile is analyzed, and missing data is searched in the CRM. During this process all data is transferred into an object structure, which allows the code immediate access to all invoice details.
The user can select the invoice layout, the destination language, the sender data and other elements in a dialog. The program then generates then the invoice, adds report pages if requested and inserts the bank account data of the relevant office.
Finally, the new document is saved automatically in the DMS using the correct client/matter-number. Edits by the user are allowed in some non-protected parts of the documents.
Request: A company in the service sector uses CRM software product. The controlling department asks for reports, which cannot easily be provided by the CRM system. The reports should be used not only by the controlling staff, but also by the sales staff for regular meetings. The sales persons should have the possibility to add information to the report data, but not to change the numbers. Excel is the desired format.
Difficulties: The source database is in Paradox. Due to the grown database structure some data need to be consolitdated and converted. Direct access to the production database in Paradox is not possible. The creation of a report data set via ODBC is technically not satisfying. The requested output tables are complex.
Solution: Multiple Excel-AddIns are providing the desired functionality. Not all AddIns are loaded on startup, but only the broker AddIn, which calls the others on request. One of the AddIns allows the creation of a monthly report data set in an Access database, into which the needed Paradox tables are transferred and consolidated.
In the following the sales person can create the reports for his area. Pivot tables or simple summarizing queries are not possible due to the complex table layout, so that dynamic SQL statements are generated on run-time and used for data transfer. Finally, the user can add text about indiviual sales actions in preparation for the meeting.
Update 2005: This project was developed for use in an desktop environment using Windows 98, Paradox and Office 97. In 2005 the solution was migrated to Windows 2003 Terminal Server, Office XP, and SQL Server 2000, with the need to redesign the AddIn procedures. In the new concept Data Transaction Services and Stored Procedures on the SQL-Server are used to generate the monthly report sets.
Request: During an interim period, i.e. until the implementation of new software, certain insurance contracts are to be be produced through a Word frontend. The data is transmitted nightly from the Host contract database to the PC file system in the form of position-based textfiles in ZIP archives. This is the starting point.
Solution: A Visual-Basic-Program monitors the transfer directory. On arrival of the files they are unzipped, copied to an archive and copied into the production folder. A Word AddIn creates the contracts from the data. Depending on the type of contract, different layouts (logos, headers and footers) and other additional documents (terms and conditions) are implemented. The rules for the combination of the variables to the text blocks are defined in Excel tables, which can be edited outside the program.