7 Replies Latest reply on Apr 21, 2017 4:40 AM by Vinny

    New Child Records Orphaned

    Vinny

      Hello,

       

      I have customers and contacts.  Contacts are children of customers by way of a FK relationship to the parent customer.

       

      I have the relationship set up so that if a customer is deleted, it also deletes any children (contacts).

       

      However, if a user creates a new contact that has not been committed (record state 1), and before he/she commits, another user deletes the parent, then we end up with an orphaned record.

       

      I can't commit the child record upon creation to lock in the FK relationship because I have field validation going on.

       

      I might be able to find a way to open the parent record and protect it from being deleted by another user while the user is creating a child record.

       

      I could also do something in the child OnRecordCommit to check if it still has a parent, and if not, inform the user that the child will be deleted.

       

      How are people handling this???

        • 1. Re: New Child Records Orphaned
          Jason Wood

          Sounds like the child record is being created by script (otherwise the parent record would be locked automatically when you start typing in the portal), so you can use the "Open Record/Request" script step to open (lock) the parent record before you create the child in a new window.

          • 2. Re: New Child Records Orphaned
            Jaymo

            I would agree with Jason. Sounds like something else is going on here other than just the straightforward adding of records through a portal.

            • 3. Re: New Child Records Orphaned
              Vinny

              Yes, I failed to mention that I am using a script to add the record.

               

              Contacts are modified / viewed / added in a separate layout.  Not a portal.

               

              I want to be able to add a contact in other contexts.  For example, add a contact to a customer while viewing a customer's order.

               

              If I am in another context (i.e. a customer order), which is not based on the parent, then I will have to find a tricky way to lock the parent record.

               

              Also, if the parent record lock is not available (already in use by another user), then I would have to block the ability to add a contact... which doesn't sound good.

              • 4. Re: New Child Records Orphaned
                philmodjunk

                There are two ways to open the parent record for editing, then add/modify related records without changing layouts in the current window and thus committing the parent record.

                 

                Open a different window and navigate to the appropriate layout for adding the new record.

                 

                Or add the new record from the context of the parent layout. This can be done via a portal that your script interacts with or using a method known as MagicKey.

                 

                You could also set up a "garbage collection" script that finds all child records created on or before a certain date that do not have a matching parent record and deletes them. This script could be run from a server schedule.

                • 5. Re: New Child Records Orphaned
                  planteg

                  Hi Vinny ,

                   

                  if you don't mind, I will come up with something which is not a direct answer to your question, but is somewhat related.

                   

                  I think records should be deleted under specific conditions only, through a script.

                   

                  Say for example that a client and a contact of this client are connected to a document like an invoice. Now say the contact and/or client record(s) is(are) deleted. What will happen if you display the invoice ? The connections between the invoice and client/contact will be lost and the invoice will then be 'broken', or orphan if you prefer.

                   

                  All this to say in my opinion, a record should never be deleted if it's used at least one time somewhere else. Then what should be done if the contact leaves the customer ? Mark it as inactive and have him disappear from the list of contacts. Same thing with the customer. You don't do business with him, mark it as inactive.

                   

                  In conclusion, the inactive records should not appear in value lists used to create new records, but still should be present in value lists for reporting purposes.

                   

                  This way, you never get orphan records.

                  • 6. Re: New Child Records Orphaned
                    Jason Wood

                    I want to be able to add a contact in other contexts. For example, add a contact to a customer while viewing a customer's order.

                     

                    If I am in another context (i.e. a customer order), which is not based on the parent, then I will have to find a tricky way to lock the parent record.

                    Whatever context you are in, if you can view a contact, then you are only 1 step away from a customer (as long as you have it connected on the graph), so you could simply use "Set Field" to lock that record.

                    Also, if the parent record lock is not available (already in use by another user), then I would have to block the ability to add a contact... which doesn't sound good.

                    Right, if you want users to be able to add a contact while another user is modifying the customer, then this approach won't work, and you will have to do a check on or after commit to confirm that the customer still exists. Since this is a scripted process, I would probably do the check after commit (not as a script trigger), since the OnCommit trigger fires before commit and depending on the script there is still some time (perhaps infinitesimally small) in which the record could be deleted after the trigger and before the commit.

                    • 7. Re: New Child Records Orphaned
                      Vinny

                      You make a good point.  If there is transactional data related to the parent (customer), I have it locked to prevent deletion.  However, I wasn't considering a contact as something that should lock deletion.

                       

                      I like Jason wood's idea of checking after to see if a parent still exists.  If the parent does not exist, then I will simply delete the contact.  The parent will have other types of children (notes, attachments, etc.), so I'm trying to find a universal method for handling the situation.

                       

                      I will need to give this a little more thought.  As you made evident, the only context that I will have this concern is from the parent. Any other context implies that the parent can't be deleted any way.

                       

                      BUT this now raises some concern for other related transactions.  For example, an invoice.  If an invoice is being created (open state 1) but not committed, then my deletion protection will fail...!

                       

                      And one might think we can simply lock the customer record while adding a related record (or child record), however, what to do when it is already locked presents another problem...!  And it doesn't seem like good design to lock a customer just to add an invoice.