Avoiding duplicate clients

Lino Welfare offers some functionality for avoiding duplicate Client records.

This page contains code snippets (lines starting with >>>), which are being tested during our development workflow. The following snippet initializes the demo project used throughout this page.

>>> import lino
>>> lino.startup('lino_welfare.projects.gerd.settings.doctests')
>>> from lino.api.doctest import *

In Lino Welfare, a Client inherits from DupableClient.

Phonetic words

See lino.mixins.dupable.PhoneticWordBase.

>>> rt.show(pcsw.CoachedClients, column_names="id name dupable_words")
...
==== ======================= =======================
 ID   Name                    dupable_words
---- ----------------------- -----------------------
 17   Ausdemwald Alfons       *ASTMLT*, *ALFNS*
 78   Brecht Bernd            *PRKT*, *PRNT*
 19   Collard Charlotte       *KLRT*, *XRLT*
 25   Dobbelstein Dorothée    *TPLSTN*, *TR0*
 80   Dubois Robin            *TP*, *RPN*
 29   Emonts Daniel           *AMNTS*, *TNL*
 53   Emonts-Gast Erna        *AMNTS*, *KST*, *ARN*
 30   Engels Edgar            *ANJLS*, *ATKR*
 28   Evers Eberhart          *AFRS*, *APRRT*
 33   Groteclaes Gregory      *KRTKLS*, *KRKR*
 34   Hilgers Hildegard       *HLKRS*, *HLTKRT*
 38   Jacobs Jacqueline       *JKPS*, *JKLN*
 82   Jeanémart Jérôme        *JNMRT*, *JRM*
 40   Jonas Josef             *JNS*, *JSF*
 42   Kaivers Karl            *KFRS*, *KRL*
 79   Keller Karl             *KLR*, *KRL*
 43   Lambertz Guido          *LMPRTS*, *KT*
 45   Lazarus Line            *LSRS*, *LN*
 47   Malmendier Marc         *MLMNT*, *MRK*
 48   Meessen Melissa         *MSN*, *MLS*
 54   Radermacher Alfons      *RTRMKR*, *ALFNS*
 56   Radermacher Christian   *RTRMKR*, *KRSXN*
 58   Radermacher Edgard      *RTRMKR*, *ATKRT*
 60   Radermacher Guido       *RTRMKR*, *KT*
 62   Radermacher Hedi        *RTRMKR*, *HT*
 74   Radermecker Rik         *RTRMKR*, *RK*
 66   da Vinci David          *FNS*, *TFT*
 67   van Veen Vincent        *FN*, *FNSNT*
 69   Östges Otto             *ASTJS*, *AT*
==== ======================= =======================

Similar Clients

The test database contains a fictive person named Dorothée Dobbelstein-Demeulenaere as an example of accidental duplicate data entry. Dorothée exists 3 times in our database:

>>> for p in pcsw.Client.objects.filter(name__contains="Dorothée"):
...     print(str(p))
...
DEMEULENAERE Dorothée (23)
DOBBELSTEIN-DEMEULENAERE Dorothée (24*)
DOBBELSTEIN Dorothée (25)

The detail window of each of these records shows some of the other records in the SimilarClients table:

>>> translation.activate("en")

>>> rt.show(dupable_clients.SimilarClients, pcsw.Client.objects.get(pk=23))
`ⓘ <…>`__ `⏏ <…>`__ | `DOBBELSTEIN-DEMEULENAERE Dorothée (24*) <…>`__ Phonetic words: TMLNR, TR0

>>> rt.show(dupable_clients.SimilarClients, pcsw.Client.objects.get(pk=24))
...
`ⓘ <…>`__ `⏏ <…>`__ | `DEMEULENAERE Dorothée (23) <…>`__ `DOBBELSTEIN Dorothée (25) <…>`__ Phonetic words: TPLSTN, TMLNR, TR0

>>> rt.show(dupable_clients.SimilarClients, pcsw.Client.objects.get(pk=25))
...
`ⓘ <…>`__ `⏏ <…>`__ | `DOBBELSTEIN-DEMEULENAERE Dorothée (24*) <…>`__ Phonetic words: TPLSTN, TR0

Note how the result can differ depending on the partner. Our algorithm is not perfect and does not detect all duplicates.

Checked at input

If a user tries to create a fourth record of that person, then Lino will ask a confirmation first:

>>> data = dict(an="submit_insert")
>>> data.update(first_name="Dorothée")
>>> data.update(last_name="Dobbelstein")
>>> data.update(genderHidden="F")
>>> data.update(gender="Weiblich")
>>> test_client.force_login(rt.login('robin').user)
>>> res = test_client.post('/api/pcsw/Clients', data=data, REMOTE_USER="robin")
>>> res.status_code
200
>>> r = json.loads(res.content)
>>> print(r['message'])
There are 2 similar Clients:<br/>
DOBBELSTEIN-DEMEULENAERE Dorothée (24*)<br/>
DOBBELSTEIN Dorothée (25)<br/>
Are you sure you want to create a new Client named Mrs Dorothée DOBBELSTEIN?

This is because lino.mixins.dupable.Dupable replaces the standard submit_insert action by the CheckedSubmitInsert action.

The algorithm

The alarm bell rings when there are two similar name components in both first and last name. Punctuation characters (like “-” or “&” or “,”) are ignored, and also the ordering of elements does not matter.

The current implementation splits the name of each client into its parts, removing punctuation characters, computes a phonetic version using the NYSIIS algorithm and stores them in a separate database table.

How good (how bad) is our algorithm? See the source code of lino.projects.min2.tests.test_min2.