That repeating field is not the optimum choice here. If you are trying to match a photo to the data in a specific repetition in attendees, that won't work.
Replace the repeating field with a related table where you have one record for each attendee:
Start with these relationships:
Events::__pkEventID = Attendees::_fkEventID
People::__pkPeopleID = Attendees::_fkPeopleID
You can place a portal to Attendees on the Events layout to list and select People records for each given Events record. Fields from People can be included in the Portal to show additional info about each selected People record and the _fkPeopleID field can be set up with a value list for selecting People records by their ID field.
A single photo from Photos could be shown in the attendees portal row, but you can also use a different layout based on Attendees to set up a portal to show multiple Photos and some layouts will be best set up if you base it on the Photos table and include data from the other related tables here.
You may find some of the features of this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7 If you are using FileMaker 12 or newer, you can use Open from FileMaker's File menu to produce a copy of this file converted to the newer .fmp12 file format.For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Okay I did that and I almost have it.... but I need the photo of the person in the portal row to change depending on:
(a) the People_ID and
(b) that person's age at the time of the event
I have a calculation field called ATTENDEES::Age_at_Event defined as [year(EVENT::date) - year (PEOPLE::Date_Of_Birth)] which is related to PHOTOS::Age_In_Photo which populates the container in the portal row once and then never changes it.
year(EVENT::date) - year (PEOPLE::Date_Of_Birth)
Is a calculation that can be off by one. At the time of event, a person's age could be a year less than the calculated age if their birthday falls after the event date. That may or may not be an issue for what you want to do here.
Exactly how do you want the age calculated to affect which photo appears. Do you only want the photo to appear if the age in photo exactly matches this calculated age? Or do you need a more sophisticated age comparison.
And since your calculation will be an unstored calculation if you define it in a field of type calculation, it can't be used as a match field in a relationship, you'd need to use an auto-entered calculation and then be careful how you manage updates should you need to change a birth date or event date.
PS. Year ( Event::date ) - year ( People::Date_of_birth ) -
( Date ( Month ( People::Date_of_birth ) ; Day ( People::Date_of_birth ) ; Year ( Event::Date ) ) > Event::Date )
Will adjust the calculated age by 1 if their birthday is later in the year than the event date.
I was actually already planning to change the age calculation... I'm just trying to get the more complicated stuff to work first. And to answer your question, yes, I only want to the photo to appear if the age in the photo = the calculated age. So better to go to an auto-enter calculation for that?
Also... which table should that "age at the time of the event" field live in? I keep fiddling around with different relationships but I seem to be confusing myself.
Hmm, scratch that. From one record to many, an unstored calculation will work on the "one" side of the relationship.
Attendees::_fkPeopleID = Photos::PersonID AND
Attendees::cAgeAtEvent = Photos::AgeInPhoto
To select a second pair of match fields for the same relationship, drag a second time or double click the relationship line.
Thanks Phil... I think I sorted it out. It works but the photos don't seem to update instantly if you change the date... I have to tab to a different window and when I come back it's updated. Not a huge issue.
Thanks for your help.
You probably just need to commit records. Try clicking a blank area of the layout outside of the portal and see if that updates your image. That also commits records. If so, you can actually put a script trigger on the date field to commit records each time the date is changed.