Prospect Number Automation in Salesforce

Overview

Prospect number automation ensures every lead and opportunity in Salesforce receives a unique, sequential identifier at the moment of creation. The number persists through the lead-to-opportunity conversion lifecycle, providing a stable reference across the entire sales process — and into downstream systems like server-side project folder creation.

This pattern was implemented and validated during a Quarra Stone Salesforce working session. The core mechanics generalize well to any org that needs durable, human-readable record identifiers.

How It Works

Automatic Generation on Record Creation

When a new lead or opportunity is created, a flow or trigger assigns the next available prospect number in sequence. The field is write-protected for non-admin users — standard users cannot manually edit it — which preserves numbering integrity.

Quarra Stone context: The automation was confirmed working for both leads and opportunities created from the date of implementation forward. System admins (Lincoln Durham, Mark Hope, Karly Oykhman) retain manual edit rights on the field.

Persistence Through Lead Conversion

The prospect number is carried forward when a lead converts to an opportunity. This means the same identifier tracks the record from initial qualification through close, and can be used as a stable key for any external integrations (e.g., creating a named project folder on a file server).

Key decision (Quarra Stone): Lincoln Durham confirmed the intent is for everything to start as a lead. Prospect number continuity through conversion was a prerequisite for enforcing that workflow — previously, users had an incentive to skip leads and go straight to opportunities to avoid the numbering gap.

Backfilling Existing Records

Records created before automation was enabled will have no prospect number. A script can be written to iterate over all open leads (and opportunities) lacking a number and assign one, subject to two constraints:

1. The assigned number must not already exist in the system (check against both leads and opportunities).
2. The new numbers should be offset far enough from the current sequence to avoid collisions — e.g., starting from a high anchor or using a clearly distinct range.

Trade-off to communicate to clients: Backfilled numbers will not be in chronological sequence relative to existing opportunity numbers. This is expected and acceptable as long as uniqueness is guaranteed.

Implementation Notes

Field Configuration

• Create a custom field (e.g., Prospect_Number__c) on both the Lead and Opportunity objects.
• Lock the field to read-only for non-admin profiles via field-level security.
• On the Opportunity object, map the field from Lead during the standard conversion process.

Automation Trigger Logic (Pseudocode)

on Lead/Opportunity before insert:
  if Prospect_Number__c is blank:
    next_num = MAX(all existing Prospect_Number__c across Lead + Opportunity) + 1
    while next_num exists anywhere:
      next_num += 1
    record.Prospect_Number__c = next_num

For the backfill script, the same uniqueness check applies. Run in a sandbox first and validate the resulting number range before deploying to production.

Downstream Integration: Project Folder Creation

Once a prospect number exists on a lead, it can serve as the folder name key for automated server-side folder creation. This requires API access to the file server — at Quarra Stone, this involves coordinating with their IT provider (Vieth) to expose an endpoint or grant API access.

See ^[1] and ^[2] for details on the Vieth integration planning.

Related Decisions

Related Articles

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