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.