Comments from last Inspection

[lstephens]

What I am trying to do is add a box (for the technicians reference) on the form that shows the "Comments & Recommendations" from the previous Inspection performed. On the current form that is being edited it will be titled "Previous Comments & Recommendations". I cannot seem to zero-in on the correct expression to choose the last inspection performed according to the PM ID.

Each piece of equip has an ID which will show on the insp report, and I need the Comm/Rec from the last report to show in a long text field for ref to the technician. 

This is my current expression

ANY(SELECT(PMs[Comments & Recommendations],and [_THISROW].[ID]=[ID],and (MAXROW("PMs", "Date", ([Date] < [_THISROW].[Date]))))

[lstephens]

It is bringing up the same 'comments' for all reports. 

I changed the expression to this

[Suvrutt_Gurjar]

Please try an expression of 

INDEX(

            SELECT(PMs[Comments & Recommendations], AND([_THISROW].[ID]=[ID],              [_ROWNUMBER]<[_THISROW].[_ROWNUMBER)) ,

              COUNT(SELECT(PMs[Comments & Recommendations], AND([_THISROW].[ID]=[ID], [_ROWNUMBER]<[_THISROW].[_ROWNUMBER)))

             )

Please test well.

[lstephens]

this is the error it gave me for that

[Suvrutt_Gurjar]

Oops. Sorry, there were s few typos. Please  try

INDEX(

            SELECT(PMs[Comments & Recommendations], AND([_THISROW].[ID]=[ID],              [_ROWNUMBER]<[_THISROW].[_ROWNUMBER])) ,

              COUNT(SELECT(PMs[Comments & Recommendations], AND([_THISROW].[ID]=[ID], [_ROWNUMBER]<[_THISROW].[_ROWNUMBER])))

             )

[WillowMobileSys]

There are square brackets missing, see the red text:

INDEX(

    SELECT(PMs[Comments & Recommendations], 
             AND([_THISROW].[ID]=[ID],              
                 [_ROWNUMBER]<[_THISROW].[_ROWNUMBER]
             )
    ),

    COUNT(SELECT(PMs[Comments & Recommendations], 
             AND([_THISROW].[ID]=[ID], [_ROWNUMBER]<[_THISROW].[_ROWNUMBER]
             )
          )
    )
)

Please note that using [_ROWNUMBER] in this manner, order does matter.  If you are certain that the reports and the associated comments are always entered in a chronological order, then this expression will be fine.

However, if users are allowed to back-date reports and/or comments, then using [_ROWNUMBER] will get you last entered item and not necessarily the latest Dated item.