Trying to figure this one has worn me out!
I have a DB for music CDs. Each song has its own entry. Each entry has: artist, CD title, track and track time. Track time is hh:mm:ss. There are other fields for track number, disc number (if title has more than one CD), genre, year, category, and I have a way to handle compilations so they appear as single albums.
It’s easy enough to show CDs by Artist (or compilation), then CD Title, then individual tracks.
What I’m stuck on is how to show total time per CD title. Ideally I’d like to have that included in the view described above (of course). I have tried to do that as well as a separate view which would serve only to add the time totals for the CDs, and I have had no luck (well, intelligence would be the more appropriate word) with that.
My formal Notes training ended with r.5x. I’ve been working with 6x and 7x on my own to try to avoid losing everything I learned, and as a result I find myself far too often at a knowledge dead end. It doesn’t keep me from feeling stupid, however, lol. Thanks for any help.
Subject: Time totals for Music CDs
This is some ugly code that I copied from an old app that I did some time back. It might work for you.
Sub Click(Source As Button)
Dim s As New NotesSession
Dim ws As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim secs As Long
Dim mins As Long
Dim hrs As Long
Dim songList As Variant
Dim tmpStr As Variant
Dim timeOutput As Long
Dim outStr As String
Dim tmpTxt As String
'place a routine here to load your CD times into songList() - format = hh:mm:ss
Set uidoc = ws.CurrentDocument
Set doc = uidoc.Document
songList = doc.Times
hrs = 0
mins = 0
secs = 0
Forall x In songList
tmpStr = Split(x,":")
secs = secs + Clng(tmpStr(2))
mins = mins + Clng(tmpStr(1))
hrs = hrs + Clng(tmpStr(0))
End Forall
'convert seconds to minutes
If secs > 60 Then
timeOutput = Cint(secs / 60)
secs = secs - timeOutput * 60
mins = mins + timeOutput
End If
'convert minutes to hours
If mins > 60 Then
timeOutput = Cint(mins/60)
mins = mins - timeOutput * 60
hrs = hrs + timeOutput
End If
'Combine the results back into a time format string of hh:mm:ss
tmpTxt = Cstr(hrs)
If Len(tmpTxt) < 2 Then
tmpTxt = "0" & tmpTxt
End If
outStr = tmpTxt & ":"
tmpTxt= Cstr(mins)
If Len(tmpTxt) < 2 Then
tmpTxt = "0" & tmpTxt
End If
outStr =outStr & tmpTxt & ":"
tmpTxt = Cstr(secs)
If Len(tmpTxt) < 2 Then
tmpTxt = "0" & tmpTxt
End If
outStr = outStr & tmpTxt
End Sub
Subject: RE: Time totals for Music CDs
Wow! Thank you for providing this script. Unfortunately, whatever side of my brain deals with math, apparently got dropped when I was a baby. I’ve tried but at this point am resigned that I’ll never be a programmer (a year of forced Java training made this very apparent, lol). @Formula language I can for the most part deal with, but even then, if more than basic math or programming functions (loops, etc.) are involved I have to pass the work on to someone else … all of which is to say I’m LotusScript inept.
From what I’ve puzzled out so far via formula, I can see some of what your script is doing. Embarrassingly, because I have never worked with LotusScript (beyond changing minor things in scripts someone else did and explained to me), I don’t even know where I’d plug this into my view (app?).
If you have the time and patience to explain, I’d definitely like to try to work with this. Even if the other way of doing this works, having more than one means of doing something is always useful.
Subject: Time totals for Music CDs
The issue you’ll have is that Notes does not have a data-type for duration - a number of hours/min/sec. While you can use a Time data type to store a single value, the only mathematical operation that’s allowed on them is subtraction. You cannot add Time fields together. (i.e. What is 3pm + 4pm is a meaningless question, while 4pm-3pm has a meaning … 1 hour.)
The best you could get would be to store the track times in minuets, and add those up.
Subject: RE: Time totals for Music CDs
You could store them in seconds and add them up, which would be more accurate. One thing I’ve done is to create a NotesDateTime of something like “01/01/2000 12:00:00 AM” then use NotesDatetime.AdjustSeconds to add the seconds to it, then display it as a 24-hour clock with hh:mm:ss and no date. This works fairly well for simulating a typical counter style clock.
Subject: RE: Time totals for Music CDs
Thank you! I started working on the problem with seconds yesterday and have had some success. I have some of the process worked out, but now I’m coming up against results from @Integer() in a column which don’t match what I’d expect, i.e.:
a := TotalSeconds; (in this scenario, 1450)
b := @Integer(a / 60); (24.17)
and instead of getting 24 I get 22.
If I plug 24.17 into @Integer() I get 24.
b evaluates as 24.17 (I checked that) so I’m baffled as to why @Integer(b) = 22. I’m probably missing something due to my mathematical inexpertise.
If @Integer() gave me what I expect it to, I think the following formula would work for what I want:
a := sTotalSeconds;
b := @Integer(a / 60);
c := b * 60;
d := a - c;
e := @Integer(b / 60);
f := e * 60;
g := b - f;
@Text(e) + “:” + @Text(g) + “:” + @Text(d)
I’m math challenged, so if this stinks I won’t be (too) embarrassed.
What don’t I know about @Integer()? Thanks!
Subject: RE: Time totals for Music CDs
I can’t explain the math error (22 versus 24) given what you’ve posted, but your parsing method is going to wind up causing problems.
To get hh:mm:ss from the number of seconds, you should be working the other way around. That is, get the hours first, then get the minutes from the remainder after the full hours are removed, then get the seconds from the remainder after the full minutes are removed.
secondsCount := TotalSeconds;
hours := @Integer(secondsCount/3600);
remainingSecondsCount := @Modulo(secondsCount; 3600);
minutes := @Integer(remainingSecondsCount/60);
seconds := @Modulo(remainingSecondsCount; 60);
@Text(hours) + “:” + @Right(“00” + @Text(minutes); 2) + “:” + @Right(“00” + @Text(seconds); 2)
The Formula equivalent of what was posted earlier in LotusScript snippets would be to create a midnight value, then use @Adjust to add the seconds:
now := @Now;
midnight := @Adjust(now; 0; 0; 0; -@Hours(now); -@Minutes(now); -@Seconds(now);
runtime := @Time(@Adjust(midnight; 0; 0; 0; 0; 0; TotalSeconds))
The “runtime” value can be displayed in a date-time field set toi display time only, or you can use @Text with a time-only formatting string to create a text equivalent. As long as the total time never goes beyond 11:59:59, you’ll be okay.
Subject: RE: Time totals for Music CDs
Thank you! I really appreciate the explanations as well as the means. I try to understand what I’m given here. I don’t simply drop the method provided into my app and charge on.
I’m not surprised I went about things backwards. Maybe it’s math dyslexia.
I’ll get to work with this this evening and am looking forward to it (believe it or not)!
Subject: RE: Time totals for Music CDs
Now there’s a cool idea … Good one Charles.