Option field allowing any value

TLC

Hi Guys,

Long time reader - first time poster so go easy please......!

NAV2009 classic client user here (native db).

I'm using the Excel Buffer table to read many values from a spreadsheet into the buffer and write them to a table. It all works fine except I've noticed that I can import values into an option field that should not be allowed.

Specifically I have a field (lets call it field "x") which should only allow the values 1 or 2 in it, yet I've noticed that I can import any number between 1 and 9 into this field. If the number is more than 1 character it does not work. I'm wondering if it's behaving like this because the value is an integer since I have a similar field which imports decibels (the available options are 30db, 35db etc.) yet when I try to import 32db, I get the error '32db' is not an option - which is great, however I would have expected to see a similar error when I tried to import the number 9 in field x.

I should point out that since I'm using the Excel Buffer table, the value it reads from the spreadsheet is read as text, and in the case of field x, I'm using evaluate to convert it back to an integer and insert into the table - perhaps this has something to do with it?

Has anyone else come across anything like this before? Is there a simple way to stop the import when incorrect values are encountered?

Thanks in advance!

einsTeIn.NET

I didn't test it, but since option fields are internally stored as integer you should be able to import either any integer value or just the ones that correspond to the given option values of that field.

When you use an option field as parameter in a function then you don't need to set any option value. That means in that case it doesn't check for if the given option value exists, it just takes the integer value.

Is it possible that the OptionString of your option field contains some comma (e.g. Value A, Value B,,,,,,,,,Another Value)?

TLC

Hi einsTeIn.NET,

Thanks for your response...

I did think that myself - originally the OptionString was ",1,2" but I changed it to "1,2" for the purpose of testing this problem.

I also added 1;2 to the ValuesAllowed field in the properties for the field.

Neither of the changes fix the problem.

DenSter

That can happen when you don't validate the field. If you validate the field, it should cause an error.

TLC

I do validate the field but it still loads the value in spite of the option string. I've even tried loading it into a variable using evaluate then validate it into the excel buffer table:

EVALUATE(Temp, ExcelBuf."Cell Value as Text");
HiQDoorBuf.VALIDATE(HiQDoorBuf."Undercut (mm)",Temp);

DenSter

That's very strange. Well then all that's left is to actually program validation yourself, something like:

CASE Optionstring OF
  'First Value': OptionField := OptionField::FirstValue;
  'Second Value': OptionField := OptionField::SecondValue;
ELSE
  ERROR('Invalid option value ' + OptionString);
END;

einsTeIn.NET

You don't necessarily need to evaluate it into another variable. You could use the field directly. In terms of checking for the right option value that normally shouldn't make any difference but I would give it a try.

EVALUATE(HiQDoorBuf."Undercut (mm)", ExcelBuf."Cell Value as Text");
HiQDoorBuf.VALIDATE(HiQDoorBuf."Undercut (mm)");

TLC

Thanks, I was trying to avoid having to write validation code - you would expect NAV would do this, but I guess this is the only way.