Lookup Button - Is this a bug, or just very clever?

[Neil Porter]

I have a number of simple lookup tables in a MSSQL database. For simplicity, lets say that the lookup table contains just 2 fields:

Field1 - Primary Key - SysID - Int
Field 2 - Description - Char(35)

I set up a standard netweb form, for a table, called products, and I have a sysID field in this Products table. I want to use a lookup on this form, to return the SysID value into the table, and display the description on the form.

Everything works fine when I have alpha numeric values in the description field, but one one my customers has taken to entering a numeric description!! They are actually using the lookup table to store colours, therefore:

SYSID         Description
1                 Blue
2                 Green
3                 Red
4                 Pink
5                 2

When they enter 2 into the lookup field on the form, it appears that the Nettalk template is being "clever" and thinks that the user is smart enough to be entering the sysid value, and so when the form is saved, I find that a sysid of 2 has been placed into my table rather than 5 as I would have wanted.

Is there a way that anyone knows that I can work around this behaviour, short of asking my customer to prefix the number in the description field with an alphabetic character?

I'm loath to suggest this is a bug, as if I've understood it correctly, I think this is very clever, and my customer is being a little unhelpful by placing numeric values into somewhere that they weren't really supposed to, but I do have to give them a solution?

Does anyone have any thoughts?

Regards,

Neil Porter.

[JPMacDonald]

Neil,

You can turn on the setting that forces them to do a lookup.

With that they cannot enter directly into the field but must click on the lookup icon and select from the list.

That might be an option for you if the user finds it acceptable.

Regards

Parker

[Bruce]

One of the problems with allowing the user to enter the code, or the description, is that there's no way of really "knowing" which one they meant when they typed 2.

So one solution is to match the code and description in the lookup table. ie you you want to use a number in the description field, use the same number as the code field.

Another option is to tell them the description must be alpha-numeric. This is the one I'd big. Unambiguous data entry is a good thing.

Another option is to force them to select from a lookup - which is probably worse that just typing in a better description.

Like I say, it's not a bug in the sense that there's no "correct answer". If the user meant code 2, and typed a 2, they're happy. If they meant description 2 then they're sad. There's no way to change this because then you're just making a lot of other people happy.

We are working on the mind-reading-helmet. Using this we will be able to understand what a user _meant_ to do as distinct from what they actually did. But that won't be ready for some time yet.

cheers
Bruce

[springguy]

And I was hoping the mind reading feature would be in NT 7.
Oh well....

Mike Springer

[Neil Porter]

I've tried every option of forcing lookup, not using the description, and it still gets in a bit of a muddle.

I accept that having a user type numerics into what I designed to be an alpha-numeric field isn't ideal, so I've gone for the "Force Alpha-Numeric" option by adding in the following to the Validate All Routine in the form that allows updates to the lookup table.

IF NUMERIC(SUB:Description) = TRUE
  SUB:Description = CLIP(SUB:Description) & '#'
END

If the user types in an alpha-numeric, great. If they don't I add a # to the end of the number to make it alpha-numeric.

I little clunky, but it works.

Thanks for the input guys.

Regards,

Neil.