Jump to content


Photo

Removing Reference No. Facts

Ref #

  • Please log in to reply
17 replies to this topic

#1 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 15 July 2017 - 12:17 PM

i there an easy way to remove all Reference No. facts from a database? When I imported my database from TMG all individuals picked up a Reference No. fact. I have no need for these and would like to remove all of them from my database, but doing it manually on each individual would be a big chore.

 

Thanks for any help.



#2 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6162 posts

Posted 15 July 2017 - 01:00 PM

Not directly, except by using SQLite outside RootsMagic.

 

Indirectly, you could edit the Ref No. fact type to exclude it from GEDCOM. Export everyone and everything and then import to a new database. There are, however, some potential losses if you have used elements that are not supported in RM's custom GEDCOM.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#3 zhangrau

zhangrau

    Advanced Member

  • Members
  • PipPipPip
  • 1482 posts

Posted 15 July 2017 - 02:08 PM

Another possibility:

If you check your Fact Type List, do you have TWO Reference Number fact types?

The original built-in is called "Reference No" with the abbreviation "Ref #"

If you now have two similar facts for reference numbers, and one is ONLY used for the unneeded TMG numbers, you can delete that Fact Type. I would check carefully to be sure that second Reference Number fact type is distinctly TMG-related, and, of course, do a backup before deleting the extra fact type, so you can review and verify that you didn't accidentally mess up.



#4 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 15 July 2017 - 02:42 PM

I only have one fact type and that is "Reference No". Exporting to GEDCOM is not my preferred way to go,because of potential loss of data.



#5 zhangrau

zhangrau

    Advanced Member

  • Members
  • PipPipPip
  • 1482 posts

Posted 15 July 2017 - 03:22 PM

I suspect you can't delete any of the built-in facts, so you are essentially limited to one-by-one modifications.



#6 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6162 posts

Posted 15 July 2017 - 03:50 PM

If you are willing to put the effort into learning a little about using a SQLite manager with your database, you will have a way to delete all of these facts in a second and become enabled to do other powerful things with your database that you cannot do within RM or would take forever.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#7 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3442 posts

Posted 15 July 2017 - 06:46 PM

Download and install an SQLite manager. Make a copy of your database and practice in the copy. Run this script.

DELETE
FROM EventTable
WHERE OwnerType = 0 AND EventType = 35;

The script works because EventType = 35 corresponds to the Reference Number fact. A more elaborate version of the script would join the EventTable with the FactTypeTable and delete based on the name of the fact, but the script above is simpler. The OwnerType = 0 shouldn't be necessary because it's testing to be sure the fact is an individual fact instead  a family fact, but we already know the Reference Number is an individual fact. My inner nerd is including the test on OwnerType out of an abundance of caution. It shouldn't be necessary in this case, but after running the DELETE for real I would run (in order) the maintenance tasks under File->Database Tools.

 

But before I ran the DELETE, I would run the following to be sure that my reference number fact is the correct reference number fact and that my individuals with the reference number fact are properly found by the query. The OwnerID returned by the query should match the Record Numbers associated with the individuals in your database who have the Reference Number fact. You can see the Record Number for each individual in the RM user interface.

SELECT E.*
FROM EventTable AS E
WHERE OwnerType = 0 AND EventType = 35;

Do be careful with your backups and your testing before running any updates against your live database.

 

Jerry

 

 



#8 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 652 posts

Posted 15 July 2017 - 06:52 PM

I do not use the "Reference Number" feature for any new work. I might have a few individuals with this fact from old work years ago.

 

What if you go to Lists>Fact Type  List> Then select > Reference Number

 

Then change any usage from "Yes" to "No".

 

Then any usage of Reference Number would still remain in your data, but it would not print.



#9 zhangrau

zhangrau

    Advanced Member

  • Members
  • PipPipPip
  • 1482 posts

Posted 15 July 2017 - 08:47 PM

I do not use the "Reference Number" feature for any new work. I might have a few individuals with this fact from old work years ago.

 

What if you go to Lists>Fact Type  List> Then select > Reference Number

 

Then change any usage from "Yes" to "No".

 

Then any usage of Reference Number would still remain in your data, but it would not print.

 

This is what I have done. I've been fortunate to have had a number of contributors share GEDCOMs with me (both directly and through web archives such as RoosWeb). I decided to leave all of the Ref #'s, AFN's and SSN's in my database, but have them enabled for GEDCOM export only. They do not appear in the Reports > Publisher books that I share with others.



#10 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 18 July 2017 - 04:13 PM

I downloaded SQLiteManger and made a copy of my database as suggested. I opened SQLite and opened my test database. I then typed the suggested preliminary script into the Script function and named it "Check It"and saved it.

 

When I run the scrip I get the following error message: An error occurred while executing script Check It [string" -- Check It.lua"]:4: '=' expected near 'E'

 

Any idea what the issues is?



#11 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3442 posts

Posted 18 July 2017 - 09:05 PM

if you mean this script

SELECT E.*
FROM EventTable AS E
WHERE OwnerType = 0 AND EventType = 35;

then I just now copied it from this forum and pasted it into SQLiteSpy and it worked just fine.

 

To isolate the problem, you might try it without the WHERE clause. That won't give the you desired results because it won't filter by the Reference Number fact, but at least it should get something working. Or you might make further simplification as follows:

SELECT * FROM EventTable;

If that fails also, then instead of executing a script, then try just double clicking the EventTable. Again, just double clicking the EventTable won't filter by the Reference Number fact but it should at least list the entire EventTable and confirm that the SQLite manager is working.

 

Jerry

 



#12 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 19 July 2017 - 03:23 PM

Ah!! I had downloaded and installed the limited version of SQLiteManager not SQLiteSPY.  Maybe that is the problem. Yes that is the code I was referring to. I will download SQLiteSPY and try that.

 

This code is strange to me. Not like anything I have coded before. Can you explain what each line is doing.

 

Thanks for all of your help Jerry.

 

Dan



#13 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 19 July 2017 - 03:58 PM

Jerry,

 

Using SQLiteSpy worked. I was able to run the preliminary code and the delete code on my test database and it removed the reference numbers.

 

Thanks for your help, it saved me a bunch of time.

 

Dan



#14 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6162 posts

Posted 19 July 2017 - 05:31 PM

If you want to explore SQLite further, click on the link below in my signature.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#15 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3442 posts

Posted 20 July 2017 - 06:34 AM

 

This code is strange to me. Not like anything I have coded before. Can you explain what each line is doing.

 

 

It's probably best to move this discussion to Tom's wiki as suggested in his message above. But let me provide a small bit of a general overview. SQL may look strange because it's a non-procedural programming language. Many report writing systems are non-procedural programming languages. The idea is that you describe what happens to one record in a file and then that process is applied to every record in the file. You don't have to open or close the file, and you don't have to loop through the file. You might even say that RM's custom report feature is a non-procedural programming language.

 

There really aren't that many different SQL statements, but each possible statement has many, many options.The SELECT statement is the only query statement. For updating a file, there is UPDATE (changes an existing record), INSERT (adds a new record), and DELETE (deletes an existing record). There are other statements for defining new databases and defining new tables. Files are called databases, and a database consists of one or more tables. A table has rows and columns, just like a spreadsheet. The rows of the table are the records. The columns of the table are the data fields. The SQL statements are designed to operate on these rows and columns.

 

There are lots of SQL tutorials and help files on the Internet for free. I use them all the time. But I would still recommend the purchase at least one SQL book (like a real book, printed on paper). I'm not sure of exact titles, but the "Dummies" series or the "Made Simple" series might not be bad places to start. Finally, I would point out that many programming languages have different dialects that are not quite compatible with each other, and SQL has more dialects than most other languages. At some point, you will have to be sure to be using the SQLite dialect of SQL. It's pretty standard but not completely standard.

 

Jerry



#16 Dan Yaklin

Dan Yaklin

    Member

  • Members
  • PipPip
  • 10 posts

Posted 20 July 2017 - 07:58 AM

Thanks Jerry for the information. i will look into the wiki.

 

Dan



#17 RobJ

RobJ

    Advanced Member

  • Members
  • PipPipPip
  • 56 posts

Posted 20 July 2017 - 08:29 AM

Jerry, if the "Like this" button was working, I would certainly have used it!  (it works fine on other IPS forums)



#18 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6162 posts

Posted 20 July 2017 - 09:11 AM

So would I. Jerry has a truly professional educator's knack of clearly explaining complex things in simple language.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.






Also tagged with one or more of these keywords: Ref #