2. PHPRunner General questions
  3. Automatically calculate sequence within group
This topic is locked

Automatically calculate sequence within group

 		9/17/2019 5:27:10 AM
PHPRunner General questions
P
 pmuckle authorDevClub member

Hi,
I have grouped records with an independent 'sequence' field. The first record for each group starts with sequence '1'.
On Add Record, how can I check for the highest sequence number in the group, add +1 and enter it as the default?
I'm not sure where in Events I should put the code, or what the code should be...
The incomplete sql I have looks something like this:
// get highest sequence number for group
select Image_sequence, max(value) AS current_seq

from Project_photos

where Harnessing_project_ID=
// update new record with highest number +1

Update Project_photos set Image_sequence=current_seq+1 where Image_ID=
Thanks for any pointers
Peter
Sergey Kornilov admin 9/17/2019

Instead of using a default value I would suggest performing this calculation in BeforeAdd event.

$values["Image_sequence"]=<your next number>;
C
 		Corrie 9/18/2019



Hi,
I have grouped records with an independent 'sequence' field. The first record for each group starts with sequence '1'.
On Add Record, how can I check for the highest sequence number in the group, add +1 and enter it as the default?
I'm not sure where in Events I should put the code, or what the code should be...
The incomplete sql I have looks something like this:
// get highest sequence number for group
select Image_sequence, max(value) AS current_seq

from Project_photos

where Harnessing_project_ID=
// update new record with highest number +1

Update Project_photos set Image_sequence=current_seq+1 where Image_ID=
Thanks for any pointers
Peter


I am not sure what your table looks exactly, but I think this might help:

You will also need to identify the group.
Global $conn;
//Get the last seq in the group and add one

$rsLastSeq = db_query("select max(Image_sequence) from <<Your table>> where <<Group Field>> = '<<The group value>>'", $conn);

$LastSeqData = db_fetch_numarray($rsLastSeq);

$NextSeq = $LastSeqData[0] + 1;
//Update the new seq

$strSQLUpdate = "UPDATE <<Your table>> SET Image_sequence = ".$NextSeq." where Image_ID = <<This Id>>";

db_exec($strSQLUpdate,$conn);
I hope this helps at least a little, if not, please ask again with more info.