Running Totals (kind of...)

[Lynda]

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?

Lynda

[Aether]

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/

[Lynda]

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.

 

Lynda