Little help setting variable in repeat

Wappler General Need Help
1

Hi
I am having a right dumb moment but cant get this to work.

I have quote a complex sql statement doing a union on 2 tables of invoices and payments to produce me a nice statement of account. a bit like a bank statement, i display these rows in a repeat, that all works really nicely, but, i want the final column to be a running total, i.e it starts at 0, an invoice is issued for 10 so the final column has 10, then a payment of 10 comes in so its back to 0, like a bank statment, i cannot do this in the sql with the union so want to do it as a variable in the repeat.

so i set a variable at the top of my code varBalance and set it to 0.

then in the repeat I am trying to simply do varBalance = varBalance + Payment - Invoice which should give me the running total

in the repeat I have this

<dmx-value id="varBalance" varBalance.setValue={{varBalance.value-rowtotal}}></dmx-value>

i have tried all sorts of comments and searched to death and reads loads of forum posts but none the wiser.

thanks if anyone knows how to reset the variable in the repeat, many thanks in advance

2 
<dmx-value id="varBalance" varBalance.setValue={{varBalance.value-rowtotal}}></dmx-value>

This is not correct variable value…

Change it to:

<dmx-value id="varBalance" dmx-bind:value="varBalance.value.toNumber()-rowtotal"></dmx-value>

WHAT IS rowtotal?

3

thanks, rowtotal is one of the repeat values from my query. actually want the varbalance - rowtotal + amount. but when i enter that it keeps on counting the number endlessly and i have to force close

thanks

4

I have seen so many ways for this type of calculation to go wrong when generated client side, even once you get it working there are a lot of edge cases to consider. If your DB supports it, I’d suggest letting your DB provide the subtotal value for you:

ROLLUP is usually the function I use, but it isn’t available on some root-restricted installs / very old systems, so the learnsql link above offers a simple way to do it using SELECT SUM AS

5

hi, thanks couldnt reply sooner as the community board was being updated.

yeah, the sql is a bit complex to try to do it in sql, as its a union and also, the balance i want to show is not a column to sum on, its the sum of values, so it would start at 0, then either get an invoice amount added to it or a paid amount taken from it, the only way to do this in sql is using variables, which i think is being deprecated in mysql and not sure wappler would support it as such, i did try it and it didnt like it

really the simple way would be using a variable in the code, so as you loop through the repeater of rows you do a simple sum on the variable in php, add or delete the row value to give the row balance.

but i just cannot get it to work, it keeps giving a looping value that hangs my browser.

@Teodor - is there a way to do a sum on a variable in a repeat. so, i create a variable like varBalance = 0 then in the repeat i have 2 values, invoicetotal and paidtotal, i want to do varBalance = varBalance + invoiceTotal - PaidTotal

but i can’t seem to get it to work, maybe as the id is the same in the repeat on the variable?

thanks

6

I read your post a few times but i struggle to understand what are you trying to achieve.
What exactly do you want to calculate? Are these values from the database that you want to sum in the repeat?

7

Hi, sorry I will try to explain more clearly.

I have a query that runs that returns invoiceTotal and paidTotal as values.

The values from from the database query.

I have a page that looks like a bank statement that gives details of invoices and payments. I use a repeat to go through the query recordset.

that all works, but, I would love to have a column on the end the gives the running balance.

So

row1 invoicetotal: 100, Balance: 100
row2 paidtotal:50, Balance: 50
row3 paidTtoal 50, Balance 0
row4 invoicetotal 22.99, Balance 22.99
row5 invoicetotal 10.00, balance 33.99

i get my recordset using this sql

select x.id, x.client_id, x.date as 'Date', x.service as 'Desc', x.storagefrom, x.storageto, x.cost, x.quantity, x.discount, x.rate, x.rowtotal, x.amount
from 
(select 
i.id,
 i.client_id,
i.Date,  
s.Service,
id.storagefrom,
id.storageto,
id.cost,
id.quantity,
id.discount,
t.rate,
id.RowTotal,
null as Amount
from InvoiceDetails id
inner join `Invoices` as `i` on `i`.`id` = `id`.`Invoice_id` 
inner join `Services` as `s` on `s`.`id` = `id`.`Service_id` 
inner join TaxRates as t on id.taxrate_id = t.id
union all
select 
p.id,
 p.Client_id,
p.date, 
'Payment' as Payment,
null as storagefrom,
null as storageto,
null as cost,
null as quantity,
null as discount,
t.rate,
null as RowTotal,
p.Amount
from Payments p 
inner join TaxRates as t on p.taxrate_id = t.id
) x
where x.client_id = ?
order by x.date, x.id

the sql works, i get my rows with the correct data in my statement, i would just love a column on the end that gives the running balance. I thought it would be quite simple to give the balance column programatically but it isnt (for me)

thanks

8

where does the invoicetotal comes from? I don’t see anything like this returned by your database query?
Also which of the values would you like to calculate for the ballance - is it only the rowtotal?

9

its ROWTOTAL and AMOUNT - I know my naming is way off, things evolve lol

ROWTOTAL is the invoice line item total (ihvoiceTotal)

AMOUNT is the amountPaid

thanks

10

So what are the exact calculations you need with the RowTotal and Amount values returned from your query per each row?

11

varBalance = varBalance + rowtotal - amount

in my mind the above will add to balance if its an invoice line items, but not take anythign way as that row will only have rowtotal or amount, but if its on a line with amount it will add nothing and take away the amount

rowtotal or amount can be null, as its a union sql, I could change the null to output 0, but it would not look as good on the page.

12

From what i understand you need to add a variable varBallance in the repeat, but it also needs to use the value of the previous record in the repeat.
I think this should do what you need:

<dmx-value id="varBallance" dmx-bind:value="items[$index-1].varBallance.value + (RowTotal-Amount)"></dmx-value>
1 Like
13

thanks, let me give that a whirl, its what I had, but was missing the items index part, thanks

14

superstar, that got it, thanks for much

15

one last thing. I allow the user to download their statement as a pdf. At the moment the pdf creator does not work on sections/containers etc, so, I have to repeat the same code that I have in the section into a table repeat.

How would I use the varbalance in the table row from a repeat table?

exact same data/equation, but in the table repeat row-column (wish pdf creator worked on sections etc to save this duplication but thats another issue)

thanks

16

It is the same if you are using table. The table itself is using the same repeat children.

17

not sure i quite get it, my table is not what i show to the user on the browser, i use section/container/row for that, i then have a visually hidden table that i use for the pdf download, so the table has

                            <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="SC_StatementSel.data.query_StatementSel" id="tableRepeat1">
                                <tr>
                                    <td class="text-small" dmx-text="Date"></td>
                                    <td class="text-small" dmx-text="Desc"></td>
                                    <td class="text-small" dmx-text="storagefrom"></td>
                                    <td class="text-small" dmx-text="storageto"></td>
                                    <td dmx-text="cost" class="text-small text-end"></td>
                                    <td dmx-text="quantity" class="text-small text-end"></td>
                                    <td dmx-text="discount" class="text-small text-end"></td>
                                    <td dmx-text="rate" class="text-small  text-end"></td>
                                    <td dmx-text="rowtotal" class="text-small text-end"></td>
                                    <td dmx-text="amount" class="text-small text-end"></td>
                                    <td dmx-text="amount" class="text-small text-end"></td>
                                </tr>
                            </tbody>

so its that last row/cell i want to put the varballance into, but that varbalanec is in the repeater for the container.

18

You need to use the same variable in your table. You can't just take the variable values from another repeat.

19

this is what i have in the table repeat (the table repeat uses same query but is seperate from the repeat i use for the container. i hide the table but use it in the pdf creator

i renamed the variable id

                            <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="SC_StatementSel.data.query_StatementSel" id="tableRepeat1">
                                <dmx-value id="varBalanceTable" dmx-bind:value="items[$index-1].varBalanceTable.value + (rowtotal-amount)"></dmx-value>
                                <tr>
                                    <td class="text-small" dmx-text="Date"></td>
                                    <td class="text-small" dmx-text="Desc"></td>
                                    <td class="text-small" dmx-text="storagefrom"></td>
                                    <td class="text-small" dmx-text="storageto"></td>
                                    <td dmx-text="cost" class="text-small text-end"></td>
                                    <td dmx-text="quantity" class="text-small text-end"></td>
                                    <td dmx-text="discount" class="text-small text-end"></td>
                                    <td dmx-text="rate" class="text-small  text-end"></td>
                                    <td dmx-text="rowtotal" class="text-small text-end"></td>
                                    <td dmx-text="amount" class="text-small text-end"></td>
                                    <td dmx-text="varBalanceTable.value" class="text-small text-end"></td>
                                </tr>
                            </tbody>
                        </table>

despite being the same code for the variable, but in the table repeat it says its NaN, not a number.

20

Try this:

 <tbody is="dmx-repeat" dmx-generator="bs5table" dmx-bind:repeat="SC_StatementSel.data.query_StatementSel" id="tableRepeat1">
                                <tr>
                                    <td class="text-small" dmx-text="Date"></td>
                                    <td class="text-small" dmx-text="Desc"></td>
                                    <td class="text-small" dmx-text="storagefrom"></td>
                                    <td class="text-small" dmx-text="storageto"></td>
                                    <td dmx-text="cost" class="text-small text-end"></td>
                                    <td dmx-text="quantity" class="text-small text-end"></td>
                                    <td dmx-text="discount" class="text-small text-end"></td>
                                    <td dmx-text="rate" class="text-small  text-end"></td>
                                    <td dmx-text="rowtotal" class="text-small text-end"></td>
                                    <td dmx-text="amount" class="text-small text-end"></td>
                                    <td class="text-small text-end"> 
                                        <dmx-value id="varBalanceTable" dmx-bind:value="items[$index-1].varBalanceTable.value + (rowtotal-amount)"></dmx-value>
                                        {{varBalanceTable.value}}
                                    </td>
                                </tr>
                            </tbody>
1 Like