In my capacity as Sr. Product Manager for Platform, I recently had the opportunity to work with our Care 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, Quick Base usually has a simple native 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 Quick Base with other systems in their ecosystem. At a high level, integrations like these usually work in one of two ways:
Push: Quick Base uses Webhooks which trigger on a record change event (add/modify/delete), sending a message to another system or piece of middleware.
Pull: An external system periodically queries Quick Base. 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 Quick Base applications, especially when managing things like "Projects", "Tickets", or "Customers". Part of the special sauce
of Quick Base 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 Quick Base'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 Quick Base 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 Quick Base.
- Summary fields to show the "maximum date modified".
- Formula fields to compare the values in multiple date fields and determine which is appropriate.
To illustrate this, you can see the result that we are trying to achieve below. In this image we are looking at the form of a "Customer" record where there is a single field highlighted named 'Current Query Filter', which we have configured to show the most recent date and time that anything related to the "Customer" record has changed. As a result, this field can now be used in queries from an outside service in order to determine whether an update is necessary.
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 inputs
Max([Date Modified],[Maximum Contact Date Modified],[Most Recent Project Touch])
The 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 Quick Base Under The Hood
session that I mentioned above:
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 Quick Base require less time and effort, but on top of that Quick Base can compute these values more quickly as well. This special architecture allows Quick Base 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 Quick Base engine, and that's why I believe that complex processes can almost always be solved with simple native Quick Base features.
If you have any questions on how to leverage the power of the Quick Base engine, we encourage you to reach out to our amazing Care team, take a look at the resources in Quick Base University
and engage in deeper conversations with other builders like yourself in our Community