QuickBase is designed for the business user (read: non-database engineer), so the latest release is all about making reporting on powerful applications simpler. To simplify reporting, we’ve reduced the need for formula fields in advanced filtering by including some of your most common queries in search criteria.
The two new methods for building advanced filter criteria without formula code are: 1) Relative Data Criteria with Time Zone & Fiscal Year Settings and 2) Value in the Field Criteria. These changes reflect our continued focus on minimizing the need for users to develop complicated solutions for common tasks.
Relative Date Criteria
Let’s say that you want to create a report in QuickBase that will show you all of the opportunities that closed during the current month. Before this release, one approach was to create a formula-checkbox field, called [Current Month?] that would calculate whether the [Actual Close] date occurred during the current month using a formula similar to:
If (([Actual Close] >= FirstDayOfYear(Today())) and ([Actual Close]<=LastDayOfYear(Today())), true, false)
You could then create filter criteria that would only show those records where the Current Month? Field was checked, such as:
Now, there is no need to create a new field just to evaluate these filter criteria. Simply use the new is during and is not during operators to create filter criteria based on relative durations of time. For example, to view all opportunities closed this month you would select the criteria as shown below:
Note that we are also dynamically providing a live preview of the calculated duration to the right of the criteria.
Setting Your Own Fiscal Year & Time Zone
Other examples of the Relative Date Criteria include, next week, the previous 2 fiscal quarters, the next month, etc. You may be asking yourself, “My fiscal year starts in August and my week starts on Monday, how will QuickBase know that and return the correct records? The answer is: By allowing you to configure the first month of the fiscal year and the first day of the week through Customize>Application>Settings>Advanced Setting as shown below.
As a bonus, those of you with keen eyes will also notice that we have added another highly requested enhancement to these options. With this release, you can now override the billing account’s time zone with an application-specific time zone.
The Value in the Field Criteria
Another common use case for formula fields is to evaluate filter criteria that compares the values of two fields. For example, let’s say that you want to create a report that shows you all of the tasks that were completed after their estimated finish date. Within the report builder, you may now create a filter that compares the values of Estimated Finish and Actual Finish fields for each record. QuickBase will then return only those records where the Actual Finish date is greater than the Estimated Finish.
The Value in the Field operator works for more than just date fields. You can use it to compare just about any other field type that makes sense, such as text, checkbox, numeric, etc. The only restriction is that the two fields being compared must be the same field type.
To learn more and see other examples of the new features, check out the release notes and video.