Project: Implement a user defined data table for storage of data with several related data fields. The data values may be dynamically retrieved from a host screen as via screen scraping, or come from another source such as a local data file, DDE conversation with another PC application, or from user input. We’ll look at a simple example which uses a dialog box for data entry, a structured table to store the data, and use it for some elementary computation. Thus, this project entails both writing to a table, and reading from it.

Algorithm: The structure of the data table(s), including data types, lengths and numbers of fields per record are defined before the table is used. If the initial data in the table is stored in a file, a command is used to load it from the file. In our case, data is entered into a dialog box, placed in the record buffer variable and then written to the table a record at a time. Reading data from the table or searching for values of fields involves reading records from the table into the record buffer variable one at a time and evaluating or storing the values until the correct value is found or the last record is read.

Relevant Commands and Functions:
TABLE DEFINE — Prepares a table for use
TABLE CLOSE — Closes specified table
TABLE SAVE — Exports data from a table to a file or the clipboard
TABLE SORT — Sorts records of table by specified fields
RECORD READ — Reads data from table into its record buffer
RECORD WRITE — Writes contents of record buffer to a table
Record variables, @R — Automatically created according to table definition
EOF( ) function — Indicates end of table has been reached
DIALOG commands and functions — as described in Dialog Boxes example.
SHOW — Display each script command as it is executed.

See Also:
TABLE LOAD — Imports data from a file or the clipboard
TABLE CLEAR — Clears existing data from table
TABLE COPY — Copies contents of one table to another
RECORD FORMAT — Defines virtual record template for session window fields
RECORD SCAN — Retrieves data from session window to a table, using virtual record template

A Brief Example

SHOW
TABLE DEFINE 1 FIELDS CHAR 40 CHAR 30 CHAR 20 INT 10 INT 10 INT 3
%RecordCount = 0
SET #Cancel FALSE
WHILE NOT #Cancel
BEGIN
DIALOG (,,150,145) "Add a record" 1
EDITTEXT (10,10,130,10) "Full Name: " LIMIT 40
EDITTEXT (10,25,130,10) "Company: " LIMIT 30
EDITTEXT (10,40,130,10) "Phone: " LIMIT 30
EDITTEXT (10,55,30,10) 30 "Balance due: " LIMIT 10
EDITTEXT (10,70,30,10) 30 "Balance overdue:" LIMIT 10
EDITTEXT (10,85,80,10) 15 "Months remaining: " LIMIT 3
BUTTON (10,110,,) "&Add" resume
BUTTON (60,110,,) "&Continue" SET #Cancel TRUE, LEAVE
MESSAGE (10,130,,) "Total Records: " | STR(%RecordCount)
DIALOG END
WAIT RESUME
@R1.1 = EDITTEXT(1,1)
@R1.2 = EDITTEXT(2,1)
@R1.3 = EDITTEXT(3,1)
@R1.4 = EDITTEXT(4,1)
@R1.5 = EDITTEXT(5,1)
@R1.6 = EDITTEXT(6,1)
RECORD WRITE 1
INCREMENT %RecordCount
DIALOG CANCEL 1
END

TABLE SORT 1 2 ASCEND
TABLE SAVE 1 TO "table.txt" AS TEXT
DIALOG "Company Balance" 2
EDITTEXT (,,140,) "Enter company name: "
BUTTON "&Report" RESUME
BUTTON "&Cancel" CANCEL
DIALOG END
WAIT RESUME
$SearchCompany = TRIM(EDITTEXT(1,2))
DIALOG CANCEL 2
%total = 0
RECORD READ 1
WHILE NOT EOF()
BEGIN
IF TRIM(@R1.2) = $SearchCompany
%total = %total + NUM(TRIM(@R1.4))
RECORD READ 1
END

DIALOG "Search Results" 3
MESSAGE "Outstanding balance for: " | $SearchCompany
MESSAGE STR(%total)
BUTTON "&OK" RESUME
DIALOG END
WAIT RESUME
DIALOG CANCEL 3
TABLE CLOSE 1

The first part of this example illustrates creation of a table and writing data to it a record at a time, using a dialog box for data input and a WHILE loop to repeat the process until the user chooses to continue. The TABLE DEFINE command specifies the table, the variable assignment commands put the appropriate strings in the fields of the record buffer, @R1.1 through @R1.6, and the RECORD WRITE command commits them to the table. The TABLE SORT command orders the records in the table appropriately, and the TABLE SAVE command saves them to a file.

The second half of this example illustrates a manual search, reading through the structured table a record at a time and summing the data until the last record is read. This is done by using the EOF( ) function and a loop which performs the RECORD READ command. The EOF( ) function becomes true following a Record Read command which fails because the end of the table has been reached, and is False otherwise. Thus, the RECORD READ command needs to occur immediately preceding the EOF( ) conditiona. Note in this example how the RECORD READ command is used once before the WHILE condition is evaluated and is the last command inside the command block before the conditional is evaluated for the next iteration.

This example also illustrates the use of Dialog boxes to gather user input and report results, but for a more focused discussion, refer to the example on Dialog Boxes.

Further Development:

• Tables are a versatile way of organizing data collected from a user, host application, or another application. This example illustrates data collected from user input, the Screen Scraping example illustrates data input from a session, and the DDE example is a variation of data transfer between PC applications.
• Getting data from a host based application to a tab delimited file involves a combination of screen scraping and storage to a structured data table. The TABLE SAVE … AS TEXT command saves a table to a tab-delimited file.

Additional Examples:
The Address Book example uses a dialog box to allow data input of contact info, stores this information to a structured table, and sorts it as needed.