Formula action for blank "Link to table" fields

[Phorbox]

Greetings,

First of all - huge thanks to all developers for creating and supporting such an amazing tool. I’m building a very niche database to automate & control a couple of specific processes in my department, and Airtable is a not a viable solution simply because we need to have the self-hosted flexibility. Baserow’s closest competitor NocoDB is a bit unstable and clunky for our purposes (which among other things involve a great deal of duplicate many-to-many relationships which still don’t work as intended in NocoDB).

My question concerns fields set up as “Link to table” type, and how formulas in other fields referencing this “Link to table” field operate when the “Link to table” field is empty.

As an example, I have a table with a couple of fields.

• One of the fields named “Territory” is of the “Link to table” type, linking to another table with a list of countries.
• The next field (named “Availability”) has a simple formula checking whether “Territory” field has any values in it: isblank(field(‘Territory’))

It seems that the formula field works just fine when anything is linked from another table in the “Territory” field, displaying a red cross. However, when the “Territory” field is empty, the green tick is not displayed in the “Availability” field which I suspect means the formula returns an error.

Is this something that is planned to be addressed in future updates?..

Thanks,
Andrew

[nigel]

Hey @Phorbox ,

Glad to hear Baserow is working out for you! Onto your formula question:

1. When you write a formula referencing a Link to table or Lookup field (or directly use the lookup formula function) you are dealing with a list of values.
2. If you say then write isblank(field(‘Territory’)). isblank is not an aggregate function, which summarizes a list. Instead isblank is a normal function which tells you if a piece of data is blank or not.
3. When you use a normal function on a list of values in Baserow formulas, you will get a list back with that function applied to each item in the list. So you can see with the isblank formula above I get two red crosses (a list of trues and falses) when there are two linked rows:
   
   
4. So when the isblank(field(‘Territory’)) formula is calculated for a cell where field(‘Territory’) has no connections (its an empty list). The result is also an empty list and an empty cell.

If you want to instead summarize the field(‘Territory’) into a single value you should use one of the aggregate functions. In the formula editor these functions have a bullet point list as an icon:

For example you could instead write a formula like:

Which then results in a formula column with values of:

Does this answer your question?

Also your question has unearthed a bug. I initially tried to write the formula count(field('Terrorities Available In')) = 0 instead of isblank(count(field('Terrorities Available In'))). However for the empty cell I got the empty result instead of a green tick as I expected. This is a bug in the count formula function, when it counts an empty cell it doesn’t return 0 but instead the empty value. I’ve created this issue to track this bug. Count formula function should return 0 when the referenced list is empty (#923) · Issues · Bram Wiepjes / baserow · GitLab

Finally apologises for this behaviour not being obvious. We have yet to properly create formula documentation, but we do plan to. We also plan various updates/upgrades to the formula language in the future to make it easier to use and more powerful.

[nigel]

One extra oddity to point out here. When Baserow is displaying a list of trues and falses. A false value is displayed as a red cross (for example from the formula isblank(field(‘Territory’))). This is so you can clearly see which list items are false vs true.

Whereas when Baserow is showing a single true or false value (like from isblank(count(field('Terrorities Available In')))) a True value is shown as a Green tick, where-as a false is shown as a empty cell.

[Phorbox]

Understood - thanks for the explanation, Nigel!

I think I got a grasp of the logic behind fields that are treated as lists of values - I haven’t tested these linked fields with more than 1 linked value so it was curious to see that formulas are applied to each of the values in the field. Perhaps the logic can be updated to treat these as sort of a single piece of data?..

Let me show you an example (closer to what I’m achieving for my planned database), and how this example works differently in Baserow and Airtable.

Let’s say I have a table with a list of countries, and another table which lists agreements and their territories. For these territories I have two fields - one for available territories, and another for excluded territories. One additional formula field concatenates the values from the previous two territory fields to present a description of all available territories concat(field('Territory'), ' ex. ', field('Excluded Territory')) .

[Phorbox]

…And here’s how the same example looks in Airtable (using Airtable formula CONCATENATE({Territory}, " ex. ", {Excluded Territory}) ):

As you can see, the second row in Airtable example shows that Airtable likely joins or does something to the linked field values to treat them as single value.
In the third row of the Airtable example the same logic prevents empty “Excluded Territory” field from blanking the entire formula cell (as visible in Baserow).

[Phorbox]

Curiously, NocoDB behaves almost the same as Airtable:

Perhaps this is something that we’ll be able to see in future Baserow updates. Either way thanks again for the thorough explanation!

P.S.
One last curiousity that I’ve noticed - let’s say I have a table with a list of countries, say

1. Switzerland
2. Germany
3. Austria

…and another table with a “Link to table” field named “Countries” which I use to add countries from the table above.

In Baserow, if I add “Austria” and then “Germany”, the result will look as ["Germany";"Austria"], i.e. it will be arranged based on the order of rows in the “Countries” table.
In Airtable the result will look as ["Austria","Germany"], i.e. based on the order in which I’ve added the values, and not their arrangement in the “Countries” table. Technically I prefer the Airtable logic but that’s just me.