`Join()` formula should not apply to blank entries

If I have the formula:

Join(" ", [First Name], [Last Name], [RowId])

And [Last Name] is blank, the result I’ll get back will be something like:
Connor 123 where there’s a small extra space between the name and the id.

It would be better if the Join function knew to only add the space if the value is non-blank.


Before anyone recommends using the Trim() formula, this doesn’t work very elegantly. Just go ahead and try to do the trim for this particular instance.

Furthermore, it’s not always sufficient, for example, what if I’m constructing a hierarchy like @Todd_Olson was asking for the other day:

List(1, "B", "", 2).WithName([Levels List],
  Join(".", [Levels List])
)

(Let’s imagine here that List(1, "B", "", 2) is actually the result of some formula).

What I would ideally see is this:
1.B.2.

But instead, I’ll see 1.B..2. Not really useful.

What if I really want a dot to show up there? I propose that if you pass in the separator as the value to the Join it causes just that value to show up. I.e.

Join(".", 1, "B", "", 2)1.B.2., and
Join(".", 1, "B", ".", 2)1.B..2. (not 1.B...2. like it would do right now.)

This is safe to do because the only case that you can’t produce with this sort of join would be Join("", [Values]) but of course that’s just a place to use Concatenate([Values]).

Suddenly this would allow us to do things like:

Join(" ", [First Name], [Last Name], [RowId], [Column], ...)

Without having to think at all about whether one of those is blank.

The current alternative is to write a regex like [Value].RegexReplace("\s{2,}", " ") but regexes are:

  1. opaque (I mean, what does "\s{2,}" even mean?)
  2. not well known
  3. error prone

Alternatively, a formula like, RemoveDuplicateSpaces() could do the trick.

Could you filter the list to remove any entries that are empty strings?, e.g.

List(1, "B", "", 2)
  .Filter(CurrentValue != "")
  .Join(".")

1.B.2
1 Like

Yeah you could!

In fact the super short version is:

List(...).Filter(CurrentValue).Join(".")

The filter knows to treat empty strings as falsey.

But there’s two-ish problems with this.

First,

Join(" ", [First], [Last])

Is how I normally pass in the values, it’s very rare they’re actually in a list. You could get around this with:

Join(" ", ListCombine([First], [Last]).Filter(CurrentValue))

That is much more verbose and will get you what you want.

Here’s the thing though: I know how to get what I want here. But if you’re picking up Join for the first time I don’t think you’ll guess that empty entries will produce a mysterious extra space.

I guess I’ll put it to you: what would your expectations be?

1 Like

Holy mushrooms -

You are saying Coda respects truthy/falsey values? In all respects like JavaScript or just this case?

2 Likes

Oh, sorry. I kind of misread your question and didn’t understand it was about expectations of behaviour. Thanks for the tip about the filter.

From my perspective, join does exactly what I expect, and that’s probably because my expectation is shaped on other “joiny” functions that I’m familiar with: e.g.

C#: Console.WriteLine(String.Join ( ".", new String[] {"1", "B", "", "2"} ));
Google Sheets and Excel: =join(".",{1, "B", "", 2})
Javascript and Typescript: console.log([1, "B", "", 2].join("."))
Python: print(".".join(("1", "B", "", "2")))

All of these print 1.B..2

So for me at least, that consistency means the designers have done a good job in applying the principle of least surprise.

2 Likes

That would be so good!

2 Likes

Here are the falsey ones:

List(
  List(), "", false, 1, true, "A", List(1,2)
).Filter(CurrentValue).BulletedList()

* [ ]
* 1
* true
* A
* 1,2
1 Like

One of the interesting things about Coda is it gets to assume that its users don’t have a programming background, and then it gets to choose how its formulas should work. It’s resulted in them being able to rethink a lot of things.

I think this is a unique case where the choice of the commonly known pattern could have been inspected more. And yet, I’m only 40% that what I described is the better way, though I’m 70% that there might be a better way.

I could be wrong if:

  1. It turns out non-programming-background users expect and prefer the 1.B..2 output.
  2. If there’s something you can’t represent using the Join() semantics
  3. If there is an equally common usecase for Join() where the new semantics I described create the need for equally complicated formulas

Haha… and also Coda basically never implements my formula change requests, only occasionally implementing new formulas, and never to my knowledge changing the functionality of old ones — which makes sense!

Here’s a formula I’d really love, if Coda has to prioritize:

1 Like