Formula help: Concat multiple fields -- even when a field in blank

I’m trying to get the contents of these four fields (red box in image) concatenated together in to another field (not pictured), we’ll call it “Exercise”.

Given the example image, this Exercise field would display only “Bench Press”, because that is the only field that contains anything.

However nothing I’ve tried has worked 100% correctly…


Stuff I’ve tried:

  • concat(field('Workout_1'), field('Workout_2'))

  • add(field('Workout_1'), field('Workout_2'))

  • join(field('Workout_1'), field('Workout_2'))

These examples almost work. As long as both fields (Workout_1 & Workout_2) contain something, then both field’s values are concatenated together into the Exercise field. That’s fine. The problem is, the Exercise field remains empty if either of the two fields are empty.


What I want:

  • If Workout_1 = “A” & Workout_2 = “B”,
    then the concat(field(‘Workout_1’), field(‘Workout_2’)) = “AB

  • If Workout_1 = “” & Workout_2 = “B”,
    then the concat(field(‘Workout_1’), field(‘Workout_2’)) = “B

  • If Workout_1 = “” & Workout_2 = “”,
    then the concat(field(‘Workout_1’), field(‘Workout_2’)) = ""


I feel like this is totally doable, what am I doing wrong?

I’ve been playing around with this since I posted this. Its still not working but now I’m leaning towards "this feature is broken " rather than "I just don’t know how to use it ".

I read the docs that say that you can’t do this with linked table fields. So I left the fields as is, created 4 new Formula fields with the formula field(‘Workout_1’). Then tried again using the Add, Join and Concat forumlas against these fields. I got the same results. As long as both fields contain at least one character it works, if you empty either field out it stops working.

So I tried Add, Join and Concat forumlas against “Single Line Text” fields and it works as expected. Both fields can be blank, both can be full, and either field can be empty/full and it works fine.

Where things got interesting is when I started mixing the field(‘Workout_1’) formula fields with Single Line Text fields using Join, Concat and Add… I can mix one formula field with as many Single Line Text fields as I want and it functions correctly. But the second I add a second, empty formula field, it breaks. If I add a second, formula field that’s not empty, it works correctly…

So there appears to be something about the Add, Join and Concat functions, when calculating multiple formula fields, that results in no output. My guess would be that those formula share some base level JS function that results in no output. Whether that’s intentionally built that way or a mistake, I have no clue. But thats what it feels like to me. Could be wrong.

Hello @baserow.rvor6! First of all, welcome to the Baserow community :wave:

Sorry that you are having issues with formulas and for any inconvenience this causes. It indeed may be a bug, we’ll investigate it further. I’ll keep you posted :raised_hands:

1 Like

cool thanks.

I was able to find a workaround for my use case.

I created 4 Single Select fields, and 4 Formula fields. The Formula fields did nothing but mirror the value of each Single Select field, like this field("SingleSelect Field #1"). Then I was about to use a 5th Formula field to join the 4 Formula fields, like this Join(field('formula field #1), field('formula field #2), field('formula field #3), field('formula field #4)).

I wanted to share this workaround with you as it might help the developers understand the issue a little better. :man_shrugging:

2 Likes

Hi @baserow.rvor6

Sorry for the delayed response, August holidays :slight_smile:

What you have encountered here is a side effect of what is going on under the hood with Baserow formulas. The formula that I believe you wanted is:

join(field('W1'), ',') + join(field('W2'), ',') + join(field('W3'), ',')

But why Baserow is behaving this way? I agree it’s not ideal and we plan to fix, I’ll explain:

Why does referencing an empty Link/Lookup field cause empty results

Baserow formulas under the hood are just SQL. There is no JS involved, formula results are calculated in Baserow’s backend using SQL.

  1. When you reference a link/lookup in a formula using the field formula, Baserow translates this into a relational join onto the linked/looked up table.
  2. When you use multiple field references to link/lookup tables in the same formula, this results in multiple joins.
  3. Currently because we are using INNER joins, this means if you join (use the field which references a link/lookup field) and the result is empty, the entire resulting list of results will be empty.

Another way of thinking about this is that currently, when referencing multiple “lists” of data (a link/lookup field) Baserow will generate a new list of data with every possible combination of the items in the referenced lists. If one of those lists is empty, then Baserow will always just generate the empty list.

This is both surprising and not ideal.

The current work around

When working with multiple different lookup/link fields in a formula, I would suggest always wrapping them with an aggregate function first before mixing them with other references to link/lookup fields. This will collapse the list of data down to a single value, which then can be worked with in a way that you expect. This is what I did with the suggested formula above: wrap each link reference with the aggregate join function that joins a list of values with a delimiter, then concat the resulting single values.

Potential Future fixes

I don’t think anyone wants Baserow formulas to behave this way by default. Nor do I think anyone wants the “Cartesian product” feature caused by using SQL joins this way under the hood. One fix I think we could do is:

Everytime someone references a link/lookup field in a formula and doesn’t use an aggregate function we implicitly wrap it in a join(sub formula referencing a link/lookup field, ','). This way you could easily write the

field('Link 1') + field('Link 2')

Formula and it would return Workout 1, Workout 2, Workout 3 by default.

@devishian I believe we’ve discussed a fix like this before, any opinions?

What is the Join function

I noticed you were using the join function a bit oddly and wanted to clarify exactly what it does.
The join function is an aggregate function. You can see all the aggregate functions in the formula modal if you scroll down and look for the list icon:

It works by taking a list (a formula referencing lookup/link field) and then “joining” together all the items in that list with the text provided in the second parameter. So for example:

join(field('My Workouts'), ' ---- ')

Will take the My Workouts link field, and join each linked value with the text ' ---- '

Add vs Concat

When you write text formula + text formula in Baserow under the hood we translate this to concat(text formula, text formula). The + operator and add function are the exact same thing, so add(text, text) is equivalent to concat(text, text)

Why does switching to a single select help

A single select field isn’t a list of values, just a single one, so no SQL joins will be going on when you reference one in a formula, you’ll just get the value of that cell empty or not just like you expect.

Hopefully this explains everything, i’ll discuss the potential long term fix a bit more internally and let you know when we make an issue to fix this :slight_smile:

1 Like