perform calculations on database lists

LisaM

Member
Licensed User
Longtime User
Iam using Margaret's new db class module and it is fantastic I do have a query though I would like to calculate a field from my database file.
e.g each day i enter how many calories burnt during exercise.
I would like to have a label that display's the total no. of calories burnt.
How would i go about coding this?
should i use the
B4X:
db.getfield("name")
function or the
B4X:
db.PullList(dbFields() As String, Filter As String)
function

So something like:

B4X:
Sub CalorieTotals
      Dim Cal As List
      Dim Value As String
      Cal = db.PullList(Array As String("Calories"))
      For each Value + next value in list

I hope this makes sense It's late and my eyes hurt haha
I've been searching for 8hrs now and i'm not quite finding an answer to my question. (I probably have found the answer I just don't know it being a noob lol)
 

margret

Well-Known Member
Licensed User
Longtime User
OK, need a little more info.

Are you the only one in the list?

Are you just wanting all values added together?

Do you want a date range? If so, did you store a date to each record?

Do you just want a total based on your name?

Let us know. The last option looks like what you would need to do.

You could also do something like:

B4X:
Sub AddCal
   Dim CalTot As Long
   db.FirstRecord
   For l = 0 To db.Record_Count -1
      CalTot = CalTot + db.GetField("Calories")
      db.NextRecord
   Next
End Sub
 
Last edited:
Upvote 0

LisaM

Member
Licensed User
Longtime User
Thanks that code looks like it is what i am looking for. I will try it out today

Sorry for my vague post.
Basically this is my database structure
B4X:
Sub SelectdbW               'Structure for database #2 created in workarea 1
   db.CreateDataBase(1, "workout.dat", File.DirInternal, Array As String("date", "workout", "phase", "duration", "calories", "maxhr", "avghr", "completed", "skipped", "intensity"))
End Sub

for each value added to field "Calories" i would like to display the total calories in a label.
so say i have entered 3 days worth (300, 350, 400) Then my label should read 1050
I also want to do averages so what is the average value of the data added in the MaxHR. So i want to retrieve all data from the MaxHr field which may look like (180, 177, 190) my code should calulate the average (180 + 177 + 190) / 3) 3 being the list size. and my label should show 182.

There are a few values i want to pull from my databases like (Highest value in the list, finding all values that equal a certain value etc. I'm just not very good with lists i figured if i got help with one of my calculations i could figure out the rest from there :)

Thanks again for your time and help.
 
Upvote 0

LisaM

Member
Licensed User
Longtime User
It works well thanks
I added a line at the end to set the label . Now i just need to figure out the other calculations!! Thanks for pointing me in the right direction :D

B4X:
Sub AddCal
    Dim CalTot As Long
    db.FirstRecord
    For l = 0 To db.Record_Count -1
        CalTot = CalTot + db.GetField("Calories")
        db.NextRecord
    Next
   Label14.Text = CalTot
End Sub

For anyone else looking for these calculations I'll post the subs as i get them working.

B4X:
'To find the average and display it as a label.
Sub AvgMaxHR
    Dim AvgMax As Long
   Dim AC As String
    db.FirstRecord
    For l = 0 To db.Record_Count -1
        AvgMax = AvgMax + db.GetField("maxhr")
        db.NextRecord
      AC = AvgMax / db.Record_Count
    Next
   Label19.Text = AC
End Sub

B4X:
'To find the highest Value in a list
Sub HMaxHR
    Dim HMax As List
   HMax.Initialize
    db.FirstRecord
    For l = 0 To db.Record_Count -1
      HMax.Add(db.GetField("maxhr"))
      db.NextRecord
   Next
   HMax.Sort(True)
   Label21.Text = HMax.Get(1)
End Sub
 
Last edited:
Upvote 0
Top