6 Replies Latest reply on May 22, 2016 2:04 AM by erolst

    Most recent comment using MAX calculation from related field?

    KrisYells

      Parent table contains a property id.

      Child table contains events using conditional value lists. Input by portal. an event set of records contains  Date, Category,  Event and a Comment field.

       

      In the parent table, field titled Executive Summary, I need to display the latest dated comment from the Negotiations Category. Inside the negotiations category I have 5 different events. The events can happen at different times and occasionally have multiple occurrences of the same event.

       

      I have tried different version of the calculation below and each time all I can get returned is the date.

       

      Case(   

      Events::Event Category="Negotiations" ; Max ( Events::Event Date) Events::Event Comment;"" )

       

      What I'm trying to say with the calculation is if the related event category equals negotiations return the comment from the latest dated event. If no matches are found return nothing.

       

      thanks in advance for the help

        • 1. Re: Most recent comment using MAX calculation from related field?
          Jade

          Try:

           

          If( Events::Event Category = "Negotiations" ; Last( Events::Event Comment ) ; "" )

           

           

          If the Events TO is sorted by Event Date in descending order, then that should give you what I think you want.

          • 2. Re: Most recent comment using MAX calculation from related field?
            KrisYells

            Works like a charm. Thank you so much. this will save me hours of work.

            • 3. Re: Most recent comment using MAX calculation from related field?
              Jade

              Hi Kris,

               

              Take a little of that time you saved to test it well.  Depending on your found set of related data, there may be some circumstances where it doesn't work well…just saying.

               

              HTH,

              Jon

              • 4. Re: Most recent comment using MAX calculation from related field?
                erolst

                Jade wrote:

                If the Events TO is sorted by Event Date in descending order, then that should give you what I think you want.

                 

                That formula is looking at the category of the first record (i.e. the newest), and if that is of the desired category, it reads the comment from the last record (the oldest, via this order), which will be of category "anybodysguess".

                 

                In short: if that works as intended, it is by pure accident, and will change on a non-deterministic basis – i.e. is impacted by whatever record you add next … if it is not of category "Negotiations", you won't get any result at all (which may be better than a false one, but still somewhat disappointing).

                 

                You could do with with a calculation, but it needs to be a bit more complex; assuming a relationship (portal won't do) sorted descending by event date …

                 

                Let ( [

                  theList = List ( Events__byDateDesc::Event Category ) ;

                   ofLatestNegotiation = ValueCount ( Left ( theList ; Position ( ¶ & theList & ¶ ; "¶Negotiations¶" ; 1 ; 1 ) ) )

                  // position in that list of the first occurrence of "Negotiations"

                  ] ;

                  GetNthRecord ( Events__byDateDesc::Event Comment ; ofLatestNegotiation )

                )

                 

                You could also try (without any relationship sorting):

                 

                ExecuteSQL ( "

                  SELECT \"Event Comment\"

                  FROM \"Events\”

                 

                  WHERE \"Event Type\" = ?

                  AND \"Property ID\" = ?

                 

                  ORDER BY \"Event Date\" DESC

                  FETCH FIRST 1 ROW ONLY

                 

                  " ; "" ; "" ; "Negotiations" ; Property::ID

                )

                 

                or (depending on the count of related records) simply use a sorted portal with a filter of

                Events::Event Category = "Negotiations"

                 

                or create a relationship that is matched on a hardcoded global calc (= "Negotiations") and sorts descending …

                 

                Location Options, Options, Options!

                • 5. Re: Most recent comment using MAX calculation from related field?
                  KrisYells

                  Erolst,

                  I'm using the  SQL function and I seem to be really close. The problem I have now is I am getting the same result for every record.

                   

                  How do I isolate the result to the matching PK and FK fields so when I scroll through the records I get the related max dated comment?

                   

                  The end result of this will be exported to an Excel sheet along with other fields from the parent table.

                  • 6. Re: Most recent comment using MAX calculation from related field?
                    erolst

                    KrisYells wrote:

                    I am getting the same result for every record.

                     

                    How do I isolate the result to the matching PK and FK fields

                    Look the sample code where this is already included – you add the property foreign key to the WHERE clause and specify the property primary key as argument to inject:

                     

                    AND \"Property ID\" = ?

                      " ; "" ; "" ; "Negotiations" ; Property::ID

                    )