Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
2 days ago
Solved

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

    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.

  • 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.

    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.