Prospect Number Generation in Salesforce

Overview

When a Salesforce org tracks prospects across both the Lead and Opportunity objects, a common requirement is a unique prospect number that follows the record through its lifecycle. The number must be generated at whichever stage the record first enters the system, transferred cleanly on conversion, and never duplicated — even when records are created out of sequence.

This pattern was surfaced during a ^[1] Salesforce review and implemented by the Twistellar development team.

The Problem

Out-of-the-box Salesforce does not provide a shared auto-number sequence across object types. A naive implementation that generates a number only on the Opportunity object breaks down in two scenarios:

1. Lead-first flow — A Lead is created for early-stage vetting. No prospect number exists yet, so the Lead cannot be referenced in file systems, reports, or folder structures until it is converted.
2. Opportunity-first flow — Some opportunities are created directly (e.g., for known, repeat clients) without a preceding Lead. If number generation only fires on Lead creation, these records are skipped.

The result is either duplicate numbers, gaps, or manual workarounds.

The Pattern

Rule: Generate at First Touch, Transfer on Conversion

This ensures every record in the system has exactly one prospect number, regardless of entry point.

Uniqueness Constraint

The number must be unique across both objects. This requires a shared counter or sequence mechanism — not separate auto-number fields on each object, which would produce collisions (e.g., Lead #1042 and Opportunity #1042 coexisting).

Implementation options include:
- A custom metadata or custom setting storing the last-used sequence value, incremented atomically via Apex
- A dedicated custom object acting as a sequence table (one record, one counter field, updated with row-level locking)

Transfer Logic on Lead Conversion

Salesforce's standard lead conversion does not automatically map custom fields unless a lead field mapping is configured (Setup → Object Manager → Lead → Map Lead Fields). The prospect number field on Lead must be mapped to the corresponding field on Opportunity so the value is copied at conversion time.

The Opportunity-side automation should check: if a prospect number already exists on this record, do not generate a new one. This prevents overwriting a transferred number.

Implementation Notes

• Automation is best handled in Apex triggers or Flow with a before-insert entry point on both Lead and Opportunity.
• The fallback check on Opportunity (if isBlank(Prospect_Number__c)) must fire on both direct creation and conversion paths.
• Test coverage should include: Lead created alone, Lead converted, Opportunity created directly, and bulk insert scenarios (to validate the counter under concurrency).

Relationship to Folder Automation

At Quarra Stone, the prospect number is also used as the folder name key when creating project folders on their network drive (iDrive, managed by Veith Consulting). The folder creation automation depends on the prospect number being present and stable at the moment the Opportunity is saved. This makes the transfer-on-conversion logic especially critical — the number must not change after the folder is created.

See ^[2] for the related pattern.

Related Concepts

• ^[3]
• ^[2]
• ^[4]

Client Evidence

• ^[1] — Prospect numbers were working on the Opportunity object but absent from Leads. The gap caused friction when Lincoln Durham's team began using Leads as a formal vetting stage. Sergei Logvin (Twistellar) was assigned to implement the dual-object generation with carry-over logic.