Updating field with most recent order

[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 32780 times

here.jpg (36.32 KiB) Viewed 32780 times

[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

[lmire]

Hello. Thank you for the response.

This is the edited code I pasted into orders.php

php.png (7.86 KiB) Viewed 32766 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?

[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/

[pbottcher]

Agree with Jan,
Try

Code: Select all

sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']}."' WHERE `login` = '".$data['account']."'", $eo);

[lmire]

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 ','

[pbottcher]

sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `login` = '".$data['account']."'", $eo);

[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?

[pbottcher]

Hi,

can you post your tabledefinitions? Are you using any lookups? Are the accounts login and the Orders Account the same?

[lmire]

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 32745 times

[lmire]

TABLE
Orders
FIELD
Account

Lookup

TABLE
Accounts
FIELD
Login

[pbottcher]

sql("UPDATE accounts SET `LastOrderDate` = '".$data['LastOrderDate']."' WHERE `id` = '".$data['account']."'", $eo);

assuming that id is the PK.

[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.

[D Oliveira]

Code: Select all

sql("UPDATE Accounts SET LastOrderDate = '{$data['LastOrderDate']}' WHERE Login = '{$data['Account']}'", $eo);

[lmire]

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.

[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.

[pbottcher]

Hi,

please post the fields of your database, otherwise it is only stumling in the dark. Please with upper/lowercase.

[lmire]

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 32691 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.

[lmire]

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.

[D Oliveira]

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);

[D Oliveira]

TABLE
Orders
FIELD
Account

Lookup

TABLE
Accounts
FIELD
Login

this would be the right structure:

TABLE
Orders
FIELD
Account

Lookup

TABLE
Accounts
FIELD
id

[pbottcher]

ok, we clarified this offline.

Result.

Code: Select all

sqlvalue("UPDATE Accounts SET `Balance` = Balance-'".$new_balance."' WHERE `id` = '".$data['Account']."'");

works fine.