Restful API Report digesting JSON
I really want to batch UPSERT to a unique field in Pipelines.
I am using RestfulAPI to get a report ~ the data is filtered at the source.
ChatGPT has shown me {{b.json.metadata.totalRecords > 0 }} is how I know my report has values.
I want to iterate over the JSON, Restful API returns JSON ... I cannot select the Restful API as an input for the iterate step.
Why must I add a FetchJSON step in front of the iterate step and essentially move the values from the Restful output to the fetch and then to the iterate?
Here is my working solution, I need to test it and verify it works all the time.
First use a Restful API Report step to get the data>
I used these API call below in this recipe.
api.quickbase.com/v1/reports/369/run?tableId=bje35c29whttps://422a9c520eb5b465e7b32cf16982ed96.m.pipedream.net
https://api.quickbase.com/v1/records
Above is the report QID, below is the output.
Type:action
Channel:quickbase
Index:b
Pipeline:HR: Employees: PG Onboarding: Clear Dates
Step:Restful API Report
Run timestamp:Thu Mar 13 2025 08:41:46 GMT-0700 (Pacific Daylight Time)
Input
url:https://api.quickbase.com/v1/reports/369/run?tableId=bje35c29w
method:POST
headers
name:QB-Realm-Hostname
value:hardermech.quickbase.com
content_type:application/json
disable_ssl_verification:false
Output
url:https://api.quickbase.com/v1/reports/369/run?tableId=bje35c29w
status:200
content:{"data":[{"16":{"value":true},"3":{"value":15845},"338":{"value":true},"343":{"value":"Air Products at PBF - Martinez"},"369":{"value":true},"370":{"value":"2025-02-26"},"383":{"value":"2025-02-26"},"469":{"value":true},"471":{"value":"2025-02-24"},"478":{"value":"User , QuickBase Test - 15845"},"758":{"value":true}}],"fields":[{"id":3,"label":"Record ID#","type":"recordid"},{"id":343,"label":"Project Name","type":"text"},{"id":478,"label":"Full Name - HMC ID","type":"rich-text"},{"id":16,"label":"Active","type":"checkbox"},{"id":370,"label":"Email Payroll Date","type":"date"},{"id":383,"label":"Verification Email Date","type":"date"},{"id":471,"label":"Instructions Send Date","type":"date"},{"id":369,"label":"Email Payroll Trigger","type":"checkbox"},{"id":338,"label":"Email Verification Trigger","type":"checkbox"},{"id":469,"label":"send_instructions_trigger","type":"checkbox"},{"id":758,"label":"PG Onboarding Clear Dates","type":"checkbox"}],"metadata":{"numFields":11,"numRecords":1,"skip":0,"totalRecords":1}}
elapsed:0.99378
json
data
Below is what the data looks like for humans.
3
value:15845
16
value:true
338
value:true
343
value:Air Products at PBF - Martinez
369
value:true
370
value:2025-02-26
383
value:2025-02-26
469
"value":true
471
"value":"2025-02-24"
478
"value":"User , QuickBase Test - 15845"
758
"value":true
id:3
label:Record ID#
type:recordid
id:343
label:Project Name
type:text
id:478
label:Full Name - HMC ID
type:rich-text
id:16
label:Active
type:checkbox
id:370
label:Email Payroll Date
type:date
id:383
label:Verification Email Date
type:date
id:471
label:Instructions Send Date
type:date
id:369
label:Email Payroll Trigger
type:checkbox
id:338
label:Email Verification Trigger
type:checkbox
id:469
label:send_instructions_trigger
type:checkbox
id:758
label:PG Onboarding Clear Dates
type:checkbox
metadata
recordrecordnumFields:11
numRecords:1
skip:0
totalRecords:1
Info has this but it's not very helpful.
*Hint:* you can use `to_json` filter like `{{a|to_json}}` or `{{a.attribute|to_json}}` to obtain json representation of an object. You can even combine objects exported from several steps, like `{{ {'trello_card': a, 'salesforce_task': b}|to_json }}`
ChatGPT ask how to find the number of records returned in the report
{{ b.json.metadata.totalRecords > 0 }}
This informs the IF step to go no go.
Now I want to get the values for ID 3:
I start looking for solutions. I try parse JSON, iterate JSON but none of these are working. I asked Quickbase Support and after weeks of back and forth they said iterate would work. I don't see how without adding a loop.
Going back to what I know works, I can use the API's
First step is to see if I can update one or more records using API. First thought about API_ImportFromCSV but decided to use Restful JSON API since the output of step a is JSON. Here is the Restful API URL.
Post: https://api.quickbase.com/v1/records is perfect. All that is needed is the table dbid and a data array with key value pairs in the format below.
{
"to": "bnb2ikxvn",
"data": [
{
"3": {"value": 10},
"7": {"value": true},
}
]
}First test I use 3 = 3 record_id ~ I know I can use any uniquified field I want. This goes into the body of a Pipeline step named Make request (Make an HTTP or REST API request.). Method is POST. Headers name = QB-Realm-Hostname, Value = realm.quickbase.com ~ handles authentication.
One record test sample body into testable 5.0 below.
{"to": "bnb2ikxvn",
"data": [{
"3": {"value": 10},
"7": {"value": false}
}],
"fieldsToReturn": [7]}
Works!
Many record test sample body below.
{"to": "bnb2ikxvn",
"data": [
{"3": {"value": 10}, "7": {"value": true}},
{"3": {"value": 58}, "7": {"value": true}},
{"3": {"value": 3464}, "7": {"value": true}},
{"3": {"value": 3465}, "7": {"value": true}},
{"3": {"value": 3466}, "7": {"value": true}},
{"3": {"value": 3467}, "7": {"value": true}}
],
"fieldsToReturn": [7]}
Success! By changing true to false, I can check and uncheck the check box in all the records in one batch upsert. I am hand coding the record_id values. I can use "fields to return" to handle errors? Need to investigate.
Now I have to make something that can generate the JSON above for each value returned in the first GET step. Below are some of the jinja I tried to get data.
{{a.content | to_json}} ~ returns entire body
{{a.content[0] | to_json}} ~ returns {
ChatGPT says I should do this.
{% for field_id, field_data in data[0].items() %}
{% if field_id == 3 %}
{{ field_data.value }}
{% endif %}
{% endfor %} ~ doesn't work, don't understand why... see explanation below.
{{data}} ~ nothing
{{ a.json.data | to_json}} ~ sends json body
Here is where I figured out the problem ~ and solution.
{{ a.json.data[0]["3"]["value"]}} ~ a.json.data[0][3] fails because it's trying to use an integer key (3), but no integer key exists in the JSON object from Quickbase. ChatGPT mislead me by making [3] an integer, it's a string, ["3"] and magic, it works. I put this into the UPSERT step and sent it to requestbin. Using requestbin, I can look at the actual data being sent to quickbase. It is looking pretty good.
{% if a.json and a.json.data and a.json.data[0] %}
{% for key, field in a.json.data[0].items() %}
{% if key == "3" %}
{{ field.value }}
{% endif %}
{% endfor %}
{% else %}
Data not found
{% endif %} ~ result "body": 15845 which is the record_id of the JSON. I have the record_id now all I need to do is write the field ids and make all the values blank.
First I tried.
{%- if a.json and a.json.data and a.json.data[0] -%}
{
{% for key, field in a.json.data[0].items() %}
"{{ key }}": "{{ field.value }}"{% if not loop.last %},{% endif %}
{% endfor %}
}
{%- else -%}
{}
{%- endif -%} ~ works for one record.
Asked ChatGPT for a loop.
{%- if a.json and a.json.data -%}[
{% for record in a.json.data %}
{
{% for key, field in record.items() %}
"{{ key }}": {{ field.value | tojson }}{% if not loop.last %},{% endif %}
{% endfor %}
}{% if not loop.last %},{% endif %}
{% endfor %}
]
{%- else -%}
[]
{%- endif -%} ~ gives me all the values in the JSON.
Asked ChatGPT:
I want to get the values for id 3 and then all the other keys returned make blank.
{%- if a.json and a.json.data -%}
[
{% for record in a.json.data %}
{
{% for key, field in record.items() %}
{% if key == "3" %}
"{{ key }}": {{ field.value | tojson }}
{% else %}
"{{ key }}": ""
{% endif %}
{% if not loop.last %},{% endif %}
{% endfor %}
}{% if not loop.last %},{% endif %}
{% endfor %}
]
{%- else -%}
[]
{%- endif -%}
Asked ChatGPT:
I want to insert the output of the jinja above so it fits in the "data" array required by Restful API_Records UPSERT.
{%- if a.json and a.json.data -%}
{
"to": "bnb2ikxvn",
"data": [
{% for record in a.json.data %}
{
{% for key, field in record.items() %}
{% if key == "3" %}
"{{ key }}": { "value": {{ field.value | tojson }} }
{% else %}
"{{ key }}": { "value": "" }
{% endif %}
{% if not loop.last %},{% endif %}
{% endfor %}
}{% if not loop.last %},{% endif %}
{% endfor %}
],
"fieldsToReturn": [7]
}
{%- else -%}
{
"to": "bnb2ikxvn",
"data": [],
"fieldsToReturn": [7]
}
{%- endif -%} ~ Works!
In three steps I can dynamically update all the records returned in a Quickbase report.