I’ve just noticed something I overlooked.
I’ve got two reports, a weekly and a monthly.
The monthly only crashes if the weekly is run first, otherwise it’s fine.
The weekly only uses about 0.2 GB extra.
I was resetting the variable used for the list in the weekly, and re-using it in the monthly, but changed it to make sure it wasn’t the problem.
Here’s the code for the both reports. A bit long I’m afraid.
-- ¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢ Client Weekly Report ¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢
on MakeClientWeekly()
tell application "Numbers"
activate
if not (exists document 1) then
display dialog "There is no document open." buttons {"Cancel", "OK"} default button 2
my SetUp()
end if
set StartWeek to current date
set {year of StartWeek, month of StartWeek, day of StartWeek} to {DataYear, (StartFlag + 1), 1}
set EndWeek to current date
set year of EndWeek to DataYear
set x to 31
repeat
set month of EndWeek to endFlag + 1
set day of EndWeek to x
if month of EndWeek as integer = endFlag + 1 then exit repeat
set x to x - 1
end repeat
tell theArrayController to set GraphData to arrangedObjects() as list
set theRows to {}
repeat with i from 1 to count of GraphData
try
set oneRow to item i of GraphData
set end of theRows to GraphClient of oneRow
on error errmsg
display dialog errmsg
end try
end repeat
set AllTheClients to theRows
set ClientCount to count of AllTheClients
set NumberOfWeeks to (EndWeek - StartWeek) / (3600 * 24 * 7)
if NumberOfWeeks - (NumberOfWeeks div 1) > 0 then set NumberOfWeeks to NumberOfWeeks + 1
set NumberOfWeeks to NumberOfWeeks div 1
set TheSheetName to "CLIENT WEEKLY DATA " & DataStart & " to " & DataEnd & " of " & DataYear --& " " & time string of (current date)
tell document 1
tell sheet 1
set this_table to make new table with properties {name:TheSheetName, column count:NumberOfWeeks + 1, row count:ClientCount + 1}
tell table TheSheetName
-- set any global cell properties
set the height of every row to 30
set the height of row 1 to 80
set the width of columns 2 thru (NumberOfWeeks + 1) to 60
set the width of column 1 to 200
set the vertical alignment of every row to center
set the alignment of every row to center
tell column 1
-- set specific properties for the title column
set the vertical alignment to center
set the alignment to left
-- insert labels
set value of cell (1) to DataYear
repeat with i from 2 to ClientCount + 1
set value of cell i to (item (i - 1) of AllTheClients)
end repeat
end tell
tell row 1
-- set specific properties for the title row
set the vertical alignment to center
set the alignment to center
-- insert labels
set FlagWeek to StartWeek
repeat with x from 1 to NumberOfWeeks
set value of (cell (x + 1)) to "Week " & x & " " & date string of FlagWeek
set FlagWeek to FlagWeek + 7 * days
end repeat
end tell
end tell
end tell
end tell
end tell
my GraphItClientWeekly(AllTheClients, StartWeek, NumberOfWeeks)
end MakeClientWeekly
on GraphItClientWeekly(AllTheClients, StartWeek, NumberOfWeeks)
set DataClientYear to theYear's titleOfSelectedItem() as integer
set TallyName2 to ((path to desktop) & "Mail Manager Folder:Mail Data " & DataClientYear) as text
set theCountlist to {}
repeat with x from 1 to count of AllTheClients
set end of theCountlist to 0
end repeat
set WeekFlag to StartWeek
set TheBarIncrement to 100 / (NumberOfWeeks)
Progress's setMaxValue_(100)
Progress's setDoubleValue_(0)
set BarCount to 0
try
repeat with theWeekCount from 0 to NumberOfWeeks - 1
set BarCount to BarCount + 1
Progress's setDoubleValue_(TheBarIncrement * BarCount)
set WeekFlag to StartWeek + (7 * theWeekCount * days)
repeat with WeekCycle from 0 to 6
set WeekSeek to WeekFlag + (WeekCycle * days)
set theMonth to (month of WeekSeek as integer)
if theMonth < 10 then set theMonth to "0" & theMonth as text
set theDay to (day of WeekSeek as integer)
if theDay < 10 then set theDay to "0" & theDay as text
set FileName to (DataClientYear) & " " & theMonth & " " & theDay & ".dat" as text
tell application "Finder"
if exists (file FileName in folder TallyName2) then
set individualFile to TallyName2 & ":" & FileName as text as alias
set tempWholeList to my ReadFile2(individualFile as text) as list
repeat with paragraphCycle in paragraphs of item 1 of tempWholeList -- run through the clients in .dat
set tempClientString to my SeparateOutClient(paragraphCycle)
if tempClientString ≠"" then
repeat with yy from 1 to count of AllTheClients
if tempClientString = item yy of AllTheClients as text then set item yy of theCountlist to (item yy of theCountlist) + 1
end repeat
end if
end repeat
end if
end tell
end repeat
tell application "Numbers"
tell document 1
tell sheet 1
tell table TheSheetName
tell column (2 + theWeekCount)
repeat with z from 1 to count of AllTheClients
set value of cell (z + 1) to (item z of theCountlist)
set font size of cell (z + 1) to 14
end repeat
end tell
end tell
end tell
end tell
end tell
repeat with x from 1 to count of theCountlist
set item x of theCountlist to 0
end repeat
end repeat
on error errmsg
display dialog errmsg
end try
Progress's setDoubleValue_(0)
my addTitle(TheSheetName)
end GraphItClientWeekly
-- ¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢ Client Monthly Report ¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢¢
on MakeClientMonthly()
tell application "Numbers"
activate
if not (exists document 1) then
display dialog "There is no document open." buttons {"Cancel", "OK"} default button 2
my SetUp()
end if
set StartWeek to current date
set year of StartWeek to DataYear
set month of StartWeek to StartFlag + 1
set day of StartWeek to 1
set EndWeek to current date
set year of EndWeek to DataYear
set x to 31
repeat
set month of EndWeek to endFlag + 1
set day of EndWeek to x
if month of EndWeek as integer = endFlag + 1 then exit repeat
set x to x - 1
end repeat
tell theArrayController to set GraphData to arrangedObjects() as list
set theRows to {}
repeat with i from 1 to count of GraphData
try
set oneRow to item i of GraphData
set end of theRows to GraphClient of oneRow
on error errmsg
display dialog errmsg
end try
end repeat
set AllTheClients to theRows
set ClientCount to count of AllTheClients
set NumberOfMonths to (endFlag - StartFlag) + 1
set NumberOfWeeks to (EndWeek - StartWeek) / (3600 * 24 * 7)
if NumberOfWeeks - (NumberOfWeeks div 1) > 0 then set NumberOfWeeks to NumberOfWeeks + 1
set NumberOfWeeks to NumberOfWeeks div 1
set TheSheetName to "CLIENT MONTHLY DATA " & DataStart & " to " & DataEnd & " of " & DataYear --& " " & time string of (current date)
tell document 1
tell sheet 1
set this_table to make new table with properties {name:TheSheetName, column count:NumberOfMonths + 1, row count:ClientCount + 1}
tell table TheSheetName
-- set any global cell properties
set the height of every row to 30
set the height of row 1 to 30
set the width of columns 2 thru (NumberOfMonths + 1) to 70
set the width of column 1 to 200
set the vertical alignment of every row to center
set the alignment of every row to center
tell column 1
-- set specific properties for the title column
set the vertical alignment to center
set the alignment to left
-- insert labels
set value of cell (1) to DataYear
repeat with i from 2 to ClientCount + 1
set value of cell i to (item (i - 1) of AllTheClients)
end repeat
end tell
tell row 1
-- set specific properties for the title row
set the vertical alignment to center
set the alignment to center
-- insert labels
set FlagMonth to StartWeek
repeat with x from 1 to NumberOfMonths
set value of (cell (x + 1)) to ((month of FlagMonth) as text)
set month of FlagMonth to (month of FlagMonth) + 1
end repeat
end tell
end tell
end tell
end tell
end tell
my GraphItClientMonthly(AllTheClients, StartWeek, NumberOfWeeks, NumberOfMonths)
end MakeClientMonthly
on GraphItClientMonthly(AllTheClients, StartWeek, NumberOfWeeks, NumberOfMonths)
set DataClientYear to theYear's titleOfSelectedItem() as integer
set TallyName2 to ((path to desktop) & "Mail Manager Folder:Mail Data " & DataClientYear) as text
tell application "Finder"
set current view of window of folder TallyName2 to icon view
set arrangement of icon view options of window of folder TallyName2 to arranged by name
end tell
set theCountlist2 to {}
repeat with x from 1 to count of AllTheClients --< only 3 Clients chosen
set end of theCountlist2 to {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
end repeat
set WeekFlag to StartWeek
set Startmonth to (month of StartWeek) as integer
set endmonth to Startmonth + NumberOfMonths - 1
tell application "Finder"
set theSearchFiles to files of folder TallyName2 as alias list --< 365 files
end tell
Progress's setMaxValue_(count of theSearchFiles)
Progress's setDoubleValue_(0)
set BarCount to 0
repeat with FileName in theSearchFiles
set BarCount to BarCount + 1
Progress's setDoubleValue_(BarCount)
try
tell application "Finder"
set Checknumber to (word 2 of (name of FileName as text)) as integer
end tell
if Checknumber ≥ Startmonth and Checknumber ≤ endmonth then
set tempWholeList to {}
set tempWholeList to my ReadFile2(FileName as text) as list
repeat with paragraphCycle in paragraphs of item 1 of tempWholeList -- run through the clients in .dat, might be 200 paragraphs
set tempClientString to my SeparateOutClient(paragraphCycle)
if tempClientString ≠"" then
repeat with yy from 1 to count of AllTheClients
if tempClientString = item yy of AllTheClients as text then
if Checknumber - Startmonth + 1 > 12 then say "OOPS"
set item (Checknumber - Startmonth + 1) of (item yy of theCountlist2) to (item (Checknumber - Startmonth + 1) of (item yy of theCountlist2)) + 1
end if
end repeat
end if
end repeat
end if
on error errmsg
display dialog errmsg
end try
end repeat
tell application "Numbers"
activate
tell document 1
tell sheet 1
tell table TheSheetName
repeat with x from 1 to NumberOfMonths
tell column (1 + x)
repeat with yy from 1 to count of AllTheClients
set value of cell (yy + 1) to (item x of item yy of theCountlist2)
set font size of cell (yy + 1) to 14
end repeat
end tell
end repeat
end tell
end tell
end tell
end tell
Progress's setDoubleValue_(0)
my addTitle(TheSheetName)
end GraphItClientMonthly