8 Replies Latest reply on Mar 3, 2014 2:33 PM by philmodjunk

    Can't seem to reference related container

    FMNewbie

      Title

      Can't seem to reference related container

      Post

           I'm putting together a historical database for a family tree of sorts.  I would like to arrange it so that people I list in the EVENT::attendees[repeating field] on an EVENT layout are shown in a portal that shows EVENTS::person_age based on EVENT::DATE, as well as the correct PHOTOS::photo that corresponds to their age at the time of the event.   

           Here are the three tables:  

      EVENTS

           EVENTS::date

           EVENTS::attendees [repeating field]  ...related to PEOPLE::person_ID to trigger the portal list.  Maybe this is the wrong way to go about this, but it's what I have set up presently. 

           EVENTS::person_age (defined as [ year (EVENTS::date) - year (PEOPLE::date of birth) ]

      PEOPLE

           PEOPLE::person_ID

           PEOPLE::date of birth

      PHOTOS

           PHOTOS::person_ID

           PHOTOS::age in photo

           PHOTOS::photo (container field) 

            

           I relate the PEOPLE::person_ID to EVENTS::attendees and it calculates the corresponding age at the time of the event quite nicely.  However, I'm having a hard time figuring out how to relate the PHOTOS:photo container field so it populates in the portal row of the corresponding person.  

           Let me know if that makes sense and if anyone has any suggestions..... 

           Cheers. 

        • 1. Re: Can't seem to reference related container
          philmodjunk

               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-----<Attendees>-----People

               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
          • 2. Re: Can't seem to reference related container
            FMNewbie

            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.  

                 Thoughts? 

                  

            • 3. Re: Can't seem to reference related container
              philmodjunk

                   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.

              • 4. Re: Can't seem to reference related container
                FMNewbie

                     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? 

                      

                • 5. Re: Can't seem to reference related container
                  FMNewbie

                       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.  

                  • 6. Re: Can't seem to reference related container
                    philmodjunk

                         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.

                    • 7. Re: Can't seem to reference related container
                      FMNewbie

                           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.  

                      • 8. Re: Can't seem to reference related container
                        philmodjunk

                             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.