FM - Getting Portal Row Info

My boss wants to add the following at the bottom of a weekly email:
      Latest note:
      2nd Latest note:
      3rd Latest note:

I can either pull this from the portal row or from the original record, but cannot figure out how to do it.  Our Notes used to be in repeating fields, so I could easily just indicated the repetition# I wanted to pull from, but how do you do it with records.  Didn't see anything in FM Help.  (Note:  he doesn't want every note entered, just the last three as indicated by the headers above.)
LVL 2
rvfowler2Asked:
Who is Participating?
 
Will LovingPresidentCommented:
The issue seems to be having a way to determine whether the record exists other than using "not isempty" or "not isvalid" with GetnthRecord() which doesn't seem to like either of those. I tried the following, which also works:

Case( GetNthRecord( Notes::NoteDateText ; 1 ) <> "?" ; GetNthRecord( Notes::NoteDateText ; 1 ) ) &
Case( GetNthRecord( Notes::NoteDateText ; 2 ) <> "?" ; "¶" & GetNthRecord( Notes::NoteDateText ; 2 ) ) &
Case( GetNthRecord( Notes::NoteDateText ; 3 ) <> "?" ; "¶" & GetNthRecord( Notes::NoteDateText ; 3 ) )
0
 
Will LovingPresidentCommented:
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.
0
 
Will LovingPresidentCommented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rvfowler2Author Commented:
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.
0
 
rvfowler2Author Commented:
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))
0
 
Will LovingPresidentCommented:
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.
0
 
Will LovingPresidentCommented:
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.
0
 
Will LovingPresidentCommented:
Example file
Concatenated-Notes.fp7
0
 
rvfowler2Author Commented:
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 ) )
0
 
rvfowler2Author Commented:
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.  
0
 
Will LovingPresidentCommented:
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.
0
 
rvfowler2Author Commented:
See screenprint for ? marks.
-PortalRecListing.JPG
0
 
Will LovingPresidentCommented:
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?
0
 
rvfowler2Author Commented:
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
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...
0
 
rvfowler2Author Commented:
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
0
 
rvfowler2Author Commented:
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?
0
 
Will LovingPresidentCommented:
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 ) )
0
 
rvfowler2Author Commented:
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?
0
 
Will LovingPresidentCommented:
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.
0
 
rvfowler2Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.