Building an Array with Unique Values from a Data File
by Raju Patel

Building an array with unique values from a data file is a useful technique for displaying a pick list in a Request Box 20 or in a combo or list box in a dialog box. Building an array so that it contains only unique values from a data file requires the use of the Superbase SELECT query statement and the AFTER SELECT statement. The SELECT command with the DISTINCT option runs a query that only selects unique records from a data file. You use the AFTER SELECT command is used to do processing after a record is selected. In this case, you use the AFTER SELECT to stuff the array. The following program demonstrates the use of the SELECT and AFTER SELECT commands to build an array. The program builds an array called State$ from the Customer file which is in the STOCK directory of samples that come with Superbase 2.0. The WHERE command in the SELECT statement assures that no blank elements are placed in the State$ array. This is of significance if you use State$ in a list or combo box in a dialog box because in these cases, a blank element designates the end of the list and any elements after the blank element do not display in the list.

SUB main()

  	OPEN FILE "c:\sb4w\samples\stock\customer"
 	ERASE State$'				Erase any existing State$ array
 	GLOBAL State$( RECCOUNT ("") - 1)'	Dimension Array for number of 	
	REM						     Records in the data file
	CALL BuildArray()'				Call Procedure to build array
END SUB 

SUB BuildArray()
	i%% = 0'					Reset array counter 
 	REM Stuff the array with unique States using a query
 	AFTER SELECT 
 		State$(i%%) = State.customer'	Place State into element of array
    		i%% = i%% + 1'			Increment array counter
 	END SELECT 
 	SELECT DISTINCT State.customer
 	WHERE state.customer <> ""
 	TO "temp"
 	END SELECT 
 	DELETE "temp"'				Delete the temporary ASCII file
 END SUB

Note that the SELECT statement has a TO "temp", that means the query is output to a ASCII file.  The reason for this is to avoid screen flicker when running the query.  If you omit the TO, the query result goes to the screen and overwrites the current form.  This is not acceptable if a form is on the screen and you want to pop up a dialog box with a list box without the user knowing that a query was run to build the array in the list box.















Program:	Superbase	
Versions:	2.0
Date:	June 22, 1993	
D
Date: