2. PHPRunner General questions
  3. Storing report grouptotals in a field
This topic is locked

Storing report grouptotals in a field

 		8/3/2008 8:53:06 AM
PHPRunner General questions
M
 motfuz author

I want to store Subtotaal, BTW and factuurbedrag of every bedrijfsnaam (customer) into seperate fields into a table X. Table X has fields called X_Subtotaal, X_Btw, X_Factuurbedrag and X_bedrijfsnaam. How can I do that?
SELECT

SUM(_tbl_verkoop.v_aantal_tbl_product.p_tarief) AS Subtotaal,

SUM(_tbl_verkoop.v_aantal_tbl_product.p_tarief 0.19) AS Btw,

SUM(_tbl_verkoop.v_aantal_tbl_product.p_tarief * 1.19) AS Factuurbedrag,

`_tbl_klant`.bedrijfsnaam,

`_tbl_product`.p_omschrijving,

`_tbl_verkoop`.v_aantal,

`_tbl_product`.p_tarief

FROM `_tbl_verkoop`

INNER JOIN `_tbl_product` ON `_tbl_verkoop`.v_productkoppel = `_tbl_product`.p_id

INNER JOIN `_tbl_klant` ON `_tbl_verkoop`.v_klantkoppel = `_tbl_klant`.id

GROUP BY `_tbl_klant`.bedrijfsnaam, `_tbl_product`.p_omschrijving, `_tbl_verkoop`.v_aantal, `_tbl_product`.p_tarief
Greetings from the Netherlands,
Wim
J
 		Jane 8/4/2008

Wim,
to save calculated values into another table use Before record added/updated events and Save new data in another table action on the [b]Events tab.
M
 		motfuz author 8/4/2008

Okee, thank you. Does it also works with the aliases?

Wim,

to save calculated values into another table use Before record added/updated events and Save new data in another table action on the [b]Events tab.
J
 		Jane 8/4/2008

Aliases do not save calculated values in the fields. You can only show it on the list/view pages.
M
 		motfuz author 8/4/2008

Aliases do not save calculated values in the fields. You can only show it on the list/view pages.


With this code the alias is stored into the _tbl_factuurtotaal, but he (or she uhum) stored only the record totals, not the grouptotals. Is there another way to store grouptotals?. I need this to create an invoice for each client. Every client can have one or more orders. Maybe there is a better way to create invoices.
When I stored the date field 'v_factuurdatum' into ft_factuurdatum he inserts 0000-00-00. What am I doing wrong?
global $conn;$strTableName;

$strSQLSave = "insert into _tbl_factuurtotaal (ft_klantkoppel, ft_subtotaal, ft_btw, ft_factuurbedrag, ft_factuurdatum) values (";

$strSQLSave .= $values["v_klantkoppel"].",";

$strSQLSave .= $values['Subtotaal'].",";

$strSQLSave .= $values['Btw'].",";

$strSQLSave .= $values['Factuurbedrag'].",";

$strSQLSave .= $values['v_factuurdatum'];

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
J
 		Jane 8/5/2008

Hi,
MySQL use following date format: yyyy-mm-dd. Also you need to add single quotes around text and date field in teh SQL query.

Try to use this code:

global $conn;$strTableName;

$strSQLSave = "insert into _tbl_factuurtotaal (ft_klantkoppel, ft_subtotaal, ft_btw, ft_factuurbedrag, ft_factuurdatum) values (";

$strSQLSave .= $values["v_klantkoppel"].",";

$strSQLSave .= $values['Subtotaal'].",";

$strSQLSave .= $values['Btw'].",";

$strSQLSave .= $values['Factuurbedrag'].",'";

$strSQLSave .= date("Y-m-d",strtotime($values['v_factuurdatum']));

$strSQLSave .= "')";

db_exec($strSQLSave,$conn);
M
 		motfuz author 8/5/2008

Hallo Jane,
Didn't work. No errors, but still the zero's. Do you have another solution? I hope so.
Do you have tips for creating invoices? Is it only possible with changing the output htm files? I hope there is an easier sollution.
Greetings,
Wim

Hi,

MySQL use following date format: yyyy-mm-dd. Also you need to add single quotes around text and date field in teh SQL query.

Try to use this code:
J
 		Jane 8/5/2008

Wim,
to debug your code print executed query before:

echo $strSQLSave;



Then execute it in teh database directly.
If it doesn't help publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.