7 Replies Latest reply on Jun 13, 2012 10:55 PM by mbraendle

    Deleting Records in Join Tables


      I have a database that links multiple tables together via join tables.


      An example of how we have this setup it:

      Transactions::_pk_id -> TransactionCompanies::_fk_transid

      TransactionCompanies::_fk_compid <- Companies::_pk_id


      The Transactions table as well as the Companies table have portals related to the join table to view the related information


      The problem we have run across is when a record is deleted in either of the main tables, the record int he join table remains, leaving a blank line in the portal.


      What i have done to aleviate the issue is setup a button to run a script before deleting the record that sets the ID of the record to a variable, searchs the join table for that id and deletes the related records in the join table. This takes care of the blank lines.


      However, the client does not like having the extra step to run prior to deleting the record.


      Since i have FMPA i tried setting up a custom menu that utilizes the current delete button and run the script but this does not work as the button is disabled after i made the change.


      So my last option that i can think of is to setup a script to run nightly that will search the join table for Transaction IDs and Companie IDs that do not exist in either the Transactions or Companies tables. Unfortunately this is where i am stumped, not sure how to search for something that does not exist.


      Any help or other suggestions would be greatly appreciated.

        • 1. Re: Deleting Records in Join Tables

          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...



          • 2. Re: Deleting Records in Join Tables

            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.

            • 3. Re: Deleting Records in Join Tables

              MattLeach wrote:


              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.

              • 4. Re: Deleting Records in Join Tables

                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.



                • 5. Re: Deleting Records in Join Tables

                  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



                  • 6. Re: Deleting Records in Join Tables

                    I went ahead with the cascading deletions and everything appears to be functioning flawlessly. Thanks to all for your input.

                    • 7. Re: Deleting Records in Join Tables

                      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.