6 Time-Saving Baserow Formulas

Tips&Tricks
,
1

Hello folks,

I’ve prepared an overview of 6 easy but time-saving Baserow formulas with examples and tips to help you improve the integrity of your workflow.

  1. Function: Length
  2. Function: Concat
  3. Function: Date diff
  4. Function: Trim
  5. Function: Left/right
  6. Function: Upper/lower

Check out how these functions work in detail :eyes: 6 Time-Saving Baserow Formulas // Baserow.

If you have another great formula you use for data management — share it in the comments. Or if you can’t figure out which formula to use to perform a specific function, feel free to ask here in the forum.

4 Likes
2

I’ve got a few formulas I use all the time, I’ll share them here. All of them make use of the regex_replace function, which is so incredibly powerful, I love it.

This one is probably what I use the most:

regex_replace(join(totext(lookup('LINK FIELD', 'LOOKUP FIELD')), '§') + '§', '§.*', '')

Since formulas in Baserow don’t support array-indexing, you can use this as a lookup which extracts the first value from the array. Very useful when you have link fields that will only ever contain a single link. It will convert the value to a string though, so you have to convert it back if it’s supposed to be something else.

if(field('NUMBER FIELD') = 0, '', if(contains(totext(field('NUMBER FIELD')), '.'), regex_replace(totext(field('NUMBER FIELD')), '(?<=\d)(?=(\d{3})+\.)', ','), regex_replace(totext(field('NUMBER FIELD')), '(?<=\d)(?=(\d{3})+$)', ',')))

This one converts a numeric field into a comma-separated number. If you use commas for decimal marker and periods for digit separation, just swap all instances of '.' and ',', and change the \. in the regex to ,. And while we’re still waiting on currency fields in Baserow, you can add '$' + (or your preferred currency symbol) right before the part that starts with if(contains(, to format a number as a currency.

These two are useful for dealing with imperial length measurements (feet & inches):

if(field('NUMBER FIELD') < 12, '', totext(trunc(field('NUMBER FIELD') / 12)) + "'") + if((field('NUMBER FIELD') / 12) = trunc(field('NUMBER FIELD') / 12), '', regex_replace(totext(round(field('NUMBER FIELD') - 12 * trunc(field('NUMBER FIELD') / 12), 2)), '\.?0+$', '') + '"')

This takes a numeric value in inches and converts it into ft’in" notation.

if(contains(field('TEXT FIELD'), "'"), tonumber(left(field('TEXT FIELD'), greatest(search(field('TEXT FIELD'), "'") - 1, 0))) * 12, 0) + if(contains(field('TEXT FIELD'), '"'), tonumber(regex_replace(field('TEXT FIELD'), '.*?(\d+)\"', '\1')), 0)

And this converts from ft’in" notation to a numeric value in inches.

Hope these are helpful to someone!

3 Likes
3

Hey @Jamac, thanks for sharing these formulas and for providing descriptions of how they work. I am sure your examples will be very useful and helpful to other community members :slightly_smiling_face:

1 Like
4

Maybe we can have a Baserow hosted Baserow database with formulas, explanation and samples :wink: I know you can search Discourse but over time it could get harder to collect or find the formulas.

1 Like
5

Hey @Peter that’s a great idea, and I think @nigel had a similar one. I’ll create a table with these formulas asap. I’ll also create a form, so everyone could add their ‘most used/useful’ formulas to that table.

4 Likes
6

Thank you for sharing

1 Like
7

Can I know how to use this, especially comma seperated number case?
Just copying the code to the Formula?

8

I got to know how to use the above code(for Comman Separated Number).
It(CSN by the code) is a string, so at the bottom, SUM is not activated.

Is there any solution for using SUM at the bottom for CSN?

9

Hey @hslee, could you please elaborate a bit more on this?

10

Comma Seperate Number(number like 120,000)
is string (if I use the code Jamac wrote the above).

So it cannot be summed at the bottom of baserow.

11

Hey @hslee, thanks for the explanation. I suspect you’ve added CSNs to the text field type, which means you can’t use the sum function in footer aggregations. A possible workaround is to create a new field and use a formula to convert these values using the tonumber function. Then, you can use footer aggregations. Here’s what I mean:

image
image1440×1500 42.2 KB

Formula 1: tonumber(regex_replace(field('CSN'), ',', '')) - to convert your values into numbers
Formula 2: if(isblank(field('CSN')), 0, tonumber(regex_replace(field('CSN'), ',', ''))) - to convert your values into numbers plus exclude any empty values if they exist

We also have an issue that will introduce advanced number formatting, which will include a thousand separator format: Advanced number formatting (#1270) · Issues · Baserow / baserow · GitLab. This should fix your issue.

Hope it help! :slightly_smiling_face:

12

Thank you for your kind answer.

1 Like