FM - Getting Portal Row Info

You should be able to use FileMaker's Get Nth record function to create a concatenated text field holding the three most recent notes. Make sure the relationship is sorted the way you want - presumable most recent first - in the relationship diagram, not the portal.

List(
GetNthRecord ( Contact::NoteText ; 1 ) ;
GetNthRecord ( Contact::NoteText ; 2 ) ;
GetNthRecord ( Contact::NoteText ; 3 ) ;
)

If you want to go further and create a leadin for each note that includes the date, you could created a concatenated field in the notes area with something like:

CreatedDate & " - " & NoteText

and then substitute that for the field listed above.

Didn't work; empty field.  Wondering if this function even does get info from a related record (see 1st para below under Description).  Tried every possible iteration I could think of, including:

GetNthRecord(LT_NOTES::NOTE; 271)
GetNthRecord(LT_NOTES::NOTE; LT_NOTES_RecID)
GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber))


Description
The result of GetNthRecord() will not be updated when the record referred to by GetNthRecord() is a record other than the one in which the calculation is currently being evaluated.
GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted.
GetNthRecord of a related table returns the Nth record of the related set (relative to the current record), regardless of how the related table (or portal) is sorted.

OK, closer.  I realized that I need to create the field in the related table, LT_Notes, then bring it back into LeaseTrack and/or into my email (see below).  However, two problems:  1) Despite checking to see if it is empty, it still places a ? if no record is present.  Second, how do I get the last record and work my way back.  This gives me the first record and moves forward.  Thus, if I have 5 notes, it will give me Notes 1-3, not 5,4, and 3.  

If (not IsEmpty (GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)));GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)) & "¶") &
If (not IsEmpty (GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)+1));GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)+1) & "¶") &
If (not IsEmpty (GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)+2));GetNthRecord(LT_NOTES::NOTE;Get(RecordNumber)+2))

I think I'm probably missing something without being able to see an example file, but I suggest that you must use
Max(Get(RecordNumber))
Max(Get(RecordNumber)) - 1
Max(Get(RecordNumber)) - 2

I also strongly encourage you to use the List() function rather than concatenating using " & "¶" ) & "  The List() function creates a return-separated list and if the value is empty is skips the extra return. See my original example.

OK, I did some testing and I'm seeing the issue which has to do with how the Record Number is determined and whether the record exists. Here's a calculation that works for me:

Case( not IsEmpty( GetNthRecord( Notes::NoteDateText ; 1 ) ) ; GetNthRecord( Notes::NoteDateText ; 1 ) ) &
Case( not IsEmpty( GetNthRecord( Notes::NoteDateText ; 2 ) ) ; "¶" & GetNthRecord( Notes::NoteDateText ; 2 ) ) &
Case( not IsEmpty( GetNthRecord( Notes::NoteDateText ; 3 ) ) ; "¶" & GetNthRecord( Notes::NoteDateText ; 3 ) )

This will work IF your relationship from the main record to the Notes record is set to Sort descending by date.

For some reason the List() function does not work properly with GetnthRecord() I'm interested in understanding why. There is quite likely a simpler way of getting the same result as this calc, but it will get you there.

Example file
Concatenated-Notes.fp7

Here's how I translated it to my specific db.  The sort worked fine; however, the not is empty checked did not.  I'm still getting ? marks.  Have to test this in the morning.

Case( not IsEmpty( GetNthRecord( LT_NOTES::NOTE ; 1 ) ) ; "Latest Note: " & GetNthRecord( LT_NOTES::NOTE ; 1 ) ) &
Case( not IsEmpty( GetNthRecord( LT_NOTES::NOTE ; 2 ) ) ; "¶2nd Latest Note: " &  GetNthRecord( LT_NOTES::NOTE ; 2 ) ) &
Case( not IsEmpty( GetNthRecord( LT_NOTES::NOTE ; 3 ) ) ; "¶3rd Latest Note: " &  GetNthRecord( LT_NOTES::NOTE ; 3 ) )

Just spent a half hour playing with this and can't make it work.  Very frustrating because it looks so simple, but I keep getting ? marks when there is no data.  

Can you create a sample file using just the basic fields from your setup and upload it? Sometimes breaking out the bare bones of the fields and relationships can help pinpoint the problem.

See screenprint for ? marks.
-PortalRecListing.JPG

It's hard to tell what's going on here. On my sample, I put both the date and the Note ID in the Note string so I could tell they were in the correct order. Is the note that's showing in fact the correct note from the standpoint of order? Are there in fact other notes to be displayed? If you isolate and only use one line of the calculation, does it give you the correct note? In other words, is the issue related to finding the correct note or something else?

-PortalRecListing2.JPG
-PortalRecListing3.JPG

No, the order is fine; the correct note is fine; it is simply showing ? marks for empty records. e.g., if record 2 and 3 are empty, I will have a ? mark for each.

Is this "LT_NOTES::NOTE" field a text field or calc field? is it possible that there is anything in that field? You say that if record 2 and 3 are empty you get the "?" but if those records are present do they show correctly? The only sample I've seen appears to only have one note record...

OK, I found that actually your file does the same thing.  If you don't have a min of 3 records, it also shows ? marks.  See screen print.
-PortalRecListing4.JPG

Not sure what is up with FM on this.  Can we/should we do a workaround using the Substitute function to get rid of the 1 mark?

This works. I'm certain there is a more elegant way, probably using the List() function, but this will do it:

Case(  Count( Notes::zk_NoteID ) > 0 ; GetNthRecord( Notes::NoteDateText ; 1 ) ) &
Case(  Count( Notes::zk_NoteID ) > 1 ; "¶" & GetNthRecord( Notes::NoteDateText ; 2 ) ) &
Case(  Count( Notes::zk_NoteID ) > 2 ; "¶" & GetNthRecord( Notes::NoteDateText ; 3 ) )

Thanks, Will, that worked.  Maybe you should write an article on this since FM did not act in the way we would expect in at least 2 cases.  Also, can you recommend any training or is their an online tutorial for calculations?

I've used GetnthRecord before and there are many uses for it, but I hadn't specifically come up against how it behaves with either the List() function or isempty() functions. It seems odd that it would give an error character "?" rather than simply a null value. There are a few circumstances where a question mark appears, it's just seems incorrect that it should appear in this instance. I think to write an article I'd need to do some research and see whether there is some consistent logic about when FM does and does not produce a "?" error character.

As far as tutorials or training, I'm not sure I can specifically point you to anything in particular. There are so many functions, not to mention the ability to created or reuse other's custom functions - that it's largely a situation of learning as you go. That said, I highly recommend Matt Petrowsky's FileMaker Magazine videos. I've gotten lots of great tips over the years from them including on calculations. Some of his videos are pretty specific and esoteric but most are good general purpose building blocks.

Thanks.