Nancy Lytle
word_diva at hotmail.com
Thu Aug 25 11:43:53 CDT 2005
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 >