Showing the best photo of a grandchild table

[Ziphius]

I have a table of individuals, and each individual gets a catalog number (Individuals:CatalogID). Each individual has multiple images (parent-child relationship), and I flag the best photo for that individual (Images:PrimaryPhoto = "yes"). When a new individual is added to the table, I match its primary photo to the rest of the table to look for matches. If a match is found, it gets the same CatalogID and the best primary photo gets assigned the catalog photo (Images:CatalogPhoto = "yes"). I would like to create a grandfather table that shows only each unique catalog number and only the catalog photo. I know how to show just the primary photo in an individuals table (parent child relationship), but I'm not sure how to create the relationship from a grandfather table (Catalog) to a grandchild table (Images).

[bcooney]

When a new individual is added to the table, I match its primary photo to the rest of the table to look for matches.

That's where I lost you. To me, you have a simple one-to-many relationship here, Individuals to Images. I would store the "PrimaryPhoto" ID in Individuals (_kF_PrimImageID), not use a field in Images like you describe (Primary = Yes).

But, the process of adding a new individual is unclear to me. What are you matching? Wouldn't you find in individuals to see if they exist before you would decide that they are a new individual?

[Ziphius]

I've attached a file that may help to explain what I am trying to do. The Catalog table is where I am having difficulty. I want a layout that shows one record per catalogID (each unique individual) and only the photo that is flagged Catalog = Yes from the Images table. The IndividualSighting table can have the same individual multiple times if he was sighted on more than one survey. After I add new individuals from a new survey to the IndividualSightings table, I would like to match it against the best catalog image (Catalog Layout) so I only have to compare it once to each unique individual in the catalog. If it matches, it gets the same catalogID as the match in the IndividualSighting table and I update the best catalog photo in the Images table if it is better than the one already flagged. I hope this makes more sense.

Catalog.fp7.zip

[bcooney]

I think my confusion comes from your naming convention.

You have:

Catalog (which are People?)

Surveys

IndividualSigthings [sic] which is a join between Catalog and Survey

Images

When a Person (Catalog) is somehow entered as an IndividualSighting, they are several images that are related to that sighting. Of those images, you wish to mark one as "the best" and one as "Catalog". What is the difference? The "best" for that survey, but not the best for that individual?

I might be still confused, but I'd store the ImageID for the best image from the Individual Sighting in the IndividualSighting record. That is, each indsite record has an imageID.

I'd then lookup the CatalogID in the Image record. I'd build a relationship from Catalog to Images by CatalogID, and show a portal on the Catalog form. A button on the portal row would set the ID of the "best" image for that Catalog record in a Catalog:ImageID. A calc flag in Images would look up to Catalog and equal 1 if its ID is the one stored in Catalog.

Also, another approach is a portal of IndivSighting records on the Catalog form. Since you have the ImageID that is the best from that IndSighting, allow the user to select from the IndivSighting records which ImageID becomes the "best" for that Catalog record.

[comment]

If I can make a guess here, it seems that "PrimaryPhoto" is the best image of an individual sighting, and "CatalogPhoto" is the overall best image of an individual.

I agree completely that instead of flagging the images, the ImageID of "PrimaryPhoto" should be stored in a field of IndividualSightings - and similarly, the ImageID of "CatalogPhoto" would be stored in the Catalog (Individuals?) table.

I'd then lookup the CatalogID in the Image record

I don't see why that would be necessary.

[bcooney]

Well, I got a bit lost with this one. I was thinking that to make a relationship from Catalog to Images it would be helpful to have the CatalogID in Images.

But, then it occurred to me that he seems to want to flag the best of the image sightings related images, and from these the best for the catalog. So, a direct relationship to Images isn't necessary (yet).

[Ziphius]

Quote "If I can make a guess here, it seems that "PrimaryPhoto" is the best image of an individual sighting, and "CatalogPhoto" is the overall best image of an individual."

Correct. I should clarify that I am working with manta rays which have unique spot patterns on their belly that identify them, but requires matching against the catalog to determine if they are new or previously sighted. Some images may show particular scars or injuries so I flag the best ID image to be used for comparison against the catalog. The other images are interesting for later on if I want to look at mating scars or other areas of interest.

Could either of you explain how I would store the imageID for the best individualsighting image, and the imageID for the best overall individual image in the IndividualSighting table? And then are you saying I could show that image through a relationship between the Catalog table and the IndividualSighting table?

Edited September 6, 2010 by Guest

[comment]

Something like this, perhaps?

Paparazzi.zip

[bcooney]

Paparazzi, lol, and mating scars all in the same thread, oh my.

[comment]

Is this not a paparazzi solution?

Individuals = Celebrities

Sightings = Events

IndividualSightings = Attendance

Images = Images

[Ziphius]

Thanks for that. I'm trying to mimic your database but I don't quite understand the "set field" button and how that works. If I want any information on the filemaker button scrips or calculation functions, can you recommend a good resource for looking these up?

What I tried to do since I already have the images flagged as best sightingimage and best catalogimage, is I created a CatalogID field in the Individuals table as a calculation (CatalogID= If ( Images::CatalogVentral? = "Yes"; Images::IMAGEID ; "" )) and a BestImageID field in the IndSightings table as a calculation (If (Images::PrimaryPhoto? = "Yes"; Images::IMAGEID ; "" ). It updated the CatalogID and BestImageID for most of them but not all for some reason. I was trying to set it up so that for each image in the Images table, I can flag it as the catalog image (CatalogVentral? = Yes) and/or the best sighting image (PrimaryPhoto? = Yes) and it would automatically add the IMAGEID to Individuals:CatalogID, and to IndSighting:BestImageID via the calculation. Maybe if I understood what the "set field" is doing and how that works I can go that route. Thanks for your help.

Edited September 7, 2010 by Guest

[comment]

Set Field[] Is a script step - it sets a field to a value. You can read more about it in Help > Reference > Script steps reference. While you are there, you could familiarize yourself with the functions, too.

If ( Images::CatalogVentral? = "Yes"; Images::IMAGEID ; "" )

That won't work, because the expression Images::CatalogVentral? refers to the FIRST related record in the Images table.

To transfer your flags to the parent tables, you could run a script like:

Go to Layout [ Images ]

Enter Find Mode []

Set Field [ (Images::PrimaryPhoto? ; "Yes" ]

Perform Find []

Go to Record [ First ]

Loop

Set Field [ IndSightings::BestImageID ; Images::ImageID ]

Go to Record [ Next ; Exit after last ]

End Loop

Make sure you have a backup... :P

[Ziphius]

Thank you, the "Pavarozzi" example worked great once I updated all the BestImageIDs and CatalogImageIDs. Sorry for the delay in responding, I was in the field for the last few days.

Would you have a suggestion on the best approach to add new surveys to this database? For example, following todays survey I will add a new record to the surveys table (Date, location, etc.), under which I will add individual manta rays sighted to the IndSighting table, then I will add the photos for each manta ray to the Images table. From their I can flag the BestIMage and BestCalogImage using the format from the "Paparozzi" portal layout.

Currently when I add a record to the Survey table, the layout has a portal to the IndSightings table where I can add the individuals sighted. Within the portal row, I created a button to take me to a related records layout based on IndSightings, which then has a portal to the Images table where I can add the photos for each individual sighted. Is this the best way to add new surveys/indsightings/photos? I've attached a modified Pavarozzi database to show what I mean.

Paparazzi2.fp7.zip

[comment]

1. Your layout "Add Survey" needs to show records from the Surveys table;

2. The portal in the "Add Photos" layout needs to show records from Images (and the fields in both portals need rechecking, too).

[Ziphius]

My bad, sorry, I rushed that mock database but made the proper corrections with the portal button to add photos.

Does this seem like a good way to add new survey data that has nested data (Surveys>IndSightings>Images) to go with each survey?

Paparazzi2.fp7.zip

Edited September 10, 2010 by Guest
added attachment

[comment]

It's certainly one possible way to do it. A lot here depends on your actual workflow.

[Ziphius]

Ok, I'm back and resurrecting the Paparazzi solution. I've attached the Paparazzi file used earlier. To summarize, I'm trying to pick the best photo from an individual encounter (grandchild table) and show only that photo as the best for that sighting. Then I want to look at all the sightings for that individual and pick the overall best as the catalog photo for that individual.

In the database example, the "set field" action does well going from the best individual sightings table to provide the best overall photo, but choosing the best image for each encounter and populating the best images in the IndSightings table is not working. In the individuals layout, choosing the best image for each sighting of all the images should update the best IndSighting Image but its not. I can't believe I'm still trying to figure this out. Any suggestions on how to tweak this file to make it work?

Paparazzi.fp7.zip

[comment]

choosing the best image for each encounter

In order to choose the best image for an encounter, you must select an encounter first. Once you have the selected EncounterID in a global field, you can use that to [a] show only that encounter's images, and identify the encounter record that needs to be modified when selecting the best image.

[Ziphius]

Once you have the selected EncounterID in a global field, you can use that to [a] show only that encounter's images, and identify the encounter record that needs to be modified when selecting the best image.

I apologize but I am not following. How do I select EncounterID in a global field?

Can I not select an encounter and show all its images in a portal, and then the portal record that has the best image, I click on it and it populate the Encounter BestIMageID with that particular ImageID? I tried to do a "set field" command in the portal row but it doesn't seem to be working.

[comment]

Can I not select an encounter and show all its images in a portal, and then the portal record that has the best image, I click on it and it populate the Encounter BestIMageID with that particular ImageID?

Yes, of course you can. That would be even simpler. I meant if you want to do all your selections from the Individual layout, you must have a relationship that will show the individual's images from the selected encounter only.

[Ziphius]

Ok, great. I have a layout showing each encounter with a portal showing all images for that encounter. Next to the image in the portal row, I created a button that assigns the ImageID for that image to the Encounters:BestImageID field. Unfortunately it doesn't populate the Encounters:BestImageID field. Any idea what I am doing wrong?

[comment]

Any idea what I am doing wrong?

Not really. Does this work for you?

Paparazzi3.zip

[Ziphius]

My key field's had text in them so I was using "Set Field by Name", not sure if that was the problem but regardless, your solution works good. This allows me to choose the best image for each encounter and populate the Encounters with the best ImageID. Now the second phase is to do the same with the best Encounter images, choose the best, and this will be the best Catalog image for that individual, in other words populate the Individuals:BestCatalogImageID with Images:ImageID. Essentially just taking what you have done and moving it up a level. But once again, what I did is not working properly. Suggestions?

Also, I like what you did with the "best" text showing up next to the best image selection but I have no idea how you did that. Can you explain this?

Paparazzi4 Copy.fp7.zip

[Ziphius]

Ok, seems that I need to populate the Individuals:CatalogImageID by specifying the BestImage:ImageID not the Image:ImageID. It seems to work now. If you could still explain how you got the "best" text to show up next to the selected image, I like that feature.

Paparazzi5.fp7.zip

[comment]

how you got the "best" text to show up next to the selected image

Conditional formatting.

[Ziphius]

Great! Thank you for all your help, love the conditional formatting feature.

By the way, my main database has thousands of records and all the key fields are text. Any reason why the "Set Field by Name" would not work with text serials just as "Set Field" works with number serials? I ask because I would hate to have to change all my key fields to numbers to get this going.

[comment]

Any reason why the "Set Field by Name" would not work with text serials just as "Set Field" works with number serials?

I'm afraid you are mixing two unrelated issues. The Set Field[] step works with any type of field (and so does Set Field by Name[]).

[Ziphius]

Well for whatever reason when I use text and Set Field by Name, it doesn't work, but if I convert all my key fields to numbers and use the Set Field script, all works well. I'm attaching the final mock database that works for anyone who is interested in it. It only took me 3 years to get this working properly; embarrassing. Thanks for all your help.

Paparazzi6.fp7.zip

[Ziphius]

I have a question about the conditional formatting. Within a portal I created a button next to a photo to click if it is the best photo in the portal. This adds the ImageID value to the BestImageID field. When both of these fields are equal, the text on the button switches from white (invisible) to black. However, if the portal has no records, the button is black already because the condition is met, both fields are blank. Is there a way to set the condition so that it only makes the comparison when none of the fields are blank?

[comment]

not IsEmpty ( A ) and A = B

[Ziphius]

Thanks for this. It certainly satisfies the false condition, when the cell is empty the condition is not applied but when both conditions are met, for some reason the formatting still doesn't get applied.

Here is my condition:

not IsEmpty (DI All Individual Images::DI Images Parent) & DI All Individual Images::DI Images Parent = C Indvidual Sightings::C Best Sighting Ventral ImageID

Any ideas why this wouldn't work?

[comment]

You are using the text operator & instead of the logical operator and.

[Ziphius]

Perfect, I never new there was a difference, works great! Thank you much.