Baserow is a powerful platform for organizing, managing, and analyzing data.
One key feature is the ability to use formulas to perform calculations, manipulate text, or evaluate conditions based on the data stored in your database. They follow a syntax similar to many programming languages and spreadsheet applications.
What we’ll do
In this tutorial, we’ll explore 20 commonly used functions via an inventory management system use case.
Prerequisites
To complete this tutorial, you’ll need the following:
Let’s dive in!
Working with Baserow formulas
In Baserow, we use formulas to perform calculations or manipulate data, and functions are the building blocks of those formulas.
First, set up your Baserow database. Add a new table or use a table within an existing database.
To get started with our inventory management system, create the following fields:
| Field name | Field type |
|---|---|
| Product Name | Single line text |
| Supplier | Single line text |
| Cost | Number |
| Unit | Single line text |
| Current Quantity | Number |
| Minimum Quantity | Number |
| Reorder Date | Date |
| Total Sales | Link to table |
1. concat()
To create a Purchase Order Number or Product ID/SKU, we can use a combination of concat(), today(), and the existing fields in the table.
If we want the Purchase Order Number to follow a format like “PO-yyyy-mm-dd-001” where “yyyy-mm-dd” represents the current date and “001” represents the row ID:
- Use the
today()function to extract the current date and format it appropriately. - Use the
concat()function to combine the “PO” prefix, date, and a sequential number.
concat('PO-', today(), '-0', row_id())
2. add(), multiply(), divide(), minus()
The add() function adds its two arguments together. It can be used with numbers, text, dates, and date intervals. The multiply() function returns its two arguments multiplied together. The divide() function returns its two arguments divided, the first divided by the second. The minus() function returns its two arguments subtracted.
3. if()
Next, we want to assign priority levels to restocking based on the current quantity of each product. By using the if() function, we can evaluate the “Current Quantity” against the “Minimum Quantity” to determine whether a product needs to be restocked.
If the first argument is true then returns the second argument, otherwise returns the third.
IF(field('Current Quantity') < field('Minimum Quantity'), "⚠️ URGENT: Reorder needed", "No action required")
field()function returns the field named by the single text argument. Syntax:field('a field name')
4. multiply()
The multiply() function returns its two arguments multiplied together. This is particularly useful for calculating total costs, revenues, or where you need to multiply two values together.
field('Current Quantity') * field('Cost')
This calculates the total cost by multiplying the current quantity of the product by its cost per unit.
5. length()
The length() function calculates the length of a text string. You might use it to ensure that product names do not exceed a certain character limit.
length(field('Product Name'))
6. left()
The left() function extracts a specified number of characters from the beginning of a text string. You could use it to extract the first few letters of a product name for abbreviation purposes.
upper(left(field('Product Name'), 3))
This takes the first three characters of the ‘Product Name’, converts them to uppercase, and returns the result.
7. datetime_format()
The datetime_format() function formats a date or time value into a specific text format. You can use it to display dates in a more readable format.
datetime_format(field('Reorder Date'), 'MM-DD-YYYY')
It’s always in UTC. Use datetime_format_tz() to specify a timezone.
8. date_diff()
The date_diff() function calculates the difference between two date or time values (‘year’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘seconds’). It calculates and returns the number of units from the second argument to the third.
date_diff('day', field('Delivery date'), field('Reorder Date'))
This determines the number of days between the reorder date and the delivery date.
9. when_empty()
The when_empty() function checks if a field is empty and returns a specified value if true. It can be useful for displaying custom messages or calculations based on empty fields.
when_empty(field('Supplier'), 'No Supplier Yet')
It must be given exactly 2 arguments and both inputs for when_empty() must be the same type.
10. and()
This function evaluates multiple conditions and returns true only if all conditions are met. It returns the logical and of the first and second argument, so if they are both true then the result is true, otherwise it is false.
and(field('Current Quantity') < field('Minimum Quantity'), field('Reorder Date') <= TODAY())
The and() function combines these conditions and returns true only if both conditions are met simultaneously. If both conditions are true, it suggests that the current quantity is below the minimum required and the reorder date has arrived or passed, indicating that it’s time to reorder the product.
11. or()
Similar to and(), this function returns true if at least one of the conditions is true, providing flexibility in data analysis. It’s useful for evaluating multiple conditions with alternative outcomes.
OR(field('Total Sales') > 100, field('Current Quantity') < field('Minimum Quantity'))
This shows whether a product needs attention or action. It returns true if the total sales of the product exceed 100 units, the current quantity of the product falls below the minimum quantity required.
12. replace()
This replaces all instances of the second argument in the first argument with the third argument.
replace(field('Product Name'), 'Apples', 'Oranges')
Regular expressions (regex) allow for flexible pattern matching in text strings. You can use regex to search for specific patterns or characters within text fields.
The regex_replace() function allows you to replace any text in the first input that matches the regular expression specified by the second input with the text in the third input.
13. button()
The button() function creates a button using the specified URI (Uniform Resource Identifier) as the link and the provided label as the text displayed on the button.
button('http://baserow.io', 'Try Baserow')
You can use dynamic content within the function, such as field values or calculated results. When clicked, the button will open the specified URI in a new browser tab.
14. date_interval()
The date_interval() function returns the date interval corresponding to the provided argument.
Let’s say the reorder interval for your products is 7 days from today. You want to calculate the reorder date by adding this interval to the current date.
ADD(TODAY(), date_interval("7 days"))
The add() function adds its two arguments together. It can be used with numbers, text, dates, and date intervals.
15. join()
Formula functions, for example, isblank(), or when_empty() work with simple values like text, number, or date fields. Computed fields like Link-to-table, Lookup, and Rollup fields can contain multiple items which makes them arrays or lists.
To create formulas to make a boolean test on data in field A, taking data from field B if it’s TRUE, otherwise taking data from field C if it’s FALSE, you need to convert any array to text using the join() function.
Use join() to convert the list to text and handle the empty scenario correctly.
if(isblank(join(field('Total Sales'),'')), field('Product Name'), field('Supplier'))
16. divide()
The divide() function returns its two arguments divided, the first divided by the second.
((field('Current Quantity') - field('Minimum Quantity')) / field('Minimum Quantity')) * 100
This calculates the percentage increase or decrease in quantity compared to the minimum quantity.
It retrieves the value of the “Current Quantity” field and the value of the “Minimum Quantity” field.
field('Current Quantity') - field('Minimum Quantity'): This calculates the difference between the current quantity and the minimum quantity.((field('Current Quantity') - field('Minimum Quantity')) / field('Minimum Quantity')): This calculates the relative change as a ratio, comparing the difference to the minimum quantity.((field('Current Quantity') - field('Minimum Quantity')) / field('Minimum Quantity')) * 100: Finally, this multiplies the ratio by 100 to display the result as a percentage.
17. filter()
This filters down an expression involving a lookup/link field reference or a lookup function call.
Syntax
filter(an expression involving lookup() or field(a link/lookup field), boolean)
Let’s filter to sum numbers less than 10 from a linked field:
sum(filter(lookup("Total Sales", "Quantity"), lookup("Total Sales", "Autonumber") < 10))
18. contains()
You can use the contains() function to check if the “Product Name” field contains a certain text string. It returns true if the first piece of text contains at least once the second.
contains(field('Product Name'), "Apple")
This formula will return true if the text “Apple” is found within the “Product Name”, and false otherwise.
19. today()
This function returns the current date in UTC.
We’ll use the “Reorder Date” field with the today() function.
today() > field('Reorder Date')
This formula checks if today’s date is greater than the reorder date, and displays a boolean value indicating whether a reorder is due or not.
now()returns the current date and time in UTC.
20. year(), month(), day()
The year(), month(), and day() functions return the year, month and day in the provided date.
The
second()function cannot extract seconds from a date without time.
concat('Year ', year(field('Reorder Date')), ', Month ', month(field('Reorder Date')), ', Day ', day(field('Reorder Date')))
The formula concatenates the year, month, and day from the “Reorder Date” field into a single text string with additional labels.
year(field('Reorder Date')): Extracts the year from the “Reorder Date” field.month(field('Reorder Date')): Extracts the month from the “Reorder Date” field.day(field('Reorder Date')): Extracts the day from the “Reorder Date” field.concat(): Concatenates these extracted values along with the specified labels.
This creates a detailed representation of the date, including the year, month, and day, which can be useful for reporting or analysis.
Summary
In this tutorial, we covered some of the most essential functions, but Baserow offers a wide selection of functions that help you create elaborate formulas.
As you become more comfortable creating formulas, you can explore functions for manipulating text with regular expressions, working with dates and times in more detail, and using statistical functions for deeper data analysis.
The key to mastering Baserow formulas is practice! You should experiment with different functions and see how they can help you organize and manage your data.
Other useful resources
The following articles may also be helpful:
- How to enable workflow automation for teams
- 6 time-saving Baserow formulas
- Explore Baserow use cases
In case you’ve run into an issue while following this tutorial, feel free to reach out to ask for help in the Baserow community.
