Page 1 of 1

Dynamically generate sequential numbers

Posted: 2020-05-22 01:52
by Jay Webb
Using, Appgini 5.82

Hello

I'm looking for a method to dynamically generate sequential numbers, assigning said number to a letter A through W, and then having the number only used once per letter, as in A.001, A.002, A.003 through A.100000, same for every letter, B.001 through B.100000, C.001 through C.100000 and so on to W.
All this when a surname is selected in a drop-down list. Results, never having to worry there will be a duplicate code created and assigned.

Is even possible with script and Appgini?
Suggestions?

Re: Dynamically generate sequential numbers

Posted: 2020-05-22 19:19
by pbottcher
Hi,

you can try e.g. for A.xxxxxx

Code: Select all

SELECT lpad(max(cast(SUBSTRING(field, 3, length(field)-2) as unsigned))+1,6,0)
FROM `table` a
where field like 'A.%'
this will give you the next number for A.
Replace table and field by the appropriate entries of your db.

Re: Dynamically generate sequential numbers

Posted: 2020-05-24 05:50
by D Oliveira
this is some old code of mine, it can be done more efficiently but still works, maybe it can help:

Cheers

Code: Select all



$date0 = new DateTime();
$date0x = $date0->format('Y-m-d');

$current_year = substr($date0x, 2,2);


$max_id = intval(sqlValue('SELECT MAX(CAST(gutter_id) AS UNSIGNED)) FROM guttertable')) + 1;


				if ($max_id < 10) {
				
								$new_id =  "G".$current_year."-000" . strval($max_id);
								echo $new_id;
								
								// store in db $max_id (I used gutter_id as my field for that, you wanna keep track of the number rather than just the string) 
								// store in db $new_id as your display ID and store it in another field... (e.g: gutter_id_display)
								//progression:  G19-0001, G19-0010, G19-0100, G19-1000
								
				}

				if ($max_id >= 10 and $max_id < 100) {
				
									//$new_id =  "G19-00" . strval($max_id);
									$new_id =  "G".$current_year."-00" . strval($max_id);
									echo $new_id;

				}

				if ($max_id >= 100 and $max_id < 1000) {

									//$new_id =  "G19-0" . strval($max_id);
									$new_id =  "G".$current_year."-0" . strval($max_id);
									echo $new_id;

				}

				if ($max_id >= 1000) {

								//$new_id =  "G19-" . strval($max_id);
								$new_id =  "G".$current_year."-" . strval($max_id);
								echo $new_id;

				}


Re: Dynamically generate sequential numbers

Posted: 2020-05-26 19:26
by Jay Webb
Hi,
tried both suggestions and couldn't get working but that's ok, using triggers to prevent duplicates and all is well, also dumped using dropdown list and went to tables and works much better.

Thanks for the input...