Need Query to list Multimedia by Individual

DougR · Post by **DougR** » 19 Nov 2012 19:51

Please help me resolve the development of a custom query. I want to create a query to produce a single list which shows the Multimedia Objects belonging to each and every Individual. I want to list the individuals alphabetically and the objects by their title. There are 147 individuals and 417 objects. I do not want thumbnails.

I have tried reading the help material and tried experimenting but cannot crack the problem.

Many thanks in anticipation

ID:6600

YouTube · Post by **Jane** » 19 Nov 2012 20:56

I suspect doing so with a query might be impossible, as media can be attached to a person or a fact or a source and could be attached to many people, you could do it easily enough with a plugin, the List All Related Media plug in does some of what you are looking for, but as you are only on V4 that won't help.

Post by **tatewise** » 19 Nov 2012 21:47

Multimedia Objects belonging to each and every Individual.

What exactly do you mean by this?
(1) Linked directly to the Individual Record and thus shown on the Individual Property Box ~ Multimedia tab?
(2) Linked directly to Facts and thus shown via the Individual Property Box ~ Facts tab ~ Media column?
(3) Linked indirectly to Individuals via a cited Source Record ~ Multimedia tab?
(4) Linked indirectly to Individuals via other routes such as Citations, Note Records, etc?

If you can narrow it down, it might make a Query feasible (no promises).

P.S.
I know it's not what you asked for, but a Multimedia Report with some customisation, will provide an inverted list for most of the above.
i.e. List by Multimedia Record, all Individuals, Families, Facts, Citations, and Sources linked to the Record.

Post by **tatewise** » 20 Nov 2012 11:03

This is what the Multimedia Links Report looks like:

The Bold headings list each Multimedia Record with its [Record Id].

Each one is followed by all its links:

Family of ... is a link to a Family Record.
Linked Individuals: is a numbered list of links to Individual Records.
Fact name Date & Place : ... is a link to a Fact in an Individual Record or Family Record.
Source: ... is a link to a Source Record.
Source title: ... is a link to a Citation (rare).

The Report Options used are:

Main tab ~ untick all except Record Ids.
Data Items tab ~ Edit and Hide every item.
Pictures tab ~ set both To the Right of the Text Size Custom Height 0 mm Width 0.2 mm and Picture Gaps all 0 mm.
Sources tab ~ untick Show Source Citations.
Format tab ~
- Font use Edit on Heading Level 1/2/3 as desired,
- Hdg Style use Edit on Hdg Level 1/2/3 so every Gap: is 0 pt and no Horizontal Line,
- Heading set Record to %OBJE% [=RecordId()] and People to Linked Individuals:.

Post by **tatewise** » 21 Nov 2012 21:08

Doug, I suspect you sent me the Instant Message below by mistake.
To reply to a Forums thread please use the Post Reply button on the right, instead of the icons with the orange men.

Hi and thanks for your offer of help.

Your option 1 is exactly my wish.

Prior to posting my message, I had downloaded an FHUG Custom Report titled 'Individual Multimedia Report' and ran it for all individuals. It produced what I wanted but extended to 47 pages of print. This is because it reported in the form of large thumbnails. If I could have accessed the underlying commands I would have tried to adapt it to report the multimedia objects by title instead of by large thumbnail pictures. Sadly I was stuck at this point.

Your help would be very much appreciated particularly if I could learn the underlying skills. I am retired and so have time to invest!

If I can offer any other clarification then please ask and I will try my best. Many individuals in my tree have no multimedia objects at all and no links to another individuals objects.A few individuals link to an object owned by another but have none of their own.

Hope this provides clarity.

Post by **tatewise** » 21 Nov 2012 21:44

The Multimedia Individual Report does indeed list what you want, but the layout with thumbnail images make it rather long.

Unfortunately, it cannot be customised to exactly to your requirements, but also needs a little post processing.

The method of customising Reports is to open the Report and use the Report > Report Options command, or click the Options button in the panel on the right.

Step 1 ~ Customise Report Options

In the Report Options window customise each tab as follows:

Contents tab - Main Section Items - select each Label in turn, click the Edit button, and Hide them all.
Format tab
- Font - select Heading Level 1, click Edit, set Size to 8, OK.
- Hdg Style - select Hdg Level 1, click Edit, set Gap to 0 pt in two places, OK.
- Heading - select Record, click Edit, and add [=RecordId()], OK.
Layout tab - in Header - Left-aligned text delete =Record().

Now click OK and the Report is a bit more compact, albeit still with thumbnails.

To preserve these Options separate from the original Report, use Save Report As > Custom Report Type and give it a name such as Multimedia Individual Short Report.
In future, use Report > Custom Reports > Multimedia Individual Short Report to display Reports with those Options.

Step 2 ~ Save Report as RTF Document

With the Multimedia Individual Short Report, in the Select Records window, >> Add All the Individual Records.
Click the Selected Individual Column to sort in Name order, or click the Record Id column for Record Id order, and click OK.

In the Report, use Save Report As > Word-Processor Document (RTF) and choose where to save the RTF file.
Answer the Do you wish to view the new file? with Yes.

Step 3 ~ Remove Image Thumbnails

I will assume you have Microsoft Word, which will have opened the RTF document, but please advise if not.
In Word, use the Replace option, and click the More >> button.
Click Format > Style > Subsection Data, OK.
That will be set as the Find what: Format: Style:, and now click the Replace All button.

That should remove all the thumbnails, and leave a relatively short document listing what you want.

A final refinement is to select everything with Ctrl-A and click the Clear Formatting button (it contains Aa and a rubber).

Save as a Word document, &/or Print as required.

jeemo · Post by **jeemo** » 22 Nov 2012 20:05

Where do I find the Multimedia Individual Report as distinct from the Multimedia Report?

Post by **tatewise** » 22 Nov 2012 21:12

As Doug intimated, it is among the FHUG Downloads ~ Report Types.

jeemo · Post by **jeemo** » 23 Nov 2012 05:45

Thanks Mike

Post by **tatewise** » 23 Nov 2012 15:18

For the special case (1) of Multimedia Records linked directly to Individual Records there is a workable Query solution.

Use View > Query Window, and in the Query Menu top right, select New Custom Query.
Enter a Query Name such as My Individual Multimedia, leave Query Type as Individual, and click Create.

On the General tab, enter a Description and a Title of your choice.
On the Columns tab, select Individual that is already in the right-hand Columns pane.
At the bottom alter Sort: from None to Ascending, and click Update on the right.
In the left-hand Fields pane, expand Miscellaneous and select Multimedia Object>>.
Click the central > button to add Multimedia Object to the Columns pane.
Select this Multimedia Object and at the bottom add 1 to its Heading: and click Update.
Now change 1 to 2 in both the Heading: and Expression: at bottom and click Add.
Now change 2 to 3 in both the Heading: and Expression: at bottom and click Add.
Repeat this ascending sequence of numbered entries a few times. They can be extended later if necessary.

Now click the red triangular Run Query button (near centre top) to display the Result Set.
This will list every Individual Record and each Multimedia Record linked to it.

To list any column in ascending order left-click on its Column Heading, or for descending order hold the Alt key while clicking the Column Heading.
Double-click any Individual Record to open its Property Box, and its Multimedia tab will show the linked Multimedia Record(s).
Double-click any Multimedia Record to open its Property Box, and its All tab will show the linked Individual Record(s).

The Query can be quickly run again by using View > Custom Queries > My Individual Multimedia or whatever name you gave it.

DougR · Post by **DougR** » 24 Nov 2012 16:43

Hi Mike and thankyou very much for your ongoing postings.

I have successfully followed the Custom Query solution and it works fine. The only drawback is the number of columns created. Two of my individuals have 14 objects each. So a very wide report!

I was successfully following the Custom Report option but have met an obstacle. I don't have M/s Word. I use the open software Libre Office. FH gives no reponse when I try to Save As RTF.

Kind Regards,
Doug.

Post by **tatewise** » 24 Nov 2012 18:35

I have discovered a variant of the Report Options that gives you exactly you want without thumbnails.
It is based on the same data references as used by the Query but in a better layout.
It works because you only want Multimedia Records linked directly to Individual Records, and is better than the thumbnails version because it gives Multimedia Record Titles and [Record Id] instead of Picture Captions.

On the Contents tab Add as many Main Section Items as needed such as:
Label: Multimedia 1
Expression: %INDI.OBJE[1]>% [=RecordId(%INDI.OBJE[1]>%)]

Show if not empty

and repeat with 2, 3, etc up to 14 instead if 1 in three places.

On the Pictures tab set Max Pics to 0.

On the Format tab Edit every Font to size 8 style Regular.

The reason FH does not offer to open the RTF file, is probably because none of your word-processor programs is associated with the RTF file type.
Try right-click on any RTF file and choose Open With > Choose Default Program then either select one if listed or Browse > C:Program Files to choose one.

DougR · Post by **DougR** » 25 Nov 2012 09:38

Hi Mike and thankyou.

Your last Report solution works brilliantly. My 147 individuals and their multimedia objects are now nicely presented in a list format which extends to only 12 pages.

The only extra thing I had to do was to untick the 'Start Records on New Page' box within the Page Layout tab.

Thanks for sticking with it. It has taught me some useful stuff.

Kind regards,
Doug.

Family Historian User Group