Treat INTEGER in VARCHAR field as STRING

[nonkelmike]

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?

[alvagar]

Can you show The SQL statement for your lookup?

[nsch2308]

Hello
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";}
else
{$answer = "this string is only composed of letters";}
nicolas

[nonkelmike]

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

[nonkelmike]

some images

[nsch2308]

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,

[nonkelmike]

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

[nonkelmike]

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

[nsch2308]

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