Question on matching KAs when cutting and pasting from spreadsheet


(James Fallows) #1

Here is a minor mystery, when I am populating a TB file from a spreadsheet:

  • The main prototype in the Tinderbox file has a set of Key Attributes. For simplicity let’s call them $A, $B, $C … $Z.
  • The spreadsheet has a list of column headings that match the KAs, and in the right sequence – but only the first half of them. Let’s say that the spreadsheet columns are labeled A, B, C… K but they stop at K.
  • The container into which I’m going to paste the spreadsheet values has an on-add action to assign items to the prototype with attributes from $A through $Z.

Here is what happens when I select and copy the right range from the spreadsheet (including the headers), and paste it into the Tinderbox file:

  • Works perfectly in creating the items, and matching and populating KA’s $A through $Z. Great!
  • But the KA display for these new imported items simply stops at $K, rather than showing the rest of the ilst (with empty or 0 values, as appropriate for the data type). If I create a new item in this same container, its KA display shows the whole range, A to Z, with empty values where not assigned. But these imported items don’t show anything to note the existence of attributes $L through $Z – even though the imported notes have been assigned to the right prototype.
  • Also, minor fit-and-finish issue: for the imported items, both the KA titles and the vaues are all in bold face, which is not how any of the other items look.

What am I not understanding here? Thanks. (And impressed that the cut-and-paste works as well as it does, with this little mystery.)


(Brian Crane) #2

I think–but am not sure–that import sets the imported attributes as key attributes in the imported notes. If it does, that would break the inheritance. Resetting KAs with a stamp might fix it.


(James Fallows) #3

Interesting, thanks. Here is the sequence I’ve tried after seeing your note:

  • I create a stamp that says $KeyAttributes=;, which I think should re-set KAs to default values;
  • After I apply that, the Key Attribute display is entirely zeroed out. None shown at all. Hmmm.
  • But then I move the note out of the container, and back in, so it is re-set to the proper prototype.
  • Now I get the full KA display in all its proper glory, with blank or zero values for the ones that were not set by the spreadsheet import, and correct values for any carried over from the spreadsheet. There is still the tiny anomaly that the KA names that were covered in the spreadsheet are in boldface, until the others. But that doesn’t matter.

I can figure out a way to automate the process above, but short answer is: this did seem to involve some kind of broken inheritance, and the sequence of setting the value back to default, and then re-imposing the prototype, did the job. Thanks!


(Brian Crane) #4

I think the bold text indicates that the attribute has a local rather than an inherited value. When I right click and select “use inherited value” the content in an attribute reverts to the the default/inherited value and is no longer bold.


(Mark Anderson) #5

IIRC, the note pane KA will display the first 15 items in KA but then you need to scroll the table to see the rest. I’d check first that your notional $A-$K aren’t just the first 15 items (yes, it realise K is letter 11, but the example may not be literal in that sense. Try scrolling the KA table to see if more items are set

If the full import set of columns isn’t being fully translated to KA, my hunch would be it might have been set an upper limit on a pragmatic basis (possibly to stop messy data causing a lot of processor load.

Could you post some demo data others can validate. As it is nothing obvious, it is easier to do further triage on a common reference.


(James Fallows) #6

Hi Mark A – Yes, I do know about the scrolling trick, and it’s something more than that. (Ie, it really does stop with the last column that is transferred from the spreadsheet–though after the re-set routine mentioned above, it shows the full list.)

Later today I can post a small sample of the actual file to see if that provides any clues. Appreciate it – and for now I have a practical work-around.


(Mark Anderson) #7

Sorry - I thought it best to bowl out a possible oversight. If it’s personal data and you’d prefer to just email me, that’s OK.


(James Fallows) #8

No, I’m grateful for the thought! And of course the real value of most of these on-line asks-and-answers is exposing the trail of diagnosis for people beyond those immediately involved. (Ie, I probably first learned about the KA-scroll feature from reading some online discussion of it, probably by you!)


(James Fallows) #9

Here is a very small sample database that does show the phenomenon. I’ve vagued up the actual data therein.

  • When I create a new item within the “Data” container, it takes on the appropriate prototype and displays all the relevant Key Attributes.
  • When I paste in spreadsheet data to that same container, the new items show only the KA fields that are matched from the spreadsheet. I have one newly created note, and seven or eight imported notes, to show the difference, but the $Text of several notes explains what you’re looking at.

The work-around is to use a stamp to reset $KeyAttributes to its default value, and to move the notes out of, and back into, the relevant container (at which point they are re-assigned the right prototype). In my quick testing, neither step on its own does the trick–either just resetting KA to default, or just moving the notes out of and back into the container.

FWIW in any clues this may offer to more coding-experienced eyes than my own.
SpreadSheetImportTest.tbx (75.2 KB)


(Mark Anderson) #10

Ah, I think I see the issue. Spreadsheet (CSV, Tab-delim) data creates a new ‘imported spreadsheet’ container and is given the ‘*Entry’ prototype. However, that prototype doesn’t itself set a prototype for its children. Anyway, the spreadsheet import sets the $KeyAttributes of its children to (only) the auto-mapped attributes. Thus, either way, you need to re-assert the desired KA once the import is complete.


(James Fallows) #11

Mark, thanks, very illuminating – and I thought this had given me the key to solving the problem!

As you note, the parent “imported spreadsheet” note takes on the *"Entry" prototype I had created, but that prototype did not include an On-Add action also setting children to *$Prototype="Entry" as well. So I thought that if I added that setting as an OnAdd action, all would be well.

And it works – part-way! Now all the imported spreadsheet entries nicely have the *Entry prototype. But the importing process over-writes (and thus breaks inheritance) for their Key Attribute list. So it’s still necessary to reset that to default, and then re-imprint it with the right values for that prototype. Easy to automate, now that I understand the underlying process. Appreciate it.


(Brian Crane) #12

Would adding your $KeyAttributes=; action to the On-Add before the$Prototype="*Entry" action reset the Key attributes automatically by clearing the local/import values before setting the prototype?


(James Fallows) #13

Sounds like a good idea! But on giving it a try, it doesn’t appear to make any difference. The imported-spreadsheet items still have (only) the spreadsheet columns as their KAs.

OTOH, a stamp with the code below, applied after all the spreadsheet items have been imported, completely does the job:
*$KeyAttributes=;$Prototype="Entry"

Thanks to all for advice.


Parsing date data in MMDDYYYY format