Discussions

 View Only
  • 1.  How do you loop through an array on a query JSON object item in a Pipeline

    Posted 03-05-2021 09:39
    I have a pipeline where I have a Fetch JSON and then Iterate over JSON records. After that step I create the quickbase record but it is only creating the first record in the array and does not create the remaining list items.



    #Pipelines #array #json


    ​​​​​ ​

    ------------------------------
    Ty G
    ------------------------------


  • 2.  RE: How do you loop through an array on a query JSON object item in a Pipeline

    Posted 03-06-2021 03:00
    Hi Ty,

    This one stumped me for a while too but then witht he help of the QB team I have worked out how to use the Webhooks channel and the Jinja2 templating language to do this and other more complex movement of data in and out of QB with Pipelines.  I use the 'Make Request' action in the Webhooks channel to call the QB Insert/Update records API (see https://developer.quickbase.com/operation/upsert) and the 'for' condition in Jinja2 to iterate through each item in the JSON from step A in the request Body.  I also use the 'if' condition with the 'loop.last' function to avoid adding an unwanted comma at the end of the cycle and some of the Jinja2 filters to get me data in the right format for my QB table.  I'm not that good at screenshots so have included a copy of the BODY of my request as follows:

    {
    "to": "gkp7xfhty",
    "data": [ {% for item in a.json.orders %}
    {% if loop.last %}
    {
    "3": {"value": {{item.order_number | int - 10678}}},
    "468": {"value": "{{item.shipped_date | truncate(19, true, '')}}"},
    "483": {"value": "{{"{:%Y-%m-%dT%H:%M:%S}".format (time.now)}}"},
    "528": {"value": "{{item.carrier_name}}"},
    "529": {"value": "{{item.tracking_short_status}}"},
    "530": {"value": "{{item.tracking_number}}"}
    }
    {% else %}
    {
    "3": {"value": {{item.order_number}}},
    "468": {"value": "{{item.shipped_date | truncate(19, true, '')}}"},
    "483": {"value": "{{"{:%Y-%m-%dT%H:%M:%S}".format (time.now)}}"},
    "528": {"value": "{{item.carrier_name}}"},
    "529": {"value": "{{item.tracking_short_status}}"},
    "530": {"value": "{{item.tracking_number}}"}
    },
    {% endif %} {% endfor %}
    ]
    }

    In this example I am updating records in QB with some shipping information I have requested from our shipping app, so I have my record ID as one of the fields ("3") but if you are adding new records just leave this field out.  For more on Jinja2 templating see https://help.quickbase.com/pipelines/about_jinja.html and http://jinja.octoprint.org/templates.html.

    Hope this helps - once you get your head around how to use the Jinja2 templating language and work out the syntax nuances it completely changes what you can acheive with Pipelines!

    Cheers

    ------------------------------
    Roger Dartnell
    ------------------------------



  • 3.  RE: How do you loop through an array on a query JSON object item in a Pipeline

    Posted 03-21-2022 11:22
    Edited by Daniel Johnson 03-21-2022 11:23

    Hey Roger,

    I'm trying the following but getting an error:

    {
    "to": "12345abcd",
    "data": [ {% for item in a.body_json.deltas %}
    {
    "14":{"value":{{item.date}}
    }
    {% endfor %}
    ]
    }

    In pipelines the error just says, "Validation error: Body is not a valid JSON object" and when I try it on JSONlint.com it says, "Expecting 'STRING', '}', got 'undefined'" after the curly brackets before the for loop starts. Any ideas?



    ------------------------------
    Daniel Johnson
    ------------------------------



  • 4.  RE: How do you loop through an array on a query JSON object item in a Pipeline

    Posted 17 days ago

    Hi,

    I get the same error in pipeline & JsonLint.com. Any fix we know?



    ------------------------------
    vivek vishwanath
    ------------------------------



  • 5.  RE: How do you loop through an array on a query JSON object item in a Pipeline

    Posted 17 days ago

    I'm not sure if you're doing the exact same thing but if you're attempting the same loop the synxtax errors include not having a comma and not closing out the value object. See below:  
     {
    "to": "12345abcd",
    "data": [

       {% for item in a.body_json.deltas %}
         {
              "14":{"value":{{item.date}} }
         }

         {% if loop.last == false %},{% endif %}
       {% endfor %}
    ]
    }



    ------------------------------
    Chayce Duncan
    ------------------------------