Tinderbox Forum

Auto-create links when importing from Excel data

I believe TB can import data from an Excel spreadsheet. Using this feature, I would like to be able to create links automatically between existing notes and notes that I import from Excel. Here is what I would like to achieve - could anyone point me to the right functions in TB that could help?

  • I have several already created notes that each represent a user.
  • I have Excel files that contain IP adresses / URLs visited by those users (for simplicity sake, let’s assume that each spreadsheet contains only the IP addresses for one of those users, so I will do several imports - one for each)
  • I would like that when I first import a spreadsheet, all the IP adresses it contains appear as notes and are automatically linked to the relevant user (I’ll need to think about a mechanism to indicate which user/note relates to the spreadsheet I’m about to import, or maybe just insert a column in the spreadsheet with the user’s ID)
  • Then, when I do the same import procedure for the other users, IP addresses that already exist are not created but only linked, and the others are created and linked to that user.

What should I look into to start setting this up? That would be for me as much as a TB learning project and a useful tool :wink:

Thanks a lot!

This is a rough outline of what I would do.

  1. In the Excel file have (at least) three columns: $Name, $Text and $TargetNote
  2. The name of the note that the new note created by each link of the Excel will should be in the $TargetNote column.
  3. In the file, define a string attribute $TargetNote
  4. Create a stamp whose action is linkTo($TargetNote);(1)

Import the file. Select the new notes created via the import process. Use the stamp on those notes. I assume the $TargetNotes are the user notes that you mentioned, and that they exist when the stamp is run. If so, you’ll get the links you wanted.

See more on linkTo here.


(1)You could use an rule or edit or agent, but a stamp is fast and convenient, since the action is needed only once.

Thank you! I did not think about using a stamp, I’ll look into this! I was also considering using agents to look into the “Imported spreadsheet” container created, and match only notes (IPs) that did not already exist outside the container, and if so, then creating a link to the user (outside the container, either pre-created or using the $User attribute in the note itself)

I think it is worth noting that whilst Tinderbox has many and varied forms of import, it does not merge with or update existing notes. It may be what we wish to do, but it joust doesn’t happen to be how the app was designed to use data (it’s not a database!).

This won’t be a problem I imagine. Everything is contained in an imported Excel, and if I need to update it (more IPs are added), I’m fine with just re-importing the whole thing.

My question now is more how to iterate over all notes in a container with an Agent that will create aliases (or copy? Move?) each note from the container if it does not already exist outside. It’s essentially de-duplicating first, and then I’ll look into linking those to the adequate user (using your stamp technique).

EDIT: I’m currently looking into values / collect / to first create a set, that would take care of deduplicating, and then having another agent work out of that set to move the matched notes and act on their Link information (stored as an attribute)

Well, this, or maybe I’m still way over my head and did not understand what agent where capable of :wink:

An agent can’t create new aliases (other than within the agent), but it can ‘move’ them by dint of altering the $Container. This is because $Container is intrinsic, i.e.the attribute value for both alias and its original are discrete rather than shared as with normal (not intrinsic) attributes. Bear in mind that if you use an agent action to move an alias (out of the agent) at the next cycle the agent will create a new alias and export that one too, etc. IOW, you get a looping behaviour and an ever-growing set of aliases. To avoid this, your agent action needs to ensure the acted on note/alias no longer meets the agent query.

Here’s is a trivial (for simplicity/scarcity) example. Let’s say we’re finding notes using the ‘pProject’ prototype and using an action to move and alias to a container ‘projects’ at path ‘/work/projects’. Query:

$Prototype=="pProject"

Action:

$Container="/work/project";

This has the loop effect. To avoid that we modify the query:

$Prototype=="pProject" & !(inside("projects"))

Now the first time at alias moves, the moved alias matches the second query term and so avoids further aliases being created and moved.

There are other ways to do this latter guarding effect but I hope that gives the basic ideas. See more here.

Thank you very much! This is going to be helpful! (and help me better grasp TB’s capabilities!)

1 Like