Page 1 of 1
Calculated Field with Random Number
Posted: 2022-07-04 12:30
by hgarbe
Hi everyone,
beforehand: appologies if this a dumb question or not best practice in coding. Still working my way through Mysql.
The idea or what I tried to achieve: getting a randomized Id with a query in a calculated field. Format something like Year-Random Alphanumeric
What I got is this:
Code: Select all
SELECT CONCAT(YEAR(CURDATE()),"-", UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM data_protection_data WHERE "random_num" NOT IN ( SELECT random_id FROM TABLENAME) LIMIT 1
obviously not working. random_id is the calculted field, but also is not working in phpmyadmin. When I change "random_id" to "id" (primary key of the table) it is working. Which is cool but not helping as I want to check its uniquness against the random_id column.
Code: Select all
SELECT CONCAT(YEAR(CURDATE()),"-", UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM data_protection_data WHERE "random_num" NOT IN ( SELECT id FROM TABLENAME) LIMIT 1
I got no clue why. Because its the primary key, unique or Auto Increment?
Re: Calculated Field with Random Number
Posted: 2022-07-04 13:55
by jsetzer
What about creating the initial random value in before-insert hook?
Re: Calculated Field with Random Number
Posted: 2022-07-04 14:07
by hgarbe
Would make most sense as otherwise the random value would be regenerated every time the tv oder dv is opened.
But other side: I didn't manage to get a result initially with phpmyadmin. I played around with the setting of the random_id column. Can the Default "null" of the column be reason i get an empty result?
Re: Calculated Field with Random Number
Posted: 2022-07-04 14:11
by jsetzer
(1) well, your SQL statement should update only records where random value is null (in your WHERE clause)
Re: Calculated Field with Random Number
Posted: 2022-07-04 14:43
by hgarbe
I thought "NOT IN ( SELECT random_id FROM TABLENAME)" would check if the random value is part of the results of this embedded query?
Only way I get this to work is on a column that is set to no "null" and has an Index. Trying it with a by default appgini generated column and it doesn't work.
Re: Calculated Field with Random Number
Posted: 2022-07-04 14:49
by jsetzer
Stupid question: are you sure you only update exactly 1 record?
Where YOUR_PK='%ID%'
Re: Calculated Field with Random Number
Posted: 2022-07-04 14:50
by jsetzer
Once again: what about using before_insert or after_insert hook?
Re: Calculated Field with Random Number
Posted: 2022-07-04 15:21
by hgarbe
First of all: thanks for taking time to still answer.
Yes I do. As you suggest I did include this in the hooks:
Code: Select all
function table1_before_insert(&$data, $memberInfo, &$args) {
$IntRef=sqlValue("SELECT CONCAT(YEAR(CURDATE()),'-', UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM table1 WHERE 'random_num' NOT IN (SELECT random_id FROM table1) LIMIT 1");
$data['reference_field'] = $IntRef;
return TRUE;
}
So first keen to just update/insert one field. Problem is: depending on the setting for the column "random_num" this sql query in the hooks (same as running it in phpmyadmin) creates a result or not.
Re: Calculated Field with Random Number
Posted: 2022-07-04 15:51
by jsetzer
What about using PHP?
https://www.php.net/manual/de/function.uniqid.php
Create a unique string with prefix of current YEAR. Check if this already exists in database by using sqlValue function. Do this in a do () {} loop. If not exists, you have found a unique value. Theb update Data variable. Else repeat loop.
If your field is readonly, update database record directly by using sql- or sqlValue function in AFTER insert hook.