Introducing formula queries

Qrew Captain

3 years ago

This feature allows formulas to operate on data from other records or other tables in the same app – no relationship needed. This feature is perfect for intermediate and advanced builders with some experience with formulas. In this article, you’ll learn how you can use formula queries to solve more problems in Quickbase while using fewer tables and relationships.

Why we're focusing on formulas
Background on formulas
The challenge
How formula queries help
Who should use formula queries
How to build a formula query
Where relationships fit in
Query performance
Learn more

Why we’re focusing on formulas

Quickbase enables business users to build and iterate on custom apps, analyze data, and integrate with systems of record. We’ve seen companies deploy solutions on the Quickbase platform in days which would take months on other platforms. But the best part is that these apps can be updated with ease as business needs change. One of the ways we enable this kind of innovation is with our formula language. In the blink of an eye, a single Quickbase formula can span 100 million cells of information, leveraging the power of our purpose-built, in-memory database to deliver actionable information to your users, secure and validate your data, and even drive workflow.

Over the past twenty years, creative app builders all over the world have used Quickbase formulas to solve stubborn process challenges. This has helped them to keep up with the demands of the market and provide better service for their customers. But the work of building an enterprise-grade platform is never finished. That’s why we asked ourselves what the next twenty years of solving your most urgent process challenges will look like. To do this, we need an improved formula language which is more powerful and more flexible than ever before.

Background on formulas

Formula queries make it much easier to build calculations based on data from other records, and data from other tables. Before we explore those new capabilities, let’s review how formulas combined data from multiple fields before queries were added.

Quickbase formulas could operate on data from any field, as long as that data was on the same record.

To include data from another table in a formula, a lookup or summary field needed to be added first. Sometimes, such fields already existed on the table. If they did not exist, it was straightforward to add a new lookup or summary field.

But what happened if the table you wanted to pull data from was only indirectly related, like pulling data from a grandparent record or a great-grandparent record? In that case, you could use the concept of lineage to add a lookup field to each of the three relationships (you can watch this video to learn more about relationship lineage).

In some cases, you may have needed to create one or more relationships that allow you to add the lookup or summary fields you need for a formula. That may even require creating new tables to set up the formula you need. This approach is still vital to delivering real-time insights your users can rely on to make important business decisions. However, at a certain level of complexity, using this approach to build a formula becomes troublesome.

The challenge

The most valuable Quickbase apps have evolved over a period of months or years. As business needs change, builders make incremental changes to their app over time. The longer an app is used, the more opportunity there is to fine-tune it to your business’s needs. Accelerating continuous improvement like this is an important way companies see immense value in scaling out their operations on Quickbase.

We’ve seen very sophisticated and robust apps developed over the years, linking dozens or even hundreds of tables and relationships together. This is a key element that enables Quickbase to solve such a broad range of process challenges. However, adding tables and relationships just to build a formula can lead to inefficiencies when updating and maintaining your app. This can result in missed customer deadlines, missed performance targets, and general frustration.

How formula queries help

Quickbase provides a range of app-building tools which can be used in many different ways, such as fields, relationships, and formulas. Formula queries can also be used in a variety of ways. You can use them to:

• Find duplicate records in a table (learn more about this on the community)
• Set up records with automatic, sequential IDs (learn more about this on the community)
• Calculate running totals (cumulative statistics)
• Create advanced calculations, for example identifying company holidays within a certain period of time
• Create advanced report filters
• Create fields with outputs similar to lookup or summary fields without creating additional relationships

You may already be brainstorming new formula fields you can create using these new queries. Additionally, because our formula language can be used throughout Quickbase, many other areas of your apps just got a major upgrade, such as:

• Report formulas, which enable you to gain temporary insights quickly
• Custom data rules, which allow you to implement more sophisticated data validation
• Your custom scripts, which can now run more elaborate formulas, reducing the need to write custom code (read about the RESTful API's runformula endpoint that supports this on the API Portal)

We’ll work together to discover new and exciting ways to streamline your apps using formula queries, and we look forward to seeing the creative solutions you build.

Who should use formula queries

The Quickbase formula language is available on all billing plans, and so is the formula queries feature. All app builders have access to use the new query functions in their apps. However, we do not expect new app builders to be using formula queries on day one. We’ve designed formula queries as a more advanced feature that helps you solve more process challenges using Quickbase.

The new functionality will be the best fit for intermediate and advanced app builders with experience building formulas. Some knowledge of building queries in our API is helpful, although that is not essential. That’s because it is the API query format that’s used for formula queries. Learning how to use this new feature is a wonderful opportunity for citizen developers looking to develop deeper technical skill to build their knowledge and expertise.

We also expect formula queries to be more approachable for users with a background in computer programming or databases. While Quickbase still runs completely on a purpose-built, in-memory database, formula queries operate more similarly to a traditional relational database.

How to build a formula query

We’ve added five new functions to support formula queries:

• GetRecord: fetches a specific record for reference
• GetRecords: dynamically searches for records using a custom query
• GetFieldValues: gets values from a specific field, from the records referenced in the GetRecord or GetRecords function (This gives you an actionable output you can use to either display to your app’s users, or to use in further calculations.)
• SumValues: calculate the sum of values in a field for records that match the query
• Size: counts the number of records in a text list, user list, or record list (This function can use GetRecords as input, or it can simply use a user list field or multi-select text field as input instead.)

You can use these new functions in any type of formula field. For example, imagine a budgets table has a numeric field with field ID 15, that tracks the estimated total of the budget. And the budgets table also has a text field with field ID 41, that tracks the status of the budget.

If you wanted to find the total estimated cost of all approved budgets, and use that calculation on another table, you could use this formula:

SumValues(GetRecords("{41.EX.'Approved'} "), 15)

You can find more information about how the new formula functions work in the help center. This article walks you through how to build the queries themselves, which is the portion of the formula between the curly brackets. This uses the format from the Quickbase API.

NOTE: Fields used in formula queries appear in field usage, making it easy for you to keep track of which fields are being used in a query.

Where relationships fit in

Formula queries are another tool in the Quickbase toolbox that allow you to build flexible, complex work flows and data flows with no code. The relationship capabilities of our in-memory database have been refined over two decades to provide lightning-fast performance. However, you all continue to surprise us with your creativity. That is why we spent time during the beta program optimizing performance, putting limits in place to educate and protect builders and our platform, and learning how you were using these new tools. Below are a few scenarios that offer guidelines on when to consider either method. We'll continue to learn together over time.

Query performance

Formula queries are powerful tools, and queries that are more complex will take longer to calculate. We’ve engineered formula queries to run as fast as possible. However, app performance is a shared responsibility between Quickbase as the platform vendor, and app builders like you. That’s why we have updated the Performance Analyzer to make it easier for app builders to improve and optimize the unique calculations you build (read more about the analyzer in our help center).

• The analyzer now evaluates the performance of formula fields that include queries. You can use this tool to analyze the performance of a report or a form loading in your app.
• When using the analyzer, you click into a formula that includes a query, and you will see how long each query took to run. If your formula includes several queries, the analyzer will display each one individually.
• When you save a formula that includes a query, Quickbase will automatically evaluate the performance of the query. If the formula does not meet our performance criteria, a message will appear prompting you to adjust your query.