Hi there, I can’t seem to get my condition export working for a $DueDate Attribute.
Here is what I’m using
^if^(^value($DueDate)<=date(“now”))^<font color=“red”>ASAP</font>^else^^value($DueDate.format(l))^^endif^
All of my data are resolving as “ASAP” even though half of them are 12/20/20, i.e. NOT today or earlier.
If feel like I’m doing something wrong with the argument
<=date("now")
but can’t figure out what that might be.
Couple of things:
date("now")
should be
^value(date("now"))^
because you are using export code here.
Also, if you test this in a template
Due Date ^value($DueDate)^ </br>
Now ^value(date("now"))^ </br>
you will get this result
Due Date 2020-11-17T10:01:00-05:00
Now 12/2/20, 10:15 AM
which means you’ll want to be formatting the ^value()^ results before testing the comparison. (It is a string comparison, so don’t get fooled into thinking it is a date comparison.) I’ll leave that step to you – you know what to do 
This is another case where it helps to set up a small test file and build up complex templates step by step while evaluating intermediate results, as I did above.
Edit:
Might be simpler in your test to use
^value(eval($DueDate - date("now")))^
and test it the result is <= zero.
Then you wouldn’t need to futz with formatting.
I believe that the query
^value($DueDate > date("today") )
will do the trick. The argument to ^value is an expression.
Sadly, that does not do the trick. Please see the attached file: ProjectDateConditional.tbx (121.9 KB)
I tried this and it is not working as I expect it to:
<td>^if^(^value($DueDate)>=("today"))^<font color="red">ASAP</font>^else^^value($DueDate.format(l))^^endif^</td>
Since c.v5 ^if()^ has used action code queries so I don’t think ^value()^ is needed inside ^if()^. I think there is an overlying error. I’f I change Task1’s $Text to
DueDate: ^value($DueDate.format(l))
DueDate: ^value($DueDate)
In preview (on a month-day order system) this previews as:
DueDate: 11/15/2020
DueDate: 2020-11-15T17:38:02Z
So the query being evaluated for ^value($DueDate)>=("today") is comparing:
"2020-11-15T17:38:02Z">"today"
Plus, I think in the latter part you want the date that is today which is data("today"). So, if we use this query:
^if^($DueDate>=date("today"))^
We get two tasks as ‘ASAP’ and two dates. Now the query works but the outcomes are reversed. The query’s >= needs to be <=, or swap the actions taken in the if/else logic branches.
See ProjectDateConditional1.tbx (118.1 KB)
Fantastic!!! Works great. For some reason I was thinking I needed to use the ^value element (clearly out of ignorance). It did not occur to me to simply challenge the value directly in the condition. Thanks so much. I hope others that stumble across this file will find it useful.
Now, on to my next question, RE this same example. When I copy the table and past it into Excel I get the gray arrow output but what I want is the blue arrow output. Is there a way to tell tinderbox to encode the HTML $Text to be treated as one block of text rather than a text with line breaks?
Note: I’m using ^text()^ as I have some value I want to be processed. I tried adding the plain argument but then the value are not processed. Also, I would like to keep the line breaks, but I want them as <br>s not <p>s so that they can be pasted into excel.
UPDATE: After doing some research if figure this out.
If you code the <br> or <p>tags as follows: <br style=“mso-data-placement:same-cell”;> I get exactly what I want. The question is, how do I tell Tinderbox to apply this style to the <br> or <p> tags?
I really hope to figure this out as this issue is impacting nearly everyone of the reports I’m creating.
OK, so we’re dealing with this line of the template:
<td>^text($Text)^</td>
If you look at this note you’ll see that the argument you’ve supplied is incorrect†. You want to use:
<td>^text^</td>
†. In writing this answer I’ve updated my notes on ^text^ and ^title^ to clarify the meaning of the optional ‘item’ parameter.
The paragraphs in the source text are exporting as that and so Excel is treating them the same. If you use ^text(plain)^ or ^value($Text)^ you fix the line break issue but the `^value()^ codes in the $Text are not evaluated. If that doesn’t matter in the real-world use case, than either of those codes will do.
Otherwise, it is a bit harder. It’s late and there may be a more elegant solution, but this works:
<td>^action($HTMLFirstParagraphStart="";$HTMLFirstParagraphEnd=" ";$HTMLParagraphStart="";$HTMLParagraphEnd=" ";)^^text^^action($HTMLFirstParagraphStart=;$HTMLFirstParagraphEnd=;$HTMLParagraphStart=;$HTMLParagraphEnd=;)^</td>
Unpacking the code layout for legibility:
<td>
^action($HTMLFirstParagraphStart="";$HTMLFirstParagraphEnd=" ";$HTMLParagraphStart="";$HTMLParagraphEnd=" ";)^
^text^
^action($HTMLFirstParagraphStart=;$HTMLFirstParagraphEnd=;$HTMLParagraphStart=;$HTMLParagraphEnd=;)^
</td>
Basically , on the fly we ware suppressing the defaults for 4 attributes as the first paragraph of $Text uses mark-up from a discrete pair of attributes. In both cases for the ‘Start’ attribute we are suppressing <p> mark-up and replacing it with nothing and for the ‘End’ replacing </p> with a single space. Then after exporting the text we reset the attributes to their (inherited) default.
See: ProjectDateConditional2.tbx (121.1 KB)—the last new code above is in export template ‘HTML item2’.
Thanks, Mark. This was very helpful. It got me really close to were I want to be, but it removed the line breaks from the ^text^ that I want.
Using the <br style=“mso-data-placement:same-cell” ;> that I noted above I modified your code.
<td> ^action($HTMLFirstParagraphStart="";$HTMLFirstParagraphEnd="<br style=mso-data-placement:same-cell;>";$HTMLParagraphStart="";$HTMLParagraphEnd="<br style=mso-data-placement:same-cell;>";) ^^text^ ^action($HTMLFirstParagraphStart=;$HTMLFirstParagraphEnd=;$HTMLParagraphStart=;$HTMLParagraphEnd=;) ^</td>
This produced the visual and the HTML that I want (see how in Task 4 there are line breaks between the lines):
And, in the HTML I get the mso-data placement code (without the standard " " though, i.e. " mso-data-placement:same- cell", every time I tried to put in " " it did not work at all):
Now here is where it gets strange and does not work as I expect.
If I copy the preview code into Excel I get this, see how the there are four cells, each with the four line breaks.
Now, I copy the HTML from Tinderbox, put it in a text editor,
and save the file as HTML, and open a browser I get this:
Then if I copy this HTML and paste it into an Excel file I get exactly what I want:
So, my conclusion is that the Tinderbox Preview is doing something to the HTML, it is probably striping the style=“mso-data-placement:same- cell” in the <BR>, so it looks right but when I copy the preview into the Excel it does not work like it does from the browser. If this is what is happening, is there someway to have the preview NOT break the HTML? Or, might something else be going on?
Is there anything to be done? I produce a ton of these type of reports. It would be extremely beneficial for me if I could get this to work, i.e. be able to copy the preview and past it into an excel spreadsheet without line breaks breaking across cells.
Here are my test files: [ProjectDateConditional3.tbx|attachment] (upload://uaPM1Ekwg1OcO9lPaJc7DkTfTix.tbx) (124.1 KB) and tinderbox.html.zip (1.1 KB)
Well I think that’s because you’ve written badly-formed HTML. Your:
$HTMLFirstParagraphEnd="<br style=mso-data-placement:same-cell;>"
isn’t quoting the HTML attribute’s value string. As that must be straight double quotes, use singles for the Tinderbox attribute value string"
$HTMLFirstParagraphEnd='<br style="mso-data-placement:same-cell;">'
Remember, inside an ^action()^ code you are writing action code, so single or double quotes can be used - as long as properly/paired nested.
It’s late here, but it does feeling like you’re missing an angle here. I’d stop and work out what Excel (which is full of weird formatting issues, given its age) really wants. Only then would I keep pressing on this. for instance it might be that what you need is a vertical tab characters lather than a line break (or HTML simulacrum. I’ll try and take a look at this tomorrow.
Awesome, thanks.
Did not know this. Tried a lot of stuff, but not that. 
Thanks, I fixed the HTML. From what I read, Excel wants style=“mso-data-placement:same-cell” in HTML in lieu of its native ALT + ENTER action. As noted above, the HTML the Tinderbox is producing works great when pasted in a text editor and then opened in a browser, but when copied from the preview something is not working/does not get copied over. It is soooooo close, just off on the one element. Anyway, will keep playing around.
Thanks.
Good night.
It came to me once I was abed: why are we exporting HTML when the target is Excel, which only had HTML import shoe-horned on quite far into its long life? Why not use a tab delim export? The latter should route around this predictable but annoying and hard to fix problem.
Also why copy from the preview pane and not the HTML pane? I ask as the HTML as created is what you want to pass to Excel. The Preview is a render of said HTML and copying may be introducing some new factor. IDK, but I think copying preview copies the the RTF render of the HTML as opposed to the HTML.
So, I’d go with plain text tab-delim and you can use Excel’s own conditionals to colour up cells, etc - i.e. working with rather than against the app.
Don’t forget, we covered how to export the same source content two different ways when discussing ^include()^. IOW, you can have the existing template for internal HTML-preview but a separate export to create Excel-friendly data. Not to be unfair to Excel, which has many strengths but rich text isn’t really one of them!
Ask if you get stuck. Bit busy right now to write a fresh demo.
You’re right, I could, with the Attribute Browser. But, this has some limitations, the first being time:
Whereas my preferred solution is two steps - copy preview and paste (no time). I almost have it working, were it not for this one tricky piece.
I am trying to copy form the Preview Page and not the HTML pane. To your point, the preview pane is doing something undesirable, as you suggest if might be RTF, I just don’t know. Copying from the HTML pane takes too many steps to be useful.
Agreed, this could work, if the Attribute Browser were easier to use. I have numerous reports across multiple tinderboxes. Having to create them every time by manually adding each attribute is simply not scalable with my use cases. I run these reports multiple times a day.
Ya, that could work. I’ll play around with created a tab delimitated export template. Again, my limitation is with the use of the Attribute Browser for this purpose. Thanks for all your help. I’ll keep playing.
Note that you can use tab-delimiter or comma delimited export from HTML Export. For CSV, just set the tags, for example, to
HTMLFirstParagraphStart : ""
HTMLParagraphStart : ","
HTMLFirstParagraphEnd: ""
HTMLParagraphEnd: ""
Be sure to include the terminating [return] in the template.
No, I wasn’t thinking of AB view at all but using an alternate set of export templates to produce the tab-delim export. We covered this in some detail a few weeks back, IIRC. I’ll try and find the references to make a demo tomorrow (though in a seminar most of the day).
If you’re making a full HTML page and them only trying to copy the table part of it then I’d argue you’re definitely going a sub-optimal route and CSV/TSV is better for the intended task.
Actually, this is essentially whats being done in (part of) the last TBX I posted. The mistake here is using HTML as actual the export method.
As I’ve tried to explain it is easy to have HTML templates for preview in ‘preview’ and separate templates for actual CSV/TSV export. the user can either swap out the templates for actual export or make an note that ^includes()^ the same data but using the alternate set of templates as was discussed here not long back.
I totally agree with you and the swapping out of templates through actions and stamps is easy enough to do. I’ll certainly explore how to make the deliminated export. Conceptually I get it, pub not yet practically.
Partway through a demo but broke for a conference. A missing part here (I’ll raise a feature request†) is a form of ^text^ that processing inline ^code^ but doesn’t apply normal hypertext mark-up as turning that all-on/off is either 30+ attributes or using $HTMLMarkupText. However, the latter turns off all code parsing, so in your demo with ^value()^ calls inline in $Text is an edge case this proposal would address.
If it seems odd that this is needed, web pages, HTML and out use of it within Tinderbox has got much more complex over 20 years—some things aren 't needed until they are.
Regardless of this issue, I think tabular text is the way to go rather than HTML. I hope to have the demo done later today after this seminar (typing this in a break).
†. I’ll raise it by email to support or on the backstage (so some edge-case issues can be chewed over) so don’t worry if you don’t see it raised here.
OK, feature request re $text export made, as above. I don’t think we can fix this in simple fashion without a change but, see ProjectDateConditional4.tbx (132.6 KB)
Changes:
My feature request above would not change the output but it would avoid the egregious level of HTMK-related attribute fiddling seen in the HTML and TSV templates.
I think this shows that you also need to think carefully before ‘just’ copying the preview and expecting it to work. The user can’t see what’s in the copied data (plain text, rich text, other, etc.) so it’s self-serving to simply blame source and/or destination application. Once you’ve set up a template, don’t overlook exporting and then importing that file to Excel. Copy/paste might seem easier but isn’t if you have to do lots of fiddling to make the content work as desired in the receiving app.
HTH. I should make tomorrow’s Zoom which might be a good plave to pick over the details.
You, sir, are a genius!!! Much love and gratitude being sent your way….I bow to your greatness.
Just so you know, I’m paying it forward, working with Pablo, and am helping him out with his project: Timeline and Content manager for a podcast - #2 by satikusala.