Restful API Report digesting JSON

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=bje35c29w

https://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.

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.