4 Replies Latest reply on Jul 4, 2017 7:07 AM by Cécile

    How to keep the letter but lose the accent?

    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.

        • 1. Re: How to keep the letter but lose the accent?
          TomHays

          I have dealt with a similar issue by stripping off the accents before I do my analysis.

           

          -Tom

           

          Substitute(text;

          ["²";"2"];

          ["³";"3"];

          ["¹";"1"];

          ["¼";"1/4"];

          ["½";"1/2"];

          ["¾";"3/4"];

          ["À";"A"];

          ["Á";"A"];

          ["Â";"A"];

          ["Ã";"A"];

          ["Ä";"A"];

          ["Å";"A"];

          ["Æ";"AE"];

          ["Ç";"C"];

          ["È";"E"];

          ["É";"E"];

          ["Ê";"E"];

          ["Ë";"E"];

          ["Ì";"I"];

          ["Í";"I"];

          ["Î";"I"];

          ["Ï";"I"];

          ["Ð";"D"];

          ["Ñ";"N"];

          ["Ò";"O"];

          ["Ó";"O"];

          ["Ô";"O"];

          ["Õ";"O"];

          ["Ö";"O"];

          ["Ø";"O"];

          ["Ù";"U"];

          ["Ú";"U"];

          ["Û";"U"];

          ["Ü";"U"];

          ["Ý";"Y"];

          ["Þ";"P"];

          ["ß";"B"];

          ["à";"a"];

          ["á";"a"];

          ["â";"a"];

          ["ã";"a"];

          ["ä";"a"];

          ["å";"a"];

          ["æ";"ae"];

          ["ç";"c"];

          ["è";"e"];

          ["é";"e"];

          ["ê";"e"];

          ["ë";"e"];

          ["ì";"i"];

          ["í";"i"];

          ["î";"i"];

          ["ï";"i"];

          ["ð";"d"];

          ["ñ";"n"];

          ["ò";"o"];

          ["ó";"o"];

          ["ô";"o"];

          ["õ";"o"];

          ["ö";"o"];

          ["ø";"o"];

          ["ù";"u"];

          ["ú";"u"];

          ["û";"u"];

          ["ü";"u"];

          ["ý";"y"];

          ["þ";"p"];

          ["ÿ";"y"];

          ["ƒ";"f"];

          ["…";"..."];

          ["′";"'"];

          ["″";"''"];

          ["⁄";"/"];

          ["℘";"P"];

          ["ℑ";"I"];

          ["ℜ";"R"];

          ["™";"(R)"];

          ["−";"-"];

          ["∗";"*"];

          ["≡";"="];

          ["Œ";"OE"];

          ["œ";"oe"];

          ["Š";"S"];

          ["š";"s"];

          ["Ÿ";"Y"];

          ["ˆ";"^"];

          ["˜";"~"];

          [" ";" "];

          [" ";" "];

          [" ";" "];

          ["–";"-"];

          ["—";"-"];

          ["‘";"'"];

          ["’";"'"];

          ["‚";","];

          ["\“";"\""];

          ["\”";"\""];

          ["\„";",,"];

          ["‹";"'"];

          ["›";"'"]

          )

          2 of 2 people found this helpful
          • 2. Re: How to keep the letter but lose the accent?
            Cécile

            OMG you are a gem to share that!

            • 3. Re: How to keep the letter but lose the accent?
              Cécile

              Oh I am COD about that but, I was not the one who did the last 20 years of data entry without validation.

               

              Plus in this particular case, the fault is sometimes on the contact: they often change the spelling of their own name from one communication to the other.

               

              Finally substituting at origin seems a bad idea: how do you restitute the accents when you auto-generate response letters that you address to the person?

              • 4. Re: How to keep the letter but lose the accent?
                coherentkris

                keep one field in your table for your unaltered text.

                This wont solve the problem of how to match Gerry and Gerald, Bill and William, Pat and Patrick, or Pat and Patricia

                • 5. Re: How to keep the letter but lose the accent?
                  Cécile

                  coherentkris a écrit:

                   

                  keep one field in your table for your unaltered text.

                  This wont solve the problem of how to match Gerry and Gerald, Bill and William, Pat and Patrick, or Pat and Patricia

                  Indeed. For the time being this is done manually by user. Eventually, creating a rule to check potential aliases is something that can be looked into.