6 Replies Latest reply on Oct 13, 2009 7:09 PM by RickWhitelaw

    Finding "orphaned" child records

    RickWhitelaw

      Title

      Finding "orphaned" child records

      Post

      Hello,

       

      Let's say I have a set of 50 "parent" records, each of which has 20 "child" records. To find if there are or aren't orphaned child records is easy. If there are more than 1000 child records related to the group of parent records there are records that shouldn't be there. This situation can arise when a user, rather than deleting a "parent" record, chooses to alter it after "child" records have been created from the original parent. It gets more complicated when there are several other groups of "parent" records with various numbers of "child" records.

      My question: is there a way to find child records that don't have a parent record?

      To be clear, I've set up the relevant relationships to delete child records when a parent record is deleted. Also, I'm in the process of scripting, with "On Modify" triggers, all appropriate fields in the parent records to prevent alteration where it's an issue, so the problem will cease to exist. Also, I know how to do this with a looped script omitting records as it loops. Somehow I suspect I'm missing something obvious and simple.

       

      Thanks. 

        • 1. Re: Finding "orphaned" child records
          comment_1
            

          Any legitimate modification of the parent record should NOT break the relationship to its children. This is best achieved by basing the relationship on an auto-entered serial number of the parent, which the user cannot modify (and doesn't even need to be aware of). No script trigggers should be required.

           

           

          To find orphans, try:

           

          Go to Layout [ Child ]

          Enter Find Mode []

          Set Field [ Parent::Matchfield ; "*" ]

          Omit Record

          Perform Find []

          • 2. Re: Finding "orphaned" child records
            RickWhitelaw
              

            Thanks Comment,

             

            I've found an easy solution to preventing "orphaning" without scripting. My match field (for better or worse . . . I know there are strong opinions on this subject) is an auto-entered text field that concatenates one code with an engagement end date. For example, this week's run of The Sound of Music looks like "somus10/11/2009". The user has no control over this. I don't allow Command N to create a new parent record. A series of dialogs come up asking the key information. Of course if the user changes the end date after compiling a payroll this orphans the payroll records. So, (dohhh!) all I have to do is not allow entry into the engagement end field in browse mode. There's no reason not to do this. Like I said, I knew it was something simple. Thanks for the help with the Find script. 

             

            RW 

            • 3. Re: Finding "orphaned" child records
              comment_1
                

              RickWhitelaw wrote:

               

              I know there are strong opinions on this subject)


              I don't think these qualify as "opinions". The primary key of a table should be meaningless - your case being a good example why.

               

              See also:

              http://articles.techrepublic.com.com/5100-10878_11-1045050.html


              • 4. Re: Finding "orphaned" child records
                RickWhitelaw
                  

                Comment,

                 

                  It's ironic that in the first paragraph of the article to which you directed me is the sentence "Database developers have strong opinions on this facet of primary key design." 

                  Good article . . . thanks! BTW most of my solution does use serial keys and I mostly agree with you.

                 

                RW 

                • 5. Re: Finding "orphaned" child records
                  comment_1
                    

                  RickWhitelaw wrote:
                  It's ironic …

                  No, just more polite than I would have been …

                  • 6. Re: Finding "orphaned" child records
                    RickWhitelaw
                      

                    And yet another reason to use serial keys:

                     

                    Suppose we have three tables: A, B and C. "A" can create records in "B" and "B" can create records in "C". "A" has a working but non-serial ("natural") key to "B". "B" has a serial ("surrogate") key to "C". A layout is created based on another TO of A which needs to contain fields from a TO of C but NOT B. In this scenario a TO of B must appear in the relationship graph between the TOs of A and C for the relationship to work. If the match fields between tables A, B and C were all based on an auto generated serial originating in table A, a direct relationship between A and C would be all that was needed for the relationship to work.