* Querying media linked to individuals

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile. If your question fits in one of these subject-specific sub-forums, please ask it there.
Post Reply
avatar
RobertW
Silver
Posts: 7
Joined: 27 Jun 2016 03:36
Family Historian: V6.2

Querying media linked to individuals

Post by RobertW »

The query "Media linked to a Record" from the User Group website which I have installed as a Custom Query works well but only works for one selected record. Can I modify it so that the output is all individuals with any media linked to them (or alternatively all media objects with any individual ID linked to them)? I suppose the more general question is how do you reference individual information from a Media type query or vice versa?
User avatar
DavidNewton
Superstar
Posts: 464
Joined: 25 Mar 2014 11:46
Family Historian: V7

Re: Querying media linked to individuals

Post by DavidNewton »

For the first result - all individuals with at least one media linked directly to them - create an individual INDI query excluding all individuals for which %INDI.OBJE[1]>% is null.

The second result, all media directly linked to individuals, can be obtained without a query. Click the Media dropdown in the toolbar and selct All Media. When the screen opens select All Individual Media in the top dropdown above the list. This lists only the media records directly linked to an individual it does not include media linked via a source for example.

David
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Querying media linked to individuals

Post by tatewise »

Robert, the answer to your question somewhat depends on exactly which Media links you are interested in.

The Media Linked to a Record Query finds Media linked to:
  1. The Media tab of chosen Individual record
  2. Any Fact of the chosen Individual record
  3. Any Citation within chosen Individual record
  4. The Media tab of any Family record linked to chosen Individual record
  5. Any Fact of any Family record linked to the chosen Individual record
  6. Any Citation within any Family record linked to chosen Individual record
A simple change could restrict it to just the first three cases above.

The Exclude if %INDI.OBJE[1]>% is null suggested by David only relates to Media tab case 1. above.
It does not deal with Fact and Citation linked Media, nor Family linked Media of any flavour.

The Media Window > Media List filters suggested by David work as follows:
The All Individual Media filter only handles Media tab case 1. above.
The All Family Media filter only handles Media tab case 4. above.
The All Event/Fact Media filter handles the Fact cases 2. and 5. above.
There is no filter for the Citation cases 3. and 6. above. That is an omission raised by posting Media Filters (15964).

One possible solution might involve the Where Used Record Links Plugin.

So, please provide a clearer idea of what Media links you want, and why you want them.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
RobertW
Silver
Posts: 7
Joined: 27 Jun 2016 03:36
Family Historian: V6.2

Re: Querying media linked to individuals

Post by RobertW »

Thanks for the replies.

Exclude if %INDI.OBJE[1]>% is null seems to give me only the object which has Preference 1 in the property media box for an individual, it does not give the full name of the object (ie the path and the extension are missing) and it only seems to give about half the results I was expecting. That is, I have 1671 media files linked on a many to many basis to individuals which should produce 2810 results from this query, but in fact only 1320 of them show up.

I only have media linked to individuals. They are all either jpg or pdf files. I simple want a listing of all these files together with all people they are linked to - so each person will appear as many times as they have files linked to them and each file will appear as many times as it has people linked to it. I then export this as a tab delimited text file and link it as a table to an Access database where I do most of my document manipulation. The purpose of this query therefore is to help to keep my Access database in sync with my FH project.
User avatar
Jane
Site Admin
Posts: 8508
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Re: Querying media linked to individuals

Post by Jane »

Queries will not be able to do this as they only return one line for the record type they are based on. so you will either have to add many columns incrementing the index as you go, up to the maximum number of images per person or write a plugin to create the result set you require.

To get the file name use %INDI.OBJE[1]>_FILE%

To see the files in columns would be like

%INDI.OBJE[1]>_FILE%
%INDI.OBJE[2]>_FILE%
%INDI.OBJE[3]>_FILE%
etc

Of course if you wanted to go the plugin root, you could with some work build a plugin to write directly to Access via COM as shown in this example for working with Word
plugins:code_snippets:word_load_data_direct_into|> Word Load Data Direct Into (code snippet)
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Querying media linked to individuals

Post by tatewise »

You cannot get the relationships you ask for with a Query because each record of the Query Type, whether Media or Individual, can only appear on one row, whereas you want them on multiple rows.

The Where Used Record Links Plugin provides that full Media record to Individual record cross-reference.
It does not include the Media Filename, but I could add that if essential. (See later.)

In the Records Window on the Media tab select all the records.
i.e. Select the first record and hold down Shft key whilst selecting last record.
Run the Where Used Record Links Plugin.
The Result Set lists every Media record used by every Individual record (and any other records).
Save the Result Set as a tab delimited file and delete the unneeded columns using a spreadsheet program.

One way to obtain the Media Filename per Media record is as follows.
In the Query Window create a New Custom Query of type Media.
In its Columns tab add a column for =RecordId() and for %OBJE._FILE% then run Query.
Save the Result Set to get the Media record to Filename relationship.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
RobertW
Silver
Posts: 7
Joined: 27 Jun 2016 03:36
Family Historian: V6.2

Re: Querying media linked to individuals

Post by RobertW »

Thanks Mike. That's brilliant. Works a treat. I'll have to look through the other plugins to see what else I can do.
Post Reply