A question came up in the Quickbase community that I see a lot in different forms: when a new Application record comes in, a pipeline should create a Principal Investigator (PI) record if the applicant's email isn't already in the PI table, and otherwise link the Application to the PI that's already there. The pipeline used a Search Records step, a loop, and an IF/ELSE inside the loop, and it kept failing at the IF/ELSE.
The original thread is here: Pipeline help on the Quickbase community.
The short version: don't try to decide "create or link" inside a search loop. Upsert the PI first, then search for it. Below is why the loop approach breaks, and a step-by-step build of the version that works.
Why IF/ELSE inside a search loop doesn't work
A loop in Pipelines runs once for each record the search step returns. That sounds obvious, but it has two consequences that sink this pattern:
- If the search is filtered by email (the sensible thing to do), a brand-new applicant returns zero PI records. Zero records means the loop runs zero times. The ELSE branch that was supposed to create the PI is inside the loop, so it never runs. Nothing gets created, nothing gets linked.
- If the search isn't filtered (it returns every PI and the IF does the matching), the loop runs once per PI. The THEN branch fires on the one matching PI, but the ELSE branch fires for every other PI in the table. With 200 PIs, that's up to 199 duplicate PI records from one application.
Either way, the "create it if it doesn't exist" step is in a place where it can't do its job. The fix is to make sure the PI exists before you search for it.
The fix in one sentence
Use an upsert on the PI table keyed on Email Address (existing email gets updated, new email gets created), then search the PI table for that email and link the Application to the one record it finds.
Because the upsert guarantees the PI exists, the search always returns exactly one record, and the loop always runs exactly once.
Before you start: make Email Address unique
An upsert needs a merge field, and Quickbase requires the merge field to be unique.
- Open the PI table's settings and go to Fields.
- Open the Email Address field.
- Check Unique (I'd also check Required so you never get a PI with a blank email) and save.
If Quickbase refuses to save because of duplicates, you already have two or more PIs with the same email. Run a summary report grouped by Email Address, merge or clean up the duplicates, then try again. This step is worth doing anyway, since duplicate PIs are exactly what this pipeline is trying to prevent.
Step-by-step: build the pipeline
Here's the full pipeline. Step letters are what Pipelines usually assigns in this order, but check yours, since they shift if you add or remove steps.
Step A: Trigger on new Applications
- Channel: Quickbase, trigger Record Created (or On New Event set to on add).
- Table: your Applications table.
- Make sure the fields you need are available to later steps: Applicant Name, Applicant Email, Departmental Affiliations, and Related Investigator.
Step B: Prepare Bulk Record Upsert
From the Quickbase channel, under Bulk Record Sets, add Prepare Bulk Record Upsert.
- Table: your PI table.
- Fields: Full Name, Email Address, and Departments (or whatever your PI fields are called).
- Merge field: Email Address. This is what tells Quickbase "match on email; update if found, otherwise create."
Step C: Add a Bulk Upsert Row
Add Add a Bulk Upsert Row and point it at the bulk record set from step B. Map the Application's values into the PI fields:
| PI field | Value |
|---|---|
| Full Name | {{a.applicant_name}} |
| Email Address | {{a.applicant_email}} |
| Departments | {{a.departmental_affiliations}} |
Since the trigger is a single record, this step doesn't need to be inside a loop. It just stages one row.
One small hardening tip: people type emails inconsistently. If you want an email typed with capital letters or a stray space treated the same as the lowercase version, normalize it with {{a.applicant_email | lower | trim}} here, and use the same expression in step E.
Step D: Commit Upsert
Add Commit Upsert. This is the step that actually writes to the PI table. Add a Bulk Upsert Row only stages the data; nothing is saved until Commit Upsert runs.
After this step, a PI with the applicant's email is guaranteed to exist: it was either there already (and was updated) or was just created.
Step E: Search Records on PI by email
Add Search Records on the PI table with a filter:
- Email Address equals
{{a.applicant_email}}
Include Record ID# in the fields the step returns.
Step F: Inside the loop, link the Application
Inside the loop for step E's results, add Update Record on the Application from the trigger (step A) and set:
- Related Investigator =
{{e.id}}(the Record ID# of the PI that step E found)
That's it. No IF/ELSE at all. The upsert handled the "does it exist?" question, so the loop only has one job.
What I saw when I tested it
I tested the data side of this in a sandbox app with two tables: a PI table (Full Name, Email Address set to Unique, Departments) and an Applications table with a Related Investigator relationship to PIs. I seeded one PI, Sarah Lee in Biology, then ran the same sequence the pipeline does (upsert on Email Address, search by email, update the Application) for two new applications:
| Applicant | PI search before upsert | Upsert result | PI search after upsert | Application linked to |
|---|---|---|---|---|
| Sarah Lee (existing PI) | 1 record | Updated PI #1 | 1 record | PI #1 |
| Mike Chen (new) | 0 records | Created PI #2 | 1 record | PI #2 |
The Mike Chen row is the whole story. Searching before the upsert finds nothing, so a loop over that search would run zero times and an ELSE inside it would never fire. After the upsert, the same search finds exactly one PI, and the link works.
The Sarah Lee row also shows something to think about. Her application listed "Biology; Chemistry", and the upsert overwrote her PI record's Departments from "Biology" to "Biology; Chemistry". That may be what you want. If it isn't, read the next section.
Don't want to overwrite existing PI details?
An upsert updates every field you map for records that already exist. If your PI records are the source of truth and you don't want an application to overwrite a PI's name or departments, there are two options:
- Map only Email Address in steps B and C. For an existing PI, nothing changes. (In my test, an email-only upsert for Sarah Lee came back as "unchanged".) For a new PI, the record gets created with just the email, and you can fill in the name and departments afterward, either by hand or with a separate Update Record step that only runs for new PIs.
- Keep the full mapping but accept updates if the application form is the most current source of a PI's details. For a grant portal where PIs apply repeatedly, that's often fine.
A few things that trip people up
- Commit Upsert is required. If PIs aren't showing up, check that step D exists and runs before step E.
- Run it on create only. If the trigger also fires on edits, every Application change re-runs the upsert. That's harmless but noisy.
- The search must be filtered. An unfiltered Search Records on PI will loop over every PI and set Related Investigator over and over, ending on whichever PI came last.
- The email must match exactly. If the search in step E uses a different expression than the upsert in step C (for example, one is lowercased and the other isn't), you can get zero results even though the PI was just created. Use the same expression in both.
- Unique is enforced on the field. Once Email Address is Unique, anyone adding a PI by hand with an existing email will get an error. That's a feature, but let your users know.
Wrapping up
Whenever you catch yourself writing "search, then IF found do X ELSE create" in Pipelines, stop and ask whether an upsert can answer the "does it exist?" question for you. Make the matching field unique, use Prepare Bulk Record Upsert, Add a Bulk Upsert Row, and Commit Upsert, then search for the record you now know is there. It's fewer steps, there's no branching to debug, and it can't create duplicates.
If you want the full details on the bulk upsert steps, Quickbase's help article on bulk record sets covers each one.