Deleting Records from Master and Detail File
Summary: When deleting records in a master file, it is often necessary to delete matching records in a detail or transaction file. Superbase 2.0 provides a menu item, specifically, Data | Remove | Cascade, which does this for you, but the option is only available when a linked form is open. This technote demonstrates how to delete a record from a master file along with all matching records from a detail file using SBL. The procedure is straightforward, and you can attach it to a menu item or a button on a form to invoke it. 
Deleting records
The program opens two files: Master and Detail. It assumes that each file has an index on a field named Keyfield, and Keyfield is the common field between the two files. It is not necessary that the common field between the files have the same name, but it is required that they are both defined exactly alike. The index on the Keyfield in the Master file is assumed to be a unique index, and the index on the Keyfield in the Detail file should be a normal index. To use the program with your files, just replace the file names and the field names to match your database.
The main() procedure opens the two files, sets the correct index, and calls DeleteRecord(). The DeleteRecord() procedure allows the user to choose the record to be deleted by popping up a pick list of all Keyfield values in the Master file. Once the user selects a value, the program removes that record from the Master file, then removes all of the matching records from the Detail file.
	SUB main()
		REM Open the files
		OPEN FILE "Detail"
		INDEX Keyfield.Detail
		OPEN FILE "Master"
		INDEX Keyfield.Master
		CALL DeleteRecord()
	END SUB 

	SUB DeleteRecord()
		REM Request which master record to delete
		REQUEST "Select Record to Remove","Warning: All matching detail records will also 
		be removed ",20,choice%%,delkey$,40,Keyfield.Master
		(Previous two lines should be placed on one line in the Program Editor)
		IF choice%% = 0 THEN END SUB'	If User clicks Cancel, end procedure 
 
		REM Delete the records
		REM Select the chosen record in the Master file and delete
		SELECT KEY delkey$
		SELECT REMOVE 
		REM Remove associated records from Detail file
		FILE "Detail"
		REMOVE FROM FILE "Detail" WHERE Keyfield.Detail = delkey$

		REM View the Master file
		FILE "Master"
		SELECT CURRENT 
		VIEW 
	END SUB 
 
You may want to modify the program so that instead of popping up the pick list which lets the user choose a record to delete, the procedure deletes the current record in the Master file and the associated records in the Detail file. Just remember that once a record is deleted, it is gone for good.















Program:	Superbase
Versions:	2.0
Date:	April 23, 1993