AppGini Community Forums

Example: 4004300359

first two numbers is unit1 number
3rd and 4th number is the month and day
last 0359 is autoincremented

How can I make that be my primary key on save to code1

Is this done through hooks or in appgini?

Could someone please help me on this one.

Hi,

you can try this.

I created a table test, have the field ID being autoincremented and use the field code1 as primary key,
will result in

id code1 unit1 data
1 66605030001 666 vvv
2 77705030002 777 vvv

Hope that helps.

pböttcher,

Thank you so much for the help, is this put in a hooks file? also, the format I am looking for is this.

Example code1: 4004300359

40 is unit1
04 is the month (so if the month is May, it will be 05 or if its June, it will be 06)
30 is the day of the month.
0359 is auto incremented, EX. 0359 0360 0361.

I have racked my brain till I am about to explode LOL!

Hi,

this would go into the database definition as a trigger. the table I created was just an example. you would need to replace the fieldnames with yours.

concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max)
will create your uniq key.

in the example I create above you would add e.g. via insert
INSERT INTO `test`(`unit1`, `data`) VALUES (40,'vvv')

This will create your code1 as 4005030003

where
40 = the value of unit1 (see INSERT statement)
05 = month (we have may, so it generates 05)
03 = day (we are the 3'rd today )
0003 = autoincrement value of the ID field (which is the autoincrement of this table, for this instert 3, see above examples for id=1 and id=2)

So basically you need to add this trigger ( I adjusted the code to directly retrieve the autoincrement value )
to your table.

be aware that there is a little chance that, if 2 people update the table at the same time, this might fail as the SELECT statement above would retrieve the max id of your current autoincremented id. But if meanwhile another insert happens, the id could already be incremented and hence you would get an incorrect result.

regards

When you mean trigger, is this in mysql or hooks?

Or.....tablename-dv.js file ?

it is in mysql

So I have a database called lcso
Table called accident
unit1
code1 (primary)

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;

Would that be correct

Also, failed to mention. the unit1 number is entered by the person, it could be 43 55 99 16 03 ...its just two numbers, so when they enter their unit number, it needs to be part of the Code1 when it saves.

Hi,

yes that is what I assumed. UNIT 1 is retrieve when the user clicks has entered the data into the form and saves it.

in the code you see it as new.unit1, this is the sql syntax to handle the actual value for that field.

Error I get when I enter a new record.

Unknown column 'accident' in 'where clause'

Query:
insert into `accident` set `date1`='2018-5-3', `time1`='3:35:00', `unit1`='66', `days`='Monday', `type`='45', `parties`='Smith vs Douglas', `location`='123 Mains Street London, Ky'

can you post the trigger you created, also the table definition of accident.

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;

needs to be

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table-name = 'accident' into _max;

This is the error I get when I do it.

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table-name = 'accident' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;

sorry typo, it needs to be table_name instead of table-name

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table_name = 'accident' into _max;

Out of range value for column 'code1' at row 1

Query:
insert into `accident` set `date1`='2018-5-3', `time1`='1:25:00', `unit1`='77', `days`='Wednesday', `type`='46', `parties`='Smith vs Evans', `location`='1802 HWY'

The above info is displayed because you are currently signed in as the super admin. Other users won't see this.Back

Hi,
where is your autoincremented "ID" field? In the picture I see that code1 is your primary key, but is that also your autoincrement?
if this is the case, the above will not work.
can you post a in phpMyAdmin a

DESCRIBE accident

to see you table definitions in MYSQL.

pböttcher you are the best! I figured it out, it was Big Integ, I set them to that, and it worked!

Original code: set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);

New code: set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%y%-'),_max);

Notice the "-" <---dash will that put that in the database?

Hi,

what do you try to acheive with the date_format of '%y%-'? I do not think that this will work. I thought you needed the month and day.

It is, just trying to add a dash in there.

if code1 is a big int, it cannot contain a - (dash).
if it is a varchar, you can do this. the %y will add the year ,not month or day. To add the - (dash) you do not need the second %

%y-

is enough.

I tried with varchar with auto increment, with primary, and appgini says it can only be a integ data types.

sure autoincrement can only be an int type.
So does it work for you now?