Update in SQLite
Following on from my previous posts, I have now put in some data through SQLite - fabulous!
However, on checking the data and RM, I have noticed that inital SQL input brings the data up as shown in image 1, 1. Only when I go into the DNA section and click update image 2, that this then changes the data as shown in image 1, 2.
Is there a code I could run in SQL to save me going into every DNA match and clicking update? I'm about to input 3K DNA match data, and don't fancy doing a manual fix..
Thanks.
Uploaded files:
I don't use RM's DNA feature, but it doesn't sound like there is an easy way to do the DNA update calculation from SQLite. It looks like RM is doing calculations on the newly added data, and short of doing the same calculations yourself in SQLite and inserting the results into RM's database, I think that updating in RM is the only choice.
Since I don't use RM's DNA feature, I don't know the answer, but is there some sort of "update all" option for DNA data?
Quote from thejerrybryan on 2025-05-30, 10:38 am, but is there some sort of "update all" option for DNA data?
Not that I've seen.
I wonder if generating the DNA Kinship List would do the desired batch updating. Like Jerry, I am unfamiliar with the RM10 DNA features.
Quote from Tom Holden on 2025-05-30, 5:54 pmI wonder if generating the DNA Kinship List would do the desired batch updating. Like Jerry, I am unfamiliar with the RM10 DNA features.
I'm afraid not. Still showing.
Looking at the data structure of the DNA Table and the description on https://sqlitetoolsforrootsmagic.com/rm9-data-dictionary/, I suspect that the Update function is one record at a time because it runs the two-person Relationship Calculator function on the pair of people in the record and instead of presenting the results graphically, it stores them in the fields Relate1, Relate2, CommonAnc, CommonAncType. You will notice that these fields are similar to ones in PersonTable that store the results of Set Relationships for one person.
As for Set Relationships, I suspect that it would also be necessary to run the Update (Relationship Calculator) on a DNA record if the lineage for either member of the pair was changed in the database - that may also be an argument for a batch Update which you wish for. You might want to test my supposition to build your justification to RM Inc for such an enhancement.
Meanwhile, what you want in SQL is a Relationship Calculator-like function/procedure that can take the PersonIDs from a record in DNATable and return the values to populate the four fields identified above. I've a work-in-progress intended to replicate RM's Set Relationships without "spouse" labels overriding blood relationships but I've gotten hung up on "half" relationships. DNATable also wants "half" flagged. So, while it delivers Relate1 and Relate2, it does not deliver the MRCA and screws up when attempting to detect "half". Moreover, it's a one-to-many process, not one-to-one.
That said, I think a batch Update(Relationship Calc) procedure is do-able entirely within SQLite and certainly under a higher-level language such as python with calls to SQLite to interact with the database. I'm busy with many things so it's not something I can dig into at the moment. Maybe someone else here is already well down the path...