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

[baserow.rvor6]

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?

[baserow.rvor6]

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.

[olgatrykush]

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

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

[baserow.rvor6]

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.

[nigel]

Hi @baserow.rvor6

Sorry for the delayed response, August holidays

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

[Mary-Lou]

Hi @nigel,
I think I am facing a similar issue.
I would like to concat several field types:

• single-select fields
• link fields
• lookup fields
• text fields
  Any of the fields could be empty.

I have tried wrapping all lookup and link fields with a join function and then concat all fields, unfortunately, it does not work.

[olgatrykush]

@davide could you please help out here?

[davide]

Would something like this work for you @Mary-Lou ?

totext(field('single select')) + totext(field('multiple select')) + join(field('LinkTable 2'), ',') + join(field('Lookup Name'), '')

[Mary-Lou]

Hello @davide,
Yes, it worked. Thank you! But I was not able to include a date field. Any suggestions?

[davide]

You can use totext or datetime_format (or datetime_format_tz if you need to localize time)

[mpstaton]

Just to hammer the point home to the Baserow team on this thread:

I am trying Baserow as an alternative to Airtable and we have what I might call “small, complex, highly-interrelated data.” Let’s call it SCHID marks just because I think it’s a whole category of real life use case completely ignored by pretty much all databases and data tech I am aware of. If you care, my use case is data management for private equity and venture capital.

The main hack to handle highly inter-related data I have found is to use concat strings as default IDs (though not 100% unique), and those strings can be parsed by scripts on the UI/client side. So, “Big-Company::Capital-Event:|{2024-10-04}|USD&{2,000,000}|” can be parsed through regex as Big Company raised $2M on October 4th, 2024.

I have stayed in the “trying” Baserow phase for a while now, and haven’t fully committed because of this shortcoming discussed on this thread.

I want to write functions that can aggregate any kind of field into a string, and I’m constantly hitting errors for two reasons:

1. many fields are empty, which is normal and fine in real life but requires these workarounds you are detailing above.

2. many fields have different data types. My most common is an array trying to fit into a string.

The error handling and function feedback messaging is not generally helpful.

While I am sure that people that spend a ton of time in databases understand the logic you have to deal with, the whole market for “Database Apps” like Baserow is so non-engineers can more or less set up and manage data models and data integrity. So, I can’t be the only one with this need and deep burning desire for the functions to use human logic rather than database logic.

If I say I want an array, a number, a string, and a linked field to all show up in a concatenated string, that’s exactly what it should do and it shouldn’t be harder than using the “+” operator, and definitely shouldn’t be harder than using the concat( all, my, objects, lumped, together ).

If the output is unwieldy, then that’s on me to parse.

[olgatrykush]

Hey @mpstaton, thank you so much for sharing your feedback. I’ll make sure to discuss it with the team to see how we can make formulas more human-friendly for non-technical people.

[mpstaton]

Again, it seems enough of the userbase wants to concat the values of other fields, particularly linked fields to other tables, into a string. I use the join() operator, which works sometimes and not others for reasons I don’t understand.

[olgatrykush]

Hey @mpstaton, we’ve created this general issue to investigate your feedback further:
Make formulas more user-friendly (#3154) · Issues · Baserow / baserow · GitLab. We can’t promise to address it quickly, but we’ll get there.

Meanwhile, if you face any issues with formulas, please feel free to post here, and we’ll do our best to help you fix it. Additionally, providing a specific example of what’s not working for you would be extremely helpful. It allows us to break down the task into smaller, more manageable issues.

[qtld]

Hello,
I’m facing the issue to concat multiple fields because I would like the formula field to treat each item in the linked field as one and not aggregate all the items linked in the “field 1” as one item and all the items from “field 2” as the second item.
Like in my exemple, To get my form working the best way for users I need to separate Cameras, lenses etc…
So I have one linked “Camera” field and one Lens field in my user table that link to my gear table.
Now If I want in my gear table a boolean field that tell me if the gear is out in rental with an user, I must have a field for each category (Camera, Lens,…). It would be much easier if I can create in my user table a field “All Gear” that get the value from all categories fields into one.
Then I create a rollup to checkmarck if the Name of the item is in this “All Gear” field.

Could you tell me if it’s possible ?

Thanks a lot.

(PS : since I’m in a phase test with 3 categories instead of dozens, i hae a workaround by creating a hidden rollup field for each category and a formula field to checkmark if any of the 3 field has a checkmark. but this is a lt of trouble if it get’s bigger… but maybe this is it.)

[AlexP]

Hello, @qtld

Can you please share an example of your table structure so it’s clearer what you want to do? A screenshot would be really helpful

[qtld]

Hello,
Thanks for your help, here is a screenshot of the connected gear fields:

And the screenshot of the formula field:

As you can see, I can’t find a way that the formula field list simply each item from the camera + lens + battery fields separatly, without repeatition.

I would love having it look just like this for the first line:

So I hope that if I get it this way I will be able to creat a rollup to this field only to checkmarck if the gear is out or not.

Thanks !

[AlexP]

Hey @qtld ,

Can you try this formula:

join(field('Camera'), ', ') + ', ' + join(field('Lens'), ', ') + ', ' + join(field('Battery'), ', ')

[qtld]

Hi and thanks for your try !

It is already better looking but it is text only so it’s not recognised as an “item” like in a linked field so the lookup from another table does not recognize it.

Maybe it’s a limitation from baserow ?

[bud]

One potentially simple workaround would be to try using “totext()” wrapping your "field()"s. I haven’t tested, but that may work?