Media+-+Set+Primary+Photo+for+Persons

This query responds to a problem with an import from Family Historian via FTM-flavoured GEDCOM reported by Stewartrb in the RootsMagic Forums thread [|GEDCOM import with media]. The images linked to the person in Family Historian were ultimately tagged as such in RootsMagic but none was marked as the Primary photo. Consequently, none showed on the main screen nor in reports other than Scrapbooks. This query sets the last image-type media file added to the Gallery among those that have been tagged to a person as that person's Primary photo, when none of the tags for that person have been so checked. It could be readily modified to be the last tag added to the person rather than the last imported mediafile or to be the first instead of the last.



code format="sql" -- MediaTags-SetPrimaryForPersons.sql /* 2013-09-23 ve3meo Sets the last mediatag as the Primary photo for a person with 1 or more tags for image-type media, none of which are so checked. UPDATE MediaLinkTable SET   IsPrimary = 1 WHERE LinkID IN       ( -- a list of the last LinkIDs for persons with image-type media tags, none of which marked as the Primary photo       SELECT  LinkID       FROM    ( SELECT  * FROM    ( SELECT    ML.LinkID        -- tag number                                , ML.OwnerID        -- RIN for person, given the constraint below                                 , ML.IsPrimary      -- 1 if tag box checked "Primary photo for this person" else 0                        FROM       MediaLinkTable ML -- media tags table                                   INNER JOIN MultiMediaTable MM                        USING      (MediaID)        -- mediafiles table                        WHERE      ML.OwnerType = 0 -- person                        AND        MM.MediaType = 1 -- image type                        ORDER BY   ML.OwnerID       -- sort by ownerid then                                 , ML.IsPrimary     -- isprimary, putting any tag for the person set as primary to the bottom of the list                        ) GROUP BY OwnerID -- only the last LinkID in the list for the person comes out )      WHERE   NOT IsPrimary -- only the last LinkID for a person with tags, none marked primary       ); code