Dropdown in an editable grid

I am creating a form with an editable grid. The grid uses a table BO. One of the variables in the table is stored as a bit in a database. This seems to be pulled through as an int. In order to allow this value to be edited, I am using a dropdown with yes/no options.

For the list options I am using a select statement:-

SELECT CAST('Yes' AS Char(3)) AS Name, CAST(1 AS bit) AS Value

UNION

SELECT CAST('No' AS Char(3)) AS Name, CAST(0 AS bit) AS Value

I am casting the values as bits, as I get an error when submitting the page if I use an int/smallint/text. Everything seems to work, but there is a bug when the form loads. The value in the dropdown appears to be blank, but when I click to edit the dropdown, it automatically selects the correct option as if it has bound properly. I can submit the form before editing the dropdown (when it appers blank) and the value will be correctly saved.

Is this is a known bug? I've been reading around and I cannot find a similar issue.

Find more posts tagged with

BPM

Comments

Jerome_Pearce

You do not need to select from the database. Use 'from an expression that returns a list of values' and enter something like:

ListItems(Pair("Yes",1),Pair("No",0))

We use this approach a great deal in our examples (link below). We also have a function in our Process Mapping Library that returns this, as I get bored typing it each time I need it.

Joe Tinkler

Thanks for the suggestion Jerome, I did try using an expression after I found this example in one of your previous posts. Using ints for the values causes a problem when loading or submitting my form. On page load the dropdown is not set to any value and on submit I get the error:-

String was not recognized as a valid Boolean. Couldn't store <1> in Chargeable Column. Expected type is Boolean.

That was my reason for using a Select with a cast to bit. I get the same results as my SqlSelect if I use this expression:-

ListItems(Pair("Yes","True"),Pair("No","False"))

I use your expression further up the form for a dropdown outside of the grid and it works fine, it's just within the editable grid that this occurs. Outside of the grid the dropdown will accept an int value and convert it to bit in my table, but within the grid it wants a boolean (and when given a boolean it gives the display bug).

Jerome_Pearce

Ah, I see. It is the coversion to a bit field that is the problem.

I never use bit fields. In SQL Server there is a bad bug where they can be overwitten by updating other fields. I used to have a script that demonstrated it perfectly for SQL Server 200, at least. Tiny ints are better (and Metastorm uses them for this reason).

Having said that, if you have no control over the data, you need to convert the result to a bit field. I am not sure how you may do that. You could try using true and false (without quotes of any kind), but I suspect Metastorm would convert them to ints in any case.

Joe Tinkler

Using true/false without quotes gives the same problem sadly. It is as if the binding is working, but there is something stopping the value from being displayed on the form load.

I have written a script to set the dropdown value in the form OnLoad event, which solves the problem. Weird bug though.