Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

EmpowerPro Pipeline Qrew Recap

empower jinja pipelines Oct 18, 2024

Hopefully you got to catch miss my presentation at EmpowerPro!

At this event I shared a peek into one of the Pipelines I built for a client that has since become a new standard practice when I need pipelines to run fast and flow seamlessly with the user's experience.

Sadly, Quickbase did not record the training or Qrew sessions 😥

But you are in luck!

Below I share a bit about the use case and all the details for the Pipeline (and then some) that you can use for reference.

Background

Client: Residential New Construction Roofing Company

Client Use Case: Vendor Bid Management

The "Old Way" (pre 2020)

Gathered vendor bids via email & spreadsheet
100s of items bid quarterly (some monthly)
7 vendors across 5 regions each... and growing
Time consuming, error prone, and lacked visibility

The "Improved Way" (2021)
To get away from the "old way" using spreadsheets & email the client wanted to start using Quickbase since they were already using it for other internal processes.

Launched Quickbase Vendor Pricing app & Vendor Portal app
Leveraged automations & table webhooks to automate the creation, request, and receipt of external bids
Single source of truth, real time visibility, simple interface, scalable structure, & improved decision making

The "Sustainable Way" (early 2024)
As Automations and table Webhooks reach end of support/life, it was important to rework the apps without them.

Converted 21 Automations & 12 table Webhooks into Pipelines
Improved maintenance through consolidated Pipeline usage
Maintained speed and optimized the user experience through the use of APIs (JSON & XML), Jinja, and code pages <<< THIS IS THE FOCUS OF THE EMPOWERPRO PRESENTATION

The "Connected Way" (late 2024)
With the vendor bid process now running smoothly the client wants to incorporate it into other new apps cutting down on effort and errors.

Connecting the Vendor Pricing app to other apps for Estimating & Quoting

Simplified App Structure & Pipeline Function

For the EmpowerPro session, I shared 1 of more than 30 Pipelines that support the Vendor Bid Management workflows. Many of the unshared Pipelines use a similar structure to query and add records at lightning speed.

In this workflow a single record on the Control Panel table in the Vendor Pricing app displays the bid packs from the Vendor Portal app (previously added by another process) based on a filter for the year & quarter. Each bid pack represents an individual year-qtr-region-vendor and includes several action buttons.

The vendor manager can use the action buttons to "Add Items" which copies empty bids from the Pricing app to the Vendor app, "Request Bids" which sends an email to the vendor with a unique pin to access and price their bid pack, "View Bid Pack" which allows the vendor manager to view the completed submitted bid packs, or "Reset" the bid pack to un-submit a bid pack and allow the vendor to make additional edits.

The action we'll look at is "Add Items" which copies the empty bid records from the Pricing App into the Vendor Portal.

"Add Items" Workflow Components

Clicking a button and having hundreds of records magically appear is great, but it actually takes quite a bit of work to make the magic happen.

Checkbox Field: used to trigger the Pipeline
Formula URL Field (aka API Button): used to check/uncheck the trigger that kicks off the Pipeline, redirects to the processing code page for a specified time, and returns to the control panel
Processing Code Page: used to delay returning the user to the control panel until the Pipeline has finished (which is really an estimate of when it will be finished)
Pipeline: used to fulfill the request. In this case, query for what records to copy followed by importing the copies.

I'll share the details, starting with the Pipeline.

Copy Bids Pipeline Structure

This 3 step Pipeline my appear simple, but it is super powerful and FAST. Not to mention this same structure can be leveraged in more complex Pipelines anywhere you need a Search and Add Record.

Step A: Record Updated

This is the trigger step that triggers on the checkbox field changing and includes relavant data from the Vendor Bid Pack for the subsequent steps. In this case that is the field that includes the year-qtr-region-vendor id.

Step B: Make Request - using the query api

This is the query step. You'll need to populate the following values.

SEE FULL STEP SCREENSHOT BELOW

URL: https://api.quickbase.com/v1/records/query

Method: POST

Headers:
Note, you do not need to include authorization in the header when using this API through Pipelines as it will use the permissions of the Pipeline.

Name:

QB-Realm-Hostname
User-Agent

Value:

company.quickbase.com
FileService_Integration_V2.1

Content Type: application/json

Body: see below

The body is constructed using JSON and includes 3 parts:

The "from" table you want to query
Here this is the table's dbid (actual id omitted from the screen shot).
The "select" fields you want to return
Here I enter the ids of the fields I want to return for use in the next step that will create the bid in the Vendor Portal. You'll also notice that using a bit of Jinja I can dynamically change this list of values.
Just after the 13 and before the 33, I've included this:
{{ {'1':'26', '2':'27', '3':'28','4':'29', '5':'30', '6':'40', '7':'42', '8':'45', '9':'46', '10':'51', '11':'52', '12':'53' }[a.year_qtr_region_vid.split('-')[3]] }}
This is performing 2 functions. First is this piece a.year_qtr_region_vid.split('-')[3] is a lot like the Part() function in Quickbase formulas. Here I'm getting the 4th part or 'split' of the Year-Qtr-Region-VID field using the "-" as a delimiter. It is the 4th part because indexing starts at 0. That gives me just the Vendor ID portion of the field value. Next I apply some Jinja that is similar to the Case() function only the value I'm looking for is at the end vs the beginning. So this piece {'1':'26', '2':'27', '3':'28','4':'29', '5':'30', '6':'40', '7':'42', '8':'45', '9':'46', '10':'51', '11':'52', '12':'53' } is basically a set of key & value pairs such that if the Vendor ID is 1, 26 is returned, if the Vendor ID is 2, 27 is returned, and so on. In the Vendor Pricing app, this particular field captures the prior quarters price for the specific bid and the field differs by vendor, so I only need to include the field that is specific to that vendor in my query. There are a number of ways you could active this both in Pipelines and in the app, this is how I chose to do it.
The "where" query/filters you want to apply
Here I'm querying only for those bids that match the year-qtr-region-vendor that triggered the pipeline. For more information on writing queries, see Query Writing 101 for Quickbase.

Step C: Make Request - using the API_ImportFromCSV api

This is the import step. You may prefer to use the JSON version, but I think the XML version is more straightforward to construct.

You'll need to populate the following values.

SEE FULL STEP SCREENSHOT BELOW

URL: https://company.quickbase.com/db/table_id (this is the table id for your destination table)

Method: POST

Headers:
Note, you do not need to include user authorization in the header or body when using this API through Pipelines as it will use the permissions of the Pipeline.

Name:

QUICKBASE-ACTION

Value:

API_ImportFromCSV

Content Type: application/xml

Body: see below

The body is constructed using XML which consists of 4 main parts:

<qbdbapi> ... </qbdbapi> this is placed at the start/end of the body
<apptoken> ... </apptoken> this is where your app token goes if the app requires tokens
Note, you do not need to incude the <usertoken> when using this API through Pipelines as it will use the permissions of the Pipeline.
<records_csv> ... </records_csv> this is the comma separated list of values you want to import enclosed in the <![CDATA[ ... ]]> brackets.
Here I've used jinja to reference the output from step b. Notice the insert of the Activity from step b. The format of b.json.data is used over and over again to reference different parts of the output. The first piece {%- for i in b.json.data %} establishes the start of a 'for' loop for each "i" or item ("i" is a variable I chose) in the b.json.data object. Then there is a series of "set" statements that are used to define a variable (such as "related_package") and assign it a value from the b.json.data output. As an example, this piece of jinja {%- set related_package = b.json.data[loop.index0]['24']['value'] %} creates a variable called "related_package" with the value coming from field id 24 for the given loop (or row of data). After all the variables are defined they can be referenced using a jinja expression with the variable name, such as {{ related_package }}, with commas between to create the CSV import. Yay!
<clist> ... </clist> this is a period separated list of the destination field ids that is in the same order as the values specified in the <records_csv>.

Formula URL API Button

This button is used to check/uncheck the trigger that kicks off the Pipeline, redirect to a processing code page for a specified time, and return the user to the control panel (which is embedded in a dashboard webpage widget).

It utilizes the API_EditRecord API to edit the record when the button is clicked to check/uncheck field id 14 for the "Get Bids Trigger" that triggers the above Pipeline.

Next it redirects to Quickbase page ID 5 (the processing code page explained next). In addition it passes a title "my_title" to display on the center of the processing page and the amount of time "my_timer" (in milliseconds) for the processing page to run.

This amount of time is my best guess based on testing of how long the Pipeline needs to run. The code page does not actually know when the Pipeline has finished. There are ways to set that up with polling on the code page, but that is not something I typically setup as it involves numerous API calls.

Neither the title or timer are required as the code page has default settings as well, but this allows me to use one processing code page in many different workflows that require different titles and timers.

Processing Code Page

I don't know who originally came up with the idea to display a "processing" page while an automation, webhook, or Pipeline was running... but I got to say I'm glad they did!

There are plenty of instances where a process can run in the background and the user can go about their work and not concern themselves with when the process finishes. BUT when the process is just one step in a multi step workflow that the user must take action on ... the processing page is SUPER helpful.

If it takes more than a second or two for the process to 'run' the user may not know that is is working in the background and that they need to constantly refresh their screen until it has finished so they can see the output and take their next action.

Steps to add a code page:

Open the App settings
Click on Pages
Click on +New Page
Choose Code Page and click Create
Give your page a name like "Processing.html"
Enter the details for your code page

For that last piece you can do what I did which was start with RunPauseRefresh code page from the Code Pages app built by Ryan Pflederer at Quickbase then augment it to use the "my_title", "my_timer", and the progress bar (I did that using ChatGPT and some of my own html knowledge 😜).

Or, you can download a copy of my code page.

Presentation Wrap-up

So, the BIG question is why do all this extra work!

It's not that much work when you know what you're doing and have done it before (as in copy/paste where possible)
It preserves the user experience not leaving them to refresh or wonder if the process is done.
These API pipelines run SOOOOOO much faster just check out these differences for the above Pipeline. (yes I rebuilt this Pipeline all these different ways just to test how they compare 😃)

The original automation ran pretty fast especially if you were running it for vendors 1-3 which created the majority of the copies by the time the screen refreshed.

The final Pipeline detailed above, had a run time of 4 seconds which isn't quite fast enough to complete while the screen refreshes, but a simple processing page fixes that.

That final Pipeline compared to one using standard search and add record steps runs in 95% less time, or 2,325% faster. 🙌 🎉