John W. Colby
jwcolby at colbyconsulting.com
Thu Aug 25 12:38:02 CDT 2005
Nancy, The system I use started with a spreadsheet of field definitions I was given by the insurance company (AGI) who is my client's client. So they sent me something in spreadsheet format that looked like: AIGFldName StartPos EndPos FldLen Format And I added on from there SomeCurrFld 1 10 10 Cur SomeTxtFld 11 16 6 text SomeDteFld 17 24 8 Date Etc I took this information and pulled it into an Access table. I then added MyFldName MyFormatStr Critical IsUsed Etc So I now have a single table where I have what their field is called, where it is in the fixed width string, my matching field name, the format string I need to use to get it into the format they want, whether it is critical (don't send without it), whether I actually have any data (field) to go in that field etc. What I then did was to build a clsFld that took each FIELD DEFINITION (one record from this table) and loaded all the data for that record into variables in the class header. Each clsFld instance is then saved in a collection in the supervisor of this class (clsRecord), with the KEY being the field name in MY database. I designed my system such that there is a clsExport class, a clsRecord, and a clsFld. The record class is instantiated once, loads the clsFld class instances and stores them into a collection in the record class. A method of clsRecord is then called by clsExport (supervisor) class, passed a single record each time it is called. The clsRecord processes each field in the record it is passed - iterates the field collection using dao (could be done id ADO though). Basically clsRecord iterates through the fields in the record passed in, calls a method in the matching clsFld, passing the data to clsFld. ClsFld formats the data with the format string (remember everything is a string in the end), justifies it in the string of the required length (remember the length field?) and hands back the string to clsRecord. ClsRecord then inserts the formatted data into the "bigString" beginning at StartPos. When clsRec has iterated all the clsFld instances in the collection of clsFld, it is done with the record and has a "big string" with data in specific places in "big string". clsRecord then hands that string back to clsExport which is responsible for storing the "big string" in a file out on disk. In fact clsExport sets up a clsLog when it opens, with the path to the file to be created as well as the name - with date etc in the name. clsLog is just called passing it "big String" and it logs the string out on the disk file. So... To summarize, clsExport instantiates clsRecord (one instance) which loads as many instances of clsFld as are required to handle exactly and only the fields in the FieldDef table that are marked "Active". Each clsFld instance "knows how" to format data for one field. It knows the data type coming in, the format string to get it in the desired format, the length of the string that the formatted data is going in, whether it is left or right justified etc. ClsExport loads the recordset of data to be exported, calls clsRecord.Format once for each record to export. clsRecord calls each clsFld.Format to get each field's data formatted correctly, loads the formatted data into "big string" and hands "big string" back to clsExport. clsExport hands BigString off to clsLog which gets it into the file out on the disk. One of the bigger issues I ran into was the "denormalized" data I had to include. There are (for example) 1 to N "deductions" to be made from a claim payment, and I had to get all such deductions out of a normalized form, into a denormalized form, and then that denormalized data joined to my main data record, and processed into the "Deduction1, Deduction2, Deduction3..." fields in "big string". Thus I have an entire process that looks at the deductions, the date windows that the deductions are valid compared to the date window that the payments are for, and then flattens them into a table. Obviously the client defined N fields for this data (20 it turns out) so I had to handle a maximum of 20 deductions and get them flattened into a single deduction record to join back to the main claim record being processed. Obviously there is more, but that is the big picture and should be enough for you to start thinking about how to do things using compartmentalized code (classes). The format table is a nicety that allows me to make modifications to the system quickly and easily as the client changes their requirements. In fact I am in the process of porting my system to VB.Net. This stuff is just VB. It makes absolutely no use of Access' RAD or GUI stuff so there is no reason it couldn't be done directly in VB or VB.Net. In fact Access is less than robust in terms of its reliability as I have discovered with this system. If I were you though I would think about doing yours in VB.Net rather than VB6. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle Sent: Thursday, August 25, 2005 12:44 PM To: dba-vb at databaseadvisors.com Subject: RE: [dba-VB] VB.Net - Hook an open program NOW: Report generationapplication I have just started a on an application and database that takes in claims from doctors offices (this is in the USA) and then processes them and sends them to the providers. We get the data in many forms but it all has to go out in one format, which means we do a lot of padding of fields, and other formatting to make sure each field is in the right place and formatted correctly. Although I am working with SQL Server/VB, the table based solution you came up with sounds very intriguing, with easy updatability for any formatting changes. I'd like to see if I could replicate something like that in my SQL/VB environment. If you would care to share any more hints or pieces of how you accomplished this, I'd be very interested, as I am sure others would be. I must say you constantly amazed me with the techniques that you come up with to handle situations. Nancy Lytle N_Lytle at terpalum.umd.edu >From: "John W. Colby" <jwcolby at colbyconsulting.com> >Reply-To: dba-vb at databaseadvisors.com >To: dba-vb at databaseadvisors.com >Subject: RE: [dba-VB] VB.Net - Hook an open program >Date: Tue, 23 Aug 2005 17:32:05 -0400 > >OK< sorry, I just fired off that response. > >This is a system that pulls data out of a set of related tables and >massages >it into a text file. The end result is a fixed width report (at this >moment) in a SERIOUSLY denormalized format (ThisFld1, ThisFld2 etc by the >DOZENS). This report will be sent to a client Insurance company where it >will be imported into a mainframe. > >The "jobs" are reports that have to be sent. The same report may have >to >be >sent multiple places, attached to an email in one case, FTPed in another. >It is in fact also being sent to our own people (MY client, the call >center) >in a CSV format. Related records from a DOZEN tables (or more) have to be >pulled, some have to be denormalized (placed in a temp table) then >everything linked back together. Once the data is in a "single record" >format (SERIOUSLY DENORMALIZED) it has to be exported out to a fixed width >format. Unfortunately the client keeps changing where the fields go in the >record, what the format looks like etc. > >And yes, this is happening on a regular basis. The client (insurance >company) is moving their entire company to a new software package, they >are clueless as you might expect, and things move slowly. TWO years so >far (and counting). They have even flopped back and forth between >Fixed width and comma delimited (and back again). But... I design to >allow me to just change the table. > >As a result, I have set it all up so that it is table driven. I have a >table of what they call their fields, what position in the "big string" >each field (start and stop bytes), the name of the field in MY >application which maps to the field in THEIR application, the format >string that makes it look >like they want, whether the field MUST exist in order to even allow the >export to occur etc. > >I read the format table out and in to a field def/supervisor class >system. Another class causes the data denormalization to occur, handles >some details like checking that offsets (dates) fall within specific >windows, pulls the data out of my query and hands it off to the field >supervisor which hands each data piece to the appropriate "field class" >which formats the field per >the instructions in the format table and hands back the piece to the >supervisor which inserts it into the "big string" in the right place... OR >into a comma delimited "CSV" format string, and hands the finished >formatted >"big string" off to the data logger which writes it out to a file on a disk >with a specific name and a specific location on the hard disk. > >Once the entire file is written, the finished file is placed in a "job" >queue to be sent out to wherever it is going. > >This process is an entire application, with (in Access) 11 classes and >a handful of modules. There is absolutely nothing trivial about doing >this. The upside is that as the client comes back and says "no, move >this field to here, left justify in the field instead of right, and oh, >by the way it should have 4 decimal digits after the decimal point >instead of two", I can just go into my definition table and make the >appropriate changes, and the new report pops out as they request. >Furthermore the process is broken down >into "black boxes" that create the export data, logs the finished export >string, logs the fact that a given record was exported and what file on the >hard disk it was placed in, transmits the file to the correct place, logs >the fact that the specified file was transmitted. When I am finished I can >run a query to see if a claim record was sent, what file it is in and where >on the disk it resides, what day and time it was sent, what method and >address was used to send it etc. If a record fails at any step, an email >is >sent to me and my "boss" at my client informing me that step X of the >process failed. > >Doing that does NOT allow "setting it up in the BE and do not >interfere". This is NOT an FE in the classic sense, it is a report >generator / logger / sender application, which, just happens to be, in >an FE (of its own). All of this is working at this point but it is >written in VBA / Access FE container. I am looking at moving the >entire thing to .Net, on my own dime, as an exercise in learning >VB.Net. > >Now... For the piece about which I am asking, I am discussing the >scheduler part, what report is sent to whom, using what transmission >method, at what time of the day/night, including / excluding holidays / >weekends / custom schedule etc. The client wants it, and the client is >willing to pay for it, so the client gets it. I am just examining my >options for moving JUST THIS PIECE (since the whole thing is modular >anyway) into VB.Net. > >Does that make it a little clearer? ;-) > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > >-----Original Message----- >From: dba-vb-bounces at databaseadvisors.com >[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller >Sent: Tuesday, August 23, 2005 4:49 PM >To: dba-vb at databaseadvisors.com >Subject: RE: [dba-VB] VB.Net - Hook an open program > > >I think that you are going about this is in a seriously wrong way, JWC. >IMO, >you create the jobs and schedule them and keep the FE _way_ out of the >picture. Unless I am misunderstanding you, you are putting the FE before >the >BE -- which IMO is the biggest no-no in the SQL business. To put it another >way, everything you can blame on MS should be blamed on MS. Set it up in >the >BE and DO NOT INTERFERE. Not to say there are no reasons for services, but >from what you have described thus far, this is NOT one of them. Schedule >the >job at a frequency you like, then leave your FEs do realize the results. >You muddy the waters too much by asking the FEs to muddy the waters. You >are >the worm chasing its tail, IMO, in the direction you are going. AF`s Rule >1: >everything the BE can do, the BE should do. >Corollary: no code affecting scheduled sprocs, udfs, etc. should exist in >the FE. > >-----Original Message----- >From: dba-vb-bounces at databaseadvisors.com >[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of John W. Colby >Sent: August 22, 2005 11:06 PM >To: dba-vb at databaseadvisors.com >Subject: [dba-VB] VB.Net - Hook an open program > >I want to load an application that loads scheduled tasks into classes >from >a >database. Once the tasks are loaded I want my service to call this >application and ask it to run any tasks that the scheduler deems ready to >run. Is it possible for one program to call another open (running) >program? >I would like the scheduler to be part of the service but until the system >is >debugged I want the scheduler to be a program with a user interface that I >can watch. For this reason, the service would cause the scheduler to load, >and then periodically "call" the scheduler and ask it to check its list of >tasks and run any as appropriate. Is this possible? > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > > >_______________________________________________ >dba-VB mailing list >dba-VB at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-vb >http://www.databaseadvisors.com > >_______________________________________________ >dba-VB mailing list >dba-VB at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-vb >http://www.databaseadvisors.com > > > > >_______________________________________________ >dba-VB mailing list >dba-VB at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-vb >http://www.databaseadvisors.com > _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com