Count values in table field - after insert

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
vladimir
Posts: 11
Joined: 2018-12-06 22:31

Count values in table field - after insert

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.

Code: Select all

$OrderNumber++;
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
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count values in table field - after insert

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

Re: Count values in table field - after insert

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

Re: Count values in table field - after insert

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
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count values in table field - after insert

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

Re: Count values in table field - after insert

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!

Post Reply