Are you maximizing your use of Baserow? We’ve compiled a list of six simple yet effective formulas that can save you time, improve your workflows, and simplify data analysis. Check out the following functions to enhance your data management.
1. Function: length()
The Length function counts the number of characters in a cell.
length(text)
In a table used to track blog posts, for example, this formula is extremely useful for checking the number of characters in an article or meta description. Use the following formula to output the character count of the text field:
length(field('Meta description'))
Pro tip: Use the IF function to display a message based on character count.
One popular use for this formula is to detect how many characters you have entered and if you have exceeded the specified limit. Use a simple IF statement to display an output message based on the number of characters.
if(field('Meta description length')<=160, '✅', '❌')
2. Function: concat()
The Concat function combines arguments and/or field values from multiple fields into a single piece of text.
concat(any, any, ...)
Note: Use " " as a separator between the fields.
One of the most popular uses is to combine several values together in the primary field to have better visibility of the most important information in the first column.
Let’s use a CRM system as an example. It may be helpful to use the primary field to see the candidate’s name and the stage of the sales process. Remember, the data will be updated due to the latest changes in both linked fields.
concat(field('Company'), " - ", field('Stage'))
3. Function: date_diff()
The date_diff() function calculates the difference between two dates.
Finding the difference between two dates can be helpful for many workflows. Use the date_diff() function to find the difference in days between two specified dates.
date_diff(text, date, date)
Note: In your formulas, the end date must always be greater than the start date, otherwise the Date Diff function returns an error.
One use case for this function is to calculate how many days it took for a payment to be processed: one date is when the invoice was sent, and the other is when the payment was received.
date_diff("day", field('Transaction date'), field('Invoice day'))
Note: In Baserow, you can calculate the difference in ‘year’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, and ‘seconds’.
Alternative to date_diff()
We can also use the minus operator to subtract two arguments.
The same function carries the formula with an ampersand (-) as a minus character between values. See how we rewrote the formula from the example above using this alternative:
field('Transaction date') - field('Invoice day')
4. Function: trim()
The trim() function removes spaces from the beginning and end of a string.
trim(text)
The results will include the contents of the text field in each record without blank spaces at the beginning and end of the text.
You can use it when copy-pasting extracts from the Internet, and you don’t want to have any extra spaces at the beginning and end of a text. For instance, when adding a book synopsis to your reading table.
trim(field('Synopsis'))
Pro tip: Combined with other functions, trim() can help you accomplish more.
For example, if you want the character count formula to not count blank spaces at the beginning and/or end, use the following string:
length(trim(field('Synopsis')))
5. Function: left() and right()
The left() and right() functions extract a certain number of characters from the beginning (left) or end (right) of a string.
right(text, number)
left(text, number)
left("you are the best community member", 5)would result in “community”right("you are the best community member", 3)would result in “best”
These are helpful functions if you need to extract data from records that share a similar structure. For example, you may be importing a dataset where a single cell contains both a date string and a text string. These functions also support negative numbers.
Assuming that the dates follow the same format as DD-MM-YYYY, you can use right() to extract the date from the full field value:
right(totext(field('Date & time')), 6)
6. Function: upper() and lower()
The lower() function makes all characters lowercase, while upper() changes all characters to uppercase. On their own, these functions can help you ensure text is kept in the appropriate case.
Their functionality is very simple: lower("The Weather is Very Good Today.") results in weather is very good today. Replacing lower with upper accomplishes the opposite result: THE WEATHER IS VERY GOOD TODAY. Let’s see how this function works. Imagine, you need to uppercase the first letter in a word:
upper(left(trim(field("Text")),1))+right(trim(field("Text")), length(trim(field("Text"))) - 1)
Use these powerful formulas to take control of your data and open up new possibilities in your workflows.
Have another formula in mind?💡
If you have a formula to share, or have questions about how to use functions and formulas, drop a post in the Baserow community!
