Creating Facts
OK confirmed: auto-increment works. Here is the script I tested:
insert into
FactTypeTable (FactTypeID, OwnerType, Name, Abbrev, GedcomTag, UseValue, UseDate, UsePlace, Sentence, Flags, UTCModDate)
values (NULL, 0, "Test Fact", "TestFact", "EVEN", 1, 1, 1, "Need to define Sentence", -1, JULIANDAY('now', 'localtime') - 2415018.5)
The new Fact showed up and behaved correctly in the UI.
Now I have to learn how to access SQLlite from VBA or (preferably) Apps Script so I can run a series of inserts from my spreadsheet.
Thanks again!
If your data is in a spreadsheet, a simple approach might to create a virtual table using a csv file with no header. The values are read in as c0,c1,c2, etc which then can be used in your INSERT statement.
This is an example from a V8 script:
DROP TABLE IF EXISTS AncestryTableAddRows
;
CREATE VIRTUAL TABLE temp.AncestryTableAddRows USING csv(filename='C:\Users\Admin\Documents\RootsMagic\SQL-RM8\LAAddRows.csv');
--use new values to update the AncestryTable
INSERT INTO AncestryTable
SELECT
NULL AS LinkID
, 4 AS LinkType
, c0 AS rmID
, c1 AS anID
, 1 AS Modified
, '' AS anVersion
, 0.0 AS anDate
, 0 AS Status
, Null AS UTCModDate
FROM AncestryTableAddRows
;
create a virtual table using a csv file with no header
Oh that's brilliant! I'm still learning the nuances of SQLite, and creating a table using a CSV is a new one for me. Thx!
Part of my challenge, however, is what I'm seeking to do is a two-putt:
- Create new FactType
- Create new Roles for the new FactType
Step 2 clearly requires that I know the FactTypeID that RM generated on the INSERT. If I can do both steps in VBA or Apps Script then I could use the RETURNING clause on the INSERT in Step 1 to capture FactTypeID .
I can connect to a SQLite db via ODBC, but not JDBC, as far as I can discover. At least not in a way that works for Apps Script in Google Sheets. This may be the use case that gets me to dust off my VBA chops; I've been using Apps Script for the last several years.
UPDATE: You can also specify a value for FactTypeID on the INSERT. I looked at the DDL for FactTypeTable in SQLite Express and found that AUTOINCREMENT is not specified on the field. My assumption is that SQLite must implicitly generate an ID if one is not specified because the ID field is a Primary Key.
This also means I can start numbering my new Facts (and Roles I suspect) at whatever value I please. Which means I can do both of my Steps in one pass.
Quote from Mark Whidby on 2024-05-15, 7:47 pmThis also means I can start numbering my new Facts (and Roles I suspect) at whatever value I please.
Yes. I've done that. For example, I've created copies of all the uneditable, built-in Source Templates by Inserting copies of all their records from the SourceTemplateTable into said table with an offset of, say 20000 for TemplateID. Then it was a simple matter of adding the offset to every TemplateID in the SourceTable to convert all the Sources to the editable templates.
When it comes to what you guys are doing with PowerBI, Apps Scripts, VBA, Python et al, I can't help - you're way beyond my skillset. I chiseled a RM utility out of Visual C# years ago learning just the bare minimum along the way, most of which is long gone.
Quote from Mark Whidby on 2024-05-13, 7:59 pmDisclaimer: I have not explored the RM9 data dictionary yet....
Is it fairly straightforward to run a script to create Facts, including Roles and Template Sentences? I've got a bunch to do and the UI will take a long time.
I have not tried to use script to create facts.
I have used scripts to create citations including the blob field. These type of inserts needs to be done cautiously and one simple typo can cause disastrous results on your database. If you keep backups -- recovery should be relatively straightforward.
Something like adding in notes field based on the the factType and info (place, dates etc) could be done.
Kevin