 # 6 Time-Saving Baserow Formulas

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

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

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

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

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

Thank you for sharing

1 Like