Recursive GetNthRecord

[rellis32]

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

[Ender]

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 June 21, 2006 by Guest

[Genx]

... 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))

If not, please clarify

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

~Genx

Edited June 21, 2006 by Guest

[comment]

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

[Søren Dyhr]

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

[comment]

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.

[Genx2]

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.

[Ender]

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.

[Genx2]

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

[comment]

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).

[Genx]

... 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?

[comment]

Read the help on the function, particularly the examples.

[Ender]

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.)

[rellis32]

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

[Ender]

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

)

[Ender]

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

GetLastValue2 ( MyField ; get(recordnumber) )

[rellis32]

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 June 22, 2006 by Guest

[rellis32]

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

[Genx]

Thanks for the clarification Ender.

[Russell Watson]

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]

[Ender]

Oooh, a definitive answer!

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

[madman411]

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?