Page 1 of 1
Updating field with most recent order
Posted: 2020-02-27 14:30
by lmire
I'm trying to figure out how to update a field automatically to include the date of the most recent order.
I have two tables ACCOUNTS and ORDERS.
The ORDERS table has a FIELD named LastOrderDate which automatically date&time stamps an entry into ORDERS. (<%%creationDateTime%%>)
Does anyone know how I can update the LastOrderDate field in the ACCOUNTS table to show the most recent entry from the ORDERS table.
I've tried using multiple SQL statements in the calculated field tab, but I can't seem to figure out the correct command. Any help would be appreciated.
- here.jpg (36.32 KiB) Viewed 35529 times
- here.jpg (36.32 KiB) Viewed 35529 times
Re: Updating field with most recent order
Posted: 2020-02-27 15:17
by D Oliveira
hooks/orders.php
after insert and after update functions, paste this code:
Code: Select all
sql(“UPDATE accounts SET ‘LastOrderDate’ = ‘{$data[‘LastOrderDate’]}’ WHERE ‘login’ = ‘{$data[‘account’]’â€, $eo);
this code is case sensitive so make the appropriate changes
Re: Updating field with most recent order
Posted: 2020-02-27 16:34
by lmire
Hello. Thank you for the response.
This is the edited code I pasted into orders.php
- php.png (7.86 KiB) Viewed 35515 times
This is the error I'm getting when trying to view orders.php
Parse error: in C:\xampp\htdocs\test\hooks\Orders.php on line 239
I've tried different variations of uppercase and lowercase. Is my syntax correct?
Re: Updating field with most recent order
Posted: 2020-02-27 17:13
by jsetzer
Maybe there is a problem with the qoutes?
A couple of days ago I have published a short explanation here:
https://appgini.bizzworxx.de/appgini/qu ... -commands/
Re: Updating field with most recent order
Posted: 2020-02-27 17:56
by pbottcher
Agree with Jan,
Try
Code: Select all
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);
Re: Updating field with most recent order
Posted: 2020-02-27 19:10
by lmire
pböttcher wrote: ↑2020-02-27 17:56
Agree with Jan,
Try
Code: Select all
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);
returns
Parse error: syntax error, unexpected '}', expecting ')'
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate'])."' WHERE `login` = '".$data['account']."'", $eo);
returns
Parse error: syntax error, unexpected ','
Re: Updating field with most recent order
Posted: 2020-02-27 19:41
by pbottcher
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `login` = '".$data['account']."'", $eo);
Re: Updating field with most recent order
Posted: 2020-02-27 20:20
by lmire
Thank you for the help guys. No more parsing errors, but the LastOrderDate column still isn't being updated on the Accounts page. Any ideas?
Re: Updating field with most recent order
Posted: 2020-02-27 20:33
by pbottcher
Hi,
can you post your tabledefinitions? Are you using any lookups? Are the accounts login and the Orders Account the same?
Re: Updating field with most recent order
Posted: 2020-02-27 20:54
by lmire
pböttcher wrote: ↑2020-02-27 20:33
Hi,
can you post your tabledefinitions? Are you using any lookups? Are the accounts login and the Orders Account the same?
Yes, I'm using lookups. I should have mentioned that initially.
- lookup.jpg (52.83 KiB) Viewed 35494 times
Re: Updating field with most recent order
Posted: 2020-02-27 21:01
by lmire
TABLE
Orders
FIELD
Account
Lookup
TABLE
Accounts
FIELD
Login
Re: Updating field with most recent order
Posted: 2020-02-27 21:18
by pbottcher
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `id` = '".$data['account']."'", $eo);
assuming that id is the PK.
Re: Updating field with most recent order
Posted: 2020-02-27 21:42
by lmire
Yes, id is the Primary Key.
I dropped the DB and created from scratch. Reinserted the code into Orders.php.
LastOrderDate still isn't being populated on the Accounts page.
Re: Updating field with most recent order
Posted: 2020-02-28 01:57
by D Oliveira
Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE Login = '{$data['Account']}'", $eo);
Re: Updating field with most recent order
Posted: 2020-02-28 02:14
by lmire
D Oliveira wrote: ↑2020-02-28 01:57
Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE Login = '{$data['Account']}'", $eo);
It still isn't posting anything to the Accounts page.
Re: Updating field with most recent order
Posted: 2020-02-28 06:05
by jsetzer
Maybe you should var_dump($data['Account']); exit();
...and check if that really matches any Login.
If your where clause does not find any record, nothing will be updated.
Re: Updating field with most recent order
Posted: 2020-02-28 09:36
by pbottcher
Hi,
please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.
Re: Updating field with most recent order
Posted: 2020-02-28 17:33
by lmire
pböttcher wrote: ↑2020-02-28 09:36
Hi,
please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.
- myfields.jpg (80.17 KiB) Viewed 35440 times
https://drive.google.com/open?id=1V_RV9 ... r0eEfxHHYU
Here is the link to my APX file. It's a very simple DB.
I am also willing to pay for paid customization, if anyone here is interested. I have contacted the official support channel for paid customization, but he is unfortunately too busy to accept any additional requests at the moment.
I need 3 or 4 minor changes made, not a large project.
Re: Updating field with most recent order
Posted: 2020-02-28 17:33
by lmire
jsetzer wrote: ↑2020-02-28 06:05
Maybe you should var_dump($data['Account']); exit();
...and check if that really matches any Login.
If your where clause does not find any record, nothing will be updated.
Thank you for your help, but I do not understand what this means. I am requesting paid support.
Re: Updating field with most recent order
Posted: 2020-02-28 20:04
by D Oliveira
lmire wrote: ↑2020-02-28 17:33
pböttcher wrote: ↑2020-02-28 09:36
Hi,
please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.
myfields.jpg
https://drive.google.com/open?id=1V_RV9 ... r0eEfxHHYU
Here is the link to my APX file. It's a very simple DB.
I am also willing to pay for paid customization, if anyone here is interested. I have contacted the official support channel for paid customization, but he is unfortunately too busy to accept any additional requests at the moment.
I need 3 or 4 minor changes made, not a large project.
last try
Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE id = '{$data['Account']}'", $eo);
Re: Updating field with most recent order
Posted: 2020-02-28 20:05
by D Oliveira
lmire wrote: ↑2020-02-27 21:01
TABLE
Orders
FIELD
Account
Lookup
TABLE
Accounts
FIELD
Login
this would be the right structure:
TABLE
Orders
FIELD
Account
Lookup
TABLE
Accounts
FIELD
id
Re: Updating field with most recent order
Posted: 2020-03-01 16:07
by pbottcher
ok, we clarified this offline.
Result.
Code: Select all
sqlvalue("UPDATE Accounts SET `Balance` = Balance-'".$new_balance."' WHERE `id` = '".$data['Account']."'");
works fine.