Get to Know GetNthRecord

[stash]

Check this out at:

http://my.advisor.com/articles.nsf/aid/16936

ADVISOR TIPS

Get to Know GetNthRecord

Don't overlook this new, powerful feature that lets you get a field value from any record in the found set.

By John Mark Osborne, Database Pros president and owner & FileMaker Advisor technical editor

Add your comments on this topic....

[Lee Smith]

Hi Stash,

I understand your post okay, thanks for the information, but the link doesn't provide anything further to what you said IMHO, or do you have to be a member to see more of this article?

FileMaker 8 continues where version 7 left off by increasing the number of rapid development tools. It's difficult to choose a favorite new feature. GetNthRecord probably won't show up as a bullet point on the FileMaker press release but it's definitely worth a look.

Lee

Edited September 1, 2005 by Guest

[Ender]

I couldn't get at that article either, but I am kind of fond of the new GetNthRecord() function.

Those interested can see my demo using it to build a list of the previous/current/next records in the found set:

http://www.fmforums.com/forum/showtopic.php?tid/169329/

There's also a demo of its use in a custom function here:

http://www.clevelandconsulting.com/support/viewtopic.php?t=637

[stash]

uf sorry, it's for subscribers but wont be bad if just tell you what have heard about the GetNthRecord from the tip of John Mark Osborne.

The point is in this recursive custom function which is indeed one parser who collects Field values(Field paramether in function) out of the FoundSet to the global field or $$Variable but without looping!

Two Parameters:

Field - like it says

Found - Get(FoundCount)

The Function:

SerialNumbers(Field; Found) =

Case(

Found > 1;

GetNthRecord(Field; Found) & "¶" & SerialNumbers(Field; Found -1);

GetNthRecord(Field; 1) //Eliminates extra return at end

)

BTW, the limit of this recursive is on 10.000 records. Mark has some ideas about this problem, with the related records, but to me that seems as a mend. Cool thing to be more explored.

Thanks for new links, will go to check them out immediatly after this post!

Edited September 2, 2005 by Guest

[stash]

O-Hooooo! Flisakowski and Osborne are talking about pretty much similar(same indeed) thing.

From me side little advantage to the Flisakowski's solution is given.

Thanks Ender!

Edited September 2, 2005 by Guest

[Ugo DI LUCA]

I have incorporated it immediately as a tooltip "attached" to the phone number, so I get immediately the second phone number if it exists for any contact.

Probably a million other ideas with this single function indeed.

[comment]

It would be much more useful if it had an optional sort order parameter.

[stash]

After testing GetNthRecord cant hidde my frustration that FileMaker8 provides such nice 'one way' functionality. Pretty cool to Get field content on Nth record but could be much better if we also can Set content on Nth record. Indeed, SetField options remaind being too stiff.

[comment]

I agree. Although you CAN set field of Nth record, if you set up a relationship from GetNthRecord to SerialID, for example.

[stash]

if you set up a relationship from GetNthRecord to SerialID, for example.

Sorry, I dont get it. How that relationship could help?

Edited September 6, 2005 by Guest

[comment]

Something like this (attached)?

SetNthRecord8.fp7.zip

[Ender]

Ooh, I didn't realize GetNthRecord() can also retrieve the Nth related record. Kind of handy!

[comment]

That's why being able to override the relationship's sort order (like you can in a portal) could be REALLY useful.

[stash]

Cool thing you made Comment! Thanks for the lesson.

[Tony O]

Get to Know GetNthRecord

Don't overlook this new, powerful feature that lets you get a field value from any record in the found set.

I love it. I FINALLY made my shipping guy happy - used this to auto enter the first 8 characters of the day's fedex tracking number into new entrys in his shipping log- suchly-

Left ( GetNthRecord ( tracking_num ; Get ( RecordNumber ) - 1 ) ; 8 )

Thanks for the heads up on this new feature!

[MacWorks]

It is a great feature, but I agree with comment that a sort parameter is needed.

For instance, I use it to summarize all of my line item notes on an invoice into one summary text field for the whole invoice. It works great, but if I later enter a line item for an earlier date, my portal sorts the dates, but the text summary (based on GetNthRecord) lists records in creation order. The result is 9/21, 9/22, 9/15.

Anyone know of a workaround that could mimic the sort order in my calculation? Thanks!

[MacWorks]

And the workaround is . . . (answered my own question)

Instead of using a calculation field, I used a short script to set a text field. The script sorts my related records, sets the field and it's good. It's not automatic, but as long as I include it in my Print Invoice script it works perfect (and fast, gotta love 8).

[mav]

I too have a GetNthRecord calculation that needs to access the last record in child records that have been sorted in a portal, rather than creation order.

I'm currently using

GetNthRecord ( fieldname ; Count ( child:fieldname ) )

Is there a better answer than in a script ?

[Leader]

I tried using GetNthRecord() shortly after FM-8 came out and I tried again this week, but in both cases (seeking a record field in a different table) I do not get reliable results.

This time I actually hard-coded integers for the record numbers 1, 2, … etc. What I got was record 2 twice and then ? values. I just don't see how I could be screwing it up this badly.

Has anyone else had problems like this?

Also, I'm really curious why the record must come from a related table and if this could have something to do with my problem. It seems it SHOULD have sufficient information with the table::fieldname and record number, doesn't it?

Ah well.

[xochi]

I tried using GetNthRecord() shortly after FM-8 came out and I tried again this week, but in both cases (seeking a record field in a different table) I do not get reliable results.

This time I actually hard-coded integers for the record numbers 1, 2, … etc. What I got was record 2 twice and then ? values. I just don't see how I could be screwing it up this badly.

I would suspect that the relationship that is being used is not the one you are expecting. Take a look at your relationship graph and see if perhaps you might be getting the related value through a different chain of tables? If that's not the case, perhaps the relationship is sorted in a way you aren't expecting (I'm pretty sure that GetNthRecord will retrieve the Nth record from a table, using the relationship that is specified in the current context (i.e. layout), which means it's sorted by that relationship).

Also, I'm really curious why the record must come from a related table and if this could have something to do with my problem. It seems it SHOULD have sufficient information with the table::fieldname and record number, doesn't it?

Ah well.

Yes, it would be nice if it worked that way, but I'm pretty sure it doesn't. I wish FileMaker would allow more "on the fly" relationship creation, as in SQL. It'd be really handy to have a single script step which would set a field in a table, based on some selection criteria based on variables (e.g. "Set field X in record (the 5th record with "joe" in FirstName) in table Y to the value Z).

[comment]

I'm really curious why the record must come from a related table

It doesn't.

It seems it SHOULD have sufficient information with the table::fieldname and record number

Note that Filemaker's reference is actually TO::fieldname. And the TO is interpreted in the current context. You haven't provided any details regarding your problem, but I'd make context the prime suspect.

[Søren Dyhr]

using the relationship that is specified in the current context (i.e. layout)

Indeed sometimes is the values tunneled, take a look at the attached template being an almost instant summary of a survey.

The most important thing to get sorted is the evaluation context!!!

I know it's a weird mind that can't allow globals or constant's - but it's an excellent challenge, the issue here is that the developer can expand the choises just by changing the value list.

--sd

StatsMod.zip

[SteveB]

Comment: Brian Dunning's site has a CF that sorts a list in either order in case this helps. CF Sort Function

Steve

[comment]

Is this in reference to my earlier lament about the lack of sortfield parameter?

If so, I'd rather use a value list as a workaround than a custom function: it will sort much faster than any CF, and it doesn't require Advanced (in fact it doesn't even require v.8).

Still, it would be nice to say simply 'get me the name of the 5th lowest scorer' in a single function.

[SteveB]

Yeah, it was a reference.

Steve