Notifications
Clear all
Topic starter
I am wanting to calculate the amount of time spent (Values are recorded in obs TIME_SPENT) just for a particular month.
I know someone out here has the answer.
Thanks,
Cecil
Posted : February 12, 2015 12:12 pm
I have tried answering this twice now, each time lost all of my posting. So - if you know how to code MEL, you will have to write a function that uses LIST_OBS ( use delimited option) to pull all the values for TIME_SPENT. Then use GETFIELD to create an array from the returned LIST_OBS. Then loop through the array, checking that the obsdate falls between fromDate and ToDate - use DURATIONDAYS to do the compare. If the value falls between fromDate and ToDate (you have to enter these values into your function), then add that obsvalue to the total. Let me know if you need more spedific help.
- Beverly
Posted : February 13, 2015 9:13 am
OK - posting code that should do this...Let me know if it works for you.
- Beverly
DOCUMENT.theTotal
= fnGetTotal("TIME_SPENT","01/01/2015","01/31/2015")
{fn fnGetTotal(obsname, FromDt, ToDt) {
local iTot=""
if ( OBSPREV(obsname)<>""
AND DURATIONDAYS(FromDt,ToDt)>0 ) then
local sList = LIST_OBS(obsname,"signed","delimited","value")
local sAry = getfield(sList,"|","")
local imax = size(sAry)
local i
local thisDate=""
local aRow
iTot = 0
for i=1, i<=imax, i=i+1 do
aRow = getfield(sAry[i],"^","")
thisDate = str(aRow[2])
if ( DURATIONDAYS(FromDt, thisDate) >= 0
AND DURATIONDAYS(ToDt, thisDate) <= 0) then
iTot = iTot + val(aRow[1])
else "" endif
endfor
else "" endif
return iTot
}}
Posted : February 13, 2015 9:59 am
Topic starter
Beverly,
Thanks a lot, I just couldn't wrap my head around that process to get me to the result.
Cecil
Posted : February 13, 2015 10:01 am
I had to edit and re-edit the code I posted, because after posting, parts were stripped out. Finally got it all there. If the code is not working, try copying it now and trying again. - Beverly
Posted : February 13, 2015 10:25 am
Topic starter
Beverly,
Have you run this through your test system? This is getting hung somewhere because I'm never getting a result.
Cecil
Posted : February 16, 2015 7:51 am
I changed valuedate to just value in this line: local sList = LIST_OBS(obsname,"signed","delimited","value"). Should work now - Beverly
Posted : February 16, 2015 11:12 am
Topic starter
Beverly,
Thanks for all the help. I have it working somewhat. If I run the function at form startup it calcs just fine. When I set it to run from a Watcher statement it always returns a 1. Any ideas on this?
Email me at [email protected] and I will send you a current copy.
Thanks
Cecil
Posted : February 18, 2015 5:53 am
Cecil,
make sure that you are passing the dates as strings (and that the FromDt and ToDt have values).
Example:
DOCUMENT.theTotal = (if DOCUMENT.FromDt<>"" and DOCUMENT.ToDt<>"", fnGetTotal("TIME_SPENT", str( DOCUMENT.FromDt ), str(DOCUMENT.ToDt) ), DOCUMENT.theTotal )
If this does not fix the issue, send me the dlg, I will take a look at it.
Posted : February 20, 2015 1:14 pm
Topic starter
Beverly,
I got it all working, sorry forgot to update the post. Thanks again for all your help.
Cecil
Posted : February 23, 2015 3:45 am
