Forum Discussion

Jamesvan_der_Wa's avatar
Jamesvan_der_Wa
Qrew Trainee
2 years ago

Search recorrds then send it as json array to API url

Using a quickbase pipeline I want to schedule a task that will search for all order records that were updated in the last 24h, then create json object with an array of all the orders in question. This json object should then be sent via a API call to that accepts a list of orders in json form. How would I setup this pipeline in quickbase

Steps how I see it

  1. Search for the orders I want (filtered)
  2. Iterate through the records found and insert them into a json object
  3. Step a webhook referencing the external API that takes the json object constructed into the body
  4. External API to receive the json array for processing

Any idea how I should do this?

Thanks for the help



------------------------------
James van der Walt
------------------------------
  • There are a couple ways to tackle.  As Gary said you can start by writing your payload as formula for each record.  Then you can create a single parent record for all the data then creating a combined text field at the parent with the date filter you need.  Then using another formula field get rid of ";" and add any other json.  Then use this field (payload) in your API call.

    or

    You can use Pipelines Webhook Make Request and do a QB RESTful API call to get your records. Then make your new API call and in the body build your Json using Jinja to iterate over the records. If your API call can take a text/csv (content type) this gets easier because you just make the body look like a CSV.

    Here is a txt/csv body example:
    FirstName, RecordID
    {% for record in a.json.data %}{{record['10'].value}},{{record['3'].value}}
    {% endfor %}

    note: QB Call you'll want to use is api.quickbase.com/v1/records/query not the API_DoQuery



    ------------------------------
    Greg
    ------------------------------
  • I recommend making the JSON object as a formula field . If you have a lot of text data, you may need to handle double quotes and some other characters with SearchAndReplace. 

    "{\"hello\": \"" & [field] & "\"} \n" &

    Escaping the formula is tedious, but in my opinion this will be easier than constructing it in Pipelines. Once the JSON is in place it would be easy to send each individual record via webhook. 

    If you really need to send a single array with all of the object records, this becomes a bit more complicated. I don't think Pipelines is equipped to easily create new JSON objects, but I don't use Pipelines that much so I could be wrong.

    Let me know if sending each record individually is off the table.



    ------------------------------
    gary
    ------------------------------