I’ve had a strange thought and, given all the new features coming down the pipe these past weeks, I figured I’d let myself “dream in color” and describe a pie-in-the sky feature that I’m not sure is possible but that would address a genuine problem I encounter in how I use TBX. I’ve struggled to explain it but roughly what I’m thinking is this:
Context
My principal TBX file at any given moment is the course file I’m using to organize my teaching during the current term. I use this file to organize my lecture materials and course work, but I also use it to keep track of what (and how) students are doing. Just like in the actual courses, it is the student part (not the lectures) that is more challenging to manage.
In my course file, the challenge I face is that I have info that comes to me in spreadsheets that have to be entered manually into TBX because I need the info to be added to blank attributes of existing student notes and not imported as new notes. For example: when grades for the first essay arrive, I don’t need new notes, I need the grades added to the “gEssayOne” attribute for each students’ existing note.
Crosstabs and Import: My Odd Thought/Request
But now we have cross tabs. And without having any sense of how things work behind the scenes engineering-wise, what I see is a tool for linking temporarily two attributes to show how they interact. So is it possible, if I have a spreadsheet that contains two columns that are named after attributes (call them sID and gEssayOne), to create a function in which I import those two columns and TBX uses the first attribute (sID) to identify a note which then has the value of the attribute matching the second column (gEssayOne) set by the info in the corresponding second column of the spreadsheet?
My Specific Task
I’m not sure if what I’ve described is clear but, at the beginning of the semester, I import a spreadsheet of basic student information to create student notes. I need to add values to a series of attributes for these student notes over the term. Ideally, I could do this by simply importing from a spreadsheet to update the existing notes by pairing this spreadsheet column of new values to a column of fixed values (i.e. a student ID number that ID’s a unique note in the TBX file).
I’m not sure if that’s clear. Also not sure if it’s even possible. But that’s my off the wall suggestion.
Have a container for the “master list” of students – where the initial data and additional data for each period are collected. This is is the list of “existing notes” into which you want to import the grade data.
When you import the data, import it into a second container (or third, or fourth, as time goes on) which could be temporary or retained, it doesn’t matter. For illustration, one of the containers could be “second period”.
Build an agent (specific query and action tbd), that looks for each student in the “master list” container and copies the value on an attribute for that student’s entry in the “second period” container, and sets the value of that attribute for that student in “master list” to what’s recorded for that student in “second period”.
Could be all I am doing is replacing one problem with another, but if an agent can consolidate values on a per-note basis from multiple containers into a master container then perhaps that would work.
Might also be an scripted solution that would do the importing by reading a CSV file and slotting the grades into the corresponding note in Tinderbox.
I hadn’t thought to do this in this way. I’m not sure how #3 will work exactly but let me give it a try and see if I can figure it out. I’ll report back on what I come up with.
Thanks for the thread reference. After some messing around I got this working, but only as a stamp, never as an agent and only after I removed the collect() action.
If you’re doing this at regular intervals you might consider taking advantage of the recently added external scripting feature in Tinderbox. No need to set up an import area, and do cleanup afterward, etc. Just a click and a click (or a select, copy, and click). It could look something like this (with perhaps less grade inflation ):
set containerName to "MyContainer"
set attributeToUpdate to "gEssay1"
set uniqueIdentifier to "sID"
tell front document of application "Numbers"
tell active sheet
tell (first table whose class of selection range is range)
set theIDs to value of cells 2 thru -1 of column "A"
set theGrades to value of cells 2 thru -1 of column "B"
set numberOfUpdates to length of theIDs
end tell
end tell
end tell
tell application "Tinderbox 8"
tell front document
repeat with aNote in notes of note containerName
tell aNote
set thisID to value of attribute uniqueIdentifier
repeat with i from 1 to numberOfUpdates
if thisID = item i of theIDs then
set value of attribute attributeToUpdate to item i of theGrades
exit repeat
end if
end repeat
end tell
end repeat
end tell
end tell
Copy-paste into Script Editor (in Applications > Utilities)
Edit the names in the first three lines to match your document.
(The first time) make sure Script Editor (and Tinderbox if necessary) are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
Have both the source Numbers spreadsheet and Tinderbox open.
Click a cell in the Numbers table and then the button in Script Editor (or choose the script from the menu after you have installed it there).
If you don’t use Numbers then you can also select the cells with data (not including any header names) in, say, Excel, and command-c to copy the data to the clipboard. Then, with the Tinderbox document open, run this script (after changes to the first few lines to fit your Tinderbox document):
set containerName to "MyContainer"
set attributeToUpdate to "gEssay1"
set uniqueIdentifier to "sID"
set linesOfData to paragraphs of (the clipboard as «class utf8»)
set text item delimiters to tab
set {theIDS, theGrades} to {{}, {}}
repeat with aLine in linesOfData
set end of theIDS to text item 1 of aLine
set end of theGrades to text item 2 of aLine
end repeat
set numberOfUpdates to length of theIDS
tell application "Tinderbox 8"
tell front document
repeat with aNote in notes of note containerName
tell aNote
set thisID to value of attribute uniqueIdentifier
repeat with i from 1 to numberOfUpdates
if thisID = item i of theIDS then
set value of attribute attributeToUpdate to item i of theGrades
exit repeat
end if
end repeat
end tell
end repeat
end tell
end tell
This should take only a minute or so to test. But, as always, be sure to back up before using a script that alters values in your document.
Not sure if this could now belong in Inter-App Workflows or External Workflows rather than Off the Wall. In any case, I find simple working examples like this can help.
Thank you for this script. I have no idea how it’s doing what it does—I don’t know AS or JS—but this gives me a practical example to figure out. Much appreciated.
The good thing about scripts is that, once written by someone, they can often be reused without having to set them up each time and without knowing the details of the language.
Here’s a commented version of this one describing how it works:
set containerName to "MyContainer"
set attributeToUpdate to "gEssay1"
set uniqueIdentifier to "sID"
tell front document of application "Numbers"
tell active sheet
-- go to the table that has been clicked
tell (first table whose class of selection range is range)
-- put column A values in a list called theIDs
set theIDs to value of cells 2 thru -1 of column "A"
-- put column B values in a list called theGrades
set theGrades to value of cells 2 thru -1 of column "B"
-- get number of items in the lists (to use in loop below)
set numberOfUpdates to length of theIDs
end tell
end tell
end tell
tell application "Tinderbox 8"
tell front document
-- loop through the notes in the container
repeat with aNote in notes of note containerName
tell aNote
-- get the id of this note
set thisID to value of attribute uniqueIdentifier
-- loop through the items in theIDs list
repeat with i from 1 to numberOfUpdates
-- if this note's id matches the currently considered item in the theIDs list
if thisID = item i of theIDs then
-- set the note's attribute to the corresponding item in the grades list
set value of attribute attributeToUpdate to item i of theGrades
-- stop looping if there has been a match
exit repeat
end if
end repeat
end tell
end repeat
end tell
end tell
):