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.

- 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.
- When you use multiple
`field`

references to link/lookup tables in the same formula, this results in multiple joins.
- 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