Contributors mailing list archives


Re: Partner unicity

Camptocamp France SAS, Alexandre Fayolle
- 17/04/2018 16:54:02
Hello Alexis,

Would sure be useful.

I'd like to draw your attention on four pitfalls regarding unicity rules
and partners (lessons I learned the hard way)

1. unique mail / phone can be an issue with contacts of customers /
suppliers (you can have an invoicing address with the same phone number
as a contact for instance)

2. some partners are tricky to deduplicate : the partner of the user and
the partner of the employee (and these may have the same mobile phone
number). When using hr_expense, the partner related to the home address
of an employee is used to issue refunds (and probably the payroll module
does something similar but I've never used it). While the user's
partners may be set as contacts of the company, you must never do that
with the home_address_id of the employee (otherwise the company becomes
the commercial_partner_id of the employee's home address and you are
completely stuck when paying the refund). A consequence of this is that
it is quite tricky to merge the home addresses of employees and user's
partners, and they typically get the same name and adding a follower on
a record adds the wrong one half of the time...)

3. beware of archived partners. On large CRM databases, you will find in
time people moving from 1 company to another. The good practise is to
archive them and create a new partner (otherwise the opportunities
change companies). They may decide to keep their mobile phone number, so
you have to exclude the active=False records from the unicity constraint.

4. hr_recruitment: when you have candidates coming from headhunters, you
may have lots of candidates with the same email adress (the one of your
contact in the head hunter cabinet). I've built a tool though for a
company doing HR with odoo to help them find applicants with the same
email or mobile than a given candidate, to help them identify people
applying multiple time (simultaneously or over a period of time).

One point you have not mentionned is the "ref" field. I find it useful
to have it unique (but it can't be used to deduplicate and it is not
required). I've often made this field an accounting field copied on
contacts of a company.

Finally, maybe you may want to consider partial unique index, esp.
unique for is_company=True partners, and unique within a given parent

This is a difficult task (but a useful one).


On 17/04/2018 18:32, Alexis de Lattre wrote:

> Dear OCA friends,


> As you know, it is often difficult to keep a database without duplicate

> entries in the partner table. Odoo users often create a new

> customer/supplier in the database without prior search to see if this

> customer/supplier already exists. I know that the CRM module has a tool

> to merge partners, but I'd like to work on technical solutions that

> would help prevent duplicates, instead of trying to fix them after they

> occur. I'm not trying to find a perfect solution that avoid all

> duplicates because I think it's impossible, but I'd like to find

> pragmatic solutions that would block as many duplicates as possible.


> I have several ideas for this ; each idea could be a module to develop

> in the OCA :


> * partner_email_unique :

> Would add an sql constraint on the email field. To make it more

> efficient, this module could also inherit create to strip() and lower()

> the email field, so that the constraint is more efficient.


> * partner_mobile_unique :

> Would add an sql constraint on the mobile field. To make it more

> efficient, it would depend on base_phone, so that we are sure that the

> phone fields are "clean" and written in the standard E.164 format (+33 4

> 72 08 08 87).


> * partner_vat_unique :

> Would add a constraint on the vat field... but unfortunately the 'vat'

> field is synchronised between parent and child partners (cf


> so we cannot just add an SQL constraint... We would also need to have

> "clean" VAT numbers (inherit create to strip(), upper() and replace(' ',

> '')). Ideas are welcomed to find another option than SQL constraint

> without too much performance impact.


> This could be extended to country-specific company numbers via

> localization modules...


> Obviously, it is not possible to put a unique constraint on the phone

> nor fax field (in the same company/family, several people may share the

> same fixed phone or fax number). It would also be stupid to put a

> constraint on the name field, as several people can have the same name.


> SQL constraint should certainly include the 'company_id' field (i.e.

> "unique(email, company_id)"), because, in multi-company environments,

> partners are sometimes not shared between companies.


> All ideas and experiences are welcomed before I start to write modules

> on this topic. I haven't searched deeply if there are existing OCA

> modules on this topic, so please mention them if you know some.


> Regards,


> -- 

> Alexis de Lattre

> Akretion France - 35B rue Montgolfier - 69100 Villeurbanne - France


> _______________________________________________

> Mailing-List:

> Post to:

> Unsubscribe:


Alexandre Fayolle
Chef de Projet
Tel : +33 4 58 48 20 30

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex