4 Replies Latest reply on Jan 22, 2009 7:57 PM by miw

    Listing orphaned data

    miw

      Title

      Listing orphaned data

      Post

      Is there a script to run which would identify bad "orphaned" data between relationship tables?  In other words, let's say I had a "product code" field link between two tables in which that code was set in a field for each table and linked.  If a product code in Table 1 was subsequently erased or changed, it might not then be reflected in the Table 2.  Is there a script I can run to find such orphaned data in Table 2 and show a listing thereof?

        • 1. Re: Listing orphaned data
          Orlando
            

          Hi MIW

           

          Welcome to the Forum,

           

          If you just want to run a script to show you the orphan records then this may do the trick, it will go through all the records omitting all records with a valid relationship to the parent record, leaving you with the orphans:

           

          Show All Records 
          Go to Record/Request/Page [ First ] 
          Loop 
          If [ IsEmpty ( Table 1::LinkField ) ] 
          Go to Record/Request/Page [ Next; Exit after last ] 
          Else 
          Omit Record 
          End If 
          End Loop  
          Show Custom Dialog [ Message ; "All Done"] 
           

          Run this from the table based on your child records and the result will be all orphaned records. 

           

          Let me know how this works out for you. 


          • 2. Re: Listing orphaned data
            jsalzer_1
              

            Just a friendly reminder that, after you finish your cleanup, you may want to spend some time on prevention.

             

            If the ProductCode field in Table 1 is currently directly editable, you may want to make it uneditable on the layout and force users to click on a "Change Product Code" button.  The button would first check to make sure that the new product code is unique, change the record in Table 1, then change all the children in Table 2.

             

            Users may gripe at first, but after you tell them how many orphans had been caused by allowing it to be directly editable, they'll appreciate that the script is doing the work of changing several records for them.

             

            :) 

            • 3. Re: Listing orphaned data
              davidhead
                

              One might also suggest that the Product Code should NOT be used as a primary key in a relationship.

              Ideally, the key used should be anonymous, meaningless and private.

              • 4. Re: Listing orphaned data
                miw
                   Good idea.  Another question to help keep the orphaned records to a minimum:  Since the record in the layout for Table 2 is a drop down, it is editable and will accept something other than that as required in the drop-down list from Table 1.  Can you also recommend a script to verify that the input in the drop down field in the Table 2 layout (whether from the drop down list or manually typed) is a valid entry and agrees to a Table 1 record (on the idea that a manually typed entry may be in error and does not have a similar record in Table 1)?  Thanks for your help!