Jump to content
  • 0

Create a task to update a table field with the running total (aka sum)



Hi Hi,

I don't want to add a running total to my datapage.  Lots of examples on doing that.   I actually want to add the running total to my table.  I can use a task that I will run periodically to update my running sum.  I have a table with 4 fields: Order Date, Order #, Dollar Value, Running Sum of Dollar Value.  I want to sort by date and then calculate the running sum for each record.   Thank you in advance.

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 1

I am afraid that here we shift in conceptual point of views.
The detail you added shows a table (the one I used and that is a tipical concept of DBMS) that should bheave like a sheet (that is a tipical concepts of spreadsheets software).
While spreadsheets easly refer data as-you-see and have primitives able to handle the previous-next concept or allow you to refer data by-cell, DBMS can have a likewise functionality implementing indexes. So the prev-next record is non necessary the prev-next record you see displayed but the one according the index you are referring to....
Speaking SQL, we can obtain the result you want in this way:

SELECT  index_key,
        sum ( dollar_value ) over ( order by index_key rows unbounded preceding ) sum_of_dollar_value
FROM Test_ordes;

but I am afraid this is not possible to use in CASPIO.
The workaround you could use is to add one more colum to the table that make the index of your table
The change the task in this way:


When you run it you get:


ATTENTION: I think this is not a solution. It can be used as workaround if you consider:

  1. Values are not updated in realtime when you add a record, but only after the task runs
  2. We do not know what is the limits or constrains that could be arise when the table has a lot of records (perfomance? timeout?)
  3. The first to SET statements is the only way I found to initialize the variable. If you omit the first one the variable is instantiated as an integer and all decimals are lost
  4. The For statement cannot be done on the table, but on the SELECT result because you need to consider the ORDER BY
  5. The WHERE specification is needed otherwise you get the result of the last sum on all records.....

I say again low-code bring an encapsulation of the below infrastructure and some capabilities you have using it directly are hided.

Mark this as solved if you like.


Link to comment
Share on other sites

  • 0

Please mark as best answer if this is what you are looking for.
I defined this table:


where there are 2 orders (OR-001 and OR-002) whit some items. Then I defined this task:


After running the task the table is changed in this way:


Note the totals in sum_of_dollar_value.
Is this what you need?

Link to comment
Share on other sites

  • 0

You clearly have the expertise to get the right answer.  Here's further clarification pasted below.  The running sum should ignore any changes in order_nu or order_item.  The task should sort by date then provide the running sum as shown below.   Thank you for your help thus far.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...