Compile a custom raw bulk api string based off a bulk event trigger

Question

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
}

WillRutledge · Accepted Answer

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.

JonathanGibson47609 · Answer

I should add that my filter conditions involve checking for changes by comparing new and previous values, e.g. {% set fieldChanged = true if row.field_1 != row.field_1_old else false %}. It's my understanding that the _old suffix is the correct way to get the previous value in this context rather than the $prev method. But my jinja loop seems to be either filtering everything or just not recognizing the record array in the first place and defaulting to empty.

Sign up

Login to the community