Automatically insert a value incremented by one unit into a field when I generate a new record

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Automatically insert a value incremented by one unit into a field when I generate a new record

Post by fgazza » 2020-10-11 09:54

ciao a tutti!
nella mia tabella "document" ho un campo "document number".
Il campo document number.

il record più recente contiene un campo document number con numero 20200025.

L'automatismo che sto cercando di impostare è:
quando un user crea un nuovo record automaticamente il campo "document number" viene riempito con il numero precedente incrementato di una unità (in questo caso 20200026).

L'ideale sarebbe che questo valore venisse immesso già nel momento della creazione del nuovo record in modo che l'user che aggiunge il record possa vederlo subito e non "after insert".

Qualcuno può aiutarmi con il codice?

GRAZIE!

Fabiano

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: Automatically insert a value incremented by one unit into a field when I generate a new record

Post by fgazza » 2020-10-11 09:55

Sorry!! here is the post in english!

Hello everyone!
in my "document" table I have a "document number" field.
The document number field.

the most recent record contains a document number field with the number 20200025.

The automation I am trying to set up is:
when a user creates a new record, the "document number" field is automatically filled with the previous number increased by one unit (in this case 20200026).

The ideal would be that this value was entered already when the new record is created so that the user who adds the record can see it immediately and not "after insert".

Can anyone help me with the code?

THANK YOU!

Fabiano

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

Re: Automatically insert a value incremented by one unit into a field when I generate a new record

Post by jsetzer » 2020-10-11 11:56

This is a well known software- and database-problem.

Imagine there are 2 or more users creating a record in the same period of time. One enters insert-mode and can see the serial number. Later, another user or more users also enter insert-mode. In this common scenario all of them would see the same number 20200026 in insert mode. The first one who inserts "his" record would "win" the race for that number. "his" number in the database will be equal to the one shown before insert. But the second one (and all other concurrent users) will get an error on insert due to duplicate entry when he tries to insert "his" record. Because at that time the number 20200026 already exists and cannot be inserted twice due to the unique constraint on the database column.

This means there is no guaranteed number before insert.

Many business applications (a) do not show a number before or (b) do show a number before insert and give a hint or prefix or suffix that this number is only expected/predicted but not guaranteed or (c) they program a serial-number provider (database table) which handles concurrency.


For example Invoice-Numbers have to be unique as soon as the invoice is fixed. But the number cannot be fixed as long as you and your staff is working on drafts of invoices. First save wins the race.

I hope this helps understanding the problems with concurrent access in web- (and other) applications.

So how can we solve this:

(1) Insert very early
Make only very few fields required, so the user can insert almost immediately after he/she entered insert-mode. This will reduce probability of problems.

(2) Explain to the users that the number is only an expected number, and that the final number will be set when they insert the record.

(3) Instead of manual insert by user, you can also create a record by code and SQL, make the user the owner using the set_record_owner function and then open up the new, already created record in edit-mode. This requries changes in the workflow.

(4) Show the expected number and use some javascript timer (interval) to request the actual expected number from the server, then update the number on client side. Caution: this will reduce but not prevent problems with concurrency. This requires a lot of additional programming.

(5) build a "reservation" table with serial numbers. Whenever someone enters insert-mode of your table, take and lock the next unique number from the reservations table. This requires additional programming and logic for example if your users don't insert their records later on, there will be locked numbers never been used.

Don't forget:
As soon as someone deletes a record there will be gaps in the serial order of the numbers. Also, as soon as someone reserves a number but does not insert the record itself, the reserved number will be lost unless you unlock that reserved number. In that case someone else may take that reservation. This may lead to confusion because order of numbers might not match up with date/time order of insert.

Personally, If would not trust in expected numbers. They don't mean a thing as soon as someone else was faster with inserting his record. I would fix the number after insert because then you can be pretty sure about uniqeness.
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

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: Automatically insert a value incremented by one unit into a field when I generate a new record

Post by fgazza » 2020-10-11 14:37

thank you very much Jan!
Your explanation is very useful and clear !.
It seems to me that in the end the best (or at least the simplest and safest) way to handle the problem is to assign the number "after insert".
So I put this code in the DV function of the hook file of the table.

Here is the code I created (I recovered it from other forum posts by only updating the mysql_free_result ($ result) statement; which became mysqli_free_result ($ result);

It is not clear to me if this mysqli_free_result statement is actually needed and recommended.

I would be grateful if you could give me an explanation about it.

Here is my code that works perfectly!

Code: Select all

$ sql_string = "SELECT MAX (n_protocol) + 1 FROM match_protocol;";
$ newiscinaprotocol = sqlValue ($ sql_string, $ o);
$ sql_string = "UPDATE match_ protocol set protocol n =". $ newiscina protocol. "WHERE pkey =". $ data ['selectedID']. ";";
$ result = sql ($ sql_string, $ o);

mysqli_free_result ($ result);

return TRUE;

THANK YOU!

Fabiano

Post Reply