Results 1 to 10 of 10

Thread: Diacritic insensitive search on cWebSuggestionForm

  1. #1
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    252

    Default Diacritic insensitive search on cWebSuggestionForm

    Hello, I'm making some quality of life improvements on a web application. This is one of those things I would like to improve as it has been requested by some users. We are using cWebSuggestion forms to let users find a record quickly without navigation keys or lookups. However, by default, the cWebSuggestionForm control doesn't have a pbDiacriticInsensitive or similar property. That means, when looking for a record with a special diacritic character (like á, é, í, ó, ä, ë, ñ, ê, è,...), the exact character must be typed. That annoys users as they don't get the "full result set". I have made a custom implementation on the cWebSuggestionForm subclass so that applications with SQL databases ignore that. It does work, but it is not perfect (it won't highlight with bold font the matching characters and that kind of stuff). I need to implement it for incremental searches and DataFlex embbeded databases too. It isn't hard but it is time consuming.
    As this is something fairly common, I was wondering whether it is addressed in DF20 or perhaps somebody has already implemented it.
    Last edited by Alberto Gutiérrez Arroyo; 9-Nov-2020 at 11:12 AM. Reason: some typos
    Alberto Gutiérrez Arroyo
    A.T. Medtra, S.L.
    atmedtra.es

  2. #2
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    5,447

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    WAIT FOR DF 20.

    doing this in previous versions is not impossible, but would be a nightmare as you are seeing.

    DF 20, you would be able to change the collating attributes on the fly. (like enabling this diacritic insensitive stuff).
    Look at these 2 posts:

    https://support.dataaccess.com/Forum...930#post358930
    and
    https://support.dataaccess.com/Forum...649#post351649

    regards
    Samuel Pizarro

  3. #3
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,837

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    +1... I agree ... wait for DF20

  4. #4
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Alberto,

    In DataFlex 20 this will be easier as it supports unicode but please do not wait and try out whether it works for you or not so that we can respond when it does not do what you need.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  5. #5
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    252

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Thank you guys, I installed the DF20 beta 1 and I've tested the sample view Harm uploaded here (thanks Samuel).
    I have tried different locales and it works fine. However I haven't modified the suggestion form to ignore the diacritics yet. Could you help me get this working?
    Do I have to augment any event on the suggestion form (OnFindSuggestions) or is it enough to change the locale the application uses?
    Alberto Gutiérrez Arroyo
    A.T. Medtra, S.L.
    atmedtra.es

  6. #6
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Alberto,

    So you want to convert an 'è' to an 'e' and so on? I don't know how but may I first ask for what reason would you want to do this? The power of Unicode is to be able to store all characters including the 'special' ones.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  7. #7
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    252

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Vincent,
    I don't think it is actually related to Unicode, although it may help (we are currently storing database strings using OEM-850 so it is not a problem). This diacritic topic is quite relevant for Spanish speaking users due to the use of the tilde character over vowels (the same may apply to French speakers with the circumflex and trema marks or the German speakers with the Umlaut and similar).
    It is a rather complex topic and it is even more relevant due to historic remmants. In the past, the office and bureaucratic work was mainly done on typewriters, and it was inconvenient and discouraged to type those diacritics over uppercase letters (the type bars would write those diacritics through the uppercase character, not over it). I learnt to type on a typewriter so I know the struggle...
    That has caused old school users to keep that tradition while writing uppercase characters, even after modern computers overcame that limitation. This has caused a mildly annoying problem on databases which has been in production for decades.

    Example:
    Imagine you have your Customer table, when your application was first deployed on the nineties/early 2000s, you had a lot of old school users which used to type both the name and surname of the Customer using uppercase, with no diacritics. Years later those users begin to retire and are replaced with younger users who don't write every character with uppercase, they type with the first character on uppercase and the rest with lowercase (just like the ProperCase function of DR Reports does), using proper diacritics with every character.
    Fast forward to 2020 and you have a new user browsing the Customer table using our brand new web application with the useful
    cWebSuggestionForms. You can find the following in the Customer table, imagine it has over 20000 records:

    Code:
    Name        Surname
    -----------------------------------
    Alberto     Gutiérrez Arroyo
    Pablo       Martínez González
    Emma        Álvarez Gómez
    RAUL        FERNANDEZ GUTIERREZ
    RAQUEL      GUTIERREZ ALVAREZ
    The user writes "alvarez" in the suggestion form, expecting to find all the Customers with that surname and he finds just one person. Later he does the same with "gutierrez" and, he finds only two customers. He keeps working with that data in mind and later his boss is angry because he forgot some customers... And we are told our application doesn't find all the records in the database and we need to fix it. We explain them it is due to the diacritics but they don't like the solution (we tell them to use the provided lookup to perform those queries).
    I know technically it is not a bug or an unexpected behaviour, but it is pretty common nowadays to ignore those diacritics while looking for data using a search engine. So we have to implement it using DataFlex.
    That's why I'm asking you for clean and neat ideas to do that
    Last edited by Alberto Gutiérrez Arroyo; 11-Nov-2020 at 05:11 AM. Reason: formatting
    Alberto Gutiérrez Arroyo
    A.T. Medtra, S.L.
    atmedtra.es

  8. #8
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    5,447

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Hi Alberto

    Are you using a SQL db, or regular .dat ?

    If using SQL, you should be change your db collation to become either Case and Accented sensitive or Insensitive. on MSSQL you have options to control that at the entire DB level, or for specific tables only. There are several articles about that in the web...

    So, by doing this, you can get this partially working even with df 19.1. Actually, I believe you will have to tweak the db collation to support that, even after df 20.0.
    The new df locate attribute on Df 20. will just assist the runtime to recognize the accented and non-accented chars at the same. But if you are using a SQL db, you need to instruct the database to perform the same, as the db engine will be responsible to fetch your desired rows...
    Samuel Pizarro

  9. #9
    Join Date
    Aug 2019
    Location
    Cantabria, Spain
    Posts
    252

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    Hello Samuel,
    I'm currently using only SQL. I'm using Modern_Spanish_CI_AS (case insensitive, accent sensitive) with most of the test databases (SQL Server). Perhaps changing it to Modern_Spanish_CI_AI (case insensitive, accent insensitive) according to MS documentation is the key here. But I need to see the feasibility of changing the collate in production environments. It takes time.
    It seems after all this was not the responsibility of the DF Web application. I'm looking to the DF20 examples and it looks pretty good, I'll quickly migrate to DF20 from 19.1 once the stable version is released.
    Thank you!
    Alberto Gutiérrez Arroyo
    A.T. Medtra, S.L.
    atmedtra.es

  10. #10
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,891

    Default Re: Diacritic insensitive search on cWebSuggestionForm

    In your query, you can define the collate. (select * from customer where [name] like 'alvarez%' collate Modern_SPanish_CI_AI). You can also change the collate of one fields if you wish, but that has some disadvantages with joins in reporting and how to update your tables.

    If the tables are not too big, you could even add typo correction, so if they'd type avlarez it would still find Álvarez. Performance will be an issue if you have a millions of records, but I have done it in a few places.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •