Set Field based on content of its related nested fields

[Ziphius]

I have a relational database of manta ray sightings: Individals<Sightings<Images = each individual has many sightings and each individual sightings has many images.

I have a field in the Individuals table (Mother?) that I want to Set if any of her images show she was pregnant. For each image, a checkbox field (ImageCharacteristics) has a list of characteristics (adult, juvenile, male, female, pregnant, shark bite, mating scar, parasites, etc.) that can be checked to describe attributes of the image. If any of the images for a female for any sighting, has "pregnant" checked, I want to set the Individuals:Mother? field to "Yes", otherwise set to "No". Can this be performed by setting the Mother? field as a calculation or would it be better to create a script? I am not an expert on scripting so if a script is the better way to go, some help on creating the script would be great. Thanks.

[Vaughan]

No script needed.

Assuming the "pregnant" attribute is in the Characteristics field in Images, create a Mother calc field in the Individuals table:

Case( isempty( Filtervalues( List( Images::Characteristics ) ; "pregnant" ) ) ; "No" ; "Yes" )

Having all the characteristics in one field is not good design, there should be a related table of "Characteristics" related to Images so that each is stored as a separate record.

[Ziphius]

Thanks Vaughan, simple and works perfectly.

As for converting my Image Characteristic values to separate records in an Characteristic table, how difficult would it be to create the transition (a script) that will maintain the proper relationships? Since one image would have many characteristics and one characteristic could have many images, this would likely be a many-to-many relationship?