Auto Increment field for ticket system

[jmacdougall]

I am putting together a ticket system for my business. When one of my agents creates a New Ticket, I want the first field up top to show the new ticket number and save it upon saving the record. I have the ticket field set to "Integer" and Primary Key but when I create new ticket, the field is blank.

Please help! I appreciate the assist!
Jeff

2020-11-20_20-36-50.jpg (91.6 KiB) Viewed 3578 times

[jsetzer]

Are you sure the field is blank when creating a ticket (after save) or is it blank before you create the ticket (when opening the page, before save).

Standard behaviour of relational databases is that primary key will be incremented on insert (after save). The number will not be created at the moment when any user opens any page which could lead to an insert in the future. This could lead to a number of unused primary keys, each time a user opens the insert page but never saves the record. So, it makes sense to increment the PK on save (=on insert), not beforehand (=NOT on opening the page).

This is perfect for technical reasons but sometimes problematic in business process when you need to rely on a serial number. It is a well known problem in programming.

I have seen programmers who selected the maximum existing id from a table +1 and displayed that to the users in insert page. This is bad design! Because (a) due to deleted records the next number can be different from max(id)+1 and (b) if more than one user opens the insert page in the same period of time, only one of them may get that ticket number, but not all of them. This means doing it that way you would show numbers to the users which cannot be guaranteed.

Alternatives:

1) Let them save early: only have very few (or no) required fields, let them insert the record immediately, this will increment the PK number and it will be visible then, then let them fill the rest of the fields

2) insert the record by code
Create a link in navbar, on dashboard or somewhere else which executes a php function. In there, create the record (which increments the PK), set record owner and redirect the user to the edit page. They will see the PK number then.

3) additional field
Don't use the (technical) record identifier AKA primary key (pk) but an additional field with a custom calculated unique number, for example including location, user and timestamp fragments like DE-000351-20201231-075712-255. This would reduce possibility of duplicates in multi user scenarios.

Alternatives (1) and (2) may lead to gaps in serial numbers when users later on decide

[jmacdougall]

It is blank PRIOR to saving and I want to see it as I am making the ticket. YES, it does assign it an incremental number post-save. I would also like to start that number at my own choosing and not (1,2,3...). I want to start at say (10000,10001,10002...)

[jmacdougall]

Actually, I also wanted to say that I understand completely what you explained and I will not rely on that assigned ticket number until the record is first saved. This is fine. I would like to change the starting number though.

Thanks for your help!
-Jeff

[jsetzer]

(1) Presumed sequence number

I see no other way to reliably display the probable next number than to create the record itself. Otherwise it would never be reliable, but at most likely. Displaying a presumed sequence number that is different after the data record has been created can lead to subsequent errors and problems in the business process. I personally would not want to take that risk.


(2) Ticketnumbers starting at 10000

(a) MySQL solution

Code: Select all

ALTER TABLE table_name AUTO_INCREMENT = 10000;

Be careful!

(b) Possible AppGini solutions

(1/2) What about adding a new calculated field (integer, readonly) with SQL formula (untested)...

Code: Select all

SELECT (10000+%PKFIELD%) FROM %TABLENAME% WHERE %PKFIELD%='%ID%'

(2/2) You can also add an additional varchar field (readonly, calculated field) and combine a ticketnumber from various fragments by using concat, concat_ws and format options (MySQL commands). Example TCKT-DE-00357-00000001

[jsetzer]

After some more research I have seen there is a better approach than the bad-practice of selecting max+1. Due to deleted records max+1 may fail.

But we can use MySQL's information schema to select the next auto increment number. This approach takes into consideration deleted records for which a primary key was previously assigned.

Code: Select all

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'YOURTABLENAME'
AND table_schema = DATABASE()

For example in hooks/ TABLENAME.php, TABLENAME_dv function, in insert-mode you can use $next = sqlValue("...") function for retrieving the probable next pk value and show it by modifying $html variable.

Beware

The situation in multi-user enviromnents does not change. So, still: never rely on this predicted number, because another user could create a new record in the meantime and "grab" this primary key.