Solved

FM - Getting Portal Row Info

Posted on 2011-03-07
22
931 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 24

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 24

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
 
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 24

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 24

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 24

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 24

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 24

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 24

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 24

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 24

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 24

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now