Jump to content


Photo

Multimedia Listing Query


  • Please log in to reply
7 replies to this topic

#1 momakid

momakid

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 31 July 2018 - 03:44 AM

I created a multimedia report

I printed it to a pdf

I opened it with word

I did a copy all

I opened excel

I pasted the contents in excel

I did a find on Not Found

What query can I run in SQLite to get the multimedia report and one that will give me the not founds only?

Heavens know the query would be a lot faster than jumping through all the hoops I have been doing?



#2 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3438 posts

Posted 31 July 2018 - 05:06 AM

Firstly this filtering for unlinked media has been wished for many times, a lot of users, including myself, share your pain.

 

I am sure TomH has a query ready written over on his wiki, link below.

 

http://sqlitetoolsfo...wikispaces.com/


Customers should never be frustrated by things they cannot do.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 31 July 2018 - 07:38 AM

No, I don't have an easy query because SQLite does not have a test for the existence of a file.

However, your procedure is needlessly complicated. Save the Multimedia Report to a text file. Open Excel on a new blank sheet and use its Data Import feature.

You can also try renaming the text file with the .csv extension when you save the report. If .csv is associated with Excel or another spreadsheet program, RM will cause it to automatically open.

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.


#4 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 31 July 2018 - 07:41 AM

We do have many media scripts under the #media tag: https://sqlitetoolsf....com/tag/media/


Edited by TomH, 15 February 2019 - 03:38 PM.

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.


#5 momakid

momakid

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 31 July 2018 - 10:07 AM

OK, is it possible to get the code for that report so I can create a query? I promise I will share it when I get it done.



#6 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 31 July 2018 - 11:26 AM

Code for what report?
 
Saving the MultiMedia List to .txt file and then using Data > Import to bring it into Excel is not hard. You can filter on cell contains "*NOT FOUND*" and get a view like this:

Multimedia_List_Excel_Filtered.png


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 momakid

momakid

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts

Posted 31 July 2018 - 02:57 PM

I was wanting the program code/source code for the multimedia listing report.

I have 13,000 people in my database.  I would have to hand pick everyone of the ones that had Not Found in it which could be numerous and time consuming. Hence the reason for trying to put a query together.



#8 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6284 posts

Posted 31 July 2018 - 03:22 PM

The MultiMedia List report in RootsMagic is the RootsMagic's code, not mine. I am not employed by RM; I am a user, like you. The technique involving Excel is not hand-picking. It is a very easy setting of a column filter. 

 

One cannot write a SQLite script that does the equivalent of the MultiMedia List because SQLite has no file_exists test. You could write an extension for SQLite in C to do so. Or, you would use a programming language to do the file_exists test against a the filenames extracted by a query using the SQLite source code compiled in the application. The latter is undoubtedly what RootsMagic does.


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.