Select: How to use the entire concatenation and not just the first element?

Scriptcase 9 General
#1

Using a scriptcase form based on tableA.
It has many fields all entered by the user - except for one field (codename) that is filled from a select. I need to select into that form field a concatenation of codename with a number, both from a single record in tableB

tableA.codename varchar(24) tableB.codename varchar(16) tableB.nextnumber int

I use the Scriptcase Lookup : -
select codename, concat(codename," ", nextnumber) from codename order by codename

I get an onscreen view upon selecting “xyz 12” but the database result on save is “xyz”

Who knows the answer to this?

#2

Try:
select concat(codename, nextnumber), concat(codename," ", nextnumber) from codename order by codename

#3

Good idea - but it does not perform as expected ( the space between the two values is required).
Lookups are in two sections split by a , character,
I believe the left side gets stored in the form field, the right side is for viewing.
When looking up the form field value Scriptcase generates an error if concat is used (on the left side), but no error for the same concat on the right side.
I have got around the problem by using code within ‘onValidate’.

#4

If you need the space then use 2 times the concat:
select concat(codename," “, nextnumber) as Code, concat(codename,” ", nextnumber) as value
You must verifiy if your field accept Charaters.