# Recursive GetNthRecord

This topic is 2283 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I've been fiddling with this all night and just cannot grasp it....

Problem:

I need a CF that displays the contents of a field from the last record in which the field contained data. My thinking tells me that this could be done with

Case ( not IsEmpty (GetNthRecord(Field;Get(RecordNumber) ; Field ; GetNthRecord ( Field ; Get (RecordNumber) -1 )

I think the concept is close to what is needed, but it's not working. Am I missing something?

Bob

##### Share on other sites

This may work:

GetLastValue ( field; num; lastValue ) =

//GetLastValue(field;num;lastValue) =

Let(

[

currentValue = GetNthRecord(field; num);

lastValue = Case(IsEmpty(currentValue) ; lastValue; currentValue)

];

Case ( num ≥ Get(FoundCount) ;

lastValue ;

GetLastValue ( field ; num + 1 ; lastValue)

)

)

Start it with a call like this:

GetLastValue(MyField ; 1; "")

Edited by Guest
##### Share on other sites

... Well here's the problem right, what if you have situation a:

Data1

Data2

-

Data3

-

-

Data4

-

-

where - represents blank, do you want data4 only?

or is this situation b where you simply have:

Data1

Data2

Data3

Data4

-

If it is situation b you can just use: GetNthRecord(RelatedTable::YourDataField ; Count(RelatedTable::YourDataField))

EDIT: Umm, Ender's smarter than me so you should probably listen to him

~Genx

Edited by Guest
##### Share on other sites

And for situation a) you could call a custom function like this (untested)

GetLast ( fieldName ; n )

Let (

this = GetNthRecord ( fieldname ; n )

;

Case (

IsEmpty ( this ) and n > 1 ; GetLast ( fieldName ; n - 1 ) ;

this

)

)

which would be called as:

GetLast ( fieldname ; Count ( Child::ChildID ) )

##### Share on other sites

Is it the sortorder of the relation that prevent all these clever heads from using:

Last(

Why make a recursive function when an aggregate function, mistakenly placed in repeating functions exists?

However the sortorder can be arranged by an extra TO tied to the same parent-table.

--sd

##### Share on other sites

It seemed so obvious, I just assumed that adding a dedicated TO was not desired.

However, if we are permitted to change the sort order, then let's make it descending and address the related field directly.

##### Share on other sites

Hey look, even if we arent technically permitted to change the sort order, what we could do is add an additional calc field that returns a 1 if there is content and a 0 if there isn't and sort over that... therefore allowing us to keep the bottom "data4" value.

##### Share on other sites

Aside from the sort order, another consideration is whether the last value should be pulled from all records or just the found set. With GetNthRecord(), you're dealing with the found set. With Last(), your dealing with all (related) records.

##### Share on other sites

Ender i'm not really following could you distinguish a little further in your definition of the found set and all related records?

##### Share on other sites

With GetNthRecord(), you're dealing with the found set. With Last(), your dealing with all (related) records.

Actually, with GetNthRecord(), you are dealing EITHER with the found set OR with the related set. With Last(), you are ALWAYS dealing with the related set (repeating fields being excused from this context).

##### Share on other sites

... so being confused again here, with GetNthRecord, aren't we dealing with the related set? And how does the foundset come in to this scenario?

##### Share on other sites

Read the help on the function, particularly the examples.

##### Share on other sites

If it's only important to run this operation among a found set of 20 records (out of maybe 20,000 in the table), then the only solution may be to use GetNthRecord() on a field in the local table (or a scripted loop.) If this is what Bob was looking for, he wouldn't want that 'last value' function to pull from a record outside the found set.

If we knew more about Bob's structure and purpose, we would know better whether a function on a related field would work (it would certainly be simpler and faster.)

##### Share on other sites

Sorry I was so vague!

Firstly, this should be for the found set.

Secondly, it's kind of like GetNthRecord(Field;Get(RecordNumber)-1)

I want to be able to show data from a previous record, the one right next to it, if it's not Empty. If it is empty, I would like to pull the data from the next previous record and so on.

So

Record Data

1 55

2 12

3 _

4 _

5 Current Record

If you were currently viewing record 5, I would want the field to display 12, or if you were on record 4 or 3 it would display 12....

Does that kind of do a better job of what i'm looking for? Hope so!

Thanks for all your input, i haven't checked this until now because I didn't know it would be such a hot topic!

Bob

##### Share on other sites

My first CF would work, but it's not very efficient. This one's a little better as it works backwords from the current record until it finds a value:

GetLastValue2 ( field; num ) =

//GetLastValue2 ( field; num ) =

Case ( num > 0 ;

Case ( IsEmpty ( GetNthRecord ( field; num ) ) ; GetLastValue2 ( field ; num - 1 ) ; GetNthRecord ( field; num ) )

)

##### Share on other sites

I guess that's pretty much the same as comment's CF, but it should be called with:

GetLastValue2 ( MyField ; get(recordnumber) )

##### Share on other sites

You guys are great!

If I'm understanding this correctly, you could reverse this and get the NEXT nonEmpty value by changing the case to num <= Get(FoundCount) and of course incrementing by + 1 instead of - 1

EDIT*

Could the CF work like this?

GetLastValue2 ( field ) =

//GetLastValue2 ( field ) =

Let (

num = Get (RecordNumber) ;

Case ( num > 0 ;

Case ( IsEmpty ( GetNthRecord ( field; num ) ) ; GetLastValue2 ( field ; num - 1 ) ; GetNthRecord ( field; num ) )

)

)

/EDIT*

This recursive thing is so cool, I just wish I could wrap my mind around it!!!

Thank you all!

Bob

Edited by Guest
##### Share on other sites

I'll answer my own question after looking at it, no it won't work because the num still needs to be incremented in the Case statement....

So how can this be written so when Joe User wants to use it, it sets the num value to Get(RecordNumber)

Or should this be one of those "Private" CF's

Something else, totally off topic, I setup the RSS feed for this thread, and it's stuck on 5/26...any ideas?

Bob

##### Share on other sites

• 2 weeks later...

Thanks for the clarification Ender.

##### Share on other sites

Hi bob. Here's my definitive answer:

-------------------------------------------------

GetLastEntered__CFpub( fieldName )

/*

Returns the last entered value, starting at and including the current record

Use this function to extend column data over empty rows.

*/

GetLastEntered__CFpvt( fieldName ; Get( RecordNumber ) )

-------------------------------------------------

GetPreviousEntered__CFpub( fieldName )

/*

Returns the last entered value, starting at but NOT including the current record

Use this function, for example, to compare the value entered in the current record with previously entered value.

*/

GetLastEntered__CFpvt( fieldName ; Get( RecordNumber ) - 1 )

-------------------------------------------------

GetLastEntered__CFpvt( fieldName ; recordNum )

// Private recursive part

if( recordNum >= 1

; if( isEmpty( GetNthRecord( fieldName ; recordNum ) )

; GetLastEntered__CFpvt( fieldName ; recordNum - 1 ) // Empty => try previous

; GetNthRecord( fieldName ; recordNum ) // Not empty => result

)

; "" // start of list => empty result

)

-------------------------------------------------

The two public functions user the private function to do the work, whereby GetLastEntered__CFpub starts at the current record and GetPreviousEntered__CFpub starts at the previous record.

You could use, for example, GetPreviousEntered__CFpub in an auto-enter calculation, and GetLastEntered__CFpub in an output field which extends a sometimes-empty input field:

input_ | output_

field: | field, unstored = GetLastEntered__CFpub( input_field )

------ | ------

hello! | hello!

------ | hello!

------ | hello!

------ | hello!

byebye | byebye

------ | byebye

Does that help? [email protected]

##### Share on other sites

Looks good, but the empty result in the private function is not necessary.

##### Share on other sites

• 9 years later...

Hi bob. Here's my definitive answer:

-------------------------------------------------

GetLastEntered__CFpub( fieldName )

/*

Returns the last entered value, starting at and including the current record

Use this function to extend column data over empty rows.

*/

GetLastEntered__CFpvt( fieldName ; Get( RecordNumber ) )

-------------------------------------------------

GetPreviousEntered__CFpub( fieldName )

/*

Returns the last entered value, starting at but NOT including the current record

Use this function, for example, to compare the value entered in the current record with previously entered value.

*/

GetLastEntered__CFpvt( fieldName ; Get( RecordNumber ) - 1 )

-------------------------------------------------

GetLastEntered__CFpvt( fieldName ; recordNum )

// Private recursive part

if( recordNum >= 1

; if( isEmpty( GetNthRecord( fieldName ; recordNum ) )

; GetLastEntered__CFpvt( fieldName ; recordNum - 1 ) // Empty => try previous

; GetNthRecord( fieldName ; recordNum ) // Not empty => result

)

; "" // start of list => empty result

)

-------------------------------------------------

The two public functions user the private function to do the work, whereby GetLastEntered__CFpub starts at the current record and GetPreviousEntered__CFpub starts at the previous record.

You could use, for example, GetPreviousEntered__CFpub in an auto-enter calculation, and GetLastEntered__CFpub in an output field which extends a sometimes-empty input field:

input_ | output_

field: | field, unstored = GetLastEntered__CFpub( input_field )

------ | ------

hello! | hello!

------ | hello!

------ | hello!

------ | hello!

byebye | byebye

------ | byebye

Does that help? [email protected]

Apologies for resurfacing an ancient post.

Is there a way this CF could take into consideration a possible blank record separating data within a portal and breaking the recursive chain?

##### Share on other sites

This topic is 2283 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account