Dynamically generate sequential numbers

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
Jay Webb
Veteran Member
Posts: 75
Joined: 2017-08-26 15:27
Contact:

Dynamically generate sequential numbers

Post by Jay Webb » 2020-05-22 01:52

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?
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 869
Joined: 2018-04-01 10:12

Re: Dynamically generate sequential numbers

Post by pböttcher » 2020-05-22 19:19

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.

User avatar
D Oliveira
AppGini Super Hero
AppGini Super Hero
Posts: 201
Joined: 2018-03-04 09:30
Location: Orlando, FL

Re: Dynamically generate sequential numbers

Post by D Oliveira » 2020-05-24 05:50

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;

				}


User avatar
Jay Webb
Veteran Member
Posts: 75
Joined: 2017-08-26 15:27
Contact:

Re: Dynamically generate sequential numbers

Post by Jay Webb » 2020-05-26 19:26

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...
What we envision, we make happen.

Post Reply