1 Reply Latest reply: May 13, 2011 12:11 PM by Nick Swift RSS

    Displaying lookup fields based on max(date)

      Hi,

       

      With the following data structure (reduced):

       

      Main Table

      RecordType

      Field A

      Field B

       

      Lookup Table

      LookupDate

      Notes

      Action

      Outcome

       

      I'm trying to list data from a lookup table based on the following syntax to pass in the maximum date

       

      if(RecordType= 'Prod',MAX(if(OutcomeID>0,LookupDate)))

       

      This returns me the max date from the lookup table perfectly based on the two if conditions.  However, I now want to return the other fields (Notes, Action, Outcome etc) from that record where the max date came from.

       

      I've seen many other examples of this question being asked with several examples (mainly using firstsortedvalue), but I can't get any of them to work!  I'm trying to do this via an expression in a straight table chart.

       

      Thanks in advance
      Nick