Ok! I learned a lot about trying to generate sections dynamically. Most of it has to do with getting over some of the current shortcomings of Coda. As usual, we know how hard they are working and that they are likely to address this in the near future. This is for those that would like to see what pitfalls and limitations exist for document generation when attempting a pure Coda approach.
This is a long post, so if you’d like to just play with the doc you can find it here:
Learnings:
Newline doesn’t work as expected in Format and Concatenate, neither in the Character(10) nor the =newline nor the Shift + Enter methods.
Example to come
The best way to build nested FormulaMap formulas is by starting with the innermost component you want to render and then work outward. It’s noticeably more difficult to reason about the output of FormulaMap than it is to reason about for loops (or perhaps it’s just unfamiliar).
The important concept to get is that FormulaMap returns text, which can then be passed to an enclosing FormulaMap where Format or Concatenate is function performed on the CurrentValue.
FormulaMap(Summary, Concatenate(Format("{1}", CurrentValue.Name), FormulaMap(CurrentValue.Subitem, Format("
{1}", CurrentValue.Name))))
Output:
First Name in Summary
First Subitem in First Name
Second Subitem in First NameSecond Name in Summary
First Subitem in Second Name
Format retains all the formatting of the underlying text, Concatenate only retains the color, weight, and highlighting.
Example to come
Formulas are not at all modular, unlike functions might be in a programming language. Therefore, building them can become tedious, repetitive, and confusing. The small cell for editing doesn’t help this (a larger cell, similar to the one used for editing text would certainly help).
Here is the whole final formula to dynamically generate the Section. It’s messy, hard to read, and very annoying to edit in the cell:
FormulaMap(Summary, Concatenate(Format("{1}
", CurrentValue.Name), FormulaMap(CurrentValue.Locations, Format("{1}
{2}
{3}
", CurrentValue.Name, If(CurrentValue.[Image URL].IsBlank(), ' ', Image(CurrentValue.[Image URL], 800, 600)),
Concatenate(
Format("The intended installations are:
{1}
", CurrentValue.Devices.Name.FormulaMap(ToText(CurrentValue)).BulletedList()),
FormulaMap(CurrentValue.Devices,
Concatenate(Format("
{1}
{2}
", CurrentValue.Name, Concatenate(If(CurrentValue.[Image URL].IsNotBlank(), Image(CurrentValue.[Image URL].ToText(), 800, 600), ""),
Format("
{1}
Service Type: {2}
Color: {3}
",[Output Formatting].Details.First(), CurrentValue.[Service Type].ToText(),
CurrentValue.Color.ToText())))))
)))))
Default Text formatting for a column would be really helpful, right now I have to manually format each row (which my users won’t know to do, and won’t be able to do at all on mobile). Alternatively, a formula that formats the text display could do the trick. Perhaps FormatDisplay or extending the Format syntax could work.
E.g.
Format("#Example Header 1 ##{1}", "Example Header 2")
output:
Example Header 1
Example Header 2
This approach could do the trick too, though it currently does not work.
Lack of access to external namespaces is surprisingly not limiting.
In a standard loop you might instantiate a variable and then access it later. With Coda, you build it into the row that you’re accessing (by way of CurrentValue) with this method you can access any data (so long as your tables are linked with Lookups or cross-populated with Filter formulas).
Images are very difficult to work with in formulas. The Images function (inexplicably) only works with external images. Whereas if you’re trying to access an image that is already within your document it appears in a uselessly small thumbnail format.
As a potentially important additional constraint for any product developers looking at this, the image also needs to be able to be Concatenated with the prior and subsequent text.
The additional strangeness is that converting the image to a url with imageName.Url() and then putting that into the Image formula Image(imageName.Url(), 100, 100) doesn’t work. Just shows a blank image:
Hacky Solution
It is possible to access the direct link to the aws image. It requires uploading the image, manually copying the url, then pasting it in a new “ImageUrl” column, which can then be opened with the Image formula. Your url will look something like:
https://coda.io/contentProxy/TTUWqb1vdS/blobs/sn-G5RznG-B5I/b86f53a52af8c31399274e22a5d4b46533c2f28b8201fc353ee071838954a906c6d0c0269b5fdd0d856f3e7e68d5c9efcbe93e5941750de88b31d104e76e33f7a24126d0acfd06170205872c80a098b4c66f2cd0c1927502496f526ef5a21c4a68e87d5f
(this one has been modified for privacy, but that’s the general format). There is currently no way to access this directly in a formula. But a formula that gives access to the image could be another workaround.
Output with Hacky solution:
It looks ok! You just have to be careful to resize your images to the proper size before using them (which is annoying but workable).
See this thread to follow this issue:
Use Table.FormattedTextColumn.FormulaMap(ToText(CurrentValue)) to remove formatting from the contents of columns. Don’t use Table.FormattedTextColumn.ToText().Split(',') as it will add unsightly random spaces.
Example to come
The Image function formatting parameters (like width, height, and style) don’t work at all with the image urls I’m providing to the function. Not sure why this is the case.
They do work outside of Concatenate formulas:
But once you include them in a Concatenate formula it breaks everything but the style formatting (i.e. it gets too big):
Same is true for images passed to the Format formula.
Embed, on the other hand, does seem to work both inside Concatenate and inside FormulaMap loops. One small problem is that it is very slow to load.
One big problem is that it seems it’s impossible to Concatenate an Embed with other text. If I could do this, then I could get this thing working, but it doesn’t. I’ll keep looking for a way around it.
Never mind, this does work. Concatenate Embeds for the win (they do have too small of sizes, but better than the Image function which was massive). Here’s what they look like:
There is not a way to easily do the equivalent of a python zip() function, which makes CSVs very hard to generate. A toCSV would also work.
Got around it with:
Printing my page (to save to pdf) seems to be impossible:
It used to work, so I’ll have to debug what exactly triggered this encoding error.
All told, all of these issues can be overcome for a decent outcome. I have some remaining complaints about how pretty the output is, but those will have to come in an updated/revised post.