3 Replies Latest reply on Oct 3, 2012 11:12 PM by TheaOrr

    Filter Portal Records by last date dependant on another field entry

    TheaOrr

      Title

      Filter Portal Records by last date dependant on another field entry

      Post

           Hi,

           What I am trying to create is a portal onto a table of responses to meeting invitations. I want the portal to show only the most recent response recorded for each attendee.

           eg. Bill Bloggs may respond on the 1/11/12 that he is attending the meeting and needs accommodation, but respond again on the 5/11/12 that he is still attending but no longer needs accommodation. Meanwhile, John Doe responds on the 6/11/12 that he is attending and needs accommodation.

           I want to record each response in a table so that we have the history of what attendees have responded, however the portal just needs to show the most recent response from each person.

           I hope this makes sense! Is there a way of filtering this in a portal?

           Kind regards,

           Thea

        • 1. Re: Filter Portal Records by last date dependant on another field entry
          crtopher

               Hi Thea...I might be missing something here, but in the portal setup dialog, you have the option to sort portal records. You could sort the portal by the date of the response, assuming that responses is a portal on the invitations layout. 

          • 2. Re: Filter Portal Records by last date dependant on another field entry
            philmodjunk

                 If you can set up a self join by respondant ID, you can filter out all but the records with the most recent reponses:

                 Event-----<Responses-----<ResponsesByRespondantID

                 Event::__pkEventID = Responses::_fkEventID

                 Responses::_fkRespondantID = ResponsesByRespondantID::_fkRespondantID AND
                 Responses::_fkEventID = ResponsesByRespondantID::_fkEventID

                 Both Responses and ResponsesByRespondantID would be occurrences of the same table.

                 Then define this calculation field, cMostRecent in Responses, set to evaluate from the context of Responses:

                 Max ( ResponsesByRespondantID::DateResponded ) //select "Date" as the return type

                 Now you can use this portal filter for a portal to Responses to filter out all but the most recent responses by each respondant:

                 Responses::cMostRecent = Responses::DateResponded

                  

            • 3. Re: Filter Portal Records by last date dependant on another field entry
              TheaOrr

                   Awesome, thanks Phil! That worked perfectly. :-)

                    

                   Thanks also Chris - sorting by the date and respondant name would show all responses by all people, but ideally I wanted to not display the out-of-date responses in the portal when people change their minds, which Phil's answer solves. This way, the Responses table has the complete history of what we have been told if we need to refer back to it, and the portal just gives us the current info per person.