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.

3 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!

1 Like
#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.

#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.

3 Likes
#6

Thank you for sharing

1 Like