hhersch
Quickbase Staff
Joined 2 years ago
User Profile
User Widgets
Contributions
Flying under the radar and what's next
Hi everyone - On Tuesday, December 17th, we'll have a webinar: 2024 Year in Review. With that in mind, I’ve put together a brief 3-minute video to highlight a few key updates you might have missed over the past few months. Many of these improvements fall into a category I often call fit and finish. You may have heard me talk about this with MarkShnier__You at Empower 2024. Product Announcements I See what's new! - Empower 2024 & EmpowerPro is now on demand This work is critical, as it removes friction in your day to day lives so you can focus on your most important work. In 2025, you can expect to see even more of this. How can you help make sure your voice is heard? Engage with our product team directly in our Feedback platform. We really do read every one of them, and much of our work this year was directly inspired by all of you. Harrison Hersch Sr. Director of Product ManagementRe: Transitioning to UTF-8
Hi Jennifer & others. I wanted to jump in and let everyone know that we've developed a tool which can significantly reduce the effort required to standardize data encoding. This tool will convert from Windows-1252 to UTF-8 in one sweep at an application level. While we can't guarantee it will have a 100% match rate, it should reduce the burden massively. The reason Data Collaborative couldn't do this was indeed complexity. To develop this against our APIs would require management of triggers (like notifications or integrations) as well as last modified/date modified and could cause issues for users. We've built this tool in a special way to bypass those issues. This is available in a private beta right now and should go GA next month. If you're interested in helping test it, please ensure you are registered in our beta app and reach out to me directly. ------------------------------ Harrison Hersch ------------------------------Quickbase RESTful API upgrades and post mortem
Recap Quickbase takes reliability and performance very seriously. We know your applications are used around the world to help do mission critical work. To that end, we are always improving our infrastructure to support continued growth of the platform. In order to support the increased volume of traffic to our RESTful APIs, we began a migration in December to a newer and more scalable infrastructure. The new infrastructure has already improved performance by an average of ~30%, helping customers do more in less time. We want to take a moment to offer some clarity on a few issues we have had so far and how we are rectifying them in order to keep our migration going as smoothly as possible. We make infrastructure changes, security upgrades, and conduct maintenance regularly with minimal disruption and our plan was for this migration to be the same - only improving performance. Extensive automated and manual testing was done in development and pre-production environments, with slow production rollouts starting with our internal realms first. Rollout Throughout the rollout, monitoring was in place to review an increase or decrease in specific traffic patterns. This monitoring was leveraged during the migration to detect issues and proactively implement resolutions before any customer impact was noticed. Early in the migration, a very limited number of customers had traffic inconsistently detected by our web application firewall, and additional security layers, as malicious. Our engineering and site reliability teams quickly validated that the traffic did not pose any threats, and manually removed the offending rules. During the last phase of our rollout on the /records endpoint, our tech support team began getting cases that were difficult to troubleshoot and inconsistent with our testing and logging. After deep investigation, we determined that the newer infrastructure was more strictly adhering to the API specifications than the previous infrastructure it was replacing. This was an unintentional change that we've traced back to a specific piece of technology now in use. While our testing covers many success and failure states, the extent of combinations of how the API can be consumed and the way a Quickbase application can be configured was not sufficiently covered. For example, a customer passing in a null value for a text field or a numeric field may expect that to blank out a value from a field, others expect it to do nothing. Similarly, the translation between integers and text in JSON cannot always be mapped 1 to 1 with Quickbase fields. Identified Issues Below is a list of issues relating to increased validation against our API specifications that we have identified and/or been made aware of and have already relaxed to match the previous behavior: null can be passed as a sortBy and groupBy parameters in queries. A trailing semicolon can be passed as part of the content-type header. https can precede the hostname, like https://foo.quickbase.com. null can be passed as a field value. The object will be ignored. If an array of records is submitted in the /records POST, and one or more objects is null, we will ignore those objects. mergeFieldID will accept a numeric value passed as text. Please note: while the above are not part of the API spec, we are leaving these changes while we complete the migration. If we choose to roll them back to be more strict, those changes will be communicated within our release notes so builders and integrators have time to accommodate the changes. If we find additional areas that we determine need to be updated, we will communicate them here. Additionally, the following bugs have been resolved in the new infrastructure to match our API spec: The content-type is automatically inferred as application/json when not passed. This was not being inferred after migration. Previously, customers received an unsupported media type error. Temporary tokens were unable to retrieve file downloads. This has been resolved. Next Steps We will be completing the migration of all endpoints and customers to the newer infrastructure over the coming days. We take seriously the quality and reliability of these services and are implementing further automated testing that increases our coverage of both successful and non-successful transactions. Specifically, we will be adding more combinations of field types, empty and null values, and missing headers. This increased coverage will allow us to better monitor any unintentional breaking changes to the API. Getting Help If you are experiencing an issue, or would like support on any topic surrounding APIs, please open a support ticket including an HTTP request/response (sanitizing all sensitive information and authentication), in line with our Extending Quickbase support article. Including the qb-api-ray (returned in the HTTP Response Headers), and the source IP address your calls are coming from, will greatly expedite investigation and resolution. For more questions or further discussion, please reach out to your Quickbase account team. ------------------------------ Harrison Hersch ------------------------------Re: Formula Query vs Summary Field
Too many variables to make a blanket statement. When you define a relationship, we do some magic behind the scenes that allows us to anticipate some of your needs, like summary fields. So about the only scenario we can say for sure would be faster would be a single relationship with scalar data only with a summary field vs a query. Other than that, it is impossible to say for sure. We have many situations where the queries are faster depending on the levels of relationship, amount of derived fields, whether you are using things like now() or user() (which are amazing, they just reduce the amount of things we can cache). We have tons of internal test cases that run trying to compare them in different scenarios, and notably to make sure things are staying fast, or getting faster. What I suggest, if you are really interested, is using the performance analyzer. Remove all variables except your single field and make a form with only that field on it. Then run the analyzer. Then switch your fields on the form, and run the analyzer again. That will give you the answer for your specific build. Note that the complexity gets exponentially higher if these are used in permissions and a few other things. But the instructions above are a reasonable baseline. ------------------------------ Harrison Hersch ------------------------------Formula queries and Quickbase performance
Background When we took on the challenge of boosting our formula language, we knew we had to juggle three different and competing goals: Make things simple for builders trying to solve difficult business problems. Ensure end users have a delightful experience consuming what their builders create. Protect the platform – as we are a SaaS (Software-as-a-Service), we need to balance the traffic on the highway to ensure everyone has a great experience. To recap 2021, Quickbase formulas were taken to the next level with two major features: Formula queries: simplifying and expanding the class of problems Quickbase can solve. Summary report formulas: allowing you to perform advanced calculations on aggregate data. These are investments we made in our core engine, a special part of Quickbase that offers unparalleled flexibility and speed. As you know, all platforms have limits. Some more than others. We usually try and favor allowing creativity, with modest limits compared to other platforms. The balancing act there is build-time vs runtime. Platforms can take educated guesses at what end users are going to do, and then they have to choose between being too restrictive or too lax. We also have a responsibility to help and educate builders to create the best possible applications they can with good design choices. The challenges and opportunities are endless, especially when Quickbase applications are growing faster than ever, with even greater levels of complexity. Fun fact: One customer's application hit 650 tables this year, in production (don't try this at home). Our Limits In our case, one of our runtime limits is a prediction engine on reports and queries. Rather than letting an operation run on for too long, we sample a subset of the records to determine how long it will take and extrapolate that out over the size of the report to determine if it needs to time-out. That is when you see the "Uh-Oh!" error, shown in the following image. The important thing here is that in 100% of cases, customers are able to create a report serving their needs by optimizing. So, a little oil change and tire rotation helps builders create better applications, that their end users enjoy more, while protecting the platform for all users. NOTE: Let's take a minute to look under the hood, to explore the performance implications of the error message above: Even performing this calculation takes time. Meaning, this check and balance costs us a fraction of a millisecond on every report. That is a worthwhile trade-off, and one all software platforms take. But there are diminishing returns. The more toll booths and weigh stations added to the highway slow down the flow of traffic. Think about how Quickbase permissions allow reports to calculate different results for different people – all in real-time. What if I gave you the option of never having your end users see the "Uh-Oh" error shown above, but the trade-off was that we checked it for safety during report creation? In order to meet that goal, we would need to run your report for someone in every role in the application when you click "save". Now the millions of report changes every month have overhead which they did not have before. That trade-off isn't worth it, especially since we don't know how often that report will even get used. When creating formula queries, we needed to strike the right balance of allowing creativity while also continuing to have safety nets on the platform. Build-time protections Let's start with a relatively simple formula query: SumValues(GetRecords("{6.EX.'In Progress'}"), 4). When saving this formula, we don't know if it will be used on a report with 1 record, 1 million records, or used for permissions (which is the most expensive operation possible performance-wise). We have to take a guess. On one hand, we could assume the absolute worst. That would be seeing how long it takes to save the formula for one record and then multiplying it by the number of records on the table and seeing if it exceeds a threshold. This is the most restrictive, because it isn't likely that all operations leveraging the formula query will require all records. This would offer the most protection, but stifle innovation. On the other hand, we could just multiply it by some low value like 10, which would let almost every formula save (preventing only the absolute most erroneous from saving) but then that could be problematic at runtime. And this is a constant balancing act. Think of this like tuning a car's engine. Some cars optimize for acceleration, and others optimize for torque. It isn't right or wrong – just choices that need to be made. We have settled somewhere in the middle, that is dynamic to each table. And as we assess performance and the data we have, we will continue to refine the algorithms further. One additional nuance is that it is conceivable a formula gets through the builder checks on day 1, but something changes on day 2. Maybe a million records were imported, or an underlying field got more complex. In that case, you might see us stop the formula from saving on day 2. Runtime protections We can't count on protections just at build-time. What if the formula is saved when there are no records in the application because it is still being built? Or what if a certain role has very complex permissions? Build-time protections will pass, but it could be dangerous. When running a complex report, we would have allowed a non-optimized formula through the front door that may be extremely slow or introduce stability issues. The game changes. And because we run our own purpose-built in-memory database, we are actually considering these things down to the physical blocks of memory on the servers. This is exponentially more difficult when you factor in that a formula can be used inside of a permission, or inside of a summary field – it is infinite. Using any expensive formula to sort, for example, can cause us to do millions of additional calculations that aren't perceivable to the builder or user. This is more likely with the introduction of formula queries. And what if the formula query is inside of a field with a dependency diagram like the one shown below (it isn't even fully expanded!)? That's why a table with 5,000 records may run slower than one with 10,000,000 records – the underlying complexity of the "questions" you are asking Quickbase. And so to account for this, we added some logic special for formulas to that prediction engine I mentioned at the beginning. What you can do Enterprise software needs maintenance. Quickbase applications represent a no-code world where the amount of maintenance is much lower than traditional software, and even low-code. Within the context of Quickbase, the oil changes and tire rotations include things like optimizing reports and tuning searchable fields. Dependency Diagram In all of your complex applications, become best friends with the Dependency Diagram! It can be accessed from field usage, on any derived field (formulas, lookups and summaries). It is one of the "stickiest" builder features in Quickbase. Meaning those of you who use it, come back and use it often. It helps you visualize how your fields interact with one another and can easily uncover hidden complexity. This is a great place to start during application maintenance or troubleshooting. Performance Analyzer Next, ensure the Performance Analyzer is enabled in your application properties. This is a valuable tool for you to understand how your application performs in all ways, not just formula queries. If you've turned on "Analyze Performance" in your session (by clicking your name at the top right), saving a formula query that gets stopped by our protections will offer an additional option to quickly get more details: This is going to give you more specifics around what is being caught during the save operation. When running a report, we can even see that the sort time is being consumed by our formula query field. Under the hood: sorting by ID is listed here, even though it wasn't explicitly defined, because we need to tie-break record sorting. Searchable Fields You should regularly optimize and streamline searchable fields in all tables. We recommend selecting 10-15 fields per table which are most important for your users to search on, and mark the rest of the fields not-searchable. Pay special attention to complex derived fields, like formulas containing a query. This is one of the most important things you can do to keep your app running smoothly. Tech Support Our Tech Support team is also available to help answer questions. They also have access to more tools that can point you in the right direction. Customer Success and Solution Architecture We have a team of Customer Success Managers and Solution Architects that not only can help reactively, but on a proactive basis as well. We can help analyze your application in extreme depth, understand your usage patterns, recommend, and help implement solutions that scale. Our 20+ years of expertise combined with the tools in our toolbox can be leveraged to its fullest potential, helping you scale your Quickbase solutions to thousands of users enterprise-wide. We will update resources as we gather examples to help illustrate opportunities to improve performance in formula queries. In the meantime, please reach out to your account team to discuss additional ways we can work together. ------------------------------ Harrison Hersch ------------------------------Quickbase platform performance, 2021 and beyond
March 3 rd , 2021 was the busiest day to-date in terms of traffic in Quickbase’s 20-year history. We now regularly exceed two billion requests (a request is simply any human user or API traffic) in a given month all while at the same time, builders are creating applications with a higher complexity than ever before. Keeping the platform, and individual applications, humming requires an awesome Site Reliability Team whose primary purpose is to keep our platform up and running while balancing security and compliance. On top of the platform, and without a single line of code, our innovative builders have created applications that exceed our wildest dreams. Some apps have 270+ tables. Some have 750+ roles and others have 10,000+ reports. And late 2020, an individual table managed to get to 50,000 fields. To those of you that are thinking it – this is not a challenge. We aren’t encouraging you to try and beat those records but rather highlight the ever-growing complexity and scale we love to tackle. And if nothing else, 2020 taught us to be ready for anything. With customers using Quickbase for increasingly mission-critical use cases like vaccine distribution, we need to be right there with them. Our journey ensures that use cases targeting operational data can continue to scale on Quickbase and drive more complex insights faster. And faster means both how long it takes to create the solution, and how fast our purpose-built, in-memory, C++ database can actually “crunch the numbers”. As it relates to core performance under-the-hood, we categorize our work into three main categories: Infrastructure updates Software optimizations Helping customers maintain their applications Let’s dive into each one these and also discuss some exciting new developments. Infrastructure Our hybrid infrastructure is state-of-the-art. At the edge is Cloudflare, a recognized world leader in providing security for web applications. In December 2020 alone, Cloudflare blocked about 2,000,000 malicious requests – keeping your data and applications safe. With an average response time of 65ms and peaking at thousands of requests per second, our infrastructure is the beating heart of the platform. In the last 18 months, we have increased our compute capacity by over 180%. Just this month, we installed new hardware in our collocated, redundant, datacenters. And coming up in the next few months, we will be making additional hardware and operation system upgrades in an effort to achieve boosts in performance for certain areas of the platform. This is all while we invest significantly in each layer of the stack to improve performance, reliability, and security. Software Optimizations If our infrastructure is the heart of the Quickbase platform, our software is the brain. Because we built, own and maintain the special sauce (in-memory database), we can optimize and tweak to an extent not readily available on other platforms. This directly benefits our customers. We constantly fine-tune the engine for use cases that Quickbase is designed for. Those are things like optimizing for speed and complexity, not size. They range from updates to Visual Studio and low-level frameworks, tweaking the bits and bytes of memory storage and deciding when to cache vs when to recalculate on-the-fly. These are all things we take on, so you don’t have to. Working with many of you, we have evaluated application build patterns to make regular improvements to Quickbase. This partnership benefits every single application. Month over month, we act on our findings and ship incremental improvements. Without code commits, merge conflicts, or compiling a build, all applications running on Quickbase will instantly wake up faster. What becomes a little more challenging is making software optimizations that may benefit one application pattern but degrade another. An example is the age-old wisdom that holds true in basically any database - “filter the most records first in your query”. This is true in most cases, but not all! In fact, a user in Quickbase might have an extremely complex formula or summary field which is very expensive to calculate, but does filter out the most records. We’ve seen a single field sprawl out to millions of data points in milliseconds. If this is the case, it may actually have the opposite impact to performance by performing the costliest filter on more records. With new application patterns being created all the time, we have to invest heavily in testing a dizzying array of possibilities. This leads to the tight partnership my colleagues and I in R&D have with our customer-facing teams. We work together to prioritize these improvements, minimize risk, and communicate with customers. Out of that partnership was born a brand-new summary function, called “Has any children”. One of the most common patterns in Quickbase permissions is when view or modify permissions on a parent are based on the children. For example, “Only allow users to view projects when they are assigned to a child task”. In this case, Quickbase needs to count all of the children records on the fly. While we do this wicked fast, we found an opportunity to be even faster. For use cases where the “number of tasks” (or any other child record) is irrelevant, and just knowing if “any exist”, these new types of summary fields will calculate much faster, compared to counting all child records. Builders can begin taking advantage of this pattern in our April release. Helping customers maintain their applications Quickbase enables you to build applications far more quickly than you could using traditional technologies, while also requiring less maintenance. Builders don’t need to worry about updating software libraries, patching operating systems, or any of the other minutia that normally accompanies software. In the last twelve months, we have added functionality and solutions to help offer more visibility into applications. You can now gather information about field usage and reports from our RESTful APIs, which many of you use to document and manage applications. In fact, Ryan Pflederer used these APIs to lead the top rated session at Empower 2020: Automate your App Documentation and Management. And with Platform Analytics on the horizon, account and realm administrators will have more visibility than ever into their Quickbase ecosystem. To assist builders with keeping their applications humming along, and building scalable architectures, our team of solution architects and customer success teams regularly work with you all to create centers of excellence and diagnose, advise, and architect enterprise-grade solutions. We’ve also invested in tooling for internal resources. Our teams can now scan an application’s schema to determine its memory footprint and assess impact of different elements like fields and formulas. They then use this to tailor recommendations unique for each customer and drive thought leadership - like how to best optimize user functions for speed. I’m also happy to start talking about a new tool we are piloting in an early closed beta, an intelligent performance optimizer. This performance optimizer is a set of tools that dissect an application looking for different things it can tune. Not only are we implementing the lessons learned from 20 years and millions of applications being run on Quickbase, but this also allows us to address the challenge I mentioned above, where we don’t want to change how Quickbase behaves in a way that may benefit one customer – but degrade performance for another. The things we look to optimize can loosely be separated into two categories: Build patterns: things like the filter ordering, how permissions are set up, how a formula is written, and much more. Specific software optimizations: these are low-level tweaks that are nearly impossible to broadly roll out but now can be applied app-by-app, or even table-by-table, based on the unique characteristics of the schema. In some of our early tests, we are seeing improvements to reports and queries ranging from 10%, all the way to a 300% – and we are just getting started. I’m very proud about the progress our teams have been making and will continue to make over the coming months and years. So, what can you do right now? First, one of the most impactful things that can tune the Quickbase engine is optimizing searchable fields. Next, make sure you are familiar with our performance optimizer and develop a routine where you are regularly profiling the most common actions in your application. Pay special attention to test as role/user if the app has custom permissions. You’ll want to test as different roles or users to see how things perform for them. If you’d like help tweaking certain actions, our Technical Support team is happy to help. Finally, reach out to your account team if you’d like to have a more comprehensive review of your Quickbase ecosystem and discuss how we can help further. I’ll be sharing more updates as we begin rolling out these performance improvements so stay tuned.Adding flair with some HTML and Quickbase sorcery
Quickbase is truly an exceptional platform. Our in-memory database allows builders to create value in 1/4th - 1/5th the time of traditional technologies or low-code solutions. That being said, success in software implementations heavily relies on the human aspect, user experience and change management. Plenty of our research has shown that when builders put time into the end user's experience, the app is stickier and more successful. Lisa Sawyer, our XD Manager, did an Empower 2020 session about this which was wildly successful. Most mission critical workloads running on Quickbase use date fields one way or another, usually to drive workflow. @Ryan Pflederer and I were cooking some things up recently and thought this one was cool enough to share with the wider community. The problem we aimed to solve with this formula is similar to what many of you do in your own apps today: How can I visually show my users the status of a project at a glance? Rather than a single field displaying a stop light and status, or a simple summary field showing # of late tasks, we wanted to provide something a little more thorough. And since this is Quickbase, we want to make sure that users can take action on their insights, so each of these circles is a hyperlink to the respective task. What is cool about this is we designed it in a way to really leverage the power of Quickbase formulas and summary fields, to be flexible enough to handle the varying schemas out there. All of the following are dynamic: Number of circles Colors Text content All of this is done with just some minor HTML and CSS, and not a single line of JavaScript, PHP or C#. Just the Quickbase engine doing the heavy lifting. What I'm exceptionally passionate about is not only how cool this is today, but how excited I am for the future. Concepts like this will continue to get not only easier to implement, but more powerful at the same time as we make step-change improvements to our formula language and core capabilities. So, let's back our way into how this works. HTML Formula The main formula here is pretty simple. In the above, that is the field "Timeline Dynamic". The container styling is defined in this formula and relies on the actual circles to be filled in by a Quickbase combined text field. var text timelineStyle = "style='list-style-type: none; display: flex; align-items: center; justify-content: center'"; var text children = SearchAndReplace(SearchAndReplace(ToText([Combined Text Rich Text formula]),"| ; ",""),"|",""); "<div style='display: flex; justify-content: center; font-family: arial; color: #758D96; margin:-10px 0px -25px -30px'>" & "<ul style='list-style-type: none; display: flex; '>" & $children & "</ul>" & "</div>" By using search and replace in our formula above, we can swap out the semicolon (;) Quickbase uses to delineate a combined text field with some helpful HTML. Now, let's look at the output of that field [Combined Text Rich Text formula]. Basically, this summary field is aggregating some HTML we defined on the child table. Each child record has its very own HTML, which represents one "circle" on the timeline. Rich Text Formula Below, you'll see the visual output, the generated HTML on this record and the formula that makes it possible. var text red = "#f25e30"; var text green = "#2c9382"; var text yellow = "#d69900"; var text grey = "#ddd"; var text darkgrey = "#758D96"; var text textColor = If( [Status]="Complete", $green, [Due Date]<=Today(), $red, [Due Date]-Days(2)<=Today(), $yellow, $darkgrey ) ; var text cirColor = If( [Status]="Complete", $green, [Due Date]<=Today(), $red, [Due Date]-Days(2)<=Today(), $yellow, "white" ) ; var text lineColor = If( [Status]="Complete", $green, [Due Date]<=Today(), $red, [Due Date]-Days(2)<=Today(), $yellow, $grey ) ; var text divOne = "margin-bottom: 20px; padding: 0px 20px; display: flex; flex-direction: column; align-items: center; font-weight: 100; text-align: center;"; var text divTwo = "padding: 0px 20px; display: flex; justify-content: center; border-top: 2px solid "& $lineColor &"; position: relative;"; var text circle = "width: 19px; height: 19px; background-color: "& $cirColor &"; border-radius: 100%; border: 1px solid "& $lineColor &"; position: absolute; top: -12px;"; var text hFour = "font-weight: 600; color:"& $textColor &";"; var text topVal = UserToName([Assigned To],"FF") & "<br>" & ToFormattedText([Due Date],"MMDDYYY"); // Text to display above the icon var text botVal = [Task Name]; // Text to display below the icon var text order = ToFormattedText([Due Date],"YYYYMMDD"); // Used to ensure the proper order of the records var text link = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#]; // Hyperlink URL when clicking the icon "<li id='" & $order & "'>" & "<div style='" & $divOne & "'>" & $topVal & "</div>" & "<a href='" & $link & "' style='text-decoration: none;' title='View task'>" & "<div style='" & $divTwo & "'>" & "<div style='" & $circle & "'></div>" & "<h4 style='" & $hFour & "'>" & $botVal & "</h4>" & "</div>" & "</a>" & "</li>|" Hopefully some of you find this valuable. Have a cool pattern in Quickbase you'd like to share? We would love to hear about it! ------------------------------ Harrison Hersch ------------------------------Bad things
This thread discusses inserting custom code in a part of Quickbase where it was never intended to be used. Inserting custom code in this manner, in any software system, is considered a security vulnerability, and such code can break at any time without warning. Extending your Quickbase apps with custom code is a vital part of our platform, but all such custom code must be used in a code page. In 2021, we will begin to block any further additions of JavaScript code outside of code pages. This also means that any modifications of fields which include JavaScipt will be blocked, unless the JavaScript is removed. Places in your Quickbase apps where you have already inserted JavaScript will continue to function. However, as has always been the case, these fields can break without warning. [future link to Graham’s community post] This is a test. I'm doing bad things with Quick Base. ------------------------------ Harrison Hersch ------------------------------Re: Rich Text Field Showing Javascript Code
We have a very intentional and thorough communication plan coming on this so stay tuned. But in short, we are not intentionally breaking anything existing. There has always been the risk of those things breaking (and they do regularly actually). ------------------------------ Harrison Hersch Director of Product Operations ------------------------------
