-
vladimir
- Posts: 11
- Joined: 2018-12-06 22:31
Post
by vladimir » 2019-10-07 07:05
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.
-
pbottcher
- AppGini Super Hero
- Posts: 1635
- Joined: 2018-04-01 10:12
Post
by pbottcher » 2019-10-07 07:25
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()");
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
vladimir
- Posts: 11
- Joined: 2018-12-06 22:31
Post
by vladimir » 2019-10-07 08:06
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.
-
vladimir
- Posts: 11
- Joined: 2018-12-06 22:31
Post
by vladimir » 2019-10-07 08:19
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.
-
pbottcher
- AppGini Super Hero
- Posts: 1635
- Joined: 2018-04-01 10:12
Post
by pbottcher » 2019-10-07 10:14
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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
vladimir
- Posts: 11
- Joined: 2018-12-06 22:31
Post
by vladimir » 2019-10-07 21:55
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!