Page 1 of 1
Count values in table field - after insert
Posted: 2019-10-07 07:05
by vladimir
Hi,
I'm storing values using hooks after insert. That way when form is saved data is populated and not before.
Code: Select all
sql("update `Orders` set `OrderNo`= 'Order-{$data['id_order']}' where `id_order` = LAST_INSERT_ID()", $eo);
Now I have another field where I save order number and with every new record this number is greater by one.
I read from DB with
Code: Select all
sqlValue("SELECT OrderNo2 FROM Orders where id_order = LAST_INSERT_ID()", $eo);
Can someone give me the right direction.
Thanks.
Re: Count values in table field - after insert
Posted: 2019-10-07 07:25
by pbottcher
Hi,
what is your question? What is OrderNo2, What is your table structure?
Why are you using LAST_INSERT_ID() and not $data['selectedID']?
The
Code: Select all
sqlValue("SELECT OrderNo2 FROM Orders where id_order = LAST_INSERT_ID()", $eo);
has a syntax error,
sqlvalue does not have 2 parameters. It should be
Code: Select all
sqlValue("SELECT OrderNo2 FROM Orders where id_order = LAST_INSERT_ID()");
Re: Count values in table field - after insert
Posted: 2019-10-07 08:06
by vladimir
topic should read increment values in table field - after insert
I'm building workorder form. I want to save workorder number in a field.
Code: Select all
OrderNo - varchar - 40
id_order - integer - autoincrement
I'm using code below to store values in field OrderNo. And it's ok.
Code: Select all
sql("update `Orders` set `OrderNo`= 'Order-{$data['id_order']}' where `id_order` = LAST_INSERT_ID()", $eo);
Now I'm complicating the way that I want to store just a number in another field OrderNo2 and with every new record the number in this field incremented by 1. And after that the field OrderNo is populated with text and value in the corresponding field.
I know I'm complicating.
Re: Count values in table field - after insert
Posted: 2019-10-07 08:19
by vladimir
I did not pay attention when creating form because next year the Ordernumber is not getting reset to 0.
I shall add another table year and relate to table WorkOrders.
Re: Count values in table field - after insert
Posted: 2019-10-07 10:14
by pbottcher
Hi,
this depends on how you create your order number.
E.g. you could user Order-YYYY-xxxxxx where YYYY is the year and xxxxxx is the counter for the order number.
In such a case you could do the following in the after function:
$year = date("Y");
$next = sqlvalue("SELECT 1+MAX(0+SUBSTRING(OrderNo,12)) from Orders where OrderNo like '%{$year}%'");
$new_orderno = "Order-".$year.str_pad($next, 6, "0", STR_PAD_LEFT);
sqlvalue("UPDATE OrderNo set `OrderNo`='{$new_orderno}');
The code has not been tested, so there might be typos. Just as a basis.
Re: Count values in table field - after insert
Posted: 2019-10-07 21:55
by vladimir
I checked and you got it. Just changed the last row to this.
Code: Select all
sqlvalue("UPDATE Orders set OrderNo = '{$new_orderno}' where `id_orders` = {$data['selectedID']}");
Thanks!