Improvements to the Quickbase Formula Engine: Kicking it into High Gear
As we embark on another 20 years of Quickbase magic, we plan on taking the best of what has made Quickbase special and investing to make it even better. I've made no secret of how cool I think Quickbase is. Our proprietary engine combines the traditional layers of database, application, security and interface all into one – leading to an exceptional time to value. In seconds, we often perform millions of calculations to deliver rich insights to our users. These calculations help them make operational decisions for their business in record time.We're hard at work on some major improvements to our formula engine, allowing you to perform computation on summarized data inside of a report formula, as well as enabling formulas which can access any field in an application without needing a relationship. Read on to learn more about how this can help accelerate your business in the new year.
Formulas
Thinking about how we prioritize our roadmap for a platform like ours is not easy. We often have competing personas using Quickbase which loosely can be categorized as:
- Business: Commonly referred to as Citizen Developers, these builders typically don't have a software development or database background. They likely have experience in Excel, but usually not writing code. Their closest comparison is a spreadsheet, not C# and SQL.
- Developers: Builders who have a functioning software engineering background, who usually expect Quickbase to work like a traditional software stack.
One area that continues to shine through, reaching a common ground with all of these builders, is our formula language. This is an area we constantly invest research in whether it is via analyzing support cases, usage, or user studies with our awesome XD team. We try to understand the balance between offering more power and making things simpler.
Our formulas can be used to drive custom data rules for validation, and color coding on a report. Formulas can be hundreds, or even thousands of lines long, referencing a virtually unlimited number of fields. They can also be immensely powerful with just one line. And, of course, these fields can reference summaries, lookups or even other formulas. They can be effectively the heart of an application's logic and workflow. And unlike traditional technologies, these formulas operate as discrete fields. That means users can report on them, they can drive permissions, and be used in API calls.
In the past year, we have added some minor enhancements to the formula engine to streamline things for builders. For example, we added SearchAndReplace() which drastically reduces complexity for managing text strings. WeekOfYear() simplifies date calculations, one of the most common things done in apps.
Up Next
We know that enhancing our formula language will unlock capabilities for our builders to solve even more complex problems. We will also drastically simplify existing patterns – taking our record time-to-value further. You may have seen me preview some of this in our Empower 2020 keynote. And now, with hands on keyboard, it's time to really start the planning process.
So, as a builder what can you expect?
First, we are going to offer more advanced analytical capabilities by allowing you to perform mathematical operations on summarized data inside of a report formula. The focus for this feature will be summary reports. You may be solving these problems today by use of a statistics table in Quickbase, sometimes referred to as fact tables in relational databases. In our initial prototypes and user studies, we were able to replace six extra statistics tables and the associated relationships and fields with just a couple uses of these new formulas. Tables are invaluable for organizing your data properly. But too often we see tables being created solely to power a few specific calculations, making your app more complex and challenging to maintain.
So, get ready to calculate timecards, budgeting, forecasts, inventory and more to your heart's content!
Next, get ready to access any field in an application without a relationship. Yes, you read that correctly... You will be able to access any field in an application without a relationship. Now, we aren't planning to replace relationships. They will continue to serve as the primary way to reference data between tables. And the power that people have achieved leveraging those relationships is amazing. You are all solving problems in Quickbase using these features that arguably couldn't be achieved in any other platform, without investing exponentially more time and resources.
We know though that there are plenty of times where creating extra relationships is overkill. The most simple of which is the classic "reverse parent child". This is one of many creative patterns (like lookups of summaries, 1-to-1 relationships, and more) that we can simplify. Maybe you need to gain visibility to a "sibling" record – such as viewing the primary contact name from a customer on an opportunity.
The ability to query any table inside of a formula, or reference multiple field values as variables inside of a formula without discretely defining lookup fields is going to simplify app structures even further. Formulas will allow you to gather one, or many records. Subsequently, that formula will be able to aggregate values it finds – effectively allowing for a summary field without a relationship.
For the first time, you will be able to aggregate values across records and tables in a single calculation. We have some work ahead of us to finish these features and get them in your hands - between testing, validation and performance optimizations.
Finally, there are a collection of things in formulas we have on the list that are small, but valuable, nonetheless. Things like allowing for regex inside of a formula to detect and act on text patterns for our most savvy builders.
Tying it all together
While they are only one piece of the puzzle, formulas drive real-time insights into operational data. Those insights may come by way of a single record form and a status field, a table report, an API call from another system or rich charts and dashboards. One of the most exciting things here is how improvements to our underlying engine ties to the initiative to modernize our interface. Our updated charts, reports and dashboards will come together visualizing data powered by the most sophisticated underlying capabilities in a no-code platform. Creating a beautiful KPI widget on a modern dashboard from complex calculations will be easier than ever – providing rich insights to users at a glance.
While builders will be using some new formulas in the first half of 2021 (and they will be great), I'm personally excited by how all the output of our different teams will blend in the next 18-24 months. Delightful dashboards, modernized reports, a new interactive form builder and updates to the special sauce – our unique in-memory database – will further cement Quickbase as the leading no-code platform.
------------------------------
Harrison Hersch
Director of Product Operations
------------------------------
