Running Total in Repeating Panel

[paul1w1]

Hi,
I am trying to create a Repeating Panel to show invoices issued and payments received, with a running total (balance) column. This must be a common requirement for databases, but I can’t find a solution. I presume that’s because it is an easy set up for most Python users but beyond my beginner capabilities.

I have made an attempt at it but my running total is one line out of sync as it always calculates the first value as zero. I would appreciate any advice to point me in the right direction of how to code this properly.

Thank you.

[stefano.menci]

The last line should be =sum(rt) + credit - debit.

Also, the else: next should be removed.

… but I would add to the running total of the last invoice instead of calculating it every time. This way you only analyze one row instead of scanning the whole table.

Assuming that inv_no is a numerical column and the last invoice has the highest number, this should work:

last_invoice = app_tables.transactions.search(tables.order_by('inv_no', ascending=False)[0]
running_total = last_invoice['running_total'] + credit - debit

If inv_no is not an incremental number, then you can find the latest invoice by sorting by date.
If date doesn’t work either, then you could store the running total in its own table.

[paul1w1]

Hi Stefano
Thank you for your help. That works great.
I had it to calculate the running total each time because I will have different clients in the repeating table and I want to be able to filter on each client and just calculate the running total for that client. I expect there is a way to do that more simply, though?
Thanks,
Paul

[stefano.menci]

I never pass row objects to the client and I store only the info that needs to be stored. So I would get rid of the balance and running_total columns.

Then, when it’s time to you get the content for the datagrid, I would add the calculated columns:

rows = []
for row in app_tables.transactions.search():
    row = dict(row)
    row['balance'] = row['credit'] - row['debit']

As for the running totals per client, I would need more details, because I don’t see how you are doing that.

Please paste the code wrapped by ```, don’t use a snapshot, so I can copy/paste and edit your code instead of rewriting all from scratch.

[paul1w1]

Hi,
If I understand your last post, I should not have a client side datagrid with a list of all clients constantly loaded. Instead it would be better to just have some sort of client side search function where I enter search criterea for the client I am interested in and then just load the data for that one client?

Regarding the running totals, you can see in the database table that the running total calculation is working great, thank you, but I would also like to know if I can do a running total for each client. I think I need to store these client specific running totals in the table because I would like to be able to produce a (maybe yearly) statement for each client and the opening balance (running total) would be the closing balance (running total) from the previous year.

I hope that makes sense and thank you in advance.

Regards,
Paul

########## - ADD NEW TRANSACTION - ##########
@anvil.server.callable
def new_transaction(date, inv_no, client, credit, debit, balance, running_total):
    rt = []
    for row in app_tables.transactions.search():
      amount = row['balance']
      if amount!=0:
        rt.append(amount)
      
    
    app_tables.transactions.add_row(
    date=date, 
    inv_no=inv_no, 
    client=client, 
    credit=int(credit), 
    debit=int(debit), 
    balance=credit-debit,
    running_total= sum(rt) +credit -debit
    )

[p.colbert]

Storing running totals in the database sounds great… but are you sure that nobody will ever have to update or delete a mistaken entry? Add a missing one?

That would cause all subsequent running totals to be off. You’d have to add code to re-calculate them, whenever and wherever a correction occurs. Whereas, if the calculation is postponed until these derived values are actually used, you can be sure that they’re accurate.

[paul1w1]

Thank you for your message. I had thought about this and I agree that it is a problem. Deletions would have to be done by entering a credit/adjustment amount. Not ideal.
My problem is that if in 5 years time I wanted to produce a statement for that year, it would have to run through all the entries for the last 5 years (from the first entry) for that client. Would that be more efficient.
Many thanks,
Paul

[p.colbert]

My question was not about efficiency, but reliability. It’s like the old joke between two programmers:

1. “My version runs 100 times as fast as yours!”
2. “Yeah, but mine gets the right answer.”

In this case the tradeoff is about the extra code required for maintaining the running totals. If you keep running totals in the database, then you have to have code to set the values, and to fix them when things go wrong. That code (or some of it) takes time to run on every change to the numbers, whether you’re actually using those running totals (that time) or not.

You also have to make sure that, whenever the database structure changes, that the code for maintaining the running totals is also updated to match – and re-tested.

It’s up to you to weigh that time and effort, against how often the running totals are actually used for anything.

Perhaps only the most recent total is used for decisionmaking (e.g., to prevent overdrafts), and so the rest can be re-created on demand.

To reduce that effort, you might preserve only some of the running totals, e.g., end-of-month totals. Recreating a month’s running totals then only needs one month’s data: that month’s previous month-end total, and that month’s activity.

[paul1w1]

Many thanks for that. Very helpful advice. Actually, each client will not have a large number of invoices and statements will not be required very often, so calculating as needed is probably thr right way to go.

[paul1w1]

Hi,
Sorry to come back to this, but I am still having problems. I agree with your suggestion that a running total does not need to be stored in the database and should be calculated when needed. I want to be able to generate a statement showing each invoice (debit) and payment received (credit) with a running total on each line (like a bank statement). So, I have a ‘running total’ column in the repeating panel but not in the database table. and presume I need to do the calculation client side, but I can’t seem to achieve this.
Any advice would be appreciated.
Thanks in advance.

[stefano.menci]

My previous answer show how to add running totals: Running Total in Repeating Panel - #4 by stefano.menci

Is that a good solution?

[paul1w1]

Hi, yes thank you. It was working well run on the server but I am having problems trying to code it to run client side. I choose a contact from a dropdown and the transactions for that contact only are then displayed in the repeating panel. I just don’t know how to then incorporate the running total code. If I understand correctly, a repeating panel is not iterable? Thanks and sorry for all the questions. Still a newbie.

    self.drop_down_contacts.items = [(r['name'], r) for r in app_tables.contacts.search()]
    
    #### POPULATE REPEATING PANEL WITH TRANSACTIONS OF CONTACT SELECTED FROM DROPDOWN ####
  def btn__click(self, **event_args):
    data = self.repeating_panel_1.items = app_tables.transactions.search(contact = self.drop_down_contacts.selected_value)```

[anthonys]

@paul1w1

l run on the server but I am having problems trying to code it to run client side. I choose a contact from a dropdown and the transactions for that contact only are then displayed in the repeating panel. I just don’t know how to then incorporate the running total code. If I understand correctly, a repeating panel is not iterable? Thanks and sorry for all the questions. S

@stefano.menci 's example was on the server side before it gets returned to the client side. So you have access to it without any calculation.

If you want to do it on the client side you would need to access the repeating panel row template and update the refresh_data_bindings event handler to calculate the total.

  def form_refreshing_data_bindings(self, **event_args):
    """This method is called when refreshing_data_bindings is called"""
    self.item['balance'] =  self.item['credit']-self.item['debit']

I think this is slower than doing it on the server, but would need to time it

[george.a.alexopoulos]

@stefano.menci

for row in app_tables.transactions.search():
    row = dict(row)
    row['balance'] = row['credit'] - row['debit']

Great advice! Thank you

[paul1w1]

Hi,
Thank you for your message.
I don’t really mind if I do this server or client side. I just thought it would have to done client side if the values for ‘balance’ and ‘running total’ are being calculated for the repeating panel and not saved in the table.
Actually, it is the ‘running total’ calculation that is frustrating me: how to calculate it to appear in the repeating panel without saving in the database table.

[p.colbert]

@paul1w1

how to calculate it to appear in the repeating panel without saving in the database table.

I do this sort of thing regularly. Instead of connecting the repeating panel directly to the search() iterator, I set up an intermediary object for the repeating panel to read: a list of dicts. The intermediary can have whatever details I want to display.

[paul1w1]

Hi,
I have finally got the running total calculation to work (not sure if it is a good way) doing as you say, creating a list of dicts. However, I am not sure how to then load this data into the repeating panel. I added the print(row) at the end of the section of code just to check that it works.

Thanks in advance.

def load_transactions(contact):
  rt = []
  for row in app_tables.transactions.search(contact=contact):
    row = dict(row)
    row['balance']=row['credit']-row['debit']
    amount = row['balance']
    if amount!=0:
        rt.append(amount)
    row['running_total'] = sum(rt)
    print(row) ```

[p.colbert]

A list of dicts is exactly the format that repeating_panel.items is expecting! See Getting the Data in

[ianbuywise]

I think the only problem with your code is you aren’t storing the list of dicts anywhere:

@paul1w1

def load_transactions(contact):
  rt = []
  for row in app_tables.transactions.search(contact=contact):
    row = dict(row)
    row['balance']=row['credit']-row['debit']
    amount = row['balance']
    if amount!=0:
        rt.append(amount)
    row['running_total'] = sum(rt)
    print(row)

Try this:

def load_transactions(contact):
  rt = 0
  rows = []
  for row in app_tables.transactions.search(contact=contact):
    row = dict(row)
    row['balance']=row['credit']-row['debit']
    amount = row['balance']
    if amount!=0:
        rt += amount
    row['running_total'] = rt
    rows.append(row)
    #print(row)

  return rows

@p.colbert

repeating_panel.items

Then set this = to the result of load_transactions(contact)

On another note, you might want to mix an order_by statement into your .search() if you have any kind of transactions that happened in a specific order, or have timestamps, when doing a running total.

[ianbuywise]

@ianbuywise

    amount = row['balance']
    if amount!=0:
        rt += amount

Also I would really write this bit like:

try:
     rt += round(row['balance'], 3) # or 2 up to you
except TypeError:
  # don't use a bare exception block
  ...