AnsweredAssumed Answered

How to keep the letter but lose the accent?

Question asked by Cécile on Jun 29, 2017
Latest reply on Jul 4, 2017 by Cécile

I suppose someone must have had this issue before but I could not find it in the discussion.

 

Context

I have emulated the process by which a user can deduce whether two similar records refer to the same person or not. I use relationships keys to find whether new contacts (table CtC) already exist in the DB contact table (CiD).

 

My keyLists fields permit to relate records by using a strong identifier (one field) or making a strong key by using a specific combination of soft identifiers (various). eg: Email (strong), FullName (strong) Phone(strong)  (if results>1 must also match at least one of the soft identifiers) or  First Initial AND LastName (soft) + OR(any of the other identifiers).

 

The problem I am facing is that the accents are changing the result so I don't get a match where I should. Adding accented letters would not fix the problem.

 

e.g.

keyFirstNameInitials Calculation: Left ( CiD_First Name ; 1 )

keyLastName Calculation: Filter(CiD_Key_LastName;"1234567890qazwsxedcrfvtgbyhnujmikolpQAZWSXEDCRFVTGBYHNUJMIKOLP")

KeyList1 Calculation:  List (CiD_key_FNinitials;"¶";CiD_key_LastName)

 

 

Header 1CtC (contact to check)CiD 1
CiD 2CiD 3CiD 4CiD 5CiD 6
Gérald CôtéGerald CoteGérald Coté Gerry CotéGéraldine CôtéGaston CôtéGabriel Coterelle
no accents in filter

G

Ct

G

Cote

G

Cot

G

Cot

G

Ct

G

Ct

G

Coterelle

with accents in filter

G

Côté

G

Cote

G

Coté

G

Coté

G

Côté

G

Côté

G

Coterelle

For me, the 3 first CiD can be the same person, and that would be validated if any of the other soft fields (city, province, postal code) matches. In fact if I do a Find, they do come up. In the portal, I should get all but the last of these records.

 

no accents in filter:

However, what shows is CiD 4 and 5 but not 1, 2, 3.

The real match is actually Gerry Coté, CiD 3.

 

with accents in filter:

What would show is CiD 4 and 5; once again i missed the right one.

 

What I need is a function that would replace all accentuated letters by their equivalent without the accent and then filter to remove anything  unwanted (spaces, dots, etc).

 

I don't know how to do that though.

Outcomes