Compile a custom raw bulk api string based off a bulk event trigger
I am attempting to store changes from a table to a dedicated "change records" table. There are a handful of specific fields I want to store changes for and I need to store both the new and previous values in these fields. So I have some complex filtering logic that can't be captured in the initial filter. At the same time, the source table can have hundreds or even thousands of records changed simultaneously or near-simultaneously, so I can't be making separate API calls for each change record to be created. And I need to be able to conditionally store a payload if the bulk creation step fails, so that I can rerun it manually/by another scheduled process. The native Bulk Upsert step does not allow for this if I'm not mistaken.
For these reasons, I would like to trigger my pipeline off a bulk event, skip the default For pipeline step, and put all my filtering logic within a loop in my jinja expression for my (QB channel) Make Request step. I feel like this should be possible but I'm getting responses saying my record payload is empty. Is there a problem with the way I'm trying to access the bulk event data, or is this just not possible?
{
"to": "bqxxxxxx",
"data": [
{% ns namespace(record_count=0) %}
{% for row in a.records %}
{% if complex_filter_logic_here(r) %}
{% if ns.record_count > 0 %},{% endif %}
{
"6": { "value": "{{ r['value_1'] }}" },
"7": { "value": "{{ r['value_2'] }}" },
"10": { "value": "{{ r['value_3'] }}" }
}
{% set ns.record_count = ns.record_count + 1 %}
{% endif %}
{% endfor %}
],
"mergeFieldId": 10
}
hi, Jonathan. the loop variable is "row" in the iteration but the code refers to "r" inside the loop itself. was the value aliased somewhere upstream?
it may be easier to do this by querying the API with a scheduled script or low code integration. how often do you need to update the "change records" table? if your query returns thousands of records you could also be hitting the rate limit.
