The comprehensive solution requires understanding both technical constraints and business requirements. Here’s my proven approach for sales team consolidation with custom fields:
Phase 1: Constraint Analysis
Identify all unique constraints on your custom fields. In Odoo 14, Studio fields with uniqueness rules create database-level constraints. Query your schema:
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'crm_lead'::regclass
AND conname LIKE '%x_studio%';
This reveals exactly which custom field combinations must be unique.
Phase 2: Conflict Detection
Before merging, identify all conflicts. Create a staging table:
CREATE TEMP TABLE merge_conflicts AS
SELECT
partner_id,
x_studio_region_code,
x_studio_territory_id,
COUNT(*) as duplicate_count,
array_agg(id) as opportunity_ids,
array_agg(x_studio_commission_rate) as commission_rates
FROM crm_lead
WHERE partner_id IS NOT NULL
GROUP BY partner_id, x_studio_region_code, x_studio_territory_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
Export this to CSV for business review. With 2,847 opportunities, you likely have 300-500 conflicts requiring resolution.
Phase 3: Resolution Strategy
For each conflict type, establish rules:
# Python preprocessing script
import pandas as pd
from datetime import datetime
def resolve_merge_conflicts(opportunities_df):
resolved_records = []
for partner_id, group in opportunities_df.groupby('partner_id'):
if len(group) == 1:
resolved_records.append(group.iloc[0])
continue
# Rule 1: Keep most recent if regions differ
if group['x_studio_region_code'].nunique() > 1:
latest = group.sort_values('create_date', ascending=False).iloc[0]
# Archive others with note
for idx, row in group.iloc[1:].iterrows():
row['x_studio_merge_note'] = f"Merged into {latest['id']} on {datetime.now()}"
row['active'] = False
resolved_records.append(latest)
# Rule 2: Highest commission rate wins if same region
else:
winner = group.loc[group['x_studio_commission_rate'].idxmax()]
resolved_records.append(winner)
return pd.DataFrame(resolved_records)
clean_opportunities = resolve_merge_conflicts(all_opps_df)
Phase 4: Safe Import Process
- Backup production database completely
- Create custom import module that respects constraints:
# In custom module: sales_team_merge/models/crm_lead.py
from odoo import models, api
import logging
_logger = logging.getLogger(__name__)
class CrmLeadMerge(models.Model):
_inherit = 'crm.lead'
@api.model
def merge_with_constraint_handling(self, opportunity_ids, auto_unlink=False):
"""Custom merge that handles studio field conflicts"""
opportunities = self.browse(opportunity_ids)
# Group by partner and custom field combinations
merge_groups = {}
for opp in opportunities:
key = (opp.partner_id.id, opp.x_studio_region_code, opp.x_studio_territory_id)
if key not in merge_groups:
merge_groups[key] = []
merge_groups[key].append(opp)
merged_ids = []
for key, group in merge_groups.items():
if len(group) > 1:
# Apply business rules to select master record
master = max(group, key=lambda x: (x.create_date, x.x_studio_commission_rate))
others = [o for o in group if o.id != master.id]
# Merge activities, messages, followers
for other in others:
other.message_post(body=f"Merged into opportunity {master.name}")
master.message_follower_ids |= other.message_follower_ids
if auto_unlink:
others.unlink()
merged_ids.append(master.id)
_logger.info(f"Merged {len(group)} opportunities into {master.id}")
return merged_ids
Phase 5: Execution
- Import preprocessed data in batches of 500 records
- Monitor constraint violations in real-time
- Keep rollback scripts ready
Critical: Don’t disable constraints in production. Your constraint violations indicate real business rule conflicts that need resolution, not technical workarounds. The preprocessing approach ensures data integrity while achieving your consolidation goals.
For your 2,847 records, expect 2-3 days for conflict analysis and resolution, then 4-6 hours for actual import with proper batching and validation.
This draft is based on general Odoo knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.