Tinderbox Forum

Crosstabs, but backwards, as import

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.

It seems this task could be done by

  1. 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.
  2. 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”.
  3. 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.

1 Like

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.

This might be helpful

1 Like

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.

What I ended up with is a stamp that reads:

$SomeAttribute= values(find($sIDNumber==$sIDNumber(that)&$Path!=$Path(that)&!$IsAlias),$SomeAttribute);

Process-wise, if I:

  1. import the two columns of new data with the first column being sIDNumber values that already exist in student notes.
  2. set the $SomeAttribute name to the name of the header row of the second column of new data
  3. apply this stamp to my student notes

Then the newly imported data is added to the student notes. I can then delete the notes created by the spreadsheet import.

So no new feature needed. Thanks for the help @PaulWalters

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 :grinning:):

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
  1. Copy-paste into Script Editor (in Applications > Utilities)
  2. Edit the names in the first three lines to match your document.
  3. (The first time) make sure Script Editor (and Tinderbox if necessary) are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
  4. Have both the source Numbers spreadsheet and Tinderbox open.
  5. 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.

3 Likes

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.

1 Like

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
1 Like