calculate previous balance

[GT73]

I have three fields, Amount (dbfield), Previous Amount (customField-> Balance - Amount), Balance (customField-> sum all amounts). How can I calculate the previous amount on each line? If I do Balance - Amount will be good only for the last line ...

[sangnandar]

First, this is very important: bear in mind that database works per RECORD BASIS.

If you work with MySQL >8 you can use window-functions SUM() OVER...
Otherwise you need to call (sum_all_amounts) for each row using,
Row_Rendered()
$val = ExecuteScalar("call your value");

Note that calling value for each row, not to mention using sum() which is actually group by, is very expensive.
I would suggest to upgrade your machine to MySQL >8 or related MariaDB (I forget which version but they do have this function too).

[eayvl]

I did the following and it works for me,

create a view and generate your SQL (you can create the operation that you need)...

[GT73]

I'm using MySql 5.6 in hosting. Then I can't use something like that
SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY ID)

I've already sum all value from custom field whit custom field
custom field
c_Totali->expression->(select ifnull(sum(movcampagna.NumeroPunti),0) from movcampagna where movcampagna.Cliente = movimenti.idCliente)
and add this in Row_Rendered

// set previous balance
$punti = $this->NumeroPunti->CurrentValue;
$totali = $this->c_Totali->CurrentValue;
$saldopre = $totali - $punti;
$this->c_SaldoPre->ViewValue = $saldopre;

sangnandar
->Note that calling value for each row, not to mention using sum() which is actually group by, is very expensive.

this policy applies only to the rows displayed in the listpage right? or for all the rows of the table?

[sangnandar]

Only to rows displayed in List Page.

[GT73]

I still haven't figured out how I can get the previous balance in Row_Rendered () my code is wrong !?

[sangnandar]

Enabled debug and see if you have sql error.

[GT73]

ok works thanks