Jump to content

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

Link to comment
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
Link to comment
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))

If not, please clarify

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

~Genx

Edited by Guest
Link to comment
Share on other sites

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

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

Link to comment
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?:P

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

--sd

Link to comment
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.

Link to comment
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.

Link to comment
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).

Link to comment
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.)

Link to comment
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

Link to comment
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 ) )

)

Link to comment
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
Link to comment
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

Link to comment
Share on other sites

  • 2 weeks 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]

Link to comment
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?

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.