4 Replies Latest reply on Sep 16, 2014 3:16 PM by Stephen Huston

    setting data from the most recent related record in the parent record?

    slayden@msn.com

      I'm looking for the most efficient way to list the MOST RECENT activity of certain types in a CRM: meeting, calls, dials, connects, deals, closings, referrals, etc......

      What is a good way to do this? If I do tons of portals, it runs slow and is very inelegant.....but I only want the last ONE of each TYPE....

      Note that the related table has a field "activity type" in it.

       

      Thanks in advance for any help

      Scott

        • 1. Re: setting data from the most recent related record in the parent record?
          erolst

          You could create an unstored calc field, type text, along these lines …

           

          ExecuteSQL ( "

           

            SELECT id

            FROM Activities A1

           

            WHERE

              id_parentForeignKey = ?

                AND

              theTimestamp = (

                SELECT MAX ( theTimestamp ) from Activities A2 where A1.id_activityType = A2.id_activityType AND A1.id_parentForeignKey = A2.id_parentForeignKey

                 )

            " ; "" ; "" ; id // parent primary key

          )

           

          … and use the field as a match field into your Activities table.

           

          EDIT: Amended to take into account a parent ID.

          • 2. Re: setting data from the most recent related record in the parent record?
            BruceRobertson

            If you are going to use standard FileMaker relationship techniques (as opposed to SQL) then note that for an unsorted relatioship, the LAST function gets the one you want. That is, it gets the last record, in creation order.

             

            Example:

             

            So if there is a relationship to Activities, and say a global field zType that acts as a selector by type for this relationship, then if you set zType to "Call", then Last( filteredActivities::Description) gets you the contents of the Description field for the last (most recent) call.

             

            This assumes record creation order always correctly matches the actual sequence.

             

            If, for instance, you created a Call record today, and then realized oh wait - we had a call that we have not entered yet, that call was last Friday, and you enter THAT call descripton and date, then even though its date is earlier and you enter the correct date in the Activity DATE field, it is the "LAST" call by creation order.

            • 3. Re: setting data from the most recent related record in the parent record?
              erolst

              BruceRobertson wrote:

               

              If you are going to use standard FileMaker relationship techniques (as opposed to SQL)

               

              … then here is one way to do it; SQL approach thrown in for free.

              • 4. Re: setting data from the most recent related record in the parent record?
                Stephen Huston

                If you sort the realtionship itself, you can sort it by date/descending. That way the First related record will be the latest created. You can then put that related record's date field from the related record itself right on the parent record's layout, and it will display the latest date.

                 

                If you have several different tables from which you need the latest of several last dates, that will require a calc (probably as a field in the parent table) such as:

                • Max (tableA::fieldX ; tableB::fieldY ; etc)