In this use case, we are helping a customer with data transformation needs. They needed to take daily snapshots of each customer's accounts receivable data, aggregate it by country, and then snapshot it.
If you are someone who can code and knows how to use RESTful web services, you’re probably thinking "no problem”. Otherwise, if you are someone who knows Quickbase (a real creative problem solver) you're probably thinking “I know I could figure out a way”.
The challenge for the developer is where to host the script. They will also need to consider chunking the data because of the volume and since you're chunking, you’ll need to consider throttling, among other things. For the Quickbase creative problem solver, the volume of data would have you trying to get something working and would likely have several daily manual steps in place.
This is where Quickbase Pipelines and the New Quickbase API help. Using these two new Quickbase features, you can quickly develop a strategy and work with the customer to implement it in a couple of days. For the trending analysis, the data needed to be aggregated by Country which took the number of records down to ~60 daily. Once the daily data was aggregated it could be deleted the next day. So how did we snapshot and aggregate the daily data:
- Create a summary report grouped by Country
- Use the new API within Pipelines to execute the report and return the summarized records as a single record.
- Write them to a snapshot table.
With the new API, Run a report, you can execute reports - more importantly summary reports - and get the entire data set returned in a JSON form. Then, with Pipelines, you can easily iterate over the summarized record set with the Pipelines JSON handler and create the snapshot records. With the XML-based API, you were not able to return summarized data, but with API_DoQuery
, and with API_GenResultsTable
the summarized columns were not returned as part of the record set.
Additionally, with the New API, calls are very much in line with modern APIs:
POST https://api.quickbase.com/v1/reports/{reportId}/run?tableId={tableId}
With Pipelines, you don’t need to worry about setting up an infrastructure - you can create a new Pipeline. You don’t need to worry about coding syntax, you use the Pipeline Channels and Actions which hide the syntax aspect of coding, so all you need to do is worry about the logic - even throttling and retries are built in.
One additional plug here for the New Quickbase API is the developer portal. If you frequently work with the XML Quickbase API, we highly recommend you go check it out. It provides a playground to quickly test out each of the new API calls. Before we built the Pipeline in this post, we used the developer portal to test out and verify our expected results.