Jump to content
  • 0

Running Totals (kind of...)



The Set Up

  • I have a table; WeightLog (WeightID, ProfileID, WeightDate, Weight)
  • In my Reports/Chart page, I list the weight entries for the Current Month. Pretty boring huh?

The Requirement

  • I need to show the weight difference between one entry and the next. So I will add another column to the right of Weight (Gain/Loss) that will show the difference between that entry and the previous entry. Values are expected to be; 1, 2, 0, -1, -2, and so on.
  • It has to be dynamic, as it needs to recalculate on Add, Delete, and Edit.


The Situation

  • I have tried Running Totals. Both the solutions from the forum and the solution from the Caspio Help Tutorial. I tried to duplicate the Help Tutorial exactly and never got it working as it had so many inconsistensies. Very frustrated.
  • The reason the Running Totals didn't work in this situation, is that, I am not carrying a "Running Balance" forward.  I need to hold and replace a value on each record.

What I've Tried

  • I added a Calculated Field (PreviousWeight) and the following calculation (from the examples for Running Balance): 

SELECT IsNull([@field:WeightLog_Weight],0) FROM PreviousWeight WHERE WeightLog_WeightID <= target.[@field:WeightLog_WeightID] AND WeightLog_ProfileID = target.[@field:WeightLog_ProfileID]

  • I could not get this calculation to validate.

Does anyone have any ideas?


Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hi @Lynda - Perhaps "PreviousWeight" on your formula is from a View, if so, you need to add _v_ at the start. Like this:

SELECT IsNull(WeightLog_Weight,0) FROM _v_PreviousWeight WHERE WeightLog_WeightID <= target.[@field:WeightLog_WeightID] AND WeightLog_ProfileID = target.[@field:WeightLog_ProfileID]

This article might help as well: https://howto.caspio.com/tech-tips-and-articles/calculate-a-running-total-in-your-report/

Link to comment
Share on other sites

  • 0

I'm updating my solutions, and wanted to post my working SQL stmt in case anyone else faced this problem in the future. This statment calculates the difference from the previous record to the current record.

([@field:WeightLog_Weight] - (SELECT Weight FROM WeightLog WHERE (ProfileID = [@field:WeightLog_ProfileID]) AND

( (SELECT TOP 1 WeightDate FROM WeightLog WHERE ProfileID = target.[@field:WeightLog_ProfileID] AND WeightDate < target.[@field:WeightLog_WeightDate]

   ORDER BY WeightDate DESC) =WeightDate)))



I hope this helps.



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...