Re: Custom Auto Generated ID in SQL
(This may or may not apply here, but I couldn’t find another place to post it and I thought it was good info for the topic)
I needed somthing similar, but I needed it to work in a 2-form process where Form1 was a “To Be Assigned” value shown to the user (noted that it was a ‘temporary’ number), and the actual number needed to relate to the actual primary_key ID number in the database.
Method Used:
Main Form1 in Single Record mode
Table with numeric “id” field (INT(11))
set to AUTOINCREMENT at the database level, primary_key
Form1 or Form2 do not actually edit any data for the ID field (Form1 Attribute Values for ‘id’ set to “Calculated by the database”)
2nd field used in this example is “idtxt” (a customized text version of the ID number)
Form1 does dynamically ‘insert’ the final value into the ‘idtxt’ field at the last moment, later read again by Form2
Form 1, onLoad event: (get the last order ID, increment it by +1, create a fixed-length text string)
sc_lookup(getidnew, "SELECT CONCAT('[s_dept]-',LEFT('0000000',8 - CHAR_LENGTH(id+1)),id+1) idtxt FROM orders ORDER BY id DESC LIMIT 1");
{idtxt} = {getidnew[0][0]};
(the concat statement above is for MySQL, not MSSQL)
(the ‘[s_dept]’ in the concat statement adds a pre-stored variable containing a 4-char sales department ID to the beginning of the auto-generated text id)
Then, to make sure another user hasn’t entered a form with the same ID (such as if you have 100 users entering data at the same time, AND you are NOT running Transactions, you can still re-query a new ID before inserting the final data) :
Form 1, onBeforeInsert event:
(run the same query as above!)
The resulting text should be:
If ID = 25, and [s_dept] = “DEPT”
idtxt = “DEPT-00000025”
A crude way to get there, but easy and effective, and very customizable for your text result! I’m sure it would be adaptable to a editable grid scenerio
