Validate and enrich records before a CRM upsert in Jitterbit Studio

Introduction

Writing a record to a CRM without first validating its content risks creating duplicate entries, storing incomplete data, or overwriting accurate values with stale ones. This guide shows how to build a multi-stage pre-write pipeline that validates required fields, checks for existing records to decide between create and update, and optionally enriches the incoming data with third-party contact information before the final upsert.

A second pattern adds an LLM approval gate for agentic workflows. When a language model controls the CRM write, the system prompt enforces a two-stage confirm-then-execute flow so that the model always requests explicit user approval before creating or updating a record. This prevents a user phrasing such as "add this contact" from triggering an immediate write without confirmation.

This guide uses Salesforce as the CRM target and ZoomInfo as the enrichment source. The field validation and existence-check patterns apply to any CRM connector or API.

Design pattern

The code-based pipeline runs sequentially. Each stage either continues to the next or raises an error before reaching the write:

The LLM approval gate wraps the execute stage in agentic workflows:

Part 1: Validate required fields

Before querying or writing to Salesforce, confirm that the incoming record has the minimum fields needed to identify and create a contact. For a Salesforce Lead, the required fields are typically email, first name, and last name. Reject records missing any of these fields immediately to prevent partial or unidentifiable writes.

Step 1: Write the field validation script

Add a Script step at the start of the pre-write operation. The script checks each required field, accumulates any missing-field messages, and raises an error if the record cannot proceed:

// Validate required fields before any CRM operation
$errors = "";

If(Length(Trim($lead.email)) == 0,
    $errors = $errors + "Email is required. ";
);
If(Length(Trim($lead.firstName)) == 0,
    $errors = $errors + "First name is required. ";
);
If(Length(Trim($lead.lastName)) == 0,
    $errors = $errors + "Last name is required. ";
);

If(Length($errors) > 0,
    RaiseError("Record validation failed: " + $errors)
);

// Normalize the email address before the lookup
$lead.email = ToLower(Trim($lead.email));

WriteToOperationLog("Required field validation passed for: " + $lead.email);

Trim removes leading and trailing whitespace. Normalizing the email to lowercase before the lookup prevents case-sensitive mismatches when comparing against Salesforce records.

Step 2: Check the email format

To catch malformed addresses before they reach Salesforce, add a format check using RegExMatch:

If(!RegExMatch($lead.email, "^[^@]+@[^@]+\\.[^@]+$"),
    RaiseError("Invalid email format: " + $lead.email)
);

This check catches obvious formatting errors (missing @ symbol, missing domain) without rejecting valid addresses that contain unusual characters.

Part 2: Check for existing records

After validation, query Salesforce to determine whether a record with the same email already exists. The result controls whether the downstream operation creates a new record or updates the existing one.

Step 1: Query Salesforce for a matching lead

Use SfLookupAll to search for a lead with the same email address. SfLookupAll returns a two-dimensional array: each inner array is one matching row, and each element is a field value in the order specified in the query.

Add a Script step after the validation script:

// Query Salesforce for an existing lead by email
$sf.existingLeadId = "";
$result = SfLookupAll(
    "<TAG>endpoint:salesforce/Salesforce</TAG>",
    "SELECT Id FROM Lead WHERE Email = '" + $lead.email + "' AND IsConverted = false LIMIT 1"
);

If(Length($result) > 0,
    // Row 0, column 0 is the Id field
    $sf.existingLeadId = $result[0][0];
    WriteToOperationLog("Existing lead found: " + $sf.existingLeadId);
,
    WriteToOperationLog("No existing lead found for: " + $lead.email);
);

LIMIT 1 prevents the result array from containing multiple matches if the same email appears more than once, which can happen in CRM data with historical duplicates. For the SOQL query pattern, see Query Salesforce records using SOQL.

Step 2: Set the routing variable

After the lookup, set a variable that downstream operations use to branch to the correct write activity:

If(Length($sf.existingLeadId) > 0,
    $sf.operation = "update";
,
    $sf.operation = "create";
);

In your controller script or downstream operation chain, check sf.operation to call the appropriate Salesforce activity. For guidance on branching between operations based on a variable, see Manage workflows using controller scripts.

Part 3: Enrich the record before writing

With required fields validated and the create-or-update decision made, optionally enrich the incoming record with additional contact data from a third-party source before the write. This step fills in missing fields and flags values that may have changed since the last sync.

Step 1: Call ZoomInfo enrichment

If the record has an associated company name, use ZoomInfo to retrieve the contact's current job title, direct phone number, and email. For the full ZoomInfo integration setup, including authentication and the resource router, see Enrich contact data using ZoomInfo.

After the ZoomInfo enrichment operation runs, apply the returned values to the staging variables, preserving any values already present on the incoming record:

// Apply enrichment results — fill gaps only, do not overwrite existing values
If(Length(Trim($zoom.enriched.phone)) > 0 && Length(Trim($lead.phone)) == 0,
    $lead.phone = $zoom.enriched.phone
);
If(Length(Trim($zoom.enriched.title)) > 0 && Length(Trim($lead.title)) == 0,
    $lead.title = $zoom.enriched.title
);

The If guards preserve any value the source system already supplied. ZoomInfo enrichment fills gaps rather than overwriting data.

Step 2: Detect stale field values on existing records

When updating an existing Salesforce record, compare enriched values against the stored values to identify fields that have changed. Log any discrepancies so that downstream operations can decide whether to apply the updated value:

// Compare enriched title against the Salesforce stored value
If($sf.operation == "update"
    && Length(Trim($zoom.enriched.title)) > 0
    && $zoom.enriched.title != $sf.existing.title,
    WriteToOperationLog(
        "Title change detected for " + $lead.email
        + ": Salesforce has [" + $sf.existing.title + "]"
        + ", ZoomInfo has [" + $zoom.enriched.title + "]"
    );
    $lead.titleChanged = true;
);

Whether to apply the new value or flag it for manual review depends on your business rules. The key principle is to compare before overwriting so that an enrichment source does not silently replace accurate data.

Step 3: Run the write operation

Pass the validated and enriched record to the Salesforce write operation. Use sf.operation to call the correct operation:

If($sf.operation == "create",
    RunOperation("<TAG>operation:Salesforce - Create Lead</TAG>");
,
    RunOperation("<TAG>operation:Salesforce - Update Lead</TAG>");
);

For both operations, use a transformation that maps $lead.* variables to the corresponding Salesforce Lead fields. For the update operation, also map sf.existingLeadId to the Id field so Salesforce can identify which record to update.

Part 4: Implement an LLM approval gate

In agentic workflows where a language model processes user requests and routes to CRM write operations, add an explicit two-stage confirm-then-execute flow. The approval gate ensures that a user's intent to create or update a contact is always confirmed before the write occurs. A statement like "add this contact" does not count as approval by itself.

This pattern uses LLM function calling to route between stages. For an introduction to function calling in Studio, see Route LLM responses to Studio operations using function calling.

Step 1: Define the approval rules in the system prompt

Add an approval rules section to the system prompt that instructs the model how to determine whether explicit approval has been given. Include this text as a distinct block within the system message, before the description of available functions:

Approval rules (important):
- A user request to "clean", "update", "create", "add", or "insert" contacts does NOT
  count as approval by itself.
- Approval is valid ONLY IF:
  a) The assistant explicitly asked for confirmation to create or update a contact
     in a previous message, AND
  b) The user responded with a clear approval phrase such as: "yes", "proceed",
     "approved", or "go ahead".
- If the assistant has NOT asked for confirmation previously, you MUST select
  "confirm_create_contact" or "confirm_update_contact" depending on whether the
  action is a create or an update.
- If the assistant previously asked for confirmation and the user responds with
  "no", "not approved", or "do not create", route to "process_response".

The distinction between "requested" and "approved" is the key rule. The model checks the conversation history to determine which case applies on every turn.

Step 2: Define the routing functions

Register four routing functions in the tool manifest passed to the LLM:

Describe each function clearly in the manifest. The function descriptions carry the same weight as the system prompt rules when the model decides which function to call.

Step 3: Map routing responses to Studio operations

In the controller script that runs after each LLM response, extract the function_name from the tool call and run the corresponding operation:

$route = TrimChars(
    GetJSONString($jitterbit.response, "/choices/[0]/message/tool_calls/[0]/function/name"),
    "\""
);

Case(
    $route == "confirm_create_contact",
        RunOperation("<TAG>operation:LLM - Confirm Create Contact</TAG>");
    ,
    $route == "confirm_update_contact",
        RunOperation("<TAG>operation:LLM - Confirm Update Contact</TAG>");
    ,
    $route == "execute_create_contact",
        RunOperation("<TAG>operation:Validate and Enrich Lead</TAG>");
        RunOperation("<TAG>operation:Salesforce - Create Contact</TAG>");
    ,
    $route == "execute_update_contact",
        RunOperation("<TAG>operation:Validate and Enrich Lead</TAG>");
        RunOperation("<TAG>operation:Salesforce - Update Contact</TAG>");
    ,
    true,
        RunOperation("<TAG>operation:LLM - Process Response</TAG>");
);

The Validate and Enrich Lead operation runs the field validation and enrichment pipeline (Parts 1–3) before the Salesforce write. Separating validation from the routing script makes the validation step reusable outside the agentic context.

Step 4: Include conversation history in every LLM call

The approval gate depends on the model knowing whether it has already asked for confirmation in the current conversation. For the approval rules to work correctly, every LLM call must include the complete conversation history in the messages array, not just the current user message.

For the pattern used to accumulate and store conversation history across operation runs, see Build a multi-turn LLM chat with conversation history.

Verify the integration

1. Run the pre-write pipeline with a test record that is missing the email address. Confirm that the validation script raises an error and that the Salesforce write operation is never called. Check the operation logs for the error message.

2. Run the pipeline with a valid email address that matches an existing Salesforce lead. Confirm that sf.existingLeadId is populated and that sf.operation is set to update.

3. Run the pipeline with an email address that has no matching Salesforce record. Confirm that sf.existingLeadId is empty and that sf.operation is set to create.

4. If using ZoomInfo enrichment: run the enrichment step for a known contact and confirm that zoom.enriched.phone and zoom.enriched.title are populated before the Salesforce write operation runs.

5. To test the LLM approval gate: send the agent a message such as "create a contact for Jane Smith at Acme Corp". Confirm that the model routes to confirm_create_contact rather than execute_create_contact.

6. Reply to the confirmation prompt with "yes, go ahead" and confirm that the model routes to execute_create_contact on the next turn.

7. To test rejection: after the model asks for confirmation, reply with "no, don't create it". Confirm that the model routes to process_response and that the Salesforce write operation is not called.