AltSearchField fails when SQLDataType was changed to Integer

Waldo

Dear all,
 
I need your help on an issue.
I am at a customer which we (Mark Brummel and me) converted from Native to SQL.
All works well and we are now finetuning the system.
 
One of the "bugs" we had to fix was the order of some masterdata tables for which they use integer values. SQL orders them differently.
 
We changed the SQL Data Type to Integer but after this the AltSearchField returns an error. Is that a known issue?
 
Any ideas are more than welcome.
 

A workaround we implemented :

We created a function in a seperate codeunit:

AltSearchVendor(pcodCode : Code[20]) : Code[20]
//*** Check for numeric
IF STRLEN(DELCHR(pcodCode,'=','1234567890')) = 0 THEN BEGIN
  EXIT(pcodCode);
END;

lrecVendor.SETFILTER("Search Name", '@' + pcodCode + '*');
lrecVendor.FINDFIRST;
EXIT(lrecVendor."No.");

In the table, where we want to use the AltSearchField, we disabled the property "ValidateTableRelation", and added one line of code (OnValidate of that field):

"Charter No." := cduVendCustValidationMgt.AltSearchVendor("Charter No.");

After this workarount, it exacly behaves like it should behave.

Marije_Brummel

Wow, are we really the first one with this issue? :?

Well, it is reported to Mircosoft so in case we get an answer (of which I am sure we will) we'll get back on it.

todro

Waldo wrote:

We changed the SQL Data Type to Integer but after this the AltSearchField returns an error.

Hi,

For the table relation, the data type of both fields have to be the same. As you select integer for the field, the search field would have to be of type integer too.

The solution is to use the SQL Data Type VARIANT. This allows the correct sorting for the numeric values and the table relation for the search field.