Quote from Tom Holden on 2025-11-28, 11:43 am

Apart from some odd UX which I figured out, I found the Verify result very informative. I ran it on a database someone had sent to me to solve a specific problem It's a good size. Your utility pointed out almost 25000 records pointing to invalid IDs in other tables. I've been saying for years that RM does not clean up properly after deletions. It's better than it used to be.

Quote from Richard Otter on 2025-11-28, 1:22 pm

Would you be willing to include in your distribution a list of the SQL that's used?

Quote from keithcstone on 2025-11-28, 3:00 pm

Sure, I'm trying to determine the best way to do that. I was trying to put it in the help page. It's really not very fancy mostly:

SELECT key,fields FROM table WHERE ownertype = x and foriegnkey NOT IN (SELECT keyfield FROM othertable)

For the time being here's the SQL for Verify:

SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)

SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 3 And OwnerID NOT IN (SELECT SourceId FROM SourceTable)

If App.rmVersion > 6000 Then
SELECT LinkId,AddressId,OwnerId FROM AddressLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)

If App.rmVersion > 6000 Then
SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 4 And rmId NOT IN (SELECT CitationID FROM CitationTable)

SELECT LinkId,anId,rmId FROM AncestryTable WHERE LinkType = 11 And rmId NOT IN (SELECT MediaId FROM MultiMediaTable)

SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)

SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 2 And OwnerID NOT IN (SELECT EventID FROM EventTable)

If App.rmVersion > 6000 Then
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)
End If

SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 7 And OwnerID NOT IN (SELECT NameID FROM NameTable)

If App.rmVersion > 8000 Then
SELECT LinkId,CitationId,OwnerId FROM CitationLinkTable WHERE OwnerType = 19 And OwnerID NOT IN (SELECT FanID FROM FanTable)
End If
Else
' RM7.5
SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 4 And rmId NOT IN (SELECT CitationID FROM CitationTable)

SELECT LinkId,extId,rmId FROM LinkAncestryTable WHERE LinkType = 11 And rmId NOT IN (SELECT MediaId FROM MultiMediaTable)
End If

SELECT CitationId,SourceId FROM CitationTable WHERE SourceId NOT IN (SELECT SourceId FROM SourceTable)

If App.rmVersion > 9000 Then
SELECT RecId,ID1,ID2 FROM DNATable WHERE ID1 NOT IN (SELECT PersonID FROM PersonTable) OR ID2 NOT IN (SELECT PersonID FROM PersonTable)
End if

SELECT EventId,EventType FROM EventTable WHERE EventType NOT IN (SELECT FactTypeId FROM FactTypeTable)

SELECT EventId,OwnerId FROM EventTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)

SELECT EventId,OwnerId FROM EventTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)

SELECT EventId,FamilyId FROM EventTable WHERE FamilyId > 0 And FamilyId NOT IN (SELECT FamilyID FROM FamilyTable)

SELECT EventId,PlaceId FROM EventTable WHERE PlaceId > 0 And PlaceId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 0)

SELECT EventId,SiteId FROM EventTable WHERE SiteId > 0 And SiteId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 2)

If App.rmVersion > 6000 Then
If App.rmVersion > 8000 Then
SELECT FANId,ID1 FROM FANTable WHERE ID1 NOT IN (SELECT PersonID FROM PersonTable)

SELECT FANId,ID2 FROM FANTable WHERE ID2 NOT IN (SELECT PersonID FROM PersonTable)

SELECT FANId,FANTypeId FROM FANTable WHERE FANTypeId NOT IN (SELECT FANTypeId FROM FANTypeTable)

SELECT FANId,PlaceId FROM FANTable WHERE PlaceId > 0 And PlaceId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 0)

SELECT FANId,SiteId FROM FANTable WHERE SiteId > 0 And SiteId NOT IN (SELECT PlaceId FROM PlaceTable WHERE PlaceType = 2)

End if

SELECT LinkId,fsId,rmId FROM FamilySearchTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)

Else
' RM 7.5

SELECT LinkId,extId,rmId FROM LinkTable WHERE LinkType = 0 And rmId NOT IN (SELECT PersonID FROM PersonTable)

End if

SELECT FamilyId,FatherId FROM FamilyTable WHERE FatherId > 0 And FatherId NOT IN (SELECT PersonID FROM PersonTable)

SELECT FamilyId,MotherId FROM FamilyTable WHERE MotherId > 0 And MotherId NOT IN (SELECT PersonID FROM PersonTable)

SELECT FamilyId,ChildId FROM FamilyTable WHERE ChildId > 0 And ChildId NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,MediaId FROM MediaLinkTable WHERE MediaId NOT IN (SELECT MediaId FROM MultimediaTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 0 And OwnerID NOT IN (SELECT PersonID FROM PersonTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 1 And OwnerID NOT IN (SELECT FamilyID FROM FamilyTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 2 And OwnerID NOT IN (SELECT EventID FROM EventTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 3 AND OwnerId NOT IN (SELECT SourceId FROM SourceTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 4 AND OwnerId NOT IN (SELECT CitationId FROM CitationTable)

If App.rmVersion > 6000 Then
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 6 And OwnerID NOT IN (SELECT TaskID FROM TaskTable)
End If

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 7 And OwnerID NOT IN (SELECT NameID FROM NameTable)

SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 14 AND OwnerId NOT IN (SELECT PlaceId FROM PlaceTable)

If App.rmVersion > 8000 Then
SELECT LinkId,MediaId,OwnerId FROM MediaLinkTable WHERE OwnerType = 19 And OwnerID NOT IN (SELECT FanID FROM FanTable)
End if

SELECT NameId,Surname || ', ' || Given as Fullname, OwnerId FROM NameTable WHERE OwnerID NOT IN (SELECT PersonId FROM PersonTable)

SELECT PlaceId,Name,MasterId FROM PlaceTable WHERE MasterId > 0 AND MasterId NOT IN (SELECT PlaceId FROM PlaceTable)

SELECT PlaceId,Name,MasterId FROM PlaceTable WHERE MasterId = 2 AND MasterId NOT IN (SELECT PlaceId FROM PlaceTable)

 

Quote from kevync on 2025-12-05, 8:55 pm

Quote from Tom Holden on 2025-11-28, 11:43 am

Apart from some odd UX which I figured out, I found the Verify result very informative. I ran it on a database someone had sent to me to solve a specific problem It's a good size. Your utility pointed out almost 25000 records pointing to invalid IDs in other tables. I've been saying for years that RM does not clean up properly after deletions. It's better than it used to be.

yeah I was wondering about that -- I suspect this is what caused issue with media  sometimes for some people (like when they fix broken media).

I reported issue awhile back how it does not clear flag for relationships if they are later  (0,0)  -- while this probably wont cause issue within RM -- it just seems lazy not to clear stuff  and leave gremlins behind. Most users would never know what as they do not review from outside of program

Quote from keithcstone on 2025-12-06, 6:45 am

My feeling has been for some time that these "gremlins" are the source of many of the occational glitches I run across during my frequent use of RM.

As a lifelong technologist I know that no one covers all the edge cases in any desktop app, so what does the UI do when you hit a name without a person? A place detail without a place? They obviously have dealt with things like media links without media files, but medialinks without multimedia? On and On.

None of these individually are catastrophic, and likely only impact people with larger databases, but it would seem having DB Tools find and clean them would be a simple add to ease the support burden. IN addition, being able to easily identify and document these issues should help RM track down the sources of these gremlins.

I'm currently working on RESI -> CENS fixes and will be tackling some Notes cleanup. Some of my cousins that send me files to merge have the annoying habit of just putting things in Notes instead of as citations which makes narrative reports look like hell so I trying to pull text and place it as a Citation -> Source.

Quote from Charles Banks on 2025-12-06, 7:09 pm

Keith,

I installed your app and ran verify.  I'm not sure I understand what to do with this:

Ancestry 10148 for AncestryId 1042915367733:9000:204435873 points to invalid FamilyId 34

Is it looking at the AncestryTable?  If 10148 is LinkID and AncestryID the ANC ID then FamilyID 34 matches the correct RMID.  However, FamilyID 34 is not the correct family for RMID of 34.

Probably, I need to go back and review the Data Dictionary, but I still need to ask.

Thanks

Charles

Quote from keithcstone on 2025-12-07, 4:53 pm

Sorry, I need to fix some messages. The dangers of copy and paste. That's why people besides me need to test.

It's either Personid, CitationID, or MediaId all linked from the AncestryTable. It looks like it was CitationID instead of FamilyID.

I'll upload a version with clearer messages tomorrow.

Quote from Charles Banks on 2025-12-08, 1:10 am

No need to apologize.  It has helped me learn a lot.  Since the anID contains 9000 they must be type 4 records containing a missing citation.  I take, if the citation is missing then it would be safe to delete that record?

I'm more than happy to work with you for testing this.

 

Quote from keithcstone on 2025-12-08, 12:56 pm

I've uploaded a new version with more descriptive (and accurate) messages in Verify. I'm trying to determiine the proper fix for each instance. For example:

Places of type 0 with >0 masterid should simply have the MasterId set to 0, but places of type 2 (Place Detail) with an invalid Masterid are basically orphans and I don't know the correct fix. Change them to type 0 and clear the MasterId, delete them .. ?

Some things are easy, names with no person should be deleted, and one would assume various Links pointing to non-existant items too. I'm curious what happens when Citation and Media links with invalid foriegn keys are referenced.

What about events with legit owners, but invalid places or siteids?

Ideally we'd have a guide on what to do with each issue and have a pushbutton fix for it. (even more ideally, this capability would move to RM Tools Menu) Possibly if we can show RM the existence of these issues and potential corrections they would bump up the priority of adding this capability to RM. Wouldn't take long to do.