2. PHPRunner General questions
  3. [SOLVED]  How to display the data in the next record as part of current record
This topic is locked
[SOLVED]

 How to display the data in the next record as part of current record

 		8/6/2020 12:16:44 AM
PHPRunner General questions
R
 richard@intellico.co.nz author

Hi All,

I'm relatively new here so I apologize if my question is a silly one!

On a List page, how can you get a field in the next record and display it as a field in the current record?
I will explain...

I have a field "EntryDateTime" who's value will also be the "NextEntryDateTime" for the previous record.
Like this
PLATE..............EntryDateTime.............NextEntryDateTime

ABC123.........23/08/2020 07:43AM.........23/08/2020 08:02AM

DEF789.........23/08/2020 08:02AM.........23/08/2020 09:12AM

XYZ000.........23/08/2020 09:12AM.........23/08/2020 11:10AM

CCC123........23/08/2020 11:10AM.........(date & time of next PLATE entry)
Using the method shown at https://asprunner.com/forums/topic/21092-implementing-running-totals-in-asprunnerpro/ I have tried creating a query that has a running row count and then was linked via a [ID]=[ID]-1 but would not allow me to link the row number.
Any suggestions/pointers would be greatly appreciated!
Thanks
G
 		Grdimitris 8/6/2020



Hi All,

I'm relatively new here so I apologize if my question is a silly one!

On a List page, how can you get a field in the next record and display it as a field in the current record?
I will explain...

I have a field "EntryDateTime" who's value will also be the "NextEntryDateTime" for the previous record.
Like this
PLATE..............EntryDateTime.............NextEntryDateTime

ABC123.........23/08/2020 07:43AM.........23/08/2020 08:02AM

DEF789.........23/08/2020 08:02AM.........23/08/2020 09:12AM

XYZ000.........23/08/2020 09:12AM.........23/08/2020 11:10AM

CCC123........23/08/2020 11:10AM.........(date & time of next PLATE entry)
Using the method shown at https://asprunner.com/forums/topic/21092-implementing-running-totals-in-asprunnerpro/ I have tried creating a query that has a running row count and then was linked via a [ID]=[ID]-1 but would not allow me to link the row number.
Any suggestions/pointers would be greatly appreciated!
Thanks


If the table is

SELECT

id,

plate,

EntryDateTime,

NextEntryDateTime

FROM plate
Then in custom add write the code
$rs = DB::Query("select MAX(id) as max_id from plate");

$last_id = $rs->fetchAssoc() ;

$last=$last_id["max_id"];

if ($last){

$rs = DB::Query("select * from plate where id='".$last."'");

$lastdata = $rs->fetchAssoc() ;

$EntryDate=$lastdata["NextEntryDateTime"];

} else {

$EntryDate=date('m/d/Y h:i:s a', time());}

$data = array();

$data["plate"] = $values["plate"];

$data["EntryDateTime"] = $EntryDate;

$data["NextEntryDateTime"] = $values["NextEntryDateTime"];

DB::Insert("plate", $data );
return false;
The EntryDateTime field is not necessary in Add page.
R
 		richard@intellico.co.nz author 8/6/2020



If the table is

SELECT

id,

plate,

EntryDateTime,

NextEntryDateTime

FROM plate
Then in custom add write the code
$rs = DB::Query("select MAX(id) as max_id from plate");

$last_id = $rs->fetchAssoc() ;

$last=$last_id["max_id"];

if ($last){

$rs = DB::Query("select * from plate where id='".$last."'");

$lastdata = $rs->fetchAssoc() ;

$EntryDate=$lastdata["NextEntryDateTime"];

} else {

$EntryDate=date('m/d/Y h:i:s a', time());}

$data = array();

$data["plate"] = $values["plate"];

$data["EntryDateTime"] = $EntryDate;

$data["NextEntryDateTime"] = $values["NextEntryDateTime"];

DB::Insert("plate", $data );
return false;
The EntryDateTime field is not necessary in Add page.
R
 		richard@intellico.co.nz author 8/6/2020

Thank-you for your code Grdimitris really appreciated!
C
 		cboucher 8/7/2020

Depending on what database you are using have you looked at window functions? Many databases have a LEAD() window function that will do exactly what you want.