Solved: Output text list in columns in a G-Doc template

DMiles · 11-09-2022 05:54 AM

I have an EnumList field containing numerical ids (e.g. 1,3,6,10)
These ideas correspond to text statements in another table.
I can successfully pull out the statements, but I'd like them to output one to a line.
E.g. Instead of Statement 1, Statement 3, Statement 6, Statement 10, I'd like

Statement 1
Statement 3
Statement 6
Statement 10

How do I do this?

WillowMobileSys

I found my example but it was using EnumList of text comments rather than a Ref. But i was able to build this similar function in a test app.

Test Template Output (not pretty but one item per line)

************

I created a Virtual Column using the EnumList column as follows:

Virtual Column Definition

Full expression in Virtual Column

View solution in original post

WillowMobileSys

I assume then that you are using a SELECT() to pull the textual column based on the row ID?

What you should be able to do is, in the document template, replace the "comma" delimiter with a line break. Something like this:

SUBSTITUTE(SELECT(...), ", ", "
")

Note that the last closing "quote" is on another line because a physical link break was inserted - i.e. just press the "return" key.

I hope this helps!

DMiles

So - that works beautifully within the email template, but when I try it within the Google Doc itself, it isn't including the line breaks?

Now the annoying thing with this beautiful solution is that some of my text statements have commas in them, so it is of course splitting there as well 😞

WillowMobileSys

@DMiles wrote:

but when I try it within the Google Doc itself, it isn't including the line breaks?

Hmm, I now vaguely recall that as an issue. Let me try out a couple of tests to refresh my memory. I may have ended up solving it another way.

If you are allowing commas in the statements, then you MUST choose a different delimiter for the EnumList column. Maybe use semi-colon instead. You would specify this in the EnumList column definition.

DMiles

I'm currently finding that I cannot change the delimiter - I've tried | and ; and although the data is stored that way, the substitute function isn't seeing it - in fact the output is comma-separated.

DMiles

Okay, so weirdly, if I try this, it works, giving me a vertical list of numbers

<<Substitute([InstructionalDelivery],";","
")>>

But if I try this, it doesn't work.

<<Substitute([InstructionalDelivery][Criterion],";","
")>>

WillowMobileSys

@DMiles wrote:

[InstructionalDelivery][Criterion]

It may due to the portion above generating a list internally to the expression and therefore not subject to the column defined delimiter? What result do you get?

DMiles

Yeah, I'm pretty sure that's it. I have it working now. More details below

DMiles

Okay, since all of my statements end with a period (or at least can - I might have some editing to do, I changed the substitution search to ". , " which solved that issue, but I still need to make this work within the Google Doc.
I have seen references to a VC and Long Text, and I did get that to work pushing out the numerical values onto rows, but I'm not certain how I would do that in this case where it's the text I want.

WillowMobileSys

@DMiles wrote:

have seen references to a VC and Long Text, and I did get that to work pushing out the numerical values onto rows, but I'm not certain how I would do that in this case where it's the text I want.

I believe that is how I solved this for myself. I'm trying to find that example or prove it out in testing.

You would simply copy the SUBSTITUTE(SELECT()) into the App Formula of the Virtual Column. Define the VC as LongText. Then just simply use that column directly in the doc template AND to be consistent, use it in the email body template as well.

WillowMobileSys