Generating reports with Numbers

Here is a shot with the pop up menu selected to show the values. The SN value is used for vlookup to populate the values in the other cells.

Below is an example with 1101 “Fred” selected. Everything save the header column on the left is populated via vlookup using the SN value.

This works fine for manually stepping through to view individual grade reports. What I would like to do is to step through the students one by one programmatically, and generate a PDF page for each student from this sheet. While I could do this by hand, it would take a *very* long time as, like I said, I have about 500 total students in 6 different grades that I would like to generate reports for.

No, they are not continuous for all students, however within the grade (the first grade middle school class shown in my screenshots, for example) the numbers are continuous. In other words, I have a grade table with all of the students in that grade in the same table, and I use the student number (SN) as the key for each table. The vlookup works on two tables: the grade table, and a separate points table that lists assignments, point values, and percentage multipliers.

I have each sheet broken down by grade because I have to export the grade table regularly and they expect to have only the information for that grade. Also, each grade’s points, assignments, and percentages are calculated separately. It would be infeasible to have one master table with everything from 5 grade levels. Given that sometimes the progress between different classes is variable, I really ought to split the tables further than I do, but since I’m already pushing the “database” aspects of Numbers further than it ought to go, I haven’t fragmented it further.

If you’re asking how much I’m willing to deal with running/modifying a script to get all the work done, I’m okay with having to prepare separate scripts for each grade. I’m not expecting to be able to put together a Swiss Army knife script that deals with pulling data from anywhere and everywhere.

I’m also not married to an AppleScript solution. I’m looking at things like Keyboard Maestro to see if I can string together a macro that will accomplish pretty much the same outcome.

Thanks for taking the time to look at this problem, by the way.

Okay, related workaround problem: how would I programmatically step through changing the value of that pop up menu in the cell?

I think that I could kludge this by writing a script to copy all the tables on the sheet to a new Numbers doc, add a sheet, copy, repeat, and then just print/export the newly created file at the end. Since I’m doing a grade at a time, I shouldn’t have more than 80–100 students, which hopefully won’t hit any limits with Numbers regarding the number of sheets in a file.

I understand that I’m pushing the capabilities of Numbers. The reasons I am pursuing this idea still are:

1) These are the tools I have. Learning about AppleScript or similar scripting tools to do this will probably pay dividends later. Common programmer advice is to find a problem you have, and learn enough to solve it.

2) Time put into learning how to use a different office suite is probably wasted time. I’d have to re-implement everything I’ve already done through iterations over months (in some cases years) in a different software suite that I would — in all likelihood — never use for anything else. It’s never as easy as just importing your existing tables. I’ve tried using things like Open Office in the past. I was never able to get anything that should, in theory, work to actually work properly; and that was just printing mailing labels.

3) Given that case 2 looks like a time sink, I might just as well try to learn MySQL or R … except that I don’t really have the time or attention to spare to learn something that in-depth, which is why I’m trying to find some way of automating what I have, so that I have the time and attention to spare for more ambitious projects.

Regardless, posting here is time I’m not doing something productive, so I should probably cut my losses. If I do figure out a workable solution, I’ll post it later.

I’m not trying to argue that my approach is right, but the reality of my situation is that I can’t throw money at the problem. I’m doing this for work, but there is zero chance I’ll get money for software since I am literally the only Mac user, and the information I’m trying to track is solely my responsibility. All they care about are the final grades, and their systems are set up only to deal with that information.

I want to generate the reports in order to be more organized and granular on my end, and to provide decent feedback to students. I’ve been given more and more responsibilities over time to the point where doing things by hand has gotten ridiculous, but I can’t realistically afford the time and monetary investment to start over from scratch. Cross-applying the things I can learn from automation will (hopefully) also help with other little things I do daily.

Yes, spending a week or more of necessarily fragmented time (and several day’s pay) on setting up FileMaker Pro would probably solve this particular problem, but it wouldn’t do anything beyond that. I do also have other responsibilities where a relational database would probably be appropriate, so maybe I really should find a way to learn enough about heavy-duty databases to handle those things too.

I wasn’t being entirely facetious earlier about learning MySQL or R. If I can ever make that investment, the personal benefit would be having that knowledge and expertise, and the practical benefit would be being able to deploy a platform-agnostic system that others could use. The problem is that I don’t see realistically being able to do that anytime soon.

Anyway, no one needs to read a longer litany of my woes.