rellis32 Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
Ender Posted June 21, 2006 Share Posted June 21, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
Genx Posted June 21, 2006 Share Posted June 21, 2006 (edited) ... 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 Link to comment Share on other sites More sharing options...
comment Posted June 21, 2006 Share Posted June 21, 2006 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 ) ) Link to comment Share on other sites More sharing options...
Søren Dyhr Posted June 21, 2006 Share Posted June 21, 2006 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 Link to comment Share on other sites More sharing options...
comment Posted June 21, 2006 Share Posted June 21, 2006 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. Link to comment Share on other sites More sharing options...
Genx2 Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
Ender Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
Genx2 Posted June 21, 2006 Share Posted June 21, 2006 Ender i'm not really following could you distinguish a little further in your definition of the found set and all related records? Link to comment Share on other sites More sharing options...
comment Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
Genx Posted June 21, 2006 Share Posted June 21, 2006 ... 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? Link to comment Share on other sites More sharing options...
comment Posted June 21, 2006 Share Posted June 21, 2006 Read the help on the function, particularly the examples. Link to comment Share on other sites More sharing options...
Ender Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
rellis32 Posted June 21, 2006 Author Share Posted June 21, 2006 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 More sharing options...
Ender Posted June 21, 2006 Share Posted June 21, 2006 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 More sharing options...
Ender Posted June 21, 2006 Share Posted June 21, 2006 I guess that's pretty much the same as comment's CF, but it should be called with: GetLastValue2 ( MyField ; get(recordnumber) ) Link to comment Share on other sites More sharing options...
rellis32 Posted June 22, 2006 Author Share Posted June 22, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
rellis32 Posted June 22, 2006 Author Share Posted June 22, 2006 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 More sharing options...
Genx Posted July 3, 2006 Share Posted July 3, 2006 Thanks for the clarification Ender. Link to comment Share on other sites More sharing options...
Russell Watson Posted July 4, 2006 Share Posted July 4, 2006 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 More sharing options...
Ender Posted July 4, 2006 Share Posted July 4, 2006 Oooh, a definitive answer! Looks good, but the empty result in the private function is not necessary. Link to comment Share on other sites More sharing options...
madman411 Posted September 4, 2015 Share Posted September 4, 2015 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 More sharing options...
Recommended Posts
This topic is 3149 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 accountSign in
Already have an account? Sign in here.
Sign In Now