Solved

FM - Getting Portal Row Info

Posted on 2011-03-07
22
935 Views
Last Modified: 2012-05-11
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.)
0
Comment
Question by:rvfowler2
  • 11
  • 11
22 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 35060905
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35060952
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35061483
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 2

Author Comment

by:rvfowler2
ID: 35062090
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35062347
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35062860
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35062958
Example file
Concatenated-Notes.fp7
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35065990
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071389
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35071537
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071551
See screenprint for ? marks.
-PortalRecListing.JPG
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35071637
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071660
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071674
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35071740
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071778
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35071879
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35071960
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
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 35072001
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
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35072169
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 35072317
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 35072451
Thanks.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question