Auto Increment field for ticket system

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
jmacdougall
Posts: 26
Joined: 2015-11-02 01:22

Auto Increment field for ticket system

Post by jmacdougall » 2020-11-21 01:42

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! :D
Jeff
2020-11-20_20-36-50.jpg
2020-11-20_20-36-50.jpg (91.6 KiB) Viewed 2208 times
Jeff MacDougall

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Auto Increment field for ticket system

Post by jsetzer » 2020-11-21 07:02

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
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

jmacdougall
Posts: 26
Joined: 2015-11-02 01:22

Re: Auto Increment field for ticket system

Post by jmacdougall » 2020-11-21 15:19

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...)
Jeff MacDougall

jmacdougall
Posts: 26
Joined: 2015-11-02 01:22

Re: Auto Increment field for ticket system

Post by jmacdougall » 2020-11-21 15:28

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
Jeff MacDougall

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Auto Increment field for ticket system

Post by jsetzer » 2020-11-21 17:16

(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
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Auto Increment field for ticket system

Post by jsetzer » 2020-11-22 07:58

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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply