CP/M

home *** CD-ROM | disk | FTP | other *** search

/ CP/M / CPM_CDROM.iso / enterprs / cpm / text / pipmag3.arc / DBASE2.PIP < prev next >

Wrap

Text File | 1988-01-07 | 12.8 KB | 312 lines

********************************************************************* THIS ARTICLE IS BEING PRESENTED THROUGH THE PIP ONLINE CP/M MAGAZINE OF THE NATIONAL CP/M ROUNDTABLE ON GENIE. copyright (c) 1989 BY BILL JULIANI. ALL RIGHTS RESERVED. PERMISSION IS HEREBY GRANTED TO NON-PROFIT ORGANIZATIONS ONLY TO REPRINT THIS ARTICLE OR PASS IT ALONG ELECTRONICALLY AS LONG AS PROPER CREDIT IS GIVEN TO BOTH THE AUTHOR AND GENIE. ********************************************************************* ============================================================================== CREATING & EXPORTING CDF FILES (Word Star Mail Merge Files) By: Carl F. Howard 11/05/88 GEnie Address: CARL.H Commodore !FlagShip* RT SySop ============================================================================== Hi All! This is the second of my dBase Articles for "PIP" the GEnie Online C-128 CP/M Magazine. The dBase column is intended to help C-128 dBase II users with the fundamentals of dBase II. If you have a dBase question or an idea for an article you would like to see written drop me a line and I'll be glad to help! Well, I know I promised articles on SORTING and INDEXING files and creating REPORT files for future articles but I just couldn't help myself when I got the idea for this one when playing with my new Upgrade to Word Star (Word Star Release 4.0 for CP/M). I felt this was a great topic and one that could help a lot of new C-128 CP/Mers as these two programs are the most powerful CP/M applications available to us. I will incorporate the SORTING and INDEXING commands into the article as needed so you won't be disappointed. We will hold off REPORTS till the next issue. So here we go. This article will discuss the creation of a dBase II CLIENT.DBF (DATA BASE FILE) which we can use to store (and manipulate) information about our clients. This data can be EXPORTED by dBase II to a Comma Delimited File (CDF), a data format used by the MAIL-MERGE feature of Word Star for printing form letters and merging text files with variable data. Although you can create a CDF file with any ASCII editor (WS N Mode) this can get quite cumbersome if you have a lot of data or a lot of variables (fields). This is due to the way the data is formatted in a CDF file as we will see. Unlike the SDF format, which is sorted in a fixed column format, the CDF format stores the information of each field by separating (Delimiting) the Data with quote marks and commas. Each field must be marked off with delimiters even if there is no information in the field. Each record is separated by a carriage return similar to the SDF format. The fields must all be in the same order on each line. The quotes are used so that commas can used if contained as data. To see Why this can be confusing to create on an editor look at some sample data bellow: 'Mr.','Alfred',' ','Wilmot',' ','A 1 Sports','2353 W Lomita Blvd','Lomita',' 'Mr.','Thomas',' ','Sullivan',' ','A A A Elect Entrprs','3170 E Willow St', 'Mr.','Jerome',' ','Aaron',' ','A A A Multi Crafts','1405 E 7Th St','Long Bch Also, using an editor does not allow you to reorganize (sort) the data once it has been created. dBase gives you the flexibility of storing all your data in a central file and generating CDF's in any order that you need them. Ie. ZIP CODE order, CITY or STATE order, CLIENT NAME order etc... The basic techniques discussed in this article can be used with any version of Word Star and dBase II to create data bases for form letters and envelopes or labels. :CREATING the CLIENTS.DBF -------------------------- The first step in this process is to CREATE a dBase II DBF. I'm going to use a FILE STRUCTURE that I use at the office for sending form letters to prospective new customers and keeping track of the over all results of the various mailings that we do. To do this load dBase and type the following at the dBase Dot (.) Prompt. .CREATE CLIENTS Type in the File Structure indicated bellow: Primary use database Fld Name Type Width Dec 001 GNDR C 004 002 FIRSTNAME C 010 003 MIDDLENAME C 010 004 LASTNAME C 021 005 TITLE C 010 006 COMPANY C 025 007 STREET C 033 008 CITY C 019 009 ST C 002 010 ZIP C 005 011 PHONE C 012 012 SOURCE C 006 013 DESC C 025 014 SIZE C 006 015 MAILED C 008 016 CNTDT C 008 017 COMMENTS1 C 040 018 COMMENTS2 C 040 019 X C 001 (Note: An Actual DBF with sample data is contained in the ARK/ARC file containing this article you can use it in lieu of actually making your own DBF). Once you have finished entering the structure for Field 019 above hit return and at the prompt for field 020 hit return again. You will then be prompted to start adding your data. Enter some sample data or just use the the DBF that came with this article. Once you have finished entering the data you are ready to EXPORT it to a CDF file. If you want to use the data in the order that you have entered it you can proceed to the next step however, if you would like to change the order lets say, to generate a ZIPCODE ordered CDF file you will have to INDEX the DBF. Which gives me the opportunity to cover the SORT and INDEX COMMANDS. :SORTING vs. INDEXING --------------------- To create a New DBF whose records are physically organized on your disk in ZIPCODE order and not the order you entered them in, you can use the SORT command. To do so, you would type the following at the (.) prompt. .USE CLIENTS .SORT on ZIP to CLIENT2 This tells dBase to SORT on the ZIP field and send the output of the sort to a new file named CLIENT2. The problem with the SORT command is that it is an inefficient way (from a disk storage standpoint) of accomplishing this purpose. Which brings us to the concept of indexing. An INDEX file can be created instead. Rather than creating a new DBF each time we want to Display/list or otherwise organize the DBF we can create an INDEX file INDEXED on the FIELD who's order we want to use. Basically and INDEX file Points or tells dBase where the PHYSICAL record is Located on your disk. Essentially dBase looks at the data stored in the field you want to organize your DBF on and creates a sorted index file pointing to the actual record location on the disk. This is much more efficient and takes much less disk space than creating a whole new DBF each time we want to reorganize the data. Another advantage building index files is that by indexing the file you can use the FIND command which searches the INDEX (.NDX file) for data instead of the LOCATE command which searches the DBF. This obviously speeds up the time it takes to retrieve data, as indexed files are almost always smaller than the DBF's used to create them. Also you can INDEX the DBF on Multiple fields. We will get into the FIND and LOCATE commands in a future article. Suffice it to say INDEXING files is what gives dBase it's power in data retrieval. All index files end with an extension of .NDX. Generally when naming index files you will want to use a name that indicates both the DBF Name and the FIELD name that they are using. For example, we are going to name this zipcode indexed file CLZIP.NDX. CL for the clients DBF and ZIP for the zipcode field. If we were using the company field, we would use a name of CLCOMP.NDX or CLCO.NDX. To create the zipcode index you would type the following command at the (.)prompt. .INDEX on ZIP to CLZIP This command tells dBase to build an .NDX file named CLZIP.NDX using the data from the ZIP field. This index will stay active until you quit dBase, use or build another index or open another DBF. If you want to access your records in the original order you can cancel the INDEX by using the following command at the (.) prompt. .SET INDEX to This command will close the current index. If you ever want to use the CLIENT.DBF in zipcode order again you can access it in two ways. The first lets you select the index when opening a DBF. .USE CLIENTS INDEX CLZIP The second lets you change from another INDEX or SET it once a DBF file is already in use. .SET INDEX to CLZIP. WARNING: As a General rule whenever you have created one or more INDEX files for a given DBF you should always open it when you are editing or appending records to the DBF. If you don't do this the INDEX will become invalid and you will encounter at the least a RECORD OUT OF RANGE error and risk the chance of loosing data or getting unreliable results. If you get an error message when accessing data on INDEXed DBF use the following: .REINDEX This command will REINDEX the currently selected INDEX. : CREATING THE CDF FILE ------------------------ To EXPORT the data to a CDF format use the following command: .USE CLIENTS INDEX CLZIP (OPTIONAL IF THE DBF IS NOT ALREADY OPENED) .COPY to CLZIP.CDF DELIMITED These commands opens the DBF and selected INDEX files and then copies the data in zipcode order to the NAMED delimited file. Note: You must include the extension .CDF and the word DELIMITED. Its that SIMPLE <SMILE> : CRATING A WORD STAR MASTER DOCUMENT FOR MERGE PRINTING --------------------------------------------------------- Using the Mail-Merge feature of Word Star requires the creation of TWO files. The First is the Data file which we have have just discussed above. The second is called the MASTER DOCUMENT FILE which contains at least three sections. First you must tell Word Star the Name of the File containing the DATA to be used. This is accomplished by using the following WS Dot Command. .DF <FILENAME> Next you must define the Data Variables. This is accomplished by using the following WS Dot Command. .RV FIELD1 FIELD2 FIELD3 Etc. .... Finally you will have a section containing the Text and/or Data Variables you want printed in your document. The following is a commented MASTER DOCUMENT to create a form letter using a CDF file created from the the CLIENT.DBF. Note: The actual working MASTER DOCUMENT is a file called MASTER.DOC contained in this ARC/ARK file. ---------------------------------------------------------------------------- .OP (WS Dot command to Omit Page Numbering Used in W* Ver 4.0 ) .DF CLIENTS.CDF (WS Dot Command to Define Data File NOT PRINTED) .RV GNDR,FIRSTNAME,MIDDLE,LASTNAME,TITLE,COMPANY,STREET,CITY, .RV ST,ZIP,PHONE,SOURCE,DESC,SIZE,MAILED,CNTDT,COMMENTS1, .RV COMMENTS2,X (WS DOT Command to Define Data Variables Must be in the same order as found in the CDF) (Note: THE ABOVE LINES ARE NOT PRINTED When the (.) begins a line. ) November 11, 1988 &GNDR& &FIRSTNAME& &LASTNAME& (NOTE: & & Precede & Follow DV Name) &TITLE/O& ( /O - Omits Blank Line if Variable does &COMPANY/O& NOT exist in the Data File) &STREET& &CITY&, &ST& &ZIP& Dear &GNDR& &LASTNAME& This is a sample letter to demonstrate the Mail-Merge features of Word Star. Type your own letter here! Sincerely, Your Name .pa (This is a WS Dot Command for a Page break and sends a Form Feed to the Printer when this file is being executed.) --------------------------------------------------------------------------- Next months Articles will be on and creating REPORT files for printing list of records in your DBF. After we have mastered some more of the the fundamentals I'll show you how to write simple dBase programs (.CMD FILES) and create Menus. Regards, Carl.H