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 ]
If [ IsEmpty ( Table 1::LinkField ) ]
Go to Record/Request/Page [ Next; Exit after last ]
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.
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.
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.
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!