Skip to main content
Question

Pipeline Bulk Upsert Unique Values Only

  • Oct 13, 2025 7:53 PM UTC
  • 2 replies
  • 242 views
Forum|alt.badge.img+6

Hi,

I have a table of Account_Templates. It is a many to many table where many Templates have many Accounts. The Account has an associated lookup value, we'll call Question. Many accounts might have the same value for this Question field.

I want my pipeline to search these Account-Templates and create a record on table Template_Questions. A new record should be created for only the unique combinations of field Template and field Question ([Template]-[Question]). My pipeline is currently structured as:


1. Prepare Bulk Upsert to Template_Questions
2. Search Account_Templates for all records
Loop{
3. For each search record found, add an upsert row where the key field equals "[Template]-[Question]".
}Loop End
4. Commit Upsert

When I run this pipeline, I get an error of duplicate values trying to be created.

Is there a way to trim the upsert of duplicates before committing it?
Alternative ideas are welcome.

Thank you,
Curtis

    2 replies

    MarkShnierYou
    Forum|alt.badge.img+24

    I suggest using the new Bulk Trigger step type.

    do the search,

    Then do the build trigger.  It should be able to take the results of the search and then in a one by one sequential process, within the For Each loop, to do a search to see if the Parent Template exists, and branch on the result set empty to create it.  Don't put a Stop step in the logic flow.  There is a bug to be fixed that will stop all subsequent processing of you have a stop step. 

    Mark Shnier. Your Quickbase Coach
    Top Step
    Forum|alt.badge.img+9
    • Top Step
    • Registered
    • Oct 23, 2026 10:56 PM UTC

    The commit fails because the same key shows up more than once in a single upsert. The easiest fix is to make the search return only one record per Template-Question pair, so you can keep the fast bulk commit.

    On Account_Templates, add a formula text field [TQ Key] that builds the same value you use as the key:

    [Template] & "-" & [Question]

    Then add a formula checkbox [First of Key] that is only true on the oldest record with that key:

    var text Q = "{25.EX.'" & [TQ Key] & "'}AND{3.LT." & [Record ID#] & "}";
    Size(GetRecords($Q)) = 0

    Swap 25 for the field ID of [TQ Key]. In your Search step, add a filter for First of Key is checked. Each pair comes through once, the upsert has no duplicate keys, and pairs that already exist in Template_Questions get updated instead of failing.

    One catch: an apostrophe in a question will break the query. If that's possible, build the key from the Template and Question record IDs instead of the text.

    Top Step - https://www.topstep.tech/
    Terms & ConditionsAccessibility statement