Updating field with most recent order
Updating field with most recent order
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.
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.
- Attachments
-
- here1.jpg (32.02 KiB) Viewed 32833 times
- D Oliveira
- AppGini Super Hero
- Posts: 348
- Joined: 2018-03-04 09:30
- Location: David
Re: Updating field with most recent order
hooks/orders.php
after insert and after update functions, paste this code:
this code is case sensitive so make the appropriate changes
after insert and after update functions, paste this code:
Code: Select all
sql(“UPDATE accounts SET ‘LastOrderDate’ = ‘{$data[‘LastOrderDate’]}’ WHERE ‘login’ = ‘{$data[‘account’]’â€, $eo);
Re: Updating field with most recent order
Hello. Thank you for the response.
This is the edited code I pasted into orders.php
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?
This is the edited code I pasted into orders.php
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
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/
A couple of days ago I have published a short explanation here:
https://appgini.bizzworxx.de/appgini/qu ... -commands/
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Updating field with most recent order
Agree with Jan,
Try
Try
Code: Select all
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);
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.
Re: Updating field with most recent order
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);pböttcher wrote: ↑2020-02-27 17:56Agree with Jan,
Try
Code: Select all
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
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `login` = '".$data['account']."'", $eo);
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.
Re: Updating field with most recent order
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
Hi,
can you post your tabledefinitions? Are you using any lookups? Are the accounts login and the Orders Account the same?
can you post your tabledefinitions? Are you using any lookups? Are the accounts login and the Orders Account the same?
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.
Re: Updating field with most recent order
TABLE
Orders
FIELD
Account
Lookup
TABLE
Accounts
FIELD
Login
Orders
FIELD
Account
Lookup
TABLE
Accounts
FIELD
Login
Re: Updating field with most recent order
sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `id` = '".$data['account']."'", $eo);
assuming that id is the PK.
assuming that id is the PK.
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.
Re: Updating field with most recent order
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.
I dropped the DB and created from scratch. Reinserted the code into Orders.php.
LastOrderDate still isn't being populated on the Accounts page.
- D Oliveira
- AppGini Super Hero
- Posts: 348
- Joined: 2018-03-04 09:30
- Location: David
Re: Updating field with most recent order
Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE Login = '{$data['Account']}'", $eo);
Re: Updating field with most recent order
It still isn't posting anything to the Accounts page.D Oliveira wrote: ↑2020-02-28 01:57Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE Login = '{$data['Account']}'", $eo);
Re: Updating field with most recent order
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.
...and check if that really matches any Login.
If your where clause does not find any record, nothing will be updated.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Updating field with most recent order
Hi,
please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.
please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.
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.
Re: Updating field with most recent order
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 edited by lmire on 2020-02-28 17:34, edited 1 time in total.
Re: Updating field with most recent order
Thank you for your help, but I do not understand what this means. I am requesting paid support.
- D Oliveira
- AppGini Super Hero
- Posts: 348
- Joined: 2018-03-04 09:30
- Location: David
Re: Updating field with most recent order
last trylmire wrote: ↑2020-02-28 17:33myfields.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.
Code: Select all
sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE id = '{$data['Account']}'", $eo);
- D Oliveira
- AppGini Super Hero
- Posts: 348
- Joined: 2018-03-04 09:30
- Location: David
Re: Updating field with most recent order
ok, we clarified this offline.
Result.
works fine.
Result.
Code: Select all
sqlvalue("UPDATE Accounts SET `Balance` = Balance-'".$new_balance."' WHERE `id` = '".$data['Account']."'");
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.