Centre for Electronics Design & Technology of India A Scientific Society under Department of Electronics, Govt. of India, New Delhi
Published by CFS Documentation Cell Centre for Electronics Design and Technology of India An Autonomous Scientific Society under Department of Electronics, Govt. of India, New Delhi.
First Edition: 1999
TRADEMARKS: TRADEMARKS: All brand name and product names mentioned in this book are trademarks or registered trademark of their respective companies. Every effort has been made to supply complete and accurate information. However, However, CEDTI assumes no responsibility for its use, nor for any infringement of the intellectual property rights of third parties which would result from such use. No part of this publication may be stored in a retrieval system, transmitted or reproduced in any forms or by any means, electronic, photocopy, photograph, magnetic or otherwise, without written permission of CEDTI.
CEDTI/CFS/99/6/2.2/R1
Published by CFS Documentation Cell Centre for Electronics Design and Technology of India An Autonomous Scientific Society under Department of Electronics, Govt. of India, New Delhi.
First Edition: 1999
TRADEMARKS: TRADEMARKS: All brand name and product names mentioned in this book are trademarks or registered trademark of their respective companies. Every effort has been made to supply complete and accurate information. However, However, CEDTI assumes no responsibility for its use, nor for any infringement of the intellectual property rights of third parties which would result from such use. No part of this publication may be stored in a retrieval system, transmitted or reproduced in any forms or by any means, electronic, photocopy, photograph, magnetic or otherwise, without written permission of CEDTI.
CEDTI/CFS/99/6/2.2/R1
FOREWORD
The information technology and telecom sectors have suddenly opened up avenues, which require a very ver y large specially trained manpower. manpower. These sectors are highly dynamic and need training and re-training of manpower manpower at a rapid rate. The growing gap of requirement of the industry and its fulfillment has created a challenging situation before manpower training institutes of the country. To meet this challenge most effectively, Centre for Electronics Design and Technology Technology of India (CEDTI) has launched its nation-wide franchising scheme. Centre for Electronics Design and Technology of India (CEDTI) is an Autonomous Scientific Society under the Govt. of India, Department of Electronics with its Headquarters at New Delhi. It operates seven centres located at Aurangabad, Calicut, Gorakhpur, Imphal, Mohali, Jammu and T Tezpur. ezpur. The scheme will be implemented and coordinated c oordinated by these centres. The scheme endeavours to promote high quality computer and information technology education in the country at an affordable cost while ensuring uniform standards in order to build a national resource of trained manpower. Low course fees will make this education available available to people in relatively small, semi urban and rural areas. State-of-the-art training will be provided keeping in view the existing and emerging needs of the industrial and Govt. sectors. The examinations will be conducted by CEDTI and certificates certific ates will also be awarded by CEDTI. The scheme will be operated through all the seven centres of CEDTI. The CEDTI functions under the overall control and guidance of the Governing Council with Secretary, Department of Electronics as its Chairman. The members of the council are drawn from scientific, scientific , government and industrial industr ial sectors. The Centres have separate executive committees headed by Director General, CEDTI. The members of these committees are from academic/professional academic/professional institutes, state governments, industry and department depar tment of electronics. CEDTI is a quality conscious organisation and has taken steps to formally get recognition of the quality and standards in various activities. CEDTI, Mohali was granted the prestigious ISO 9002 certificate cer tificate in 1997. The other centres have taken taken steps to obtain the certification cer tification as early as possible. This quality consciousness will assist CEDTI in globalizing some of its activities. In keeping with its philosophy of ‘Quality in every Activity’, CEDTI will endeavour to impart state of the art – computer and IT training through its franchising scheme. The thrust of the Software Courses is to train the students at various levels levels to carry carr y out the Management Information System functions function s of a medium sized establishment, manufacture Software for domestic and export use, make multimedia presentations for management and effectively produce various manufacturing and architectural designs.
The thrust of the Hardware Courses at Technician and Telecommunication Equipment Maintenance Course levels levels is to train the students to diagnose the faults and carry carr y out repairs at card level level in computers, computer s, instruments, instrume nts, EPABX, EPABX, Fax Fax etc. and other office equipment. At Engineer and Network Engineer levels the thrust is to train them as System Engineers to install and supervise the Window NT, Netware and Unix Networking Systems and repair Microcontrollers / Microprocessor based electronic applications. An Advisory Committee comprising eminent and expert personalities from the Information Technology field have been constituted to advise CEDTI on introduction of new courses and revising the syllabus of existing courses to meet the changing IT needs of the trade, industry and service sectors. The ultimate objective is to provide industry-specific quality education in modular form to supplement the formal education. The study material has been prepared prepared by the CEDTI, document centre. centre. It is based on the vast and rich instructional experience experience of all the CEDTI centres. centres. Any suggestions on the improvement improvement of the study s tudy material will be most welcome.
(R. S. Khandpur) Director General (CEDTI)
TABLE OF CONTENTS UNIT
CHAPTER NAME
PAGE NO. SECTION - A
1
An Introduction to Database Management System
11
2
Database Creation, Its Handling And Report Generation
15
3
Command File Loops And Decision Making in Structure Programming
37
SECTION - B 4
Screen Generation And Object Adding
61
5
Relational Query By Example
69
6
Object Linking And Embedding
73
7
Making Applications Project And EXE Files
81
PREFACE
Databases play an important role in business engineering, medicine, law, education and library science. Database constitute the most important component of any computer based information systems. In view of this, it has become increasingly impor tant for any working professional to acquire the programming skills for developing database application software. This course material introduces the advance concepts for designing, using and implementing database systems using FoxPro 2.6. This book stresses the advanced features of database modelling and designing, the languages and facilities provided by database management systems and system implementation technique. This text is designed to provide the knowledge of the role of database systems in business operations, management decision making, and the strategic success of organisations. The goal is to help students become managerial endusers who can propose and participate in developing database solutions to business problems. The objective of the course is to train students to be expert users of database management systems as well as a skilled programmer of a database management application software.This course material is divided into two sections. Section A introduces the students to the basic principles of Database Management Systems and the salient features of the FoxPro Programming. This Section also instructs the students to create and maintain database using FoxPro, implement the various types of loops and decision making statements. Section B gives an introduction to the various enhanced features of FoxPro which includes Screen creation and adding objects to screen, Relational Query by Example, Object Linking and Embedding and development of application projects and creation of executable files. Assimilation exercises have been given at the end of each chapter to facilitate the students to carry out the self check of the level of understanding achieved during the course of the study.
SECTION - A
COMPETENCY OBJECTIVES The objective of this Section is to introduce the students to the basic principles of Database Management Systems and the salient features of the FoxPro. At At the end of the section, a student should be able to :v v v v
Create and maintain database using FoxPro. Understand & implement the various types of loops. Highlight the importance decision making statement. Implement the procedures in FoxPro. FoxPro.
FOXPRO PROGRAMMING
10
FOXPRO PROGRAMMING
CHAPTER - 1
AN INTRODUCTION TO DATA BASE MANAGEMENT SYSTEM
DATA BASE :
A database is a collection of related data. By data, we mean known k nown facts that can be recorded and that have implicit meaning. Therefore, Therefore, a collection of related data with an implicit meaning is called database. A database can be of any size and of varying complexity. complexity. It may be generated and maintained manually or by machine. A database has following implicit properties: a. It represe represents nts some aspect aspect of the real world, world, sometim sometimes es called called the miniworld miniworld or or the Universe Universe of Discourse (UoD). Changes to the miniworld are reflected in the database. b. It is a logi logicall cally y coheren coherentt colletio colletion n of data data with with some some inher inherent ent mean meaning ing.. A random random assortment of data cannot correctly be referred to as a database. c. A databa database se is desig designed ned,, built built and and popul populate ated d with with data data for for a specifi specific c purpose. purpose. It has has an intended group of users and some preconceived applications in which these users are interested. DAT DA TA BASE MANAGEMENT SYSTEM S YSTEM :
Data base management system is used to build and manage the database i.e. add, change (edit), delete and sort (arrange in order ) information in the data base, to keep data base upto date. It also helps to retrive information in the desire format from the data base. In general, a data base consists of units called records, defined by a common structure. Example Example :- In a telephone telephone book, a record record is a particular particular persons’ persons’s s name, name, addres address s and telephone number. number. Each of the three items is a field. TELEPHONE BOOK :
11
FOXPRO PROGRAMMING
RELATIONAL DATABASE MANAGEMENT SYSTEM :
Some Applications often involve several databases, each holding seperate data. For example, a payroll system might require three of them FIRST DATA BASE :
The primary one has the number of hours worked by each person on each project. SECOND DATABASE :
A second one contains projects budgets and expenditures organized by project number. THIRD DATA BASE :
The third has employee information including identification number, name, salary, location, telephone numbers, and home address. Now to make records easier to find, we assign unique identification numbers to employees and projects. Then we can store the ID number in the files rather than a name, for example Brown Jim have ID number 14738 and so for all the employees. We can use the ID to get the name or other information from the employee file Similarly the project numbers links the project file to the payroll. The three files form a RELATIONAL SYSTEM using employee and project number as linking fields. This is called as RELATIONAL DATA BASE MANAGEMENT SYSTEM. Fig 1.1 Shows the relations
Fig 1.1 Relationship in a simple payroll system FOXPRO
Fox Pro is powerful Database management system with menu driven interface, which gives great deals of convenience in creating and handling database. It is also user friendly. This software can be used to build very complex application for real world applications, such as accounting inventory, personnel administration, Statistical analysis and information system. It can be used to
12
FOXPRO PROGRAMMING
Fig 1.2 Microsoft FoxPro for Windows 1. 2. 3. 4. 5. 6.
Edit data records when ever required Retrive data selectively from stored records to provide specific information. Performs calculations. Create screens that can interact with the user Create menu system to run your frequently used application. Enhance reports with graphics and quality fonts. DIFFERENCE BETWEEN
1. 2. 3. 4.
5.
DBMS Storage, access path and data structures tures can’t managed by DBMS In DBMS logical and physical view of the base is considered DBMS model can not achieve the structural and data independence Only single relationship exists in DBMS
System requirement is not so important as it can run with simple system configuration
RDBMS Storage, access paths and data struccan be managed by RDBMS In RDBMS only logical view of the data data base is considered. RDBMS model can achieve both the data and structural independence One is to One, One is to Many and Many is to Many relationship exits in RDBMS It requires very powerful and complicated hardware and operating system.
LOGICAL CONCEPT OF DATA BASE :
The logical concept of database focuses on the logical nature of the data representation. Therefore the logical model of a data base is concerned with what is represented in the data base rather than how it is represented. Logical concept of data base include three types of relationship.
13
FOXPRO PROGRAMMING
1.
ONE TO MANY RELATIONSHIP
The one to many relationship is labeled “ 1 : M” For example, the relationship between the painter and the paintings is represented as P A IN T E R 1
P A IN T I N G M
Fig 1.3 - One to Many Relationship 2.
MANY TO MANY RELATIONSHIP :
The many to many relationship is labeled “ M : N”. For example, the relationship between employee and the job skills is represented as shown below. E M P LO YE E
S K IL L
M
N
Fig 1.4 - Many to Many Relationship 3.
ONE TO ONE RELATIONSHIP :
The one to one relationship is labeled “1:1”. For example, the relationship between the employee and the not dog stand is represented as shown in fig 1.5. S TA N D
1
E MP LO YE E
1
Fig 1.5 - One to One Relationship NOTE :
For many relationship double arrow head is shown and for single relationship only single arrow head is shown.
ASSIMILATION EXERCISE
Q.1
What do you mean by a database ?
Q.2
What are the key differences between DBMS and RDBMS ?
Q.3
Explain the logical nature of data representation and compare the various relationships? 14
FOXPRO PROGRAMMING
CHAPTER - 2
DATABASE CREATION, ITS HANDLING AND REPORT GENERATION
CREATING AND MAINTAINING DATABASE FILE Creating Data Table:
Let us suppose that you have to create data table for a institute which is givinig education to the students in computers. The institute wants to keep records of student to track over all performance. A data table is made up of series of information for each students stored in separate record.It is important that information is broken down systematically into different items or fields,so that you can successfully retrived the stored information whenever needed. According to above example the table structure may have the following fields. Reg.No.
Student name
adm_Date
Course
Examdate
Result
Each field of table structures is defined by four parameters the field name, type, width & index. Field name: Each field must have unique name for its proper identification. Rules for giving field name: l
l l
It must contain maximum 10 characters may be alphabets A-Z, numerals 0-9 & the under score ( _ ) may be in uppercase. First character of field name must be an alphabet. Blank space, punctuation marks are not allowed for field name.
It is good practice to give meaningful field name, so that it indicates nature of the contents of the field. For example the database create the following structure:
15
FOXPRO PROGRAMMING
Field Name
Description
1)
Reg_no.
Students Registration number
2)
Stud_Name
Students Name, with father’s name surname
3)
Date_add
Date of Admission
4)
Cou_name
Course Name
5)
Exam_dt
Date of Exam
6)
Result
Result of Exam
7)
Remarks
Memo field for putting remarks of particular student
Field types & width
The field type determines the type of data that you can enter. The field width determines how much space will be allocated for storing data. l
Character:
The character fields can be used to store letters, numbers, special symbols & blank space. The maximum field width of a character field is 254.The default width is 10.
l
Numeric:
The numeric fields can be used to store numbers on which calculations can be performed. The maximum field width of a numeric field is 20,including an optional plus/minus sign and decimal place. The default width of numeric data is 10.
l
Float:
The float field is specifically designed for scientific data. There is no difference between the numeric and float data types and float is included in Foxpro only for backwards compatibility to database files. The maximum field width of a float filed is 20, including an optional plus/minus sign .The default width of float data is 10.
l
Date:
The date fields are used to store dates. The field width of date field is always eight. The default display format is mm/dd/yy. This can be modified to other country formats by using the SET DATE command.
l
Logical:
The logical fields accepts only single character representing True/False values. Logical True(.T.) can be entered as T,t or Y (Yes) and Logical false(F) can be entered as F,f or N or n (no).
16
FOXPRO PROGRAMMING l
Memo:
The memo fields may be used to store any printable ASCII character (letters, number, blank space or special symbols). All memo fields in a database memo file extension .dbf, which is created while setting up the database file. Memo fields sizes are limited only by the amount of memory available. Each memo field occupies only ten byte spaces in the table (database).
l
General:
The general field is used to store OLE (object Linking and Embedding) objects. A Width of 10 characters is automatically assigned. This corresponds to the amount of space used in the table. The general field sizes are limited only by the amount of the memory available.
Field Type Character Numeric Float Logical Date Memo General
Field Width Max 254 Character default10 Max 20 Character Default 8 Same As numeric 1 Character 10 Character Any length Not applicable
Best Uses Name,books Calculation Scientific Calculation Answer Yes/No True/ False Date Description Graphics embedded in data
CREATING A NEW DATABASE FILE l
From file menu, select new, New dialog box appears as in fig. 2.1
Fig. 2.1 l
Select table /dbf
l
Press New
17
FOXPRO PROGRAMMING l
A Structure dialog box appears as in fig 2.2
Fig. 2.2 l l
Write field name type, width as shown in table press ok. Foxpro ask “ Input data record now?” as shown in fig 2.3
Fig. 2.3 APPEND COMMAND
To add the record in dbf file (table). The append command is used. Foxpro has two screen display modes i.e. change mode and browse mode. l
From record menu select append. Data entry windows, appears as fig. 2.4.
Fig. 2.4 18
FOXPRO PROGRAMMING l
Type related data to appropriate field. To move from one field to another press tab.
l
After filling one record the cursor append in the first field of next record.
Entering text in Memo field: l
To Enter text in memo field move cursor to memor field, press ctrl + page down. Memo editing window appears is shown in fig.2.5
Fig. 2.5
Window title at the editing windows is Student Remark. Student indicates that remarks is field of student.dbf Type the text for this window. CHECKING SPELLING:
FoxPro has built in spell cheker. To checker spelling follow the following steps. 1. 2.
Move the cursor at the begining of memo. From text menu choose spelling option, foxpro displays the spelling windows as shown in fig. 2.
Fig. 2.6 19
FOXPRO PROGRAMMING
3. 4. 5.
If any mistake occur foxpro displays appropriate option. Click on control menu box of editing window & select close option or from text menu select close option to close editing window. Notice that the word “memo” in remark field has changed to Memor indicating that field is not empty, which is shown in fig 2.7
Fig 2.7 USING BROWSE MODE:
To display browse mode, do the following steps 1. 2.
From browse menu select browse option. Foxpro display browse window as shown in fig 2.8
Fig. 2.8 In browse formatted field name displays as column headings of tables. It displays several records at a time but the screen is not wide enough to display all the fields. 3. 4. 5. 6.
To enlarge window use maxmize button. To scroll screen horizontally press tab or shift + tab Type the record as same as in change mode. To close browse window click on the control menu bar of the window & select close option. By this step all record you have type is saved. or press Ctrl + W 20
FOXPRO PROGRAMMING
EDITING RECORDS
To edit a particular record do the following steps. 1. 2. 3. 4.
From record menu choose change option, browse window in change mode appears. Move the cursor to the record Pointer of which data you want to make the changes. To Save changes click on control menu box & select Close option or press Ctrl + W To discard changes press esc key.
DELETING RECORDS:
To delete records 5 & 6 from student.dbf follow the following steps. 1.
2. 3. 4. 5.
Open student. dbf l From file menu select Open option the Open dialog appears. l Double click on student.dbf the file returns. OR In command window type use student & press enter key. Browse the table Move the cursor to record 5 drag the mouse until this record highlighted or press down arrow to this record. From Browse menu choose toggle delete option. You can pack the deleted records by selecting pack option from database menu. Recall records 5 by repeating step 2 & 3.
MOVING : Through table
If table have many records to access a particular record by arrow keys is time consuming. You can reach target quickly by using goto command. 1. 2.
From data base menu select browse option From Browse menu select Goto option, Foxpro displays dialog in as fig 2.9 l l l
To move to Top of data table click on top. To move the bottom of dbf file i.e. at last record press B or click on bottom. To move to particular record press R or click on record .The text area is highlighted. Type the number 4 and press “enter”, cursor has moved to 4th record of first field.
Fig. 2.9
21
FOXPRO PROGRAMMING
BROWSE WINDOWS
Browse window has the typical features of the system window. In addition to move resize & scroll, it has six other interesting features. Spilt the windows into two, as in l Change active window from the left window to the right window and vice-versa. l Link and unlink the split windows. l One window in Browse mode and other window in change mode or the windows in same mode. (Fig. 2.10)
l l
Fig. 2.10 Move a data field from one column to another. Change width of a field.
TO SPLIT WINDOW
1.
Click on the window split box at the bottom left corner of the window & drag the mouse to your right window emerging on the left side as shown in fig .2.11
2.
Fig 2.11 Release the button when the windows are of the sizes you want.
3.
Repeat steps 1 & 2 to close the window but with dragging the mouse to the left and then
UNLINK THE SPLIT WINDOW
By defualt the split window are linked one window scroll another window also scroll in the same way. In unlinked stage records scroll in active window, another window record does not move. 22
FOXPRO PROGRAMMING
To unlink split window:
1.
From the Browse menu choose & Link partitions option. This is toggle option
To Move Position of a field
1.
Point to fields name
2.
Press the Mouse Botton & drag the mouse left or right to the desired position.
3.
Release the Mouse button when the field is where you desire.
To Change Field Width
1. Click on vertical grid line on the right hand side of the target field name & drag the mouse left or right until the field size is what you like. 2.
Release the button
TO COPY THE STRUCTURE
To copy the structure to a new file, which will be called TEMP since it is being used only temporarily, enter COPY STRUCTURE TO C:\STUDENT\TEMP Now to make TEMP, the current file, select File > Open again, select TEMP.DBF from file Name list of the open dialog box and select the open push button SORTING AND INDEXING:
Foxpro provides two options for sorting database SORT and INDEX. SORT COMMAND:
If you want your STUDENT.DBF student name to be sorted in alphabetical order, give the following commands USE STUDENT SORT ON NAME TO S _ NAME In the above case a new dbf file named s_name is created and is sorted in ascending order (by default). For sorting in descending order enter SORT ON STUD_NAME / D TO S1 _NAME
23
FOXPRO PROGRAMMING
INDEX COMMAND:
In Index command no new file is created it has a extension of IDX. Syntax :INDEX ON
TO (index name>[ASCENDING/ DESCENDING] MEMORY VARIABLES
Data required for temporary use is not stored in a data base file. Instead, it is stored in a memory variables. MEMORY VARIABLES :- A CLASSIFICATION
Table shows the variables (memory) used in Foxpro Programming. TYPE OF MEMORY VARIABLES
DESCRIPTION
CHARACTER
Size is determined by the number of characters stored in it. The maximum limit is 254 Bytes.
NUMERIC
No limit on the number of digits that can be stored.
DATE
Created by a character to date conversion of a date stared as a character. Size is 8 bytes and is fixed.
TABLE : Foxpro Memory Variables CREATING AND INITIALISING MEMORY VARIABLES
Table below shows the different ways of initialising memory variables in Foxpro TYPES OF MEMORY VARIABLES
INITIALISATION
NUMERIC
STORE 1 TO choice, ctr, ctrl STORE 11.66 TO var Choice = 1
CHARACTER
STORE ‘ABCD’ TO Alpha 1 STORE SPACE(5) TO NAME alpha 1 = “ABCD”
DATE
STORE CTOD(“10/03/98”) TO DT DT = CTOD(“10/03/98”) STORE DATE( ) TO C_DT MDT = CTOD (“ / / “)
Table:- Initialisation of Memory Variables. 24
FOXPRO PROGRAMMING
The SPACE( ) function initializes character type memory variables with blanks. STORE :- The store funtion is used to store the charater/numeric variables
SYNTAX STORE TO where is an expression is a list of memory variables. EXAMPLE:-
1. 2. 3. 4. 5.
STORE STORE STORE STORE STORE
“ABCD” TO ALPHA 1 TO CHOICE, CTR, ICTR SPACE (5) TO NAME CTOD(“12/10/98”) TO DATE DATE( ) TO DATE1
CALCULATION DONE ON MEMORY VARIABLES
Calculations can be done on numeric variables by adding, subtracting, multiplying or dividing. Similarly, a number may be added to or subtracted from a date type variables or the difference in dates may be computed. EXAMPLE:-
1. 2. 3. 4. 5. 6.
SAL = T_SAL + BASIC + BASIC *.25 T1 = YEAR(D_JOIN)/4 W_YEAR = YEAR(DATE( ))-YEAR(D_JOIN) STORE A+1 TO A1 A2 = A2+1 A3 = A3 -7
25
FOXPRO PROGRAMMING
MATHMATICAL OPERATORS:
Following are the mathematical operators OPERATOR = > > >= < > or # or ! = $ ==
DESCRIPTION Equal to Greater than Less than Greater than or equal to not equal to Contained in (applicable only for character data) identical to (applicable only for character data)
EXAMPLE LIST FOR PIN >” 110025" LIST FOR PIN < “ 110026”
LOGICAL OPERATORS LOGICAL OPERATORS
DESCRIPTION
.AND.
Action will take place only if each or the conditions joined with .AND. are true
.OR.
If any one of the conditions joined with an .OR. is true, action will take place
.NOT.
Action will take place only if the opposite value of the conditions is true i.e. all the records which do not satisfy the conditions will be valid
EXAMPLE LIST F_NAME, L_NAME, PHONE, STATE FOR STATE = “ HARYANA” .OR. STATE = “ PUNJAB” .AND. NOT. STATE = “MAHARASHTRA” RELATING DATABASES WITH SET RELATION COMMAND
Linking of two files is done, with command SET RELATION
26
FOXPRO PROGRAMMING
EXAMPLE: MASTER .DBF Field name Code Name Address City Pin
Type Numeric Char Char Char Char
Width 4 15 18 12 6
Dec
Enter the following data CODE
NAME
ADDRESS
1200 1250 1251 1260
A.K.ARORA G.K.DEB S.SRIKANT RENU SHARMA
G-234,SAKET 12,NEW MARKET 12B,PAHARGANJ 17,IIT CAMPUS
CITY
PIN
N.DELHI N.DELHI N.DELHI N.DELHI
110016 110003 110055 110016
Next create another database file DEPOSIT.DBF FIELD NAME CODE AMOUNT DEP_DATE PERIOD RATE
TYPE NUMERIC NUMERIC DATE NUMERIC NUMERIC
WIDTH 4 7 8 5 5
DEC 0 0
0 2
Enter the records CODE 1250 1260 1251 1260
AMOUNT 10000 5000 8000 10000
DEP_DATE 04/06/98 03/03/95 06/06/95 06/07/98
STEPS :- Enter the following command at command window
USE MASTER INDEX ON CODE TAG CODE SELECT 1 USE DEPOSITS SELECT 2 USE MASTER SET ORDER TO CODE 27
PERIOD 180 45 300 250
RATE 10.0 9.0 10.0 11.0
FOXPRO PROGRAMMING
The above steps have opened both databases files and have also selected the index on the common field CODE Paraent file ————> Child file ————>
DEPOSITS.DBF MASTER.DBF
At command window enter following SELECT A SELECT 1 SELECT DEPOSITS SET RELATION TO CODE INTO B SET RELATION TO CODE INTO MASTER GOTO 2 ?CODE,B->CODE ?CODE,MASTER->CODE LIST FIELDS CODE,B->NAME,B->ADDRESS,B->CITY,B->PIN INDEX ON CODE TAG CODE BROWSE FIELDS CODE,AMOUNT,DEP_DATE,B->NAME REPORT GENERATION
Before you start the foxpro report writer, create a working environment by opening the necessary tables & indexes & establishing relations the report. This environment information can be saved in the .frx file. CREATING A NEW REPORT: (i)
(ii)
To create a new report form
1.
Choose New from file menu.
2.
Choose the Report radio button in the new file dialog.
3.
Choose OK Report layout window appears or type create report in command window.
Modifying an existing report To modify an existing report form
1. 2. 3.
Choose open from the file menu popup Make sure Report appears on the list files of type popup. Choose the report form file you want to open from the open dialog or type modify report in command window. 28
FOXPRO PROGRAMMING
Fig 2.12
A new report layout window has three bands separated by band separation bars. Additional bands become available when you define groups, title page & summary. Each band can contain text, table fields, computed fields, calculated values, lines & boxes. (i)
Band Type
(i)
Title -
The title band contains information that appears before the main report. The information contained in the Title band is printed once for the entire report.
ii)
Page Header -
At the top of the each page of the main report is the Page Header Band. A page header might include the report title, date, pageno, & so on.
iii)
Column Header -
If you select more than one column for your report, you have the option to include a heading at the top of each column.
iv)
Group header -
When data is grouped, each group can have a group header printed before it. These headers indicate the information that is contained within each level of grouping
v)
Page footer -
At the bottom of each page of the main report is the page footer band. It contains report name, date, page no., & so on. It can also include summary information for data on that page.
29
FOXPRO PROGRAMMING
vi)
Summary -
vii) Tool Box -
1. 2. 3. 4. 5. 6. 7.
The summary appears after the main report. It can contain summary information or text that summarizes the contents of the report. It is printed once per report. The toolbox is the column of buttons at the left of the report layout window. These tools allow you to create report objects. Following are the tools available with report writer.
Selection Pointer Text Tool Field Tool Line Tool Rectangle Tool Rounded rectangle tool Picture tool Fig 2.13
1.
Selection pointer
(i)
To select an object in the report layout window, click the selection pointer then click on the object select multiple objects, Shift +click on each object. When multiple objects are selected, they act as one object when moved, edited or deleted.
(ii)
To deselect all selected objects in the report layout window, position the pointer off the selected objects & click.
(iii) Sizing objects - A selected object has handles, which are small boxes in the corners & on the sides of the objects. To resize the object, point to one of the handles & drag until the object is the desired size. (iv) Deleting object - To delete an object, select it & press backspace or delete. (v)
Changing object attributes - You can change the font type face, fill & color of a selected object by choosing menu options from the object menu.
2.
Text Tool
i)
To add or edit text in your report, click the text tool & click in the report layout window where you want to add or edit text. Make the desired additions or changes.
ii)
You can change the font & color of text by selecting the appropriate option on the object menu.
iii)
Double clicking on a text object with the selection pointer opens the text dialog so you can determine the text prints, its relative position in a band & add comments.
30
FOXPRO PROGRAMMING
iv)
Comment - choosing comment allows you add comments to the .frx file. Comments are for your reference & do not appear anywhere in the printed report.
v)
Position relative to - Here you can specify whether the text you have included in the band retains its position relative to the top of the band or bottom of the band.
vi)
Print when- Choosing Print when .. in the text dialog opens the print when dialog so you can establish exactly when the text is printed in your report.
3.
Field Tool
Report can include table fields, variables, calculated values, calculated fields or user defined functions. i)
To place a field in a report, click the field tool then click in the Report layout window where you want the field to appear & drag to the desired size. Choose the Expression... push button to open the expression builder dialog. In the expression builder you can build an expression or select the fields of a table which appears in the left side. & verify that it is correct before continuing.
ii)
Formatting field - When a valid expression is displayed to the right of the Expression ... push button, you can choose Format.... to display the format dialog so you can specify the format for the field in your report.
iii)
Computed fields - Choose calculate .. in the report expression dialog to open the calculate dialog. Here you can select a mathematical operation to create a computed field. Radio buttons in the calculate dialog are Nothing Count, Sum Average, Lowest, Highest, Std, Deviation, Varience. At the top of the calculate dialog is the Reset poup control With this, you can select the point at which the value for this field will be set to zero.
When you have made the desired selection &/ or entered the desired values in the appropriate areas of the Report Expression dialog, choose OK to confirm the selections. When you return to the Report Layout Window, you will see your expression displayed in the Report Layout Window. Fields can be sized or mover as desired. 4.
Drawing Lines:
To draw a vertical or horizontal line in report, click the line tool, position the pointer where you want start of the line to be, & drag until the line is of the desired length. To draw a rectangle,click the rectangle tool,position the cursor where you want a corner of the rectangle to be & drag until the line is of the desired length.
31
FOXPRO PROGRAMMING
5.
Drawing Rectangles
To draw a rectangle, click the rectangle tool, position the cursor where you want a corner of the rectangle to be & drag until the rectangles is of the desired size. Double clicking on a line or a rectangle opens the rectangle / line dialog. 6.
Drawing Rounded Rectangles
To draw a rectangles with rounded corners, click the rounded rectangle tool, position the cursor where you want a corner of the rectangle to be and drag until the rounded rectangle is of the desired size. 7.
Defining Pictures
You can include individual bitmap pictures or general data fields which can contains a variety of bitmap pictures & other objects. To define a picture for the report, click the picture tool, position the cursor where you want a conrer of the picture to be, & drag until the picture frame is of the desired size. The report picture dialog opens. (i)
Picture From:
File - choose the File button to open the picture dialog so you can select the desire. BMP file or you can type the name & path of the BMP file in the text box beside the File button. Field - To include a general data field in your report choose the field radio button. Choose the Field button to open the choose Field/Variable dialog so you can select the desire table field. (ii)
If Picture & Frame Different Size:
Clip picture - If the picture is bigger than the frame you created in the Report Layout Window, clip picture will show a section of the original bitmap in the frame, retaining the picture’s original dimensions. Scale picture - Retain shape - This will display the whole picture, filling as much of the frame that you size in the report layout Window as possible while retaining the relative proportions of the bitmap picture. Scale picture -Fill the frame - Choosing this option will display the whole picutre filling the frame that you size in the screen design window completely. Centre picture - Checking this option insures that general field pictures smaller than the frame are centered in the frame in the report.
32
FOXPRO PROGRAMMING
Report menu
When you start the report writer, a Report menu is added to the menu bar. It has following options. 1. 2. 3. 4. 5. 6. 7. 8. 9.
Page Layout. Page preview Show position Ruler/Grid. Snap to Grid. Title/summary. Data grouping. Variables. Quick Report.
1.
Page layout - Choosing this option opens the page layout dialog. The page layout dialog allows you to determine the number & width of columns in your report, the width of the left margin & space between columns. A modal of the report or layout page displays the column & margin settings.
2.
Page preview - Choose this option to display the page preview dalog with a full page or actual size representation of the first page of report. To toggle between the two representation click on the Zoom in or Zoom out.
3.
Show/hide -Position this option displays or hides the co ordiantes of the mouse pointer & the dimensions of selected objects in the status bar. By default position is hidden.
4.
Ruler /grid - Ruler/Grid opens the Ruler/Grid dialog which allows you to set the units of measurement for the rulers or to change the size of the invisible alignment grid.
5.
Title/summary - This option allows you to specify whether a title & or summary band should be included in the report. Check New page to issue a page eject after the title bar information is printed or before the summary band information is printed. choose OK in the title/summary dialog to return to the report layout window.
6.
Data Grouping - Even though sorting or indexing a table causes the record to be printed in a specified order, many times user want to separate the record into groups. The data grouping option on the report menu allows you to create upto 20 levels of groups within groups of data & perform opertions on those groups.
Adding a group band
1. 2. 3. 4.
Choose Data Grouping push button from the report menu. Choose the Add push button. The Group Info dialog appears. Choose the Group push button to bring the expression builder. Create the group expression in the expression builder & choose OK to return to the Group Info dialog. Choose the desired grouping options. 33
FOXPRO PROGRAMMING
5.
Chosse OK. The group expression is displayed in the data grouping list.
To add more group repeat this process. Groups are numbered in the data grouping list in the order in which they are created. Group bands can be changed added or deleted by using corresponding puch buttons. 7.
Variables - Choose variables to display the report variables dialog so you can create memory variable within a report. memory variables can be used to store the results of caluclations performed while the report is being printed.
1. 2. 3.
Choose variables in Report menu. Choose add push button In the variable name text box enter the name for the variable in this text box. We can assign initial values to the variables reset the variable, specify the calculation for the variable, delete variable change the order of variables by choosing appropriate options.
8.
Quick Report - The quick report option in the report is only avaiable when the detail band of the Report Layout Window is empty.
1.
Choose Quick report to open the quick report dialog so you can automatically place selected fields in the Report Layout window. here you can specify column layout or row layout.
i)
Title check box determines whether or not field names will be displayed as titles above or next to the corresponding field. Field check box shows Field Picker dialog box. Add alias check box automatically adds alias to all field names in the Report Layout window
ii) iii)
Saving the report
1.
Choose Save from the file menu to save the report. The first time you save the report you will be prompted to name the file.
2
If you have not previously saved the environment information, an alert appears asking if you want to save the environment information.
Running Reports:
To run reports do one of the following. 1.
Choose Report from the Run Menu & select the report definition file you want to use.
2.
Type report form at command window.
34
FOXPRO PROGRAMMING
ASSIMILATION EXERCISES
Q.1
What are the various rules and conventions being followed while naming a field in FoxPro ?
Q.2
What are the various field types in FoxPro ?
Q.3
How do we: a. Add, Edit, Delete records in a DBF file ? b. Enter text in a menu field ?
Q.4
How do we sort a database in FoxPro ?
Q.5
Explain the significance of Memory Variable in FoxPro, method for their initialization and various operations performed on them.
Q.6
What are the various mathematical and logical operations performed in FoxPro ?
Q.7
Explain the importance of Report generation and how do we create/modify a report ?
35
FOXPRO PROGRAMMING
36
FOXPRO PROGRAMMING
CHAPTER - 3
COMMAND FILES LOOPS & DECISION MAKING IN STRUCTURE PROGRAMMING
COMMAND FILE
The command MODIFY COMMAND will activates the Foxpro Word Processor. The name for the program file can be given with this command. For example, MODIFY COMMAND TEST1 With a default extension of .PRG SYNTAX :
MODIFY COMMAND where is the name of the program file. EXECUTING A PROGRAM:
The DO command executes a program i.e. it executes each command in a program file. SYNTAX:
DO where is the name of program file EXAMPLE: DO TEST1 SET TALK COMMAND
When using memory variables FOXPRO shows the results of assigning or changing the values of these variables i.e. FOXPRO give response to the user when enter the value of a memory variable is changed. Similarly, the results of commands like DELETE, RECALL, REPLACE, COUNT etc. are displayed on the Screen. The SET TALK OFF command given at 37
FOXPRO PROGRAMMING
the command window or in a program, controls the display of such messages. By Default TALK is set ON. EXAMPLE:
Enter following command at command window MODIFY COMMAND TEST1 Alter pressing return key, FoxPro word Processor will be open. Type following lines SET TALK ON STORE 2.6 TO M1 WAIT Save the TEST1. PRG by pressing + Now at command window type DO TEST1 and press return key. At the console 2.6 will be displayed because TALK is set ON. Now, enter MODIFY COMMAND TEST 2 and enter following lines SET TALK OFF STORE 2.6 TO M1 WAIT Save it and run the program. There will be no response because TALK is set OFF. Some other set commands are 1. 2. 3. 4. 5.
SET DATE BRITISH/ AMERICAN SET EXACT ON / OFF SET CONFIRM ON / OFF SET SAFTY ON/ OFF SET PRINT ON/ OFF and many more
38
FOXPRO PROGRAMMING
WAIT COMMAND
The wait command is used to pause the execution of the program. When Foxpro encounters Wait in a program (or you enter WAIT in the command window), it displays a message, Press Any Key To Continue. . . CLEAR COMMAND:
The CLEAR command clears the display. On the Screen. SYNTAX:
CLEAR DECISION MAKING IN PROGRAMS: 1.
IF...ELSE...ENDIF:
The IF...ELSE...ENDIF command is fairly straight forward and easy to understand. It takes the form as under. IF BASIC > 1000 CONV = 500 ELSE CONV = 300 ENDIF In above case if BASIC > 1000, conv. = 500 otherwise conv = 300
SYNTAX:
IF [[ELSE] ] ENDIF where < expL >is a condition EXPLANATION:
If the < expL > is true, the code following the IF clause is executed. If the expression is not true, the code following the ELSE clause is executed. As the square brackets indicate, you can also use this command without any ELSE clause, in the form. 39
FOXPRO PROGRAMMING
IF < expL > . . . . . . ENDIF The code will be executed is the expression is true. NOTE :- EVERY IF HAS A CORRESPONDING ENDIF Example is given below
EXAMPLE:
1.
IF
BASIC > 2000 TAX = 200
ELSE IF BASIC > 600.AND. BASIC < = 2000 TAX = 250 ELSE TAX = 300 ENDIF ENDIF In the program segment shown above, if the BASIC is not greater than 2000, the program further checks if the BASIC is in the range of 600 to 2000. If so, the variable TAX is assigned the value of 250, otherwise it gets the value 300. 2.
IF POST = “DIRECTOR” IF BASIC > 10000 TAX = 1000 ELSE TAX = 300 ENDIF ELSE IF POST = “PRINCIPAL DESIGN ENGINEER” . . . ENDIF ENDIF
In this case, if the POST is DIRECTOR, the program further checks if the BASIC is greater than 10000. If both conditions are true, the variable TAX will get the value 1000 otherwise if BASIC is not greater than 10000, the TAX will be 300. Since an IF . . . ENDIF construct is nested inside an other IF . . .ENDIF, Such constructs are called NESTED IF CONSTRUCTS.
40
FOXPRO PROGRAMMING
2.
DO CASE . . . ENDCASE:
The program segment using the nested IF...ELSE...ENDIF construct could also be written, using the DO CASE . . . ENDCASE construct, as given below: DO CASE CASE BASIC > 2000 TAX = 200 CASE BASIC > 600.AND. BASIC <2000 TAX = 250 OTHERWISE TAX = 300 ENDCASE The DO CASE...ENDCASE construct selects a single course of action from a set of alternatives. Any number of mutually exclusive options may be listed between DO CASE and the ENDCASE commands. Each case is evaluated sequentially. As soon as a single CASE statement is found to be true, FOXPRO executes all commands between that CASE statement and the next one. All other CASE statements and commands within the construct are ignored and processing begins at the first command after the ENDCASE clouse. IF one of the conditions is found to be true, the commands after the OTHERWISE clause gets executed. The OTHERWISE clause operates in the same way as the ELSE clause in the IF statements. The ENDCASE command must be used to mark the end of the DO CASE construct in a program. PERFORMING REPETITIVE TASKS : LOOPING 1.
DO WHILE ... ENDDO
DO WHILE loop, which has the following form i.e. SYNTAX:
DO WHILE < expL > . . . . . . ENDDO where < expL > is a condition The DO WHILE . . . ENDDO construct allows statements to be executed repeatedly as long as a given condition is true. EXAMPLE: C.E.D.T. Gkp. has to print a list of the company providing components of which 5 copies are required. 41
FOXPRO PROGRAMMING
Following is the solution:
* * * COMPANY. PRG * * * USE COMPANY. DBF STORE 0 TO PRINT DO WHILE PRINT < 5 LIST TO PRINT PRINT = PRINT+1 ENDDO The variable PRINT keeps a count of the number of copies printed. The DO WHILE . . . ENDDO construct specifies a loop that continues until the condition ( PRINT < 5 ) becomes false. On encountering an ENDDO, control is passed back to the DO WHILE statement and the condition is checked once again. If the condition is still true, the statement in the loop are repeated. So, in the above program, the loop will end when the value of PRINT becomes equal to 5. 2. FOR — ENDFOR:
The FOR . . . ENDFOR executes a set of statements within a loop a specified number of times. A memory variable or an array element is used as a counter to specify how many times the statements inside the loop are executed. Consider the following DO WHILE . . . ENDDO program to display logarithm of numbers 100 through 110 that we discussed STORE 100 TO NUMBER DO WHILE NUMBER < 111 ? LOG (NUMBER) NUMBER = NUMBER +1 ENDDO The same program can be written with a FOR - ENDFOR loop in the following way: FOR NUMBER = 100 TO 110 ? LOG (NUMBER) ENDFOR END FOR By default, FOR . . . ENDFOR increments the counter (memory variable) by 1. However, if required, you can specify the increment rate through the step option with For. For instance, the following program displays the square root of the series - 10, 12, 14, ... 30. FOR NUMBER = 10 TO 30 STEP 2 ? SQRT (NUMBER ) ENDFOR NOTE: Like DO WHILE . . . ENDDO, you can also use loop and Exit commands within FOR. . . ENDFOR.
42
FOXPRO PROGRAMMING
3.
SCAN . . . ENDSCAN:
SCAN . . . ENDSCAN is another very powerful structure. A condition is specified with SCAN and Fox Pro executes the commands included within SCAN and ENDSCAN on all records of the current database that meet the condition. For example, let us see how SCAN . . . ENDSCAN can be used to get mailing address labels for a particular city. Consider the following program shown below. USE ADDRESS ACCEPT “ Enter name of City “ TO CHK_CITY SCAN FOR CITY = CFK_CITY ? NAME ? LOCALITY ? TRIM (CITY), PIN ? END SCAN 4.
TEXT. . . ENDTEXT :
If a screen contains a lot of display matter, instead of giving a number of @ SAY Statements, the actual display desired can be put within TEXT and ENDTEXT statements. When FoxPro encounters the TEXT commands, it displays the lines following it until ENDTEXT is encountered. Example:
The following example shows how TEXT... ENDTEXT can be used to display a menu. CLEAR TEXT 1> ADD a new record 2> DELETE a new record 3> EDIT a record 4> EXIT ENTER CHOICE 1,2,3,4 ENDTEXT @ 20,20 GET CHOICE READ All the lines within TEXT...ENDTEXT are dIsplayed as they are, on the screen, including blank lines, if any. INPUT & OUTPUT STATEMENT:
The @ . . . SAY statements allow designing customised formats for screens and reports. For example, consider the following screen format. 43
FOXPRO PROGRAMMING
Fig 3.1: Customised screen formats
To display this on the Screen, the command systax is SYNTAX:
@ < ROW >, < COLUMN > SAY < EXPR > where < expr > is an expression
COLUMN
Fig. 3.2 : Showing Rows & Columns Co-ordinates
The rows are numbered from top to bottom. The first(top) row is 0 and the last row is 24. The columns are numbered from 0 though 79 from left to right.
44
FOXPRO PROGRAMMING
EXAMPLE:
Suppose we have to display data at row No. 7 and coloum No. 30 than we have @ 7, 30 SAY “NAME” Thus at Screen NAME will be printed at ROW 7 and column 30. DFFINING SCREEN COORDINATES IN USER WINDOW:
If a user defined window is active, all @ commands are routed to that window. In that case, the coordinates used with @ commands are with reference to the top-left corner of the window and not the physical screen coordinates. To experiment with @ commands in a window, enter the following commands: CLEAR DEFINE WINDOW TEST FROM 5, 15 TO 15, 60 ACTIVATE WINDOW TEST @ 0,0 SAY “Position 0,0 in Window” Screen will be as below
Fig 3.3 : User-defined Window SYNTAX: For defining window
DEFINE WINDOW < file > FROM < R,C > TO < R,C > DOUBLE/PANEL/NONE Where R employs Row C employs Column 45
FOXPRO PROGRAMMING
DOUBLE :- defines a double line border PANEL
:- defines a solid border
NONE
:- if we donot want to use any border
NOTE
:- if nothing is given single border line window is drawn.
SYNTAX:-
For activating window
ACTIVATE WINDOW SYNTAX:-
For deactivating window
DEACTIVATE WINDOW Now finally returning to the point, the commands are: @ 3, 20 SAY “STORE DEPTT.” @ 5, 10 SAY “LIST OF EMPLOYEES CURRENTLY WORKING” Now Director C.E.D.T.I. . wants to design a Screen that displays the details of employees in the following format:
EMPLOYEE DETAILS EMPLOYEE NO
:
0001
NAME
DESIGNATION
:
MANAGER
DEPARTMENT :
ACCTS
DATE OF BIRTH :
03/03/70
BASIC
5000
HRA AMOUNT
2000.00
CONVEYANCE :
:
Following is the solution to above Problem:
@ @ @ @ @
@ @
@ @
2, 20 SAY “EMPLOYEE DETAILS” 4, 5 SAY “ EMPLOYEE NO: “ 4, 21 SAY EMP_NO 4, 20 SAY “NAME :” 4, 48 SAY EMP_NAME . . 8,5 SAY “DATE OF BIRTH” 8, 21 SAY D O B . . 10, 20 SAY “ HRA AMOUNT : “ 10, 30 SAY BASIC *.25 46
:
:
MONI PANDEY
600.00
FOXPRO PROGRAMMING
To avoid this tedious process, we can display more than one value in a single line with a single @ ... SAY command by using ‘+’ sign
@ @ @
2,20 SAY “EMPLOYEE DETAILS” 4,5 SAY “EMPLOYEE NO. : “ + EMP_NO 4,40 SAY “name :” +EMP_NAME
NOTE : When combining expressions/values with a ‘+’ sign, each value must be represented as a character type. For example:
@ @ @
8,5 SAY “DATE OF BIRTH :” + DTOC(dob) 8,40 SAY “ BSIC(Rs) :” + STR(basic, 7,2) 10,5 SAY “HRA AMOUNT :” + STR(basic *.25, 7,2)
NOTE:- That numeric or date types are converted to character type only if they have to be combined with an other type, using the ‘+’ sign. INPUT FROM THE USER THROUGH KEYBOARD:
Consider a situation where a list of records of either the Accounts or the Store department is to be printed, based on the value of a CHOICE. Now let us see how the user can input his CHOICE through key board, in response to a message displayed on the screen. @ 10,10 SAY “ENTER CHOICE, 1 = Accts 2 = STORES” @ 10, 50 GET choice READ The @ SAY and GET command specifies the position on the screen, where the value typed from the key board is to be accepted. The READ command accepts this value into the variable. The @ SAY and GET statements can be combined as follows: @
10,15 SAY “ENTER CHOICE 1 = Accts,2 = STORES” GET Choice READ
PROCESSING OF ALL RECORD FROM A DBF FILE Let us see how the DO WHILE ... ENDDO loop helps in processing records of file. Director C.E.D.T.I. Gkp. wants to use the employee Details Screen (refer Fig.1.7) to view the records of all the employees.
47
FOXPRO PROGRAMMING
Following is the solution :
To do this, the @ . . . SAY Statements have to be executed for each record in the employee master file. This can be done by using a looping construct in the program code. USE EMP_MAST DO WHILE .NOT. EOF( ) ... ... (@ SAY STATEMENT) ... SKIP ENDDO EXPLANATION:
When the file is opened the record painter is positioned at the first record. The loop is entered and the first record is displayed. The SKIP Command moves the record pointer to the next record. The end of file (EOF) is reached when the record pointer is placed after the last record. The statements in the loop are executed as long as end of file is not encountered, i.e. as long as the condition .NOT. EOF ( ) is true SYNTAX:
DO WHILE < expL > < Statements > where < expL > is a condition PROCESSING: A GROUP OF RECORD
We just saw how the DO WHILE . . . ENDDO loop displayed the details of all the employee records in the EMP_MAST.DBF file. Suppose the employee details are required for only a single department-stores. This implies that the records pertaining to stores have to be searched from the file EMP_MAST.DBF file. This file is indexed on the field DEPT, all the records for a particular department will be arranged togeter thus, only the first record for that department is to be searched.. USING “SEEK” COMMAND FOR SEARCHING RECORDS:
The SEEK command searches for specific records in an indexed file. USE emp_mast INDEX dep SEEK “STORES” DO WHILE dept = “STORE” .AND . . NOT. EOF ( ) . . . SKIP ENDDO 48
FOXPRO PROGRAMMING
EXPLANATION:
In this program segment, SEEK searches the index of EMP_MAST. DBF, i.e. DEP.IDX, to find the first record where DEPT is “STORES”. This record is processed and then SKIP positions the record pointer at the next record for which the department is stores. This record is processed and then SKIP positions the record pointer at the next record for which the department is stores SYNTAX:
SEEK < exps > where < expr >is the expression to be searched NOTE: The database must be indexed on this expression. USING “LOCATE” COMMAND FOR SEARCHNG RECORDS:
If the file is not indexed on the desired field/expression, or the recorde to be searched for are very few in number the LOLATE command can be used to search for the records. For example; USE EMP_MAST LOCATE FOR BASIC > 100 DO WHILE. NOT. EOF ( ) . . . CONTINUE ENDDO EMP_MAST. DBF is not indexed on BASIC. So, the LOCATE command is used to find the first recrod in the file that contains BASIC greater than Rs. 4000. The CONTINUE command finds the next record in the file that meets the given condition. This search process continues till the file reaches the end. MENU DRIVEN PROGRAMS:
A menu program displays a list of tasks to be done and the user can select one of these tasks. These programs are user friendly, since they are easy to use. In fact, the user need not know the program for executing a particular option.
49
FOXPRO PROGRAMMING
System File Edit Database Record Program Window Run C.E.D.T.I. GKP DEPARTMENT WISE EMPLOYEE INFORMATION 1. 2. 3. 4.
ACCOUNTS STORES PERSONNEL SALES
Enter your choice -
Fig 3.4 : Customized Screen Format
The RANGE option used with GET commands for getting only particular menu @ 16, 10 SAY “ENTER YOUR CHOICE” GET M_NUM RANGE 1,4 READ If the user does not enter the number in the given range i.e. (1-4), a message “RANGE: 1 TO 4 (press SPACE)” appears on the Screen. SYNTAX:-
GET [RANGE [< expN1 >], [< exp N2 >]] where
is the lower Limit is the upper Limit
The RANGE Option can be given only for numeric and date type fields. INTEGRATING THE SUB-PROGRAMMES:
Let us see how menu programmes are used to integrate sub-programmes. After accepting the choice as a number, the appropriate sub-program has to be executed. This is done as follows:
50
FOXPRO PROGRAMMING
IF CHOICE = 1 DO ACCT ELSE IF CHOICE = 2 DO STORE . . . . . . . . . ENDIF Here, the sub program ACC.PRG and STOR.PRG will get executed.After a sub-programm is executed,control has to be passed back to the main or parent programm with the RETURN statement. SEEK “ACCTS” . . . . . . . . . RETURN LIGHT BAR MENUS DESIGNING
Fox Pro provides another method of designing menus, in which, the menu options, referred to as Menu Prompts are selected by moving a high light bar.Such menus are called Light-bar menus and are attractive and are easy to use. This is because the user can select an option by just pointing to it rather than entering a value from the keyboard. The high light bar movements are controlled by the arrow keys. An option is selected by either typing the first character of the prompt or by pressing the < ENTER > key. Fig.3.5 shows a light-bar menu. The following program segment shows the menu required @ @ @ @ @
2, 20 SAY “C.E.D.T.I. “. 4, 5 SAY “DEPARTMENT-WISE EMPLOYEE INFORMATION” 6, 10 PROMPT “ACCOUNTS” 8, 10 PROMPT “STORES” 10, 10 PROMPT “SALES” MENU TO M_NUM
51
FOXPRO PROGRAMMING
Output screen is as shown below.
C.E.D .T.I. G KP. DEPARTMENTWISE EMPLOYEE INFORMATION
ACCOUNTS STORES PERSONNEL SALES
SALES Fig 3.5 : Light Bar Menus EXPLANATION:
The @ . . . PROMPT commands display the menu options at designated positions on the Screen. The MENU TO command invokes a light-bar menu, controlled by cursor keys. As the user navigates through the menu, each Option is highlighted. On selecting an option, the variables specified with the MENU TO command (in this case, M_NUM) is created and gets a numeric value depending on the order of the prompts. SYNTAX:
MENU TO < memory variable > where < memory variable > is the variable to accept the menu option. The MESSAGE option of the @ . . . PROMPT command, displays messages on a specified row, to help the user choose on option.
SET MESSAGE TO 20 @ 6, 10 PROMPT “ACCOUNTS” MESSAGE “Display records for Accounts department” These commands will display the message “Display records for Accounts department” on row 20.
52
FOXPRO PROGRAMMING
SYNTAX:
@ < row >, < col > PROMPT < expc > [MESSAGE < expc > ] where < expc > is a character FUNCTIONS: 1.
UPPER():
Where is a character expression. This function returns the upper-case value of the character string or varriable 2.
LOWER ():
This functions returns the lower case value of the character string or varriable. Example:
ANS=”y” 1.DOWHILE UPPER(ANS)=”y” ANS=”T” 2.DOWHILE LOWER(ANS)=”T” 3.
VALID ():
The valid command is used to check whether the data entered into a variable is valid or invalid. The operation is similar to that of the range clause the only disfference being that it can be used to validate all data types and complex conditions. EXAMPLE :
In order to validate is the length of NAME is grater than O,the command is @ 5,5 say “ENTER NAME”GET NAME VALID (LEN(TRIM(LTRIM)))>O READ Explanation:
In above case the variable will accepted untill name is found to be valid. The only disadvantage of the VALID command is that the user cannot specify his oven error messages because the VALID command gives an error message of its oven. 53
FOXPRO PROGRAMMING
4.
TRIM( ):
The TRIM ( ) FUNCTION REMOVES ANY TRAILING BLANKS. Syntax:
TRIM (< expc >) where < expc > is a character expression.
5.
LTRIM( ):
The LTRIM( ) is used to remove the loading blanks. Syntax:
LTRIM() where < expc > is a character expression. 6.
RTRIM( ):
The RTRIM is used to remove the trailing blanks. Syntax:
RTRIM (< expc >) where < expc > is a character expression. 7.
ALLTRIM( ):
The ALTRIM( ) command is used to remove all the loading and trailing blank. Syntax:
ALLTRIM (< expc >) where < expc > is a character expression 8.
LEN( ):
LEN( ) returns the length of a specified character expression. Syntax:
LEN (< expc >) where < expc > is character expression. Example:
CH = “RAJAN” ?LEN(CH) 5 54
FOXPRO PROGRAMMING
9.
SUBSTR( ):
Suppose for a memory variable MITEM_CODE the first 4 characters must be alphabets and the next(and Last) 2 characters should be digits whose value is greater than 1 but less than 10. The user has to enter the MITEM_CODE repatedly as long as these conditions are not satisfied. The validation is done using the SUBSTR( ) and the VAL( ) functions. The last two digits are extracted using the SUBSTR( ) function and then they are converted to a numeric value using the VAL( ) function. Example:
In the following example, the extracted word(string) starts at the third character and continues for 4 characters: IF SUBSTR(“ABCAME LOT, 3,4) = “CAME” : Syntax:
SUBSTR(< expc >), < Start >, [ < num_Char >]) where < expc > is a character expression < start > is the stating position < num_char > is the number of characters to be extracted. 10. VAL( )
The VAL( ) function is used to convert a character variable or constant that contains digits to a numeric value. In the following example C_VAR is a character variable: STORE “133” TO C_VAR IF VAL ( C_VAR ) = 133 : : Syntax:
VAL(< expc >) where(< expc >) is a character expression. 11. RECCOUNT ( ):
The RECCOUNT ( ) returns the number of records in the active database file. If there is no active database file in the currently selected work area, RECCOUNT ( ) returns a zero. For example, 55
FOXPRO PROGRAMMING
USE i_mast ?RECCOUNT ( ) 20 RECCOUNT( ) supports an optional parameter which indicates the workarea to which the function is to be applied. For example, to get the number of records in the file from workarea B, which is the second workarea, the command is ?RECCOUNT (2) 10 12. FCOUNT ( ) :
The Fcount ( ) returns the number of fields in the active database file. For example USE i _ mast ? FCOUNT ( ) 4 Like the RECCOUNT ( ) function, FCOUNT ( ) supports an optional parameter which indicates the workarea of the file to which the function is to be appliedd. For example, to see the number of fields in I _ TRAN. DBF in workarea B, the command is ?FCOUNT (2) 7 13. RECNO ( ):
The RECNO ( ) function returns the current record number, which is the physical position of a record in the active database file. For example, USE i _ mast ?RECNO ( ) 1 RECNO ( ) also supports an optional parameter which indicates the workarea to which the function is to be applied. CREATING A PROCEDURE
A procedure is like any FoxPro program except that it begins with a PROCEDURE statement to name it.Procedures can have any valid FoxPro name.Generally a meaningful one that describes the purpose is best. The last line in PROCEDURE is a RETURN staement..It indicates the end of the procedure and returns control to the caller.Although a procedure can have several RETURNs, good coding avoids multiple exit points. 56
FOXPRO PROGRAMMING
After testing the procedures, group them into one module. To access them, use the SET PROCEDURE TO command to tell FoxPro the file names where they are stored. We can even store procedures with the main program by placing them after it. CALLING A PROCEDURE:
DO calls a procedure the form is Do
When FoxPro finds the procedure, it executes there until it reaches a RETURN. RETURN makes it exit and return control to the caller. execution resumes at the line immediately after the DO Following program illustrate the flow of control for a program calling a procedure. * Main Program DO dispForm QUIT procedure dispform RETURN EXITING PROCEDURE:
The normal way to exit a procedure is via a RETURN statement. Execution continues at the line immediately after the calling DO. The following four command are used to exit from the procedure 1.
CANCEL
2.
QUIT
3.
RETURN TO MASTER
4.
SUSPEND
57
FOXPRO PROGRAMMING
EXAMPLE
*Main program DO Proced _1 QUIT PROCEDURE Proced _ 1 DO proced _2 RETURN
RETURN TO MASTER returns to the highest procedure call. This is the DO command in the main program that initiated the series of calls to reach procedure proced _2
PROCEDURE PROCED _2 < Proced _ 2 commands> If Condition RETURN TO MASTER _ ENDIF RETURN ASSIMILATION EXERCISE
Q.1
Q.2
Suppose you want to make a small FoxPro program. Give the general syntax of initializing the program file with .PRG extension and after typing in your program, by which command you will be able to run it ? What is wrong with the following program: If Choice = A do main else do not_main
Q.3 Q.4
What is the function of DOCASE ...... ENDCASE loop ? You have made a database file namely ADDRESS.DBF in which the fields NAME, PHONE_NO stores the names and phone nos. of all your friends respectively. Now, fill in the blanks with a FoxPro command in the following program to search for a name “Deepak” and also display his phone number. use Address Accept “Enter name to search :” to s_name ........................... NAME = s_name ................ * Display Name * ................ * Display Phone Number * ........................... * End of Loop *
Q.5
Design the following format using @.....Say command starting from 10th row: WELCOME MY
Q.6
TO HOME
A dbf file contains one word starting from each alphabet. How will you search for the word starting with “P” ? 58
FOXPRO PROGRAMMING
SECTION - B
COMPETENCY OBJECTIVES The objective of this Section is to introduce the students to the various enhance features of FoxPro. At the end of this section, a student should be able to :v v v v
Create Screens and add objects to screens. Develop application projects and create EXE files. Carry out relational query by example. Use object linking and embedding.
59
FOXPRO PROGRAMMING
60
FOXPRO PROGRAMMING
CHAPTER - 4
SCREEN GENERATION & OBJECT ADDING SCREEN CREATION
You create screens in the familiar way: Select File, New. Select the Screen radio button, and select the New (or OK) push button to generate the command CREATE SCREEN Untitled; then name the screen by selecting File Save As. As usual, if you are working from the command window, you can name the screen when you first create it by entering the command CREATE SCREEN . The Screen Builder window is shown in Fig. 4.1, and the icons of the toolbox are labelled.
Fig 4.1
Screen specifications are stored in an ordinary database file with the extension. SCX. When you select Program Generate, FoxPro generates the programming code needed to display that screen, which is kept in a file with the extension. SPR. This file is an ordinary FoxPro program, like the ones you wrote in PRG files, and you use it with the usual command DO . Because it does not have the default. PRG extension, you must enter the complete file name to run it. For example, DO SCREEN1.SPR. 61
FOXPRO PROGRAMMING
Edit Regions:
To add an edit region, select the edit region tool and then click-and-drag on the screen layout to place the edit region. When you release the mouse button, FoxPro displays the Edit Region dialog box, shown in Figure 4.2
Fig 4.2 Push Buttons
An asterisk is the specification code for a push button. Its picture function must begin with an asterisk followed by a list of the prompts used in the push buttons being created. For example, to create two push buttons with the prompts OK and Cancel, you can use the clause PICTURE ‘@*OK; Cancel’. FoxPro adds the angle brackets around the prompts that are listed. Fig. 4.3. In the basic form of the command— @ GET FUNCTION IPICTURE
62
FOXPRO PROGRAMMING
Fig. 4.3 Radio Buttons:
*R is the specification code for a radio button. Its picture function must begin with *R followed by a list of the prompts used in the radio buttons being created. For example, to create three radio buttons with the prompts Upper, Proper, and Lower (which you can use to determine capitalization), you can use the clause PICTURE ‘@*R Upper; Proper; Lower’ or the equivalent clause beginning with FUNCTION. You can define hot keys for radio buttons as you do for push buttons, by putting GET FUNCTION IPICTURE
Fig. 4.4 63
FOXPRO PROGRAMMING
Check Boxes:
*C is the specification code for a check box. Its picture function consist s of *C followed by the prompt that the check box uses. For example, to create a check box that capitalizes text, you can use the clause PICTURE ‘@*C Capitalize’ or the equivalent clause using FUNCTION. This command can have only a single prompt. You can give it a hot key oin the usual way, by putting\
Fig. 4.5
In the basic form of the command @ GET FUNCTION IPICTURE Popup Controls:
A caret(^) is the specification code for a popup control, and it is f ollowed by a list of the options the popup displays. For example, to create a popup control to let the user choose among four data types, you can use the clause PICTURE ‘@^Character;Number;Date;Logical’ or the equivalent FUNCTION clause. Since there are often more options to a popup than you want to list, you can use the optional clause FROM to read them from an array instead.
Fig. 4.6 64
FOXPRO PROGRAMMING
In the basic form of the command @ GET FUNCTION IPICTURE Adding Object to the Screen:
When you select the text tool, the pointer becomes an insertion bar, and you can click anywhere in the screen layout to place a cursor there and type new text or edit text that is already there. Select the selection pointer tool when you are done editing to return the pointer to its normal form.
Fig. 4.7
You can specify the font, justification, and spacing of text, as in reports, by selecting Object font to display the familiar Font dialog box or by selecting Object > Text Alignment to display a submenu that lets you select text justification and spacing. Figure 4.7, which lets you enter a comment for your own purposes. Lines, Rectangles, and Round Rectangles:
As in reports, you can place lines, rectangles, or round rectangles in screens simply by clicking the appropriate tool in the toolbox and clicking-and-dragging to place the object in the screen. After you have created these objects, you can use their dialog boxes by double-clicking them. The line and rectangle (like text objects) simply display the Comment dialog box, which lets you enter a comment for your own purposes. The round rectangle displays the dialog box shown in Figure 4.8, which lets you select the style(degree of roundness), just as you done in reports.
65
FOXPRO PROGRAMMING
Fig. 4.8
These dialog boxes do not control the thickness of the lines used to draw these objects. To change the thickness or style of lines, select the object and then select Object > Peri. This selection generates the command @ , TO , , which draws a box with the first row and column specified as the upper-left corner and the second row and column as the lower-right corner. Fields:
To place a field, click the field tool and click a location on the screen in FoxPro for Windows (or move the cursor to the location and select Screen Field in FoxPro for DOS) to display the Field dialog box, shown in Figure 4.9. This dialog box lets you place fields, field expressions, or memory variables on the screen and control their features. Typically, the Input Field (Get) radio button is selected, and this dialog box generates the ordinary @ ... GET with a field name as the variable that is displayed. This field can be edited when the READ command is executed.
66
FOXPRO PROGRAMMING
Fig. 4.9
When the Output Field (Say) radio button is selected, most options in the dialog box are disabled. You can use the Output push button to select the variable to be displayed, and you can use the Comment push button and refresh check box. The other options are dimmed, as they do not apply when a variable is just being displayed and cannot be edited. If the Say radio button is selected, however, it generates the command @ ... SAY , which simply displays the contents of the variable and ‘do’ not let the user edit it.
ASSIMILATION EXERCISE
Q.1
How do we create screens in FoxPro ?
Q.2
Briefly describe the following and method of implementing them : a. Edit regions b. Push Button c. Radio Button d. Check Box e. Pop up Control
Q.3
What are the various static objects available for screen building in FoxPro ? 67
FOXPRO PROGRAMMING
68
FOXPRO PROGRAMMING
CHAPTER - 5
RELATIONAL QUERY BY EXAMPLE
Relational Query By Example (RQBE)
The process of extracting specific information is called querying . the FoxPro, querying facility is called RQBE, which stands for Relational query By Example l l l
Relational refers to the ability of use several tables with common fields Query means to question or to enquire. By Example is the way that you ask question such as “Can you tell me how many companies are located in California?’
Simple Query
RQBE allows you to query your data in many different ways. It makes querying both quick and easy. To begin, open the RQBE window. Choose Open .... from the file menu. In the Open dialog that appears, select C:\FOXPRO\TUTORIAL\ in the Directory list. Select CUSTOMER. DBF and Open. Choose New .... from the file menu. In the New dialog, choose the Query radio button.then choose the New push button. The RQBE window appears as shown in Figure5.1.
69
FOXPRO PROGRAMMING
Fig. 5.1 Special Query:
You can use RQBE to display only specific fields specifying output fields for a query. 1. 2. 3. 4. 5.
In RQBE, window choose the fields .... check box . The RQBE select fields dialog appears. Choose remove all to clear the selected output list. Move customer, company to the selected output list. To move the field select it in the Table fields list and then choose move. Repeat step 3 for the following customer fields in the following order : contact, phone, city state and YTDPURCH, The RQBE select fields dialog . Choose OK the selected fields are displayed in the output fields list in the RQBE window.
When you do the query, the fields in the Browse window appears in the order in which they were chosen in the RQBE window. Doing the query
1. 2.
Choose do query in the RQBE window If necessary, size the Browse window
Ordering a query
You can specify the order in which data is presented. For instance, you can display date, alphabetically by City Ordering fields in a query.
70
FOXPRO PROGRAMMING
1. 2. 3. 4.
Choose the order by... check box in the RQBE window In RQUB order by dialog, move customer city to the ordering list to move the field select it from the selected output list, the choose move Choose OK to return to the RQBE window. Do the query
Specifying selection condition
To extract only specific record field, you specify selection conditions. Selection conditions tells FoxPro the requirements a record must meet in order to be included in the query output. Each selection condition consists of a field from a table, a comparison item, & a value to be compared with the field contents. Specifying selection conditions
1. 2. 3.
In the RQBE window, click on the rectangle below field name to display the field name popup select Customer State. Type OH in the text box below example be seen both letters a capitalize. Do the query.
You have created a selection condition to view only those records for companies on Ohio. In the same manner, popup controls to create multiple rows in the selection criteria area of this dialog. The selection conditions you have entered requires the STATE field to look like “OH” STATE is the field, like is the comparison item, & “OH” is the value being compared to the field contents. Like specifies that CUSTOMER, STATE must match “OH” is the value being compared to the field contents. Like specifies that CUSTOMER, STATE must match “OH” for a record to be displayed.
ASSIMILATION EXERCISE
Q.1
RQBE stands for : Relational Query By Extension
[ ]
Revolving Query By Example
[ ]
Regional Query By Example
[ ]
None of these
[ ]
Q.2
How do you select fields for a query and do the query using RQBE ?
Q.3
How can you specify the way or the order in which the data is presented ?
Q.4
How do you specify the selection conditions in RQBE ? 71
FOXPRO PROGRAMMING
72
FOXPRO PROGRAMMING
CHAPTER - 6
OBJECT LINKING AND EMBEDDING
INTRODUCTION
The ability to cut and paste information from one application to another is a basic feature of the window environment. For example we can include a picture from PC Paint Brush in a database and, by double clicking on it, open a Paint Brush session to edit it. The links defined by Windows make OLE differ from simple cut-and-paste. Many Windows applications share and exchange information through object links. A link connects a source document that original object to destination document that uses it. Objects are anything we can copy to the clipboard. Examples include sound, clipart, animation, business charts, spreadsheets and documents. We can further specify the connection between an object and its new location as linking or embedding. A linked object does not move. Example below replaces the @...SAY command with MODIFY GENERAL allowing to modify the OLE object by double clicking on it. Example: * Display an OLE picture and allow user to edit it CLEAR *Open databases and find record with picture USE STATES SET ORDER TO STATE SEEK”FL” * Define picture window DEFINE WINDOW PICWIN FROM 5,5 TO 20,20 FONT “MS Sans Serif,10,B” TITLE states.fullname; CLOSE FLOAT GROW ZOOM * DISPLAY picture MODIFY GENERAL states.image WINDOW picwin * End of program
73
FOXPRO PROGRAMMING
Because we have copied the image as an embedded object. FoxPro knows to open Paintbrush as shown in Figure 6.1
Fig. 6.1
If we change the image (in fig. 6.1 we added the text “Gulf of Mexico”).We must update the embedded version before returning to FoxPro. We can do this by selecting option Update in Paintbrush’s File pull down. However, if we forget or try to exit to FoxPro without updating it, the alert box shown in Fig. 6.2 appears to give us a second chance. If we decide to exit without updating the object, the changes are lost.
Fig. 6.2
We can also use a VERB clause in the @...SAY command to open and edit an OLE object. Verbs consist of numbers or command strings. To determine which ones are valid for OLE servers in your system ,run REGEDIT from the windows Program Manager .It opens a window with the title Registration Info
74
FOXPRO PROGRAMMING
*Display picture ACTIVATE WINDOW picwin @ 0,0 SAY image *End of program
Fig. 6.3
In this example FoxPro display the state’s image in the exact size of the original. So we need to know its size to define the window .If the window is too small, the image is truncated. Too large and it will appear in the upper left corner with space to its right and beneath it as shown in Fig. 6.3. Someone might ask why Cuba is not visible in the empty area! On the other hand, we can stretch the image to fill the window completely by adding STRETCH to the @ . . . command. @ 0,0 SAY STRETCH Fig. 6.4 shows the result. However, FoxPro may not stretch each dimension equally. Therefore it may distort the image. The result makes Florida look like Maryland, thus insulting two states simultaneously!
75
FOXPRO PROGRAMMING
Fig 6.4
If we replace STRETCH with ISOMETRIC, FoxPro enlarges the images proportionally in both dimensions until it reaches a window border. Fig. 6.5 shows the same window as Fig. 6.4 but with ISOMETRIC. Normally the object would appear only along the left side with a lot of white space to its right. However, if we add the CENTER option, FoxPro centers the stretched image in the extra space, horizontally in this case.
Fig 6.5
76
FOXPRO PROGRAMMING
Note, however, that @. . .SAY does not allow us to edit the object. To do that, we must open it using the MODIFY GENERAL command. Moving the original object, even deleting it, has no effect on the copy stored in FoxPro’s general field. Storing OLE Objects in a General Field:
To add an OLE object to a general field, first open the source or its application and copy it to the clipboard. Next activate the FoxPro for Windows session or start it from the Project Manager. Open the database where you want to embed or link the object and select the record and general field. Remember, to open a general field, double click on it or press Ctrl-Home. Next, decide whether to link or embed the object. To link it, select Paste link from the Edit pull down. To embed it, choose Paste. We can embed an object without first placing it on the Clipboard. To do so, first open the general field. Then choose Insert Object from the Edit pull down. This open a dialog shown in figure 6.6 that displays a list of all OLE servers. Simply choose one and create the object. When done, exit and FoxPro automatically embeds the entire object file (picture or text) and includes it in the general field.
Fig. 6.6 Using OLE Fields:
Suppose we have a database containing information about U.S. states with the fields: ABBREV FULLNAME IMAGE
- state abbreviation - full state name - simple outline picture of the state
The first field stores a two character abbreviation. The second stores the full name. Both fields are type character. The third field (of type general) stores a picture of the state created with PC Paintbrush. In previous sections, we learned how to link and embed objects from other applications. Therefore, it should pose no problem to create this file. We also mentioned that, in Edit or Browse mode, general fields display the characters Gen or gen and we can show their contents by double clicking. However, we may want a program to displays the OLE object immediately. Program shows the easiest way to display an OLE picture using a previously defined window. 77
FOXPRO PROGRAMMING
* * *
*
Program Display an OLE object CLEAR Open database and find record with picture USE states SET ORDER TO state SEEK ‘FD’ Define picture window DEFINE WINDOW picwin FROM 5,5 TO 30,10 FONT ‘MS Sans Serif, 10, B’ ; TITLE states. Full name; CLOSE FLOAT GROW ZOOM
An embedded object exists twice, once in its original file and once in the new location. If we edit it, we modify only the copy. The original object remains unchanged. In fact, we can delete the original file without affecting FoxPro’s ability to display or edit the object. Why Link Objects into FoxPro?
Some obvious reasons for linking objects are: l l l l l
To add a company logo to a screen or report To add a business chart to a report To add a sound clip to a record To add a picture to a record (for example, a database of houses for sale) To include a document rather than a memo field to provide fonts and formatting information with the text.
Creating an OLE Object
We cannot drop an OLE object anywhere. FoxPro for Windows supports OLE only through the General data type. It resembles a memo. In fact, FoxPro stores both in a common file. Besides, while in Edit or Browse, general fields display ‘gen’ when empty and ‘Gen’ when they contain something. We open general fields by double clicking on them. We can then edit the object by double clicking on it. If it is a picture, we can even leave the object window open and skip through the database to view its contents in each record. Other OLE objects such as sound clips play when opened or may open other applications such as a word processing object. Imagine a future in which objects could include foul polluting odors, detailed forensic reports with associated film clips, or the kind or window-relating music. OLE objects are very flexible. They can store sound, pictures, spreadsheets, or text. In fact, each record can store a different object type. For example, FoxPro includes database OLETABLE.DBF in directory \SAMPLE\OLE. It contains four OLE objects in each record: a picture, a sound clip, a graph, and a Word for Windows document. Applications can be OLE clients, servers, or both. A client only accepts objects. For example, because FoxPro accepts objects into general fields, it acts as a client. The application that 78
FOXPRO PROGRAMMING
creates them, such as PC Paintbrush, is a server. Some applications can play both roles, creating objects as well as receiving them. When we add an object to a FoxPro report, we can either link or embed it. If we link it, it does not actually become part of the destination. Rather, FoxPro stores a reference or link to it, identifying the application that created it and the directory where it and the application are found. Later, when we select the object to edit it, FoxPro runs the other application and loads the object. If we embed an object, FoxPro stores both it and a reference to its parent application. Thus we can edit either without affecting the other. This ability is useful when we must preserve a master copy of the object while customizing it Editor that provides information about all available Windows applications. Figure 6.7 shows part of the editor output that defines verbs for the Sound Recorder. Note that this application has two verbs, one to play and one to edit a sound clip. We can include either the verb number or its text string when using it. For example, to play a sound clip, we can use: @ 0,0 SAY phasers VERB “PLAY” or @ 0,0 SAY phasers VERB 0 The latter is obviously better if our aim is to keep anyone from ever deciphering what the program does. Fixing OLE Links
If we lose the link to an OLE object due to moving the file to a new directory, we can recreate it by selecting the Change Link option of the Edit pulldown. This dialog (see Figure 11-8) allows us to find the file anywhere. If the source document was deleted, we can either recreate it before using Change Link, or we can select another one instead. Convert Link to Static
If we no longer want to let users edit a linked or embedded OLE object, we can sever the link to the application. Choose Convert to Static (also good for accidentally destroying some of your teenager’s particularly awful-sounding or ear-splitting tapes!) from the Edit pulldown menu. The object remains linked or embedded. However, we cannot double click on it to edit it.
79
FOXPRO PROGRAMMING
ASSIMILATION EXERCISE
Q.1
Highlight the importance of OLE in application development ?
Q.2
What is the difference between linking and embedding ?
Q.3
How do we implement OLE in FoxPro ?
Q.4
Describe the method for creating and storing OLE objects ?
Q.5
How can we change OLE links ?
80
FOXPRO PROGRAMMING
CHAPTER - 7
MAKING APPLICATIONS PROJECT AND EXE FILES
To create a project
Select File > New and select the Project radio button. Select the new push button. Name the project later by selecting file > save As. Alternatively, use the command CREATE PROJECT To add files to the project Manager window
Select the Add push button to display the Add file dialog box, and select the file you want to add. by default, the first file you add is the main module of the application executed when the application starts. To Work on the files in a project
Highlight a file in the list in the project manager, and select the Edit push button (or simply double-click the file) to display the window you use to edit or lay out that file. To create an application
Select the Build push button of the project manager window to display the Build Option dialog box. Select the Build application radio button to create an APP file that incorporates all the files in the project. Select the Build Executable radio button to create a stand alone EXE file that incorporates all these files. This radio buttons accessible only if you have purchased the FoxPro distribution kit. To add related files automatically to the Project manager window
Select the Build push button to display the Build option dialog box, Select the Rebuild project dialog box, and FoxPro automatically adds every file called by the files already listed in the project manager to its list of files. When you make this selection, FoxPro also regenerates the code for these files.
81
