Quickbase Makes Complex Logic Simple
In my capacity as Sr. Product Manager for Platform, I recently had the opportunity to work with our Technical Support team on an interesting customer case, which really highlighted an idea that I'd like to share. The idea is that even when you are building complex processes spanning multiple systems, Quickbase usually has a simple feature that can handle your need.
In this particular scenario, the case was a fairly common request to help the customer find a way to integrate Quickbase with other systems in their ecosystem. At a high level, integrations like these usually work in one of two ways:
Push: Quickbase uses Pipelines, triggering on a record change event (add/modify/delete), sending a message to another system or piece of middleware.
Pull: An external system periodically queries Quickbase, which can also be accomplished using Pipelines. The external system will either pull all records in order to do a comparison, or use some sort of filter (i.e. date, status, etc.)
The tricky part is that regardless of whether you're using a "Push" or a "Pull", there still needs to be some criteria that tells the system exactly what data it's supposed to grab, and when it's supposed to do that. In the customer's scenario, they had a data hierarchy with multiple layers of child tables that were nested underneath one main parent table. What they needed was a way to trigger an update to data in external systems, but only when an item at the top level of their hierarchy had changed. This was complicated further by the idea that sometimes the main "Parent" record would be considered to have changed even if it was only related a child record that had actually been updated.
To illustrate the challenge that we were faced with, let's use Projects as the example of our main parent table, and Tasks as the child table. In essence, the customer would like to consider the Project as having been changed if any value was updated on either the parent "Project" record or any of its related "Task" records.
Of course, data hierarchies are extremely common in Quickbase applications, especially when managing things like projects, tickets, or customers. Part of the special sauce of Quickbase is how simply and quickly the platform can take all the complex data related to one of these records and provide real-time analysis on it in the form of summary fields, lookups, and formulas. That's why I knew that we had all the tools necessary in order to get our customer up and running.
The external service would store the last successful time that the integration ran and then check to find all "Customer" records where the value in this Current Query Filter field is on (or after) the last successful run.
To drill into this a little bit further we will use another example, this time of a "Projects" table:
The Maximum Task Date Modified field is a summary field that is pulling the most recent date modified from all related records on the child "Tasks" table. The field has a very simple configuration which can be seen here:
Using a Formula-Date field, we can write a simple formula that compares the value in the project record's existing Date Modified field, to the value in the new summary field.
This logic can be repeated at multiple levels throughout the hierarchy until it reaches the top, which in this case is our "Projects" table. That allows us to have our integration look at just one specific field, which is comparing all the potential values in order to determine the most recent date modified of all records in the hierarchy. The resulting formula could look something like this:
Clearly the SQL statement that we can see on the right would need to be written by someone with advanced SQL knowledge. Not only does Quickbase require less time and effort, but on top of that Quickbase can compute these values more quickly as well. This special architecture allows Quickbase to do these heavy computations in just milliseconds. In fact, over half of all requests to edit a record, including calculating all those derived fields, complete in about 20 milliseconds!
Extremely quick, simple, and powerful. These are some of the driving principles behind the Quickbase engine, and that's why I believe that complex processes can almost always be solved with simple native Quickbase features.
If you have any questions on how to leverage the power of the Quickbase engine, we encourage you to reach out to our amazing Technical Support team, take a look at the resources in Quick Base University and engage in deeper conversations with other builders like yourself in our Community.
In this particular scenario, the case was a fairly common request to help the customer find a way to integrate Quickbase with other systems in their ecosystem. At a high level, integrations like these usually work in one of two ways:
Push: Quickbase uses Pipelines, triggering on a record change event (add/modify/delete), sending a message to another system or piece of middleware.
Pull: An external system periodically queries Quickbase, which can also be accomplished using Pipelines. The external system will either pull all records in order to do a comparison, or use some sort of filter (i.e. date, status, etc.)
The tricky part is that regardless of whether you're using a "Push" or a "Pull", there still needs to be some criteria that tells the system exactly what data it's supposed to grab, and when it's supposed to do that. In the customer's scenario, they had a data hierarchy with multiple layers of child tables that were nested underneath one main parent table. What they needed was a way to trigger an update to data in external systems, but only when an item at the top level of their hierarchy had changed. This was complicated further by the idea that sometimes the main "Parent" record would be considered to have changed even if it was only related a child record that had actually been updated.
To illustrate the challenge that we were faced with, let's use Projects as the example of our main parent table, and Tasks as the child table. In essence, the customer would like to consider the Project as having been changed if any value was updated on either the parent "Project" record or any of its related "Task" records.
Of course, data hierarchies are extremely common in Quickbase applications, especially when managing things like projects, tickets, or customers. Part of the special sauce of Quickbase is how simply and quickly the platform can take all the complex data related to one of these records and provide real-time analysis on it in the form of summary fields, lookups, and formulas. That's why I knew that we had all the tools necessary in order to get our customer up and running.
Side note: This can get extremely complicated behind-the-scenes, but due to Quickbase's proprietary in-memory database, this logic usually happens lightning-fast. If you're interested to learn more about how this works, I highly suggest watching Quickbase Under the Hood from Empower 2018.The solution that we proposed for this might feel like a complex set of logic, but it really involves just these few simple components:
- The data relationships in Quickbase.
- Summary fields to show the "maximum date modified".
- Formula fields to compare the values in multiple date fields and determine which is appropriate.
The external service would store the last successful time that the integration ran and then check to find all "Customer" records where the value in this Current Query Filter field is on (or after) the last successful run.
To drill into this a little bit further we will use another example, this time of a "Projects" table:
The Maximum Task Date Modified field is a summary field that is pulling the most recent date modified from all related records on the child "Tasks" table. The field has a very simple configuration which can be seen here:
Using a Formula-Date field, we can write a simple formula that compares the value in the project record's existing Date Modified field, to the value in the new summary field.
This logic can be repeated at multiple levels throughout the hierarchy until it reaches the top, which in this case is our "Projects" table. That allows us to have our integration look at just one specific field, which is comparing all the potential values in order to determine the most recent date modified of all records in the hierarchy. The resulting formula could look something like this:
//Use the max of all our date inputsThe real beauty of this is just how easy it is to achieve. These summary fields can be created with just a few clicks of the mouse, where traditional tools like SQL require quite a bit of time and expertise in order to achieve the same result. You can see what I mean here in this image that I've pulled from the Quickbase Under The Hood session that I mentioned above:
Max([Date Modified],[Maximum Contact Date Modified],[Most Recent Project Touch])
Clearly the SQL statement that we can see on the right would need to be written by someone with advanced SQL knowledge. Not only does Quickbase require less time and effort, but on top of that Quickbase can compute these values more quickly as well. This special architecture allows Quickbase to do these heavy computations in just milliseconds. In fact, over half of all requests to edit a record, including calculating all those derived fields, complete in about 20 milliseconds!
Extremely quick, simple, and powerful. These are some of the driving principles behind the Quickbase engine, and that's why I believe that complex processes can almost always be solved with simple native Quickbase features.
If you have any questions on how to leverage the power of the Quickbase engine, we encourage you to reach out to our amazing Technical Support team, take a look at the resources in Quick Base University and engage in deeper conversations with other builders like yourself in our Community.
