Cross-tab report: from list
Kay C Lan
lan.kc.macmail at gmail.com
Fri Jan 9 22:50:04 EST 2009
I realise I'm a little late to the game but I just wanted to add a different
way to skin this cat, not because I believe it is better, but more because I
notice quite a few new posters to the List and so this simpler method may be
a little easier to get one's head around.
This assumes you have a field "input" and a field "output". Watch for line
wraps, this is a 12 line script
1) ON mouseUp
2) constant january = 2, february = 3, march = 4, april = 5, may = 6,
june = 7, july = 8, august = 9, september = 10, october = 11, november = 12,
december = 13
3) put field "input" into tListA
4) put "Month" & cr & "January" & cr & "February" & cr & "March" & cr
& "April" & cr & "May" & cr & "June" & cr & "July" & cr & "August" & cr &
"September" & cr & "October" & cr & "November" & cr & "December" into
5) set the itemdelimiter to "-"
6) REPEAT FOR each line tLine IN tListA
7) put item 3 of tLine into item ((word -1 of the long date) -
item 1 of tLine + 2) of line value(item 2 of tLine) of tListB
8) put item 1 of tLine into item ((word -1 of the long date) -
item 1 of tLine + 2) of line 1 of tListB
9) END repeat
10) replace "-" WITH tab in tListB
11) put tListB into field "output"
12) END mouseUp
I also want to pass on a tip I picked up from this List, I believe it was
Jim Ault who 'opened my eyes' to constant and value. I don't read the Rev
Dictionary for fun so it's only when I see others post examples on this List
with functions/commands I didn't know exist do I occasionally get a 'wow, I
can use that' moment, and as I transpose horizontal tables to vertical
tables ALL the time, and months are a typical part of that, this particular
problem struck a note.
So, first constant - look it up in the Rev Dictionary - but basically you
can make up your own constants, so you can say contant january = "Jan", or
constant january = 1. In this case, january is always the second line so
constant january = 2, february = 3, etc etc. Another cool thing about
constants is they are always case insensitive so line JANUARY, line January
or line janUARY will be interpreted as line 2:-)
That then only leaves the problem that we are going to extract the name of
the months via a variable, so we'll have line (item 2 of tLine) which will
be interpreted as line "January", or line "February" (text strings not
constants) which of course means nothing to Rev so it comes up as an error.
So we use value(); line value(item 2 of tLine) where item 2 of tLine is now
interpreted as a constant, not a string and so you end up with line 2, line
3 etc etc.
So from the month we know exactly which line we want the output to go to,
from the year we can determine exactly which item on a particular line. The
current year will be item 2 (as item 1 is the actual name of the month),
last year is item 3 etc. So if we take todays date() and extract the year,
word -1, then subtract the year from the report, and finally add 2 to it we
get what we want: 2009-2009+2 = 2, 2009-2008+2 = 3 etc etc.
Hope that has opened others eyes to something they didn't know they could do
More information about the Use-livecode