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!