10 Replies Latest reply on Nov 12, 2012 3:16 PM by philmodjunk

    Updating a calc in a portal row??

    FMNewbie

      Title

      Updating a calc in a portal row??

      Post

           I have a calculation field that calculates EVENTS:AGE using data from two related tables as such:  

                    year ( EVENTS::DATE ) - year (PEOPLE::DATE_OF_BIRTH )

           On a layout in EVENTS I have a portal that lists the demographic info of people in attendance at the event, including their age at the time of the event (as above).  When I change the date of the event, the EVENTS::AGE field only updates in one of the portal rows but not the rest.

           My sense is that there's some option that I'm not selecting in the setup of the portal.  

           What am I missing?  

        • 1. Re: Updating a calc in a portal row??
          philmodjunk

               In which table, events or People did you define this calculation?

               Is it a field of type calculation or a number field with an auto-enter calculation?

          • 2. Re: Updating a calc in a portal row??
            FMNewbie

                 It's defined in PEOPLE and its a calculation type field.  

            • 3. Re: Updating a calc in a portal row??
              philmodjunk

                   What you describe sounds more typical if it were defined in the People table.

                   Open up the calculation definition for this field. What is the "context" table occurrence specified in the drop down in the top of this specify calculation dialog?

              • 4. Re: Updating a calc in a portal row??
                FMNewbie

                     Sorry it took me so long to reply.  The context is the PEOPLE table.  There is also a PEOPLE2 table occurence but when I use that, the EVENTS::AGE calc field doesn't seem to work at all..... or am I going about this the wrong way? 

                • 5. Re: Updating a calc in a portal row??
                  philmodjunk

                       What is the relationship between Events and People?

                       What are the match fields that you are using in that relationship?

                  • 6. Re: Updating a calc in a portal row??
                    FMNewbie

                         Sigh... well... I seem to have completely deleted the file, so I'm starting over from the ground up!!   Maybe that's best anyway!! 

                         Basically what I'm looking to do is have one table of PEOPLE that lists personal data including PEOPLE::DATE OF BIRTH and another table that describes EVENTS that has information about an event including EVENTS::ATTENDEES and EVENTS::DATE.  

                         As names are listed in the EVENTS::ATTENDEES field (which I was going to do as a repeating field but I'm open to suggestions) , their personal data would populate into a portal as well as their age at the time of the event defined as a calculation type field such as (EVENTS:ATTENDEE AGE = year(EVENTS::DATE) - year(PEOPLE::DATE OF BIRTH) ).

                         If I recall, the way I had the relationships set, was an ID field PEOPLE::ID_NUMBER that was related to EVENTS::ATTENDEES; and then a second instance of PEOPLE related to EVENTS as PEOPLE2::AGE AT EVENT related to EVENTS::DATE.

                         When I did it that way, all the correct ages seemed to populate until I changed the event date.  The first record in the portal would remain unchanged, but every other record would change correctly.  

                         Hope that makes sense??  Suggestions??

                    • 7. Re: Updating a calc in a portal row??
                      philmodjunk

                           This sounds like a set up where you need another table besides just events and people. After all a person can attend many events and many people attend the same event.

                           People----<Attendees>------Events

                           People::__pkPeopleID = Attendees::_fkPeopleID
                           Events::__EventID = Attendees::_fkEventID

                           See this thread if this notation is unfamiliar: Common Forum Relationship and Field Notations Explained

                           With this setup, a portal to Attendees on the events layout will list all people attending that event. A portal to attendees on the People layout will list all events that person is slated to attend.

                           Your age calculation can be defined in attendees.

                           See this demo file for some ideas on how to set this up: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                           If you are using FileMaker 12, you can open this file from the File menu in FileMaker to get a converted copy of the file to examine and experiment with.

                      • 8. Re: Updating a calc in a portal row??
                        FMNewbie

                             Ahhhh.... okay that makes sense now.  As usual, you're a life saver!!  Or... certainly a time saver.  Cheers!!!

                              

                        • 9. Re: Updating a calc in a portal row??
                          FMNewbie

                               Just one question however... the portal itself.. should that show records from PEOPLE or ATTENDEES??

                          • 10. Re: Updating a calc in a portal row??
                            philmodjunk

                                 Please take a look at the demo file. Usually, it's a portal to Attendees with fields added from the third table. If your layout is based on Events, your portal would be based on Attendees with fields from People included to supply additional information. If your layout is based on People, the portal is still based on attendees, but now you include informaiton from Events in the portal row.