* FH -> RootsMagic -> Ancestry (Work-in-Progress)

Importing from or exporting to another genealogy program. This is the place to ask.
avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 03 Jan 2021 09:45

it might be worth you having a look at the RM-Ancestry interface as well
Yes, that's on my list for this week
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 03 Jan 2021 12:15

Shosh, can I check something with you please? I infer from your comments yesterday that it is possible in principle for FH6 to write directly to a RM database via SQLite. Even if you decide that’s not the long term solution here, I’m tempted to have a play anyway for my own interest. Do you think it is something that a hobbyist dabbler could pick up, or best left to the pros? I’m comfortable with SQL and databases, writing SELECT FROM WHERE queries was the first thing I did in computing, on an ancient DEC PDP 11/70 in the early 1980s! :D
Mark Draper

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 03 Jan 2021 17:51

I think that you can absolutely learn how to do this.

I would start with reading from the database.

An interesting task would be to read in a list of individuals from RM and compare it to the list of individuals in your project and generate a report of

(a) individuals in your project who do not exist in RM (new) and

(b) individuals who exist in RM not in FH (deleted).

That would be a useful tool for our process.

Another interesting use would be to update UIDs in FH by retrieving them directly from RM.

You will probably need to acquire a program for accessing and manipulating sqllite dbs. I'm not next to my computer at the moment. I'll get back to you with a recommendation.

I know that I've got some examples of accessing RM from FH. I played around with it some years ago. I'll see what I can find. I'm not sure, but there may be a code snippet in the kb to help you get started.

Ask if you have any questions.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 03 Jan 2021 18:35

Great, thanks. I’ll be keeping FH6 on at least one system for the foreseeable future (depending on what’s in FH7.1 and when it’s released), so that’s my playtime testbed.
Mark Draper

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 04 Jan 2021 07:53

Playing around with directly accessing the RM database:

I use 'SQLiteExpert'. You can get it here. I have the Professional Edition but I believe the Personal Edition (free) should be sufficient for your needs. Among other things, you will use this to build and test your SQL.

SQLite Tools for RootsMagic is an excellent resource. Here you will find a description of the RM database structure.

I'm attaching a simple plugin which retrieves and displays from data from RM as an example. Warning! I don't do much in the way of error checking. It only runs under FH6.

Regards...
Attachments
RM Testing.fh_lua
(8.36 KiB) Downloaded 9 times
Shosh Kalson

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 04 Jan 2021 09:47

FH - RM Interface

Some thoughts...

The more I think about it, I believe that directly accessing the RM database should definitely be in our tool kit. At the moment, we can only play with this in FH6, but theoretically it is possible to get it working in FH7. I started looking at it this morning. Unless Calico Pie releases an update which uses the version of luasql which works in lua 5.3, I'll need to set it up manually. That's possible, but requires learning a bit of new technology and a clear head. So, that's not happening today.

Ideally, I would like to develop a process which is automated as much as possible, using plugins on the FH side and Auto Merge on the RM side.

Last night, as I was drowsing (half awake) in bed, I started thinking about a possible algorithm to handle updates/deletions of facts. It's very preliminary at the moment, but if past experience is any guide, I expect that it will suddenly come together (also, probably in the middle of the night).

To that end, I believe that we should be looking at simplifying the information that we pass to RM as much as possible. As I see it, we should be exporting only the information which Ancestry will use in looking for hints and generating searches.

I'm currently trying to understand which tags are taken into account when Ancestry generates the search criteria for Search on Ancestry. The current search window allows specifying:
  • Birth
  • Marriage
  • Death
  • Lived In
  • Arrival
  • Departure
  • Military
I believe that Lived in is generated from RESI tags, but I'm not sure.

In addition, Ancestry uses only years in the search criteria, so I don't see why we would want to export a full date. That should lessen the number of updates because we generally update events like BIRT and DEAT when we get more precise information, but the year often stays the same. For example, you initially have a Q date for BIRT which you got from the GRO index, which you later update when you look at other sources, like the Birth Certificate or Death Certificate or...

I'm also considering whether including address fields (building, street) is necessary.

OK, so that's enough for now. Thoughts?
Shosh Kalson

User avatar
ColeValleyGirl
Megastar
Posts: 2206
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by ColeValleyGirl » 04 Jan 2021 10:08

Just a caution that you're probably aware of: Ancestry only (or only used to) makes trees searchable if they're linked to an Ancestry record...

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 04 Jan 2021 10:14

Yes, I know.

I've worked with TreeShare in the past. I'm focused on the FH-RM interface at the moment, but will have to look at the RM-Ancestry interface soon, as decisions make regarding the FH-RM could (and probably will) impact the RM-Ancestry interface.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 04 Jan 2021 10:19

Thanks very much! I've completed assignment 1 ;) by installing both SQLite Expert Personal and SQLiteSpy and opened a copy database in both to view the table structure. That's all I'll have time to do today, but more play beckons later in the week!

First impression is that any methods built around this would have to be full of caveats, as none of these seem to be supported by the RM organisation, and are basically enthusiasts working out how RM is structured and devising their own tools. The risk of something changing and breaking the Ancestry sync must be fairly high. It's only worth jumping through all these hoops if you want to preserve the Ancestry hints history, otherwise you would just upload a new copy database each time, and the consequences of accidentally resetting that would be fairly disastrous.
Mark Draper

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 04 Jan 2021 10:22

Congratulations!

As you say, we need to be very careful using this approach. I would say that we should make as few direct changes to the RM database as possible.
Last edited by shoshk on 05 Jan 2021 08:27, edited 1 time in total.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 04 Jan 2021 12:05

Couple of other thoughts around which fields are exported from FH -

Users may want a more detailed tree uploaded for reasons other than hint generation. It’s always possible to have two uploads, a simple synced one for hints and a more complete one with hints disabled that is simply copy updated, but that’s a bit messy.

Perhaps more importantly, if somebody is already using the existing manual options to sync, any new tool would have to export the same fields to keep everything in sync. I’m assuming we won’t be able to hack the Ancestry API to build our own automated updater (they almost certainly license access, otherwise somebody would have done it by now). I added immigration and emigration fields, and that was ok as relatively few individuals were impacted. However, when I stopped exporting my custom family ID due to apparent incompatibilities between the 3 packages, it was a pain in the thing you sit on to update everybody affected, as it had to be removed from each family in the Ancestry tree one at a time.
Mark Draper

User avatar
tatewise
Megastar
Posts: 20123
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by tatewise » 04 Jan 2021 12:27

If a user wants to upload a 'more complete one with hints disabled' then use the Export Gedcom File plugin in (ANC) Ancestry export mode and upload directly to Ancestry by-passing RM altogether.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 04 Jan 2021 12:31

While I think it's admirable to try to build a tool which accommodates the way other people have been working, I'm not sure it's within the scope of our project.

The amount of work to make our solution compatible with all of the ways that people may have been working would be enormous.

We need to at least start from a point which will allow us to build a solution with a reasonable amount of effort (we both have plenty of other things to do) and within a reasonable amount of time (I'd like to think that we have something to work with in the near future).

After developing an initial solution, we could consider expanding its capabilities to cover other scenarios.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 05 Jan 2021 14:47

Shosh,

(DATE DESCRIPTION CORRECTED ON EDIT)

This is looking quite promising.

FH doesn't seem to support the _UID tag in the same way that RM does as the Individual ID, so if we were to use that, I assume we would have to create a custom attribute? I already use the standard Individual REFN tag as both required and unique as part of my custom naming system, and it is already exported to RM, so it is ideal to use as the primary key for testing.

I have managed to modify your template plugin to retrieve both the REFN and Date Updated from the RM tables. I then use the REFN value to fetch the individual from FH and compare details. My modified output is shown below. It would be relatively straight forward to tidy it up to harmonise the date formatting and group records by

1. Present in FH but not in RM (new additions)
2. Present in RM but not in FH (recent deletions)
3. Later update in FH than in RM (modified)

My two databases are both from old copies, so are not necessarily synced together, so don't be surprised at the number of mismatched dates or missing details, it's more the general approach that I am scouting here.
Capture.PNG
Capture.PNG (26.43 KiB) Viewed 857 times
RM date is days since 1 Jan 1900, and Lua/FH is seconds since 00:00 on 1 Jan 1970, so needs simple arithmetic to bring to a common format. A couple of potential wrinkles -

1. If family details are updated in FH, the individual Date Updated does not change, so we would need to record the latest revision date of the Individual and all families they are a spouse in. There is no Date Updated stamp in the RM families table, so presumably it marks any update in the individuals concerned.

2. RM only stores date, not time, so we would need to think about how we handle any changes in FH made after the RM sync, but on the same day. Depending on whether we test for > or >=, we will either miss updates or get false positives.

It therefore seems a very viable approach to interrogate the RM database directly from FH to identify which records have been changed.

I haven't tried writing to the database via either SQLite or FH, so don't know whether it is best to update fields individually if different, or just a blanket "make this record in RM the same as in FH". Either way, it looks a fair bit of coding is likely to be required, even for a smaller subset of events.

Does RM get upset if records are added from outside its own coding? Presumably the RM _UID will be missing, but I don't know if it just tidies up automatically by adding one silently, or has a sulk and throws an error.

I'll tidy up my plugin and see if I can produce a definitive list of changes on up to date database copies, then the next stage is seeing if I can modify RM records from within FH...
Last edited by Mark1834 on 06 Jan 2021 10:47, edited 1 time in total.
Mark Draper

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 05 Jan 2021 15:28

Wow! This is a lot to digest. I'll probably get to that tomorrow (and hopefully not in the middle of the night ;) )

A few preliminary thoughts.

In my opinion, at least in the first version of the process, we should let RM do as much as possible. Directly writing to the database is fraught with risks. We can never be sure what RM is doing behind the scenes.

I believe that new individuals and families can be handled by Export-Import-Auto merge. That lets RM generate the _UID.

Deleted individuals (and families?) should be deleted from RM manually. Getting all the linkages right is not a trivial task.

I've been mulling over a procedure for handling updated/deleted facts. This is the one case where I think it may be possible to make changes in the RM database via plugin. I was thinking that perhaps updated/deleted facts could be deleted from the Event Table. Then Export-Import-Auto merge could be used to transfer the updated/deleted facts. As far as I know, the only facts which need special handling are BIRT year and DEAT year, which are also stored in the Name Table table. Obviously, we have to check this out very carefully. We also need to see how this affects TreeShare.

I have started creating some Google documents, so that our work will be collected in one place. Anybody with the link can access them (read-only). If you send me an email, I can give you editor privileges.

Currently, the docs are:

FH-RM-Ancestry -- Procedures

FH-RM-Ancestry -- Cases

I think that implementation of the process will only be possible after Calico Pie releases a version of FH7 with LUASQL added back in. It's theoretically possible to install it ourselves, but I'd rather spend my time on other things.

I did have one crazy thought. Since we both still have installations of FH6, we could include it in the interface and implement direct access to the RM database from there. It would mean a few extra steps, but is certainly do-able.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 07 Jan 2021 13:15

I have modified the plugin to generate the following subsets:
1. In FH but not in RM
2. In RM but not in FH
3. In both, but FH revision is later than RM revision

I had hoped this would give us a definitive list of individuals to review for syncing, but group 3 is everybody where a change has been made in FH since the record was synced, even if that is in a field that is not exported to RM and Ancestry. It wouldn't be difficult to tweak it again so it excludes individuals modified prior to the last RM sync. However, that takes away the extra safeguard of checking all records, so any RM updates missed in a previous pass would not be caught. The only new feature it brings to the party compared with just listing all FH individuals modified since the last sync is the ability to list deleted FH entries, but this is limited to FH6 only. We could probably do something similar in FH7 without having to rely on SQL tools by saving an copy of the export, so each new one is compared with the previous one to find differences. It's not nearly as elegant as querying RM directly, but it will be completely under our control.

I must admit, I'm struggling a little to see how we progress this. It seems highly likely that any tool we come up with will not be compatible with whatever process people use now for the FH-RM-Ancestry linking. Adopting a new one would mean starting again and losing your hint history (which is the only reason for the process in the first place). That may be a price users are prepared to pay if it made subsequent updates very much quicker, but I think there are at several major obstacles in our way to achieve this.

1. SQL tools are not available for FH7, and there is no guarantee they will become so.
2. FH does not store the RM-specific _UID, so would need additional plugin coding to maintain it. Keeping it up to date for new records added after the initial sync is potentially quite complex.
3. While the internal RM record merge is completely reliable if the _UID is available for both records, it is purely additive, not an update process.
Dealing with modifications and deletions will be either an awkward manual process, or involve direct writing to the RM database, which is highly risky.
4. The external RM comparison (between databases) copes with additions and deletions, but is not fully reliable and cannot be automated. It only works with a crib-sheet of changed records to review manually.
5. The RM-Ancestry sync is completely outside our control, and only works manually - there is no automatic "make Ancestry look like RM" option.

How do we get around these, and come up with something that is genuinely time saving and easier to use than current methods?
Mark Draper

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 07 Jan 2021 19:20

I've updated the Procedure document to describe the existing manual process. Comments invited from anybody that does it differently! This is our benchmark that we are assessing any new process against for ease of use or reliability.
Mark Draper

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 11 Jan 2021 18:00

I needed some more practice in writing FH plugins, so I have taken the opportunity to rewrite my existing FH export as a standard plugin, rather than an external Python script. This makes it much more accessible to other users, as it can be used "as is" without having to install any other software or modify program code. It works equally on FH6 and FH7, without changing the format of the output in any way.

As a reminder, this generates a GEDCOM file of basic place and date location for a subset of standard key events: birth, baptism, marriage, residence, census (converted to residence), death, burial, cremation, probate, occupation, retirement, immigration, emigration, and naturalisation, along with any custom individual ID. There are deliberately no configuration options, as the main reason to use this dedicated script rather than the much more flexible GEDCOM Export plugin is to ensure that each export is configured identically. This is essential to ensure correct synchronisation with RootsMagic and Ancestry. It is relatively easy to modify the code to tweak the output with guidance, even for novice users.

All reference to living people, and anybody else marked as "Private" is excluded from the export. I have compared the output of the plugin with that from the Python script, and they are functionally identical. The only difference is that individuals and events are in different orders, as FH does not maintain the same order in its master database file (which the Python script manipulates) as the program records (which the plugin exports directly). You could say that it has all the right output lines, just not necessarily in the original order... :D.

I have also incorporated my existing plugin that simply generates an ordered list of individuals updated since the last RM import by comparing update date and time with that of the import log file. This is both more reliable and easier to use than relying on FH's display of "last updated", as changes in just a family event do not update the revision date of the individual. The plugin checks for both.

This will become redundant if we eventually generate a better process that eases the many manual steps required at the moment, but it is worth sharing if anybody wants to give it a try.
RootsMagic GEDCOM Export for Ancestry.fh_lua
(11 KiB) Downloaded 9 times
Mark Draper

User avatar
tatewise
Megastar
Posts: 20123
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by tatewise » 11 Jan 2021 18:27

Mark, I'm interested in your comment that: "FH does not maintain the same order in its master database file as the program records".

The GEDCOM file ALWAYS keeps records in Record Id order, and that is the order Plugin loops read those records.
The Facts in the GEDCOM file are the same order as on the All tab which is the order obtained by using the various sort commands provide by FH. The Facts tab may display a different order. Also, Marriage is a special case.
In other words, if you view the Records Window in Record Id order, that is the GEDCOM order.

If Python is reading the GEDCOM file then everything should be in the same order as read by Plugin loops.
The Facts may be in a different order if the Plugin is picking Facts by specific data references rather than looping through all Facts and picking the ones it needs en route, which would ensure chronological order.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
shoshk
Famous
Posts: 225
Joined: 13 May 2015 16:28
Family Historian: V7
Location: Mitzpe Jericho, Israel

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by shoshk » 11 Jan 2021 18:57

Hi Mark,

I believe that we have gone about as far as we can at this point.

I don't plan on implementing the interface until I can access the RM database directly in order to automate the comparison process. That means LUASQL.

Perhaps Calico Pie will get it working in a future update.

Alternatively, perhaps one week I'll decide that the time has come to get it working. I don't see it as an impossible problem; I just don't want to invest the time right now. I have a lot of other things on my plate.

I enjoy working together with you and look forward to future collaboration.
Shosh Kalson

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 11 Jan 2021 20:11

Mike, mostly that, and also how the retrievals treat undated facts and when the CENS to RESI conversion is done. Order in the GEDCOM file is largely irrelevant, so I just set up whichever method is easier to code in the two languages.

Shosh, agree. I don’t see LuaSQL coming to FH7 anytime soon, so I think it is worth summarising the current process for the KB. I’ll do that later.
Mark Draper

avatar
David Potter
Megastar
Posts: 790
Joined: 22 Jun 2016 15:54
Family Historian: V7
Location: United Kingdom

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by David Potter » 11 Jan 2021 21:02

Thank you Guys, this solution looks very promising. I have downloaded the Plug-In Mark created and have just now uploaded a very clean and crisp version of my FH7 tree via RM7 into Ancestry.

I now need to read and understand the process to keep records in sync between FH and RM.

Just wanted to say thank you.

David

avatar
Mark1834
Superstar
Posts: 406
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by Mark1834 » 11 Jan 2021 21:32

David, glad it’s useful. The early part of the Procedures document in this thread will form the basis of what goes into the KB. I think the best thing to do will be to make a limited number of changes to existing entries in your FH database, and see if you can work through the process. If that goes ok, add a couple of new entries and re-run.
Mark Draper

avatar
David Potter
Megastar
Posts: 790
Joined: 22 Jun 2016 15:54
Family Historian: V7
Location: United Kingdom

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by David Potter » 11 Jan 2021 21:35

Thank you Mark. I'll give it a go tomorrow.

Thanks for the advice on how to start.

avatar
David Potter
Megastar
Posts: 790
Joined: 22 Jun 2016 15:54
Family Historian: V7
Location: United Kingdom

Re: FH -> RootsMagic -> Ancestry (Work-in-Progress)

Post by David Potter » 12 Jan 2021 21:15

Hi Again, I tried working through the procedure this evening, I created the RM Master file using the Plug-In you provided. Then I made one Fact change to one individual ran a new file and created this as the RM Update file, I ran the Compare from the Master against the Update but the ouput was as you have discovered quite hit and miss considering this is the same Base file I'm testing with.

I then re-read the posts here and the procedure and think I maybe missing something, a Plug-In maybe to generate the unique identifier to link FH with RM. Have I understood that correctly, I should also mention I'm using FH7 so not sure if that presents any difficulties with the solution you have built?

Post Reply