No announcement yet.


  • Filter
  • Time
  • Show
Clear All
new posts

  • Treat INTEGER in VARCHAR field as STRING

    I have a smal problem.

    I have a simple table with 2 field: ID, field1
    ID is autonumbering
    Field1 is a VARCHAR field containing small data used in a LOOKUP.

    1 = E1
    2 = NO
    3 = 2

    So far so good.

    But when displaying this data in a Calendar the lookup not shown correctly with the value 'looking' like an INTEGER. So in this case ID 3 is show an incorrect value (in my project the first value with alfabetic characters). When the idexes 1,2 are use to look up a value, the lookup is correct.

    So SC9 is treating the '2' value of the field as an INTEGER. When i put a underscore before the '2' and making the fieldvalue '_2' (so a STRING!) everything works fine.

    So the problem is the fact that SC treats this value as a INTEGER.

    Even more weird is the fact that in all other apps the problem does not appear. So in FORMS/GRIDS the lookup is correct even with the '2' value.

    Im using mysql
    So i tried using CASE to define the type of value = not working.

    Any suggestion? or is this a BUG?

  • #2
    Can you show The SQL statement for your lookup?


    • #3
      i work mostly with varchar and i use this to define if i find integers in the field:

      $ref_val = trim (str_replace (",", ".", strtoupper ({value})));

      $out = preg_replace ('~ \ D ~', '', $ref_val);
      if (is_numeric ($out))
      {$answer = "there are numbers in this chain";}
      {$answer = "this string is only composed of letters";}


      • #4
        SQL is very simple
        SELECT id, dienst
        FROM tbldiensten

        The strange thing is that with other types of APPs it does work. Only the calendarAPP is having problems
        Meanwhile I found out the problem only appears when i use the DESCRIPTION field in the calendar.

        So description with INTEGER = incorrect result. DESCRIPTION with VARCHAR = correct result.

        So i guess it's a bug


        • #5
          some images


          • #6
            according to the format seen in the picture 1, 0 is replaced by a '_', as it is of the varchar, it waits '02', normal that it accepts of the varchar, to integer why not date_format ()
            %d Day of the month as a numeric value (01 to 31)
            %e Day of the month as a numeric value (0 to 31), assumption only,


            • #7
              The error is not in the description (2nd line). Second shows the correct value, a '2' - not using description. First picture an incorrect value of the first line (should be '2') from the moment i add the description line in calendar view. But only when the value of the first can be interpreted as an INTEGER

              Some more explanation with every ,ext picture


              • #8
                It is a BUG in de calendarAPP but I found a workaround for people who might need it

                Just put a character in your lookup using CONCAT but in HEX SELECT id, concat(UNHEX('0020'), FIELD) FROM table ORDER BY.....

                HEXvalue 0020 is a SPACE

                Anyway SC should solve this BUG


                • #9
                  that's why I use TRIM and STRTOLOWER very often in the case of a varchar, and have to think the opposite also, good luck