Go to each occurrence of invoice attachments and each occurrence of Invoices and clear any delete options you may have set.
Then test to see if deleting one attachment record deletes all for a given invoice.
At a guess, you are also deleting the invoice as well as the invoice attachments, but that may not be the case. One of the following scenarios is probably what is happening due to an incorrectly set up "delete" option:
You have two delete options set. One deletes all invoice attachments when an invoice is deleted. This setting makes sense. But if you also mistakenly set the Invoice to be deleted when an attachment is deleted, then you get a "cascade delete where deleting the attachment deletes the invoice--which in turn deletes all other related attachments.
You have a "self join" by invoice number between two occurrences of Invoice attachments with delete specified for one of the two occurrences. In this case, deleting any attachment will leave the invoice record in place, but delete all attachments that have the same invoice number.
Wow, you are right!
It was a self join !
And that was incorrectly set.
I didn't realize that self join affected the parent of the self join in this way.
Thank you !
It's one of the things that can make a "delete" option dangerous. This option can be set in a completely different part of your relationship graph, but it'll kick in if you start deleting records anyway. Such options should be documented carefully and used with extreme care.
yes, i sure realize this now.
would you say it is better to just never check off this "delete" option?
It's safer right?
It's safer, but never leaving your home is generally safer that getting in a car and driving somewhere.
For me, the delete option is too useful not to use, but it's important to use with care and with documentation. You could, for example, put a text box in your relatinship graph where you list every instance where Delete is enabled as a quick reference when you later work with the relationship graph.
The idea about a text box to track in the relationship graph is great. Will do it!
And about the car. Ha!