Announcement

Collapse
No announcement yet.

Best way for multi-language lookup tables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Best way for multi-language lookup tables

    Hi,

    What is the best way for multi-language lookup tables ?

    For example, you have tel types and these are listed in contacs form. The idea is to let the user choose a telephone type near the tel like home/work/fax etc. But according to the selected language the list should be filled with the data from where?
    1) language variables ? which causes the new lookup item entry problem !
    2) a small table with one column for each language ? then how the select SQL will be written !?

    Any idea ?

  • #2
    If you need the data to adapt to the language too, especially if it goes for lookup's etc. then you have to find out the language you are working in. You can find that variable in the logon page, but afaik there's also a macro to find it. Then you have to add a language variable to each lookup table. If you set a globalvariable containing your language code i.e. EN for English and DU for Dutch in [glob_language] then you can add and language='[glob_language]' to each lookup statement. First you generate the basic lookup then apply the where clause by hand.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Thanks alot aducom.

      Find the macro and write a code on load event of the form to set a variable containing the language key, like ;
      $current_lang=sc_get_language();
      but when trying to set the SQL for the lookup, "select field" , how this variable will be used ? Tried to use "where", and also read in few threads you can not use it !
      Dont know if you are referring to the same thing but I desgined the lookup table with few columns , one for each language, cantaining its word in it, hoping to select only one column according to the language ?

      Comment


      • #4
        Originally posted by cagabit View Post
        Thanks alot aducom.

        Find the macro and write a code on load event of the form to set a variable containing the language key, like ;
        $current_lang=sc_get_language();
        but when trying to set the SQL for the lookup, "select field" , how this variable will be used ? Tried to use "where", and also read in few threads you can not use it !
        Dont know if you are referring to the same thing but I desgined the lookup table with few columns , one for each language, cantaining its word in it, hoping to select only one column according to the language ?
        If you make a field of type select you can generate the lookup sql. Then you will have a statement like:

        SELECT rate, description
        FROM ndtaxes
        ORDER BY description

        Now to add the extra search parameter change it manually (by hand) to

        SELECT rate, description
        FROM ndtaxes
        WHERE language='[glob_language]'
        ORDER BY description

        Be sure that you add spaces at the end of each line as scriptcase will merge the strings together.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Thanks again aducom.

          After some testing i found that my sql is not working, after your post tried more found this as the best solution ; ( for others maybe needing)

          The lookup table consists 3 colums, ID, LANG1, LANG2 ;
          And at the "load" event of the form there is:

          Code:
          [current_lang]=sc_get_language(); //get current lang
          ! Dont forget to make your global variable type=OUT and scope=session, from Form/Application/settings

          The select field SQL is :

          Code:
          SELECT ID,
          IF( '[current_lang]'='en_us', LANG1, LANG2) as items
          FROM str_person_type
          ORDER BY items
          The only thing left, if there are more than 2 languages you want to establish, you have to add more IFs to the SQL or find another way to make a conditional query.

          Comment


          • #6
            The disadvantage of your solution is that you need a column for every language. Personally I would go for ID, LANG, DESC. Then you can always add another language without the need of changing database structure and my entry would work. But it's a matter of choice of course. I know that your solution - if you create a form for it - is more friendly to maintain as you have the key and then can change all the languages. But with a single sql statement you can replicate the entries of one language into another and then change the description as needed.
            Albert Drent
            aducom software netherlands
            scriptcase partner, reseller, support and (turn-key) development
            www.scriptcase.eu / www.scriptcase.nl

            Comment


            • #7
              Agree.

              By the way when entering data to the lookup table , which i used "editable grid view" , when i enter special characters, it is saving some rubbish characters to the DB. I tried many things, I am sure thats something in the application but could not find !

              You just edit or insert a new row, enter some language specific characters and when you click the save icon, first the data on that row dissapears! When you make a refresh of the page it comes with rubbish characters ( two for each lang character)

              DB, app, connection, field all use the same collation, also tried other type of forms which seems to be OK !

              I am out of ideas, is this reminds you anything ?

              Comment


              • #8
                Which database? If it's MySQL then check if all your fields within the table are of the same storage type. I.e. if you have defined your database as utf8, then you are still able to declare fields differently. Check your sc instance too as both php as well as the database needs to match too.
                Albert Drent
                aducom software netherlands
                scriptcase partner, reseller, support and (turn-key) development
                www.scriptcase.eu / www.scriptcase.nl

                Comment


                • #9
                  Using 8.00.0025, and MySQL 5.6.21
                  Already checked the table fields Collation. They are same.
                  The thing is for the php.ini already tried on the local and remote server to changing the default_charset but this time php_mysql giving errors that the charset ("ISO-8859-9") i set is not supported, it was utf-8. but i think this is not the problem since without changing anything i just create another app with form-multirows everthing is OK, only the "editable grid view" is causing the problem !

                  Comment


                  • #10
                    In my experience it's sometimes better to create a new application under a different name then searching for the issue in the endless list of entries and properties. Simply takes too much time sometimes.
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment


                    • #11
                      Cagabit,

                      Did you found a solution to use more than 2 languages? How does you lookup SQL looks like using 3 or more languages?

                      ​regards

                      Comment

                      Working...
                      X