The magazine of the Melbourne PC User Group Xbase Programming - Part 1 Gary Taig garyt@melbpc.org.au

*|*****************************************************
	 *| Program  |  MEMBERS.PRG
	 *| Author   |  Gary Taig
	 *| Date     |  August 1991
	 *|          |  Xbase Programming Tutorial - Lesson 1
	 *|          |  PC Update, October 1991
	 *|          |
	 *| Purpose  |  THIS IS A SAMPLE WORKING SYSTEM FOR ALL
	 *|          |  BEGINNERS TO Xbase PROGRAMMING.
	 *|          |  Suits dBASE, FoxBASE and compatible
	 *|          |  software for the PC.
	 *|          |
	 *| Calls:   |  Programs:
	 *|          |  List program names as required &
	 *|          |  describe briefly what each of those
	 *|          |  programs does.
	 *|          |
	 *| Called   |  Where a program is called by another,
	 *| by...    |  note that in the header as well. Saves
	 *|          |  much time searching later.
	 *|          |
	 *|*****************************************************
	 *
	 *  REVISIONS:
	 *
	 *  Mods: (date) (name) changed this, added that etc etc
	 *        (date) (name) deleted ... routine, replaced by
	 *        this and that routine.
	 *
	 *        All revisions completed and the REASONS for
	 *        the changes can save many hours of work at a
	 *        time when you don't have free time available.
	 *
	 *******************************************************

	 Figure 1. The Program Header.

Program Comments

Every line that begins with an asterisk is a comment line. Comment lines are ignored by your Xbase software but they are as critical to a computer program as maintenance manuals are to a motor car repair shop.

Also, small comments can be added at the end of a PROGRAM LINE provided that you start them with a double ampersand like this example following ....

Your Xbase software ignores everything from the double ampersand onwards. You should leave at least TWO blank spaces between the end of your Xbase commands and the "&&".

Continuing Lines;

The SEMI-COLON at the end of a line always tells your Xbase software that this instruction is continued on the next line.

You will see that here occasionally because we are restricted by column widths. In such instances you may ignore the semi-colon if desired and continue typing the command on the same line - up to the MAXIMUM allowed by your software. Look in your particular manual for limits on the length of a command line. There is precious little chance of us ever going anywhere near that limit in these lessons.

Practice

What's wrong with the following program code...?

The problem there is that your Xbase software will look to the second line as a continuation of the first. Therefore it will NOT recognise the asterisk as the start of a comment line and it will try to perform several commands in one, becoming horribly confused in the process.

• The semi-colon makes the second line a part of the first..
• normally the asterisk "*" is used for multiplication inside an expression...
• STORE is a special reserved word. We would be trying to multiply "John", a character string, by a reserved word..

***************************************************
For those who have programmed in any of the above languages, one difference between Xbase and the above is that normally with Xbase we will CREATE the DATA FILES beforehand, as a separate exercise. Then, inside the Xbase program, we will open the required file with the command...

      USE <filename>

Data files can be and often are opened and closed many times during the operation of an Xbase program. An open data file is subject to corruption in the event of a power failure. A closed data file is much less vulnerable to damage or corruption. It is good practice to leave files closed at all times and open them ONLY for the purpose of writing data into them after that data has been obtained from the operator and verified. Remember this important principle when you are programming.

Our DATA FILES are CREATED at the DOT PROMPT. This is the line on your screen immediately above the Status Bar in dBASE and FoxBase. If your CONFIG.DB (or CONFIG.FX) file contains a line
    prompt=<something else>
you will NOT see the dot prompt, you will see the <something else>. Experiment with it!

If you place a line in your config.db or config.fx file that reads...

     prompt=Command(255)

where the (255) means to hold down the ALT key and press 255 on your numeric keypad - next time you start Xbase your DOT PROMPT will be replaced with the word "Command" followed by the invisible ASCII character 255 to separate it from the commands you type.

You should also read the appropriate section of your Xbase manual and test all the other options able to be set in the config file. We will cover some of those options in later lessons.

With FoxPro you have a command window which is essentially the same thing as the DOT PROMPT. The only difference is that when FoxPro starts up you don't see the Status Bar. If you have upgraded to FoxPro but would feel more comfortable with the Status Bar visible, type Set Status ON {enter}. It will appear, and mess up your nice FoxPro screen.

This activity is all interactive. "Interactive" means, you type the command and press {enter} or perform some other action, the activity occurs.

Irrespective of which System you are using, if your software starts up with something like the ASSIST in dBASE or FOX CENTRAL in FoxBASE, already activated, get out of it. Change your Xbase config file to ensure that it does NOT appear any more. We are going to learn how to get by without them, we will learn how to program our way through each task.

The one thing we won't program yet is creating data files - that activity will take place at the DOT PROMPT. When necessary we can create data files from within an Xbase Program but it requires use of some of the more advanced features and I will leave that until later. We may even cover it in a separate article.

Back to our discussion about the DATA itself. If you are not completely comfortable with the term "field name" refer to "Eight Bits of Impact" mentioned earlier.

Field Names

Names selected for fields should always be easy to understand. For example, lets say we are storing a persons preferred name on file as well first name and surname and so forth. We should select a name like PREF_NAME. We will not mistake it in future and this makes everything easier to follow. Refer Figure 4, field listing.

When we name a field "PREF_NAME", to hold the person's preferred name, we would select DATA TYPE as character and we might choose a length of (say) 12 characters.

These are the field's attributes - its USAGE, its NAME, its LENGTH and most important, its DATA TYPE.

Your software is not affected by the USAGE. Nor is it necessary to enter that anywhere, just keep a note of it, as I have demonstrated in the listing in Figure 4 - we will describe how to do that later in this lesson.

Data Types

Data Types are very easy to understand and select. Data is stored on disk or in memory in a form that your Xbase Software system can best manipulate, based upon the way in which you will use it.

Character Data
Character is the most common data type. Character data is like what I'm creating as I type this. Anything that is straight TEXT is usually referred to in a data environment as CHARACTER.

Names and addresses, product names, short comments and similar things are always stored as data type CHARACTER In a few moments we will cover "MEMO"s, another kind of Xbase character data

Note that street numbers, post codes, telephone numbers and ALL OTHER data that some people THINK is numeric. Just because they are digits, should ALWAYS be stored as data type CHARACTER.

Often you will come across the phrase "character string" . This is simply a string of characters, like a telephone number, or a sentence. Any group of characters appearing together make up a "character string".

Numeric Data
Data Type Numeric is selected and used ONLY when calculations are to be performed on the data. For instance you WILL add or subtract salary or wages, so store this as data type NUMERIC. You WILL multiply by TAX RATES, so store those as NUMERIC. Got the picture? Surely!

There is a further breakdown of numeric data if you are using dBASE IV or FoxPro. The available types are N and F. Study your manual to determine how these can be selected and used to suit varying requirements.

Logical Data
The next data type we use in Xbase systems is LOGICAL data. Logic is a word used in many ways but here it relates to a condition and simply equates to TRUE or FALSE. A LOGICAL field in a record uses only ONE byte of space and often this proves to be very handy. We can use LOGICAL data fields to store lots of very handy information in a small space. We'll see examples of that later.

Data Type Date
DATES are stored specifically as type DATE to make easy the task of what is known as "date arithmetic". This is quite simple to comprehend. If we have a certain date stored in a field named "birthday" and wish to find the date of the same day TWO WEEKS EARLIER we could use the simple expression "birthday-14". That is, the date of the birthday LESS 14 days.

We might use that in a program Each morning our system could run a birthday card advance reminder activity. One way we might do that is as follows...

Our first line of program code might read:
      today = DATE()

"today" is a memory variable we have just created and we have assigned to it, the value of the date on your machine.

DATE( ) is an internal Xbase function which reads and returns your computer's date. (Naturally the correctness of your program results depend upon the date you have set on your computer).

We will cover the words "variable" and "expression" next lesson so don't worry about these if you are not familiar with them.

Our second line of program code could be ....
       action = today+l4

This uses date arithmetic to create yet another variable named "action" which holds the date TWO WEEKS FROM TODAY. With this we could scan through a file conducting the following check against a field named "birthday", in each record...

Before we leave data type DATE - how would you create the variable named "action" in just ONE LINE? Yes, we would use the following, more efficient expression to achieve the same result...
     action = DATE()+14

However, don't allow that to confuse you because we will get into the programming side of things later. For now we are still looking at the various data types.

By the way, if you didn't properly understand the meaning of "expression", read the last two paragraphs again and it should now start to become a little more apparent.

The two brackets ( ) after the word DATE indicate that this is a FUNCTION rather than just a reserved word. We will cover the uses and the programming power provided by functions in our next lesson. For now it is sufficient for you to understand that there is a fundamental difference between a FUNCTION and a RESERVED WORD.

Data Type Memo
Data Type MEMO is the last of the standard Xbase data types we will encounter. "Memo"s are CHARACTER and they make it possible for us to store much longer, variable length, character strings.

When we are storing a street name for instance, we will select a FIXED length field of data type character. We simply determine the LONGEST name required stored and make that space available as a normal CHARACTER FIELD.

However, if we wanted to store variable length notes or comments about the person in each record, then we would use data type MEMO. In an earlier example I mentioned the storage of product names in a character field. In this case the product's description, which is likely to be a great deal longer and varying immensely in length, would most often be more suitably stored in a MEMO field.

Comparison of DATA TYPES

IMPORTANT: Data stored as one particular type CANNOT be compared with data stored in a different format. Remember we said earlier that Post Codes and things like that should be stored as TYPE CHARACTER?

Say for instance you have stored a post code as type CHARACTER and you want to find the NEXT HIGHEST POSTCODE, within a certain mail area, you CANNOT use the expression "postcode+1" to find the answer. If your program is likely to need that sort of capability you have two options available.

(a) store the post code as TYPE NUMERIC, Not recommended.

(b) Use one of the in-built Xbase functions that convert data for you on the run, as required.

We will cover those special, ready-made, in-built functions later. Just be aware that all these sorts of problems have arisen before and in most cases your Xbase Software provides a solution.

In the development of a data file structure, as we select the data type and length etc., (what are they called? - Attributes) it is imperative that we always consider that possible future need for the comparison or the combination of data. There would be no point for instance, in storing the cost of an item as Data Type NUMERIC and the quantity as Data Type CHARACTER. Consider the following tasks ....

Add one to the quantity:

    QUANTITY + 1 = 'Error'

Determine the TOTAL COST:

   QUANTITY x UNIT_COST = 'Error'

In each case your system will generate a "data type mismatch" error. That means something like, "you have tried to compare apples with pears and I'm confused?".

Developing a DataBase

As you read in "Eight Bits of Impact", Database refers not to the software you are using, but to your collection of data. The data files we create can take on a different complexion, dependent upon the required use as well as the source, the method of input, the amount and the type of data available or required manipulated.

Initially our Membership Database will comprise just one or two files but in later lessons we'll cover how to add more files, containing much more information, without disturbing the original data.

As we create the initial file(s) you may decide that your system needs information of a different nature. Go ahead and include it. Don't be afraid to try new ideas, life is too short. Just be sure to watch out when we start programming and add the extra lines of code required to manipulate the additional fields you have chosen. It will not be difficult, I promise!

Our Membership Data
 
File Membership Number
The first and most obvious field in any membership data file will be the member's number. It should be unique because when you search for a number you do not want to find the wrong person.

In selecting the data type for this field we must consider, among other things, the method by which our number will be generated. Will it be pre-determined and simply entered by an operator, or, will we become a little more adventurous and allow our program to generate the number for us?

If you plan on transferring data from an existing system, selection of the data type might already be fixed in concrete for you, there may be no choice. For instance, it is not possible for a membership number such as "SMIT1234", to be stored as anything other than straight character type. Such a combination of letters and digits is very definitely, character only.

We must also consider the method we will use eventually to ensure there are no duplicate entries permitted. One very easy way of doing this, especially if the membership number was determined prior to data entry, is to search the file for that particular number and alert the operator if we find it already exists in the file.

That check alone of course, will not necessarily prohibit duplicate records for the same person. The method described relies entirely upon the next available number being given out beforehand to a brand new member and not to an existing Member, by whomever does the manual allocation of membership numbers.

The safe approach

If we don't or cannot search on membership number to avoid duplicates then we must look to conducting this search on the surname PLUS initials PLUS address combination. Even though our using the membership number would be likely to produce results slightly faster, and be easier and simpler from a programming viewpoint, the latter method is much safer.

Therefore, we will adopt the approach of checking against name and address.

Since we are doing it the "safe way", we might as well allow the program to develop the membership number for us. Then you can decide for yourself which method to use. Those of you who have existing membership numbers will select to option of entering the number manually. Others will save their data entry operator much work and finish up being awarded most popular programmer of the month status.

Length of the Field

The length of the data field containing membership number will depend to some extent upon
(a) the format you decide to use and then to a lesser extent,
(b) the most likely highest number of members.

A club with 10,000 members cannot have a four character code, can it? Well, it can actually, I'll show you one day, but generally you must be aware and carefully consider the limits you can impose, just by having such shortcomings built into your system design.

I'll repeat that, its important,
....generally you must be aware and. carefully consider the limits you can impose, just by having such shortcomings built into your system design.

As for our sample, local club, I like the surname, next number format. Lets use that. It involves taking the first few characters of a name and adding either the initials and then a number, or just the next available number. It will also teach you a bit more variety of programming.

When using the first few characters of a name we then have to decide what the largest number of members will be in any one name group. For example - consider the name Smith. If we took the first four characters and added 0000 to 9999; would a maximum possible total of 10,000 "SMIT be large enough? Remember that particular name grouping will include all similar names, like Smithers, Smitson, Smitt and so on.

Unless you are planning a very large system, the first four characters plus four digits should be well and truly enough. Feel free to make it larger though - we can be flexible and I'll show you how to change the program code to suit

Before you decide, look at the Melbourne phone book. Try to find ONE set of first four characters in any name group where there would be more than 10,000 of them. If your Club has several million members and equates to the size of the City of Melbourne, change the 0000-9999 into 00000-99999 and use a nine character field.

Other Considerations

We've settled for an eight (8) character field that contains four characters and four digits, allowing maximum of 10,000 unique membership numbers for every occurring variety of the first four characters of the respective surnames.

Where a surname is shorter than four characters we must decide what to use as padding. That can be anything at all, provided we are absolutely consistent and avoid all possibility of generating an incorrect index key. If you are not sure of the meaning of index key, refer to "Eight Bits of Impact" mentioned earlier.

It is always highly desirable to maintain identical length index keys, "right padding" the name with common character and then adding the number component "left padded" with "Zero"s, is one good way of doing it.

Indexing Considerations

When creating an index, the software you are using will always sort on the ASCII value of the characters used. I'll explain this in detail because it's a very important aspect of what we are doing here.

Look at the ASCII Chart in Figure 2 and you will see the values for each character. Those values, allocated many years ago, are used by your software when sorting and/or indexing activity is taking place. The smallest value comes first, ie it is placed at the TOP of the list, just like when we count, then the largest value comes last, down at the bottom of the list.

The ASCII value of a space character is 32. The value of the digit "0" (Zero) is 48. The digit "1" has an ASCII value of 49 and so forth. When we create an INDEX KEY we must consider that those values will be applied to the task of sorting or ordering the records.

Look at the values of LOWER CASE letters and UPPER CASE letters. In a file that was indexed on surname, where would you begin searching to find the sort of error where the operator had failed to make the first letter of the surname into a "Capital Letter" ??

Yes, you would look right down at the bottom. After all the names that start with "Z".

The ASCII value of "Z" is 90 and the value of the small (lower case) "a" is 97. Indexing activity will always put Capital Letters towards the TOP of the file. Therefore any such erroneous "a" and "b" names will come after the "Z" names.

Likewise, with the value of a space being 32, what do you suppose is a neat trick often used to make a particular surname come to the TOP of an indexed file? Yes, press the SPACE BAR with INSERT ON and put a SPACE CHARACTER in front of the name. The index will automatically place that record at the TOP of your file because the space has the LOWEST used ASCII VALUE. In order to restore that record to its correct location later, simply delete the SPACE character.

Your understanding the significance of all this is critical to the successful design of any data entry situation, not just membership numbers. Often you will have to take action to ensure that data is stored in a form that can be suitably indexed and therefore located as quickly as possible.

Creating the Number

We must pass this stage, so we cannot afford to get bogged down. Let's select ASCII character 255 as our padding character, mainly because it's invisible and it will help to keep the short names separated from the digits.

Member Mike Sly might therefore be membership number SLY 0001. Whereas .John Martin would be MART0001. We will not be concerned with that until next lesson anyway.

The file Creation Process

My file is now beginning to look like Figure 3. It's about time we learnt how to create it.

At the DOT PROMPT, type CREATE MEMBERS and press {enter).

This opens a screen that will allow you to define the structure of the RECORDS in your data file. Initially you will see something resembling Figure 3 without the few names I have already entered. Simply type in the field name, field type and length for each, press (enter) after each one, then press Control-W to exit the process when finished.

Select your own Field Names

You can select any field names that suit your purpose. The sample I have given you may suit some but I'd be surprised if it suited many. Change is as you see fit but note one very important thing - there is no need to provide duplicate fields for repeat data.

If your system will be required to store, (say) for example, the varying amounts of donations received from a particular member over a period of time, the field I have named DONATION, and made type LOGICAL is all that you need. This is sufficient for use to know that we must open another file where all that kind of data will be stored.

Shall I repeat that as well? No, just read it again and again until you understand exactly what I'm saying.

We will always store repeating data in other files. In our next lesson we will create a file to hold details of those donations and guess how we will identify the various records that are added to that new file.?

Yes, the very first field in the DONATION file will be MEMBERSHIP NUMBER. That is our single, solitary index key for this exercise and EVERY OTHER FILE will have a membership number field as part of its structure.

Modifying a File Structure

If you want to change the record structure at any time simply type the command "modify structure".

In order to modify a file you must have the file open. The way to open a file in Xbase is type the words ....

    USE <filename>

... and then press the (enter) key

Also, your manual may or may not tell you but every command can be entered at the DOT PROMPT in abbreviated form It is necessary only to type "modi stru" for "modify structure". i.e. you need only type the first four characters.

Be careful - if you have data stored in a file and you change a field name, the data that was stored in that field will be LOST! Practice on some non-essential data to get the hang of it.

Structure for database: D:members.dbf
	 Number of data records: 0
	 Date of last update   : 08/14/91
	 Field
	 No. Name       Type     Width Dec Field Usage
	   1 MEMBER     Character    8     Membership Number
	   2 SURNAME    Character   20     Member's Surname
	   3 FIRST      Character   12     First Name
	   4 INIT2      Character    1     Initial No.2
	   5 INIT3      Character    1     Initial No.3
	   6 PREF_NAME  Character   12     Preferred Name
	   7 TITLE      Character    4     Miss, Mrs, Mr, Ms etc
	   8 UNIT_FLAT  Character    4     Unit or Flat No if
	                                   applicable
	   9 FLOOR      Character    2     Floor or level if
	                                   applicable
	  10 BLDG_NAME  Character   25     Name of Building
	  11 STR_NO     Character    5     Street Number
	  12 STREET     Character   20     Street Name
	  13 STR_TYPE   Character   10     Avenue, Boulevard,
	                                   Street etc
	  14 CITY_SUB   Character   20     City and/or
	                                   Suburb Name
	  15 STATE      Character    3     State or County
	  16 POST_ZIP   Character   10     Post or Zip Code
	  17 MAIL_AREA  Character   10     Mail Code if
	                                   applicable
	  18 COUNTRY    Character   15     Country if required
	  19 PHCODE_AH  Character    4     After Hours area code
	  20 PHONE_AH   Character    7     After Hours telephone
	  21 PHCODE_BH  Character    4     Business Hours Area
	                                   Code
	  22 PHONE_BH   Character    7     Business Hours
	                                   telephone
	  23 FAX_AH     Character    7     After Hours Facsimile
	  24 FAX_BH     Character    7     Business Hours
	                                   Facsimile
	  25 D_BIRTH    Date         8     Date of Birth
	  26 MARRIED    Logical      1     Married or single
	  27 JOINED     Date         8     Date joined as member
	  28 VALID_TO   Date         8     Membership
	                                   Valid to (date)
	  29 FEE        Numeric     10   2 Current membership fee
	  30 INTEREST   Memo        10     Main Area of Interest
	  31 DUTY       Character   20     Special Club Duties
	  32 ASSIST     Character   25     Assistant name
	                                   if applicable
	  33 DONATION   Logical      1     Made any donations?
	  34 COMMENTS   Memo        10     Comments from Member
	  35 NOTES      Memo        10     Special Notes
	                                   on this Member
	 ** Total **               330

	 Figure 4.  Data file field listing.

The File Listing

After you create the file and you're happy with it, you should create a new document in which to maintain details of the various fields. Do this as follows:

Make sure your new file is open. If you have re-started your Xbase system after previously creating the file, type
      USE MEMBERS {enter}
....to open the file

The name of the file will appear in the Status Bar. The name of a "USE"d (or open) file ALWAYS appears in the status bar, provided that you have status set ON of course.

Type in the following commands, press (enter) after each...

If you wish to print the file structure at any time, open the data file and type..

               LIST STRU TO PRINT

Always use the quit command when exiting your Xbase system. That closes all files properly and helps to avoid corruption. If you want to stay inside the Xbase Software but you want to close an open file simply type the command USE with nothing after it and then press {enter}. The file will be closed.

Updating our field listing

The above procedure created a text file on disk. The file is named MYFILE.DOC and you can now modify it with any word processor or text editor. I suggest you get into that document and make notes as I have, to identify the various fields and note the planned contents of each. 

Be careful when selecting your text filename because if you have an existing file named MYFILE.DOC it will be overwritten. 

As I said earlier, next month we will discuss creating other files to hold some of the additional data you might require stored. Then we will create a small data entry program. For those planning on importing and using an existing database, well describe how to import data from an ASCII file into a new Xbase data file. 

In the time you have between now and then I suggest you work out what is the best Text Editor for you to use and check that it creates pure ASCII text files. Experiment with making file listings, add notes to the documents you create and become as familiar as you possibly can with creating and modifying data files. Pay lots of attention to the type of data you will store and the maximum length of field you will need for each. Always be sure to document what you do and beware - when you use the method above to create a text file of your data file structure - what happens to an existing file that just happens to have the same name?? 

Have fun, Enjoy

[ Note this series of articles is continued in the following issue.]

Reprinted from the October 1991 issue of PC Update, the magazine of Melbourne PC User Group, Australia

[About Melbourne PC User Group]