1. If you are saving the id in a variable - you don't have to delete the related records first - you could delete the main record and then run the script. This can be pretty invisible - using custom menus, you can attach the delete record script to the Delete Record Menu item and delete related after the main record is deleted.
2. You could set up a cascade delete - where related records are automatically deleted when the main record is deleted - in the relationship graph.
3. From the server, you can set a script that does a search for join records that have valid related records in either the customer or transaction file. Then omit the valid records and the remaining records could be deleted by your nightly script.
I am sure there will be additional suggestions.
Use extreme caution with a lot of error checking when you are deleting records. If you are on the wrong layout or context, you could delete records you don't intend to delete. If the user doesnt have the proper privileges to go to a layout or delete records, you can again delete a found set you don't intend. Other bad things can happen with scripted deletes, so be sure you do as much testing and error checking as possible before setting this up. Many developers mark records for deletion, then move them to an archive, so nothing is actually deleted. This may be the wisest move...
Referring to #1 in your response, I tried to use custom menus to perform this but i found that when i attached the script to the delete record menu item, the button to delete record in the status toolbar became greyed out unavailable. I could still go to the records menu and select Delete Record, but the button itself was disabled.
I've heard about the dangers of cascading deletions but it may still be an option, have to look into.
I've heard about the dangers of cascading deletions
The only danger I know of is applying the deletion when you don't want to.
The button should not be greyed out - if it is, then delete record can't be performed using the shortcut keys Cmd or Ctrl-E. Something is wrong with how you set up your custom menu.
I agree with Michael - for deleting join tables - I usually prefer cascade delete. I find it simpler and less dangerous than scripting a delete. It is only a problem if you set it up the wrong direction - and that is easily tested on a backup to make sure it works properly before adding it to production. The only times I don't use cascade delete are: 1. If I don't want to delete the child records for some reason ( - they aren't really orphans, but have some other purpose - ), 2 if I don't have a relationship that is appropriate and I don't want to create one for this purpose only, 3. if I want to archive the data rather than delete it.
The button should not be greyed out - if it is, then delete record can't be performed using the shortcut keys Cmd or Ctrl-E.
correct. Perhaps the user does not have permission to delete records
I went ahead with the cascading deletions and everything appears to be functioning flawlessly. Thanks to all for your input.
Cascading deletions might be dangerous when you are using a self-join relationship in a multiple-hierarchy scenario.
Delete the first (root) record and all records are gone ...
Is analogous to UNIX's "rm -rf", being in the root directory and root user as well ... That happened two decades ago to my colleage who administered an IBM RS 6000 AIX workstation. After a few seconds he scratched his head when the command prompt did not appear ... then it was already too late. Fortunately, he had a backup.