Creating a table with unique values and sum_if counts

Q & A - getting started with Tinderbox
1

I’ve hardcoded a table to display the count for each tag in a container of notes. This particular container has 853 children, 23 tags and they distribute like so:

Screen Shot 2021-03-02 at 12.03.23 PM
Screen Shot 2021-03-02 at 12.03.23 PM1520×1370 179 KB

The code for any given row looks like this:
<tr><td>changeTech</td><td>^action($MyNumber=sum_if(children, $Tags.format(";").icontains("changeTech"),1))^^value($MyNumber)^</td></tr>

What might I do to create this as an export template? @satikusala I’m wondering if you might already have a video that would help? I know I saw one where you have a table print out the values for marketing emails… but what stumped me here was (1) only creating a row for .unique values of $Tags and (2) grabbing a given row’s tag to calculate that row’s sum_if() count.

2

Hey @beck, the table you’re referring would not apply in this context as that is used for generating a table from attributes. In this case, you’re just looking to pull in a value.

Is the table above not what you want, or are you looking for something different? I don’t really understand your data enough to intuit what you’re trying to accomplish. If you’d like, we can hop on a zoom and get this sorted out quick.

3

I may be missing something, but I think this can be simpler. First, I don’t think there’s any need to convert $Tags to a string; we’re looking for a specific tag and we know how its spelled. So that gives us:

<tr>
<td>changeTech</td>
<td>^action($MyNumber=sum_if(children, $Tags.contains("changeTech"),1))^
^value($MyNumber)^</td>
</tr>

Next, I think it’s a bit clearer to move the action away from the table.

^action($MyNumber=sum_if(children, $Tags.contains("changeTech"),1))^
<!-- compute the number of children with this row’s tag -->
<tr>
<td>changeTech</td>
<td>^value($MyNumber)^</td>
</tr>
4

As “changeTech” is only one of the 23 tags we need summed, one way to approach this is to first collect all the discrete values for $Tags in the container’s 853 children:

$MyList = values(children,"Tags").sort;

$MyList is now an alphabetically sorted list of the 23 tags. Within the <table> we write each row out of the loop. Put together, we get this template to use for the container:

<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8">
	<title>^title^</title>
</head>
<body>
<h1>^title^</h1>
^text^
<table>
^action(
$MyString=;
$MyList = values(children,"Tags").sort;
$MyList.each(aTag){
   var vCount(sum_if(children, $Tags.contains(aTag),1));
   $MyString = $MyString +"<tr>\n<td>"+aTag+"</td>\n";
   $MyString = $MyString + "<td>"+vCount+"</td>\n</tr>\n";
};)^^value($MyString)^^action($MyString=;$MyList=;)^</table>
</body>
</html>

Tested in v8.9.2.

2 Likes
5

I’m going to have to list you as a byline in my dissertation, @mwra.

1 Like
6

:slight_smile:

Tinderbox definitely got mention through out my thesis. The second large piece of (mixed method) research in it would have been very difficult in a tool with less flexibility and support for emergent structure. Good luck with the writing!

7

@mwra, brilliant solution!

1 Like
8

@beck ops small error in template (corrected above for later readers). Last part:

^value($MyString)^^value($MyString=;)^

should be

^value($MyString)^^action($MyString=;$MyList=;)^

The last is simply clearing out ‘in-template’ cached strings/lists.

1 Like
9

It’s annoying when people ask interesting questions. I’ve extended the output in this demo file: lists-to-tables.tbx (149.9 KB) [EDIT: small action code only code tweak - link to file and explanation in my next post below]

It has 5 templates with names starting ‘Table’. “Table” is the solution already shown above. The others are 4 more lexically sorted tags (forward (A-Z) and reverse (Z-A)), and numerically sorted (forward (ascending value) and reverse (descending value)). The eagle-eyed will not the sort/reverse order hinges on one simple addition of a .reverse operator. Before anyone asks, I’ve not figured a two tier sort, e.g. descending count with lexical tag sort for same-count tags.

I did discover a bug (which I’ve reported) edge case issue with the String.toNumber operator Edit wored around in updated TBX](it doesn’t work unless the value is passed to a Number-type object, e.g. not a variable!). The tags sort uses a look-up table, as a way to work around the bug avoid that issue.

The notes have lots of attributes as this started out in my '‘starter’ file though I’ve deleted lots of un-needed stuff.

The demo works using the templates applied to Test cell and the $Tags data of its children:

lists-to-tables.tbx 2021-03-03 13-05-14
lists-to-tables.tbx 2021-03-03 13-05-141096×354 39.5 KB

templates:

lists-to-tables.tbx 2021-03-03 13-07-33

So, for instance, here is the preview of using template “Table tag sort reverse lookup” (i.e. tags in reverse Z-A order):

lists-to-tables.tbx 2021-03-03 13-08-47
lists-to-tables.tbx 2021-03-03 13-08-47370×534 40.2 KB

Great, a whole morning of data analysis avoided. :slight_smile: Back to work…

2 Likes
10

This is awesome!

Now to :thinking:ponder some more annoying questions…

1 Like
11

I’ve just updated the above demo file: lists-to-tables.tbx (149.9 KB)

Not HTML output change but the “Table count sort” and “Table count sort reverse” templates have had a code tweak to obviate the use of $MyNumber (by chaining .format("l") to the .toNumber operator.

The earlier ‘bug’ transpires to not be that but an unexpected behaviour. The code is essentially String+Number-from-String+String. As Tinderbox type coercion works to that String+Number => String. Tinderbox sees we are coercing a string to a number but adding it to a string, so uses the source string instead. The additional format command forces the padding (text) digits to be dropped.

1 Like
12

Hi Mark and Tinderbox community – the information that comes across in these exchanges is an absolute godsend when scaling the rather daunting learning curve of TB! I’m starting to get some very useful results, so, I keep at it.

This example posted is fantastic for something I’m doing; as an additional element, how would I generate clickable links to the items gathered in the tables that comprise the accumulated statistics? So in your example, alongside “Logs 3” would be a list of the three documents that have the tag “Logs”. This is like a table of contents, so I guess it would use some sort of an “a href” code, but I don’t know how to pass the $Name and path of the target note to the href as the script gathers its data.

Other references I’ve found to a table of contents work off of a contiguous list of documents, but, as is the case of these summary tables, the target documents won’t be contiguous children…

This would allow a quick link to instances where a heavy use of one tag was evident, and to verify if tagging was done correctly on a given note.

Many, many thanks for any insights.

13

Try this: lists-to-tables-3.tbx (173.8 KB)

I’ve provided enhanced templates (normal and reverse sort to accommodate the above and validated the code/export in v9.0.0.

It turned out a lot more complicated than I thought as we need to make the HTML links in the action code and there is not action code operator that returns the same as the export code ^linkTo()^. As a result instead of working with target $Path or $Name, I had to use $HTMLExport path and then extract a working relative path and anchor name.

@eastgate: Feature request, adopt ^linkTo()^ type functionality into action code so as to inject a functional HTML [sic] link into a string. As linkTo() is already used perhaps an htmlLinkTo() and HTMLLinkToOriginal()?

[edit] Note, in the new templates I’ve shifted from the older look-up tables to the v9+ Dictionary objects which are much neater. As yet, Dictionary values can’t be lists, but that’s worked around by ‘stringifying’ the lists with a # delimiter and then reversing the process to extract the list when retrieving said Dictionary value.

14

Hi Mark, thanks a million for your Herculean efforts. My adapting the code in my file consistently causes a crash, so I’ll need to actually learn this quite carefully to figure out what’s going on. I suspect by the time I do learn this, the new version will be out!!

I checked that all my tags had no punctuation (there are apostrophes as my tags are often phrases), but I’m still getting the crash. I also expressly added the MyListA, MyDictionaryA etc. attributes to the file, as this seemed like a good idea.

The original Table tag sort templates work like a charm and these are of enormous utility.

Again, I don’t know how to thank you and the TB community in helping everyone unpack the tremendous potential utility in TB.

15

Can you post a cut down version of your document that shows the crash. Or, post the amended template in code tags (i.e. 3 back-ticks on a line of their own before and after the sample code

Also, if you are getting a crash, do send the log(s) to Eastgate as it may help track the cause and guard against the same in the future. Once one has found a condition that causes a crash, then it will do so consistently until the document or the app is changed. The number of crashes is irrelevant if it is that same trigger every time.

1 Like
16

Ha!! I traced the error in a tag descriptor that I had and it no longer crashes! Huzzah!

However, the links don’t work. I don’t know if this could be caused because I have accents in some of the $Name documents (they simply come across as underscores in the rendered document).

So, at least I have the names of the documents, and they’re underlined link-like, but I need to trace to see what they’re pointing to. I’ll come back on this.

Again, so many thanks with your great help!

1 Like
17

Yes the accents likely will be part of the problem. Do you have some examples. It would help to know:

  • the on screen $Name of the document
  • the exported HTML filename of that document
  • the generated HTML link URL to that note
  • the generated HTML anchor text for that link.
18

Hi Mark, there’s something funny going on. Even in your example template, if I put the name of a document with spaces, the interpreted label coming out of the export script truncates the spaces. The test that I did was changing the name of the document “bees” to “bees and hornets”, and the interpreted export code was like this:

<a href=“Testcell/beesandhornets.html”>beesandhornets</a>

Try this at your end, in case I have something strange… it links to the right document, though, so it’s not entirely the same issue that I have. BTW, spaces in the Tags are no problem (all my tags are in English, so I haven’t tested the accent thing there, thank goodness).

My own documents’ names get truncated and I get underscores for spaces. I also don’t know if this is because I’m running the code under an Agent which gathers the particular documents to sort—I was hoping this would simplify the path thing.

Here’s my output code for an entry:
<td>Consumer Goods, Consumption
<td>1
<td><a href=“Agents/Values/Patrones_de_producci_n_y.html”>Patrones_de_producci_n_y</a></td>
</tr>

The $Name of the target document should be “Patrones de producción y consumo sostenibles”, so you can see the truncation thing—could this be something with how HTML manages tables (long shot…)?

Please don’t rush or get a headache over this, but it’s kinda puzzling.

19

Some web servers don’t accept file names with spaces or characters not used in English. When Tinderbox was first designed, most web servers did not. So, by default, Tinderbox assigns export file names that strip spaces and replace other characters with an underscore.

If that’s now what you want, you can set the export file name for any exported page by setting its $HTMLExportFileName attribute.

20

Further to @eastgate’s explanation…

Note ‘ant’ will export as ‘ant.html’. Note ‘big ant’ with export as ‘bigant.html’. Note ‘big ãnt’ will export as ‘bigant.html’. The reason for those changes are as per the explanation above.

So, how to fix. In my last file I worked around the problem of action code having no single operator to generate the HTML code for an HTML link (i.e. <a href="path/to/NoteName.hmtl">Note Name</a>). I did this using $HTMLExportPath as that tells me the path to that note when exported using the ‘correction’ as described in the previous answer for avoiding erstwhile Web server limitations in URLs.

But taking the last part of that path and splitting out the note title gives me the exported HTML document name, not the source so in your case missing spaces, accents, etc.

Solution? Collect both $HTMLExportPath and $Name. Yet more calculation in the action code, in templates ‘Table tag sort lookup3’ and ‘Table tag sort reverse lookup3’. I’ve added some comments in template code to help explain the process. An updated solution TBX as at the end of this post.

Also a point I omitted to mention before. The original demo’s test notes had no $Text. Note with no $Text don’t export when the whole doc exported (why export and empty file!). this makes sense when actually export, but in preview we are looking at what we might export. The answer is deceptively simple: put some text, any old text, in each sample note. I’ve also added @Gilberto’s test note name so we can see the effects of HTML export on spaces, accents, etc. In addition, I’ve amde all the test notes include a space in the name.

The is a teaching point for us all in the latter exercise. Certainly, it i’s quicker to type a test note like ‘ant’ than one called ‘Anton Dec’ or ‘Producción y consumo’. This efficiency/laziness (we are efficient, other people are lazy!) can bite the experimenter in all sorts of ways. This bites for those of us with are English speaking (British, American , Australian, whatever) as English does not use accented characters and adopts ‘foreign’ words by dumping the ‘unnecessary’ accents: 'précis and ‘precis’ are pronounced the same. I’m exposing my own folly here—Tinderboxers are an international crowd and we need to remember that when doing this sort of string manipulation.

I’d note too that the restrictions @eastgate faces with web servers aren’t within the developers gift to resolve. I’ve been doing this long enough to remember when using a filename of more than 8 characters (and no spaces, accents, etc.) was definitely a thrill-seekers choice and extensions were absolutely needed by some things whilst studiously ignored by others. Things change, as must we, though perhaps not always at the bleeding edge. :slight_smile:

Now the point everyone’s skipped to, the updated file: TBX L - Build Table With Categories - solution4.tbx (343.9 KB)