Forum Discussion

JamesCalloway's avatar
Qrew Member
8 months ago

Is there a way to prevent Bulk Upsert from clearing a field if there is no data?

I have a table in Quickbase that is intended to be maintained as the image of a similar table in a foreign system, and I am attempting to use Pipelines to keep the table updated in near real time, rather than to batch load it.

When changes occur in the foreign system, it sends signals to a Quickbase webhooks URL. The signals can be of type "creation" or "fieldChange", among other types. Each change to a field results in a separate signal, and when a record is created in the foreign system, the system first send sends the "creation" signal, then sends separate "fieldChange" signals for each field that gets populated during creation. The signal looks something like this:

  "eventType": "fieldChange",
  "recordId": 16793426360,
  "fieldName": "company_name",
  "fieldValue": "Acme"

Since any given signal can be a change for any field in the foreign table, I use the following Jinja code for every field in the Quickbase table that might need to be updated:

{% if b.field_name == "company_name" %}
   {% if  b.field_value == "" %}
   {% else %}
   {% endif %}
{% endif %} 

This works perfectly if I use Update Record. The field referenced by the signal is updated and the other fields are not touched.

However, I run into trouble using Update Record when a new record is created. The signals come quickly, and because Pipelines run asynchronously, the "creation" signal isn't necessarily the first signal to be processed. To avoid losing data, I tried checking to see if the record exists before processing a "fieldChange" event and then creating the record if it does not. The problem is that occasionally, between the time one Pipelines thread gets a "does not exist" for the record and the time it attempts to create the record, another thread will already have created it, and the former thread errors out with an error 51 for trying to create a record with the same unique key as an existing record.

So then I try Bulk Upsert. I actually had started with Bulk Upsert, but forgot why I abandoned it until I tried it again: When I use the exact same Jinja code in the fields for Add a Bulk Upsert Row, it clears every field that is not referenced by the "fieldChange" signal (whereas Update Record ignores them). 

I could add a step to look up the record in question and re-enter the field value, but that runs into the occasional problem of the record not existing yet.

I'm hoping that there is a way to instruct Add a Bulk Upsert Row to ignore the field (rather than clear it) if there is no data for it. 

Alternately, if there is another approach that might work in this scenario, I would love to learn about it.

James Calloway
No RepliesBe the first to reply