Hi
I’ve got several listbox columns formatted as CheckBoxes, they work great with the exception that each CheckBox column as well as showing the CheckBox, also shows the value from the database, ie: TRUE or FALSE, which is unnecessary and looks messy.
Is there any way to have it only show the CheckBox?
I know there are ways around it, but before I code some sort of messy workaround I wondered if there is something I’m missing?
Cheers - Steve
As far as I know, there is nothing in the listbox that adds the words “true” or “false” by default when you set the CellType to a checkbox. Are you sure you don’t have anything in your PaintCellText event adding that yourself?
Hi, thanks for the reply. No nothing in the paint.
The value displayed is the actual text value from from the listbox cell, so defining the column as a checkbox does just that, it unfortunately puts the text value of the cell next to the checkbox! Weird 
Do you retrieve the boolean from the DB → set it to a VAR and apply that VAR to the CheckBox ?
Please show some code…
Hi
No I just take the value from the Listbox that is returned after the SQL is executed:
Var sql As String = ""
Var iResult As Integer = 0
sql = "SELECT a.contactid, a.firstname, a.lastname, a.jobtitle, a.uuid, "
sql = sql + "b.deliveryoptin, b.orderacknowledgementoptin, b.salesandmarketingoptin "
sql = sql + "FROM traderscontacts a "
sql = sql + "LEFT JOIN customercontact_custom b "
sql = sql + "ON a.traderid = b.traderid "
sql = sql + "WHERE a.tradertype = 'C' "
sql = sql + "AND a.mainaddressid = '" + pID + "' "
sql = sql + "AND a.contactid = b.contactid "
sql = sql + "AND a.recordstatus = 'ACTIVE' "
sql = sql + "ORDER BY a.contactid ASC"
If 0 = 1 Then mCodeBase.CopyToClipboard(True, sql)
iResult = mDatabase_DB0.SELECTReturnIntegerSetupList(sql, False, lstContacts, False)
'Format the Optin Columns as Checkboxes
lstContacts.ColumnTypeAt(5) = Listbox.CellTypes.CheckBox
lstContacts.ColumnTypeAt(6) = Listbox.CellTypes.Checkbox
lstContacts.ColumnTypeAt(7) = Listbox.CellTypes.Checkbox
'Set the Checkbox Values
For i As Integer = 0 To lstContacts.ListCount - 1
If lstContacts.Cell(i,5) = "TRUE" Then lstContacts.CellCheckBoxStateAt(i, 5) = CheckBox.VisualStates.Checked
If lstContacts.Cell(i,6) = "TRUE" Then lstContacts.CellCheckBoxStateAt(i, 6) = CheckBox.VisualStates.Checked
If lstContacts.Cell(i,7) = "TRUE" Then lstContacts.CellCheckBoxStateAt(i, 7) = CheckBox.VisualStates.Checked
Next
If iResult = -1 Then Return
I am not able to replicate this behavior, even when switching to API 1.
In Xojo, when a boolean value is converted to a string, the case it uses is “True” and “False” and not all caps as depicted in your listbox. This may indicate that it’s your code somewhere that’s adding this text from the database.
Doh !
YOU set the cell to “TRUE” of “FALSE”, then you checked the cell state and apply the value to the CheckBox !
Declare a Boolean variable (or more, as needed),
Get the Boolean value for each CheckBox,
Set in an If block the CheckBox (for each CheckBox).
This way, YOU do not set the ListBox to True|False, then…
Hi
Yes, the value in the database is TRUE or FALSE in that column, its a Text value, the database is SQLITE and the database is syncronised with a SQLServer database which is how the columns are defined in the commercial program that I’m syncronising with.
I guess I initially thought by defining them as checkbox values in the listbox it would interpret the values of TRUE of FALSE correctly, but as it didn’t I have tried changing them to values that would convert to checkbox values, such as True, False, Y or N, however whatever I change them to I have to set the checkbox states manually (which is fine) I just wish I could get them to not show the actual values. I suppose I could try setting the listbox values to blank after I’ve processed the list and set the checkbox states. Just seems like it shouldnt be that long winded? Never mind, thanks for your help anyway
Just to be sure (and I hope this is still the case with API 2): you do not care about what is written in the db file.
You use a boolean (what the CheckBox gaves you) to save the CheckBox state (value) at write time, and at read time, you read a Boolean and set that to a Boolean value, then set the CheckBox with that Boolean value.
Is it clear now ?
In the LR, check SQLiteDatabase
and go directly at the FieldType definition:
This table identifies the Column Type based on the FieldType
Hi
Yes, it’s clear, however for reasons to do with ease of syncronisation I want to use the column types as defined by the commercial product, hence not using booleans.
I was expecting (wrongly) that if I defined a listbox column as a checkbox then that is what I would see, and only that, leaving me to decide the state of the checkbox whether the underlying columns was string, boolean, integer. IMO the listbox controls should be independent of the underlying data, but maybe even make it more straightforward by attempting to map the underlying data source in a logical fashion. It doesn’t matter however I’ve just blanked the values in the listbox after I’ve set the checkbox states, it just seems a bit messy, but it works so all ends well
On a side note I have never used boolean in a database. Oracle (which I’m most familiar with) doesn’t (didn’t) have a Boolean column type, although apparently it does now, and in SQLServer it also doesn’t have a boolean type, although I would use a BIT type if I were doing it in SQLServer as it serves the same purpose. sigh there must be simpler ways to make a living lol
It could be worse though, sometimes I have to fix legacy code written is C# or VB.NET or even worse Omnis7 …
I am curious to know how you assign numbers (Integers, Floats) to a ListBox…
At Database TABLE definition (SQLite), you define Column(s)/Column(s) Types: that is here that you says you want a Column of type Boolean.
At Read Time, you ask for Record sets, then you read all Columns from one Record set; it can be …String, .Integer, .Boolean…
Storing the Boolean you get from the Record into a Boolean variable allows tou to set the CheckBox Value (in an If Block to be sure).
Something like
If wasContacted Then
// Set the CheckBox to .Checked
Else
// Set the CheckBox to .Unchecked
// If this .Unchecked is the correct command
End If
There is no need to set a string holding “TRUE” or “FALSE” to the ListBox, then check in an If block and set the CheckBox accordingly.
Now, if you are happy with what you have now, I have nothing more to say.
(unless if one day SQLite change “TRUE” to “True” or “true” and start to be case sensitive… (for examples).
Why and where are you setting the cell value for the booleans?
Hi
Agree. However as I said I don’t have control of the underlying database column types and if I did I wouldn’t use a Boolean for the reasons stated earlier.
So as a solution, after the list is built, I ctcle throug it and I read the string “TRUE” for example from listbox.cell(i,X) then set that cell to be a checkbox with the value checked, then set the column value to blank so that no actual values appear.
If I were reading from the database in a listbox I could do all this at that point, but I have a central procedure that executes the sql and populates the passed listbox for me so I have no manual involvement, this saves a ton of time with the downside that occassionally I have to do some modifications after the list is built. Like in this case
C’est la vie
Do you know what you can do with CellTagAt ?
Hi Beatrix
The values come directly from the database in a listbox. They are Text columns and have the values TRUE or FALSE.
Thanks, it’s sorted now anyway I just wondered if there was a property on listbox checkboxes to hide or show the underlying data.
Cheers - Steve
Hi Emile
Yes and if I was manually building the listbox I could use those, but as the listbox size, structure and content are generated by a single call to the database any changes to the listbox have to be done later, which is what I ended up doing.
Cheers
S
What is the purpose of the SQLite database? I would have expected to load the Listbox from the actual database which upthread I think you said is SQL Server.
You can use CellTag to store data in the listbox that you don’t want to display.
Nothing wrong with Omnis 7. It may have been discontinued over 20 years ago but it can still do stuff that Xojo cannot.
Hi Tim
The sqlite db is on a Surface Pro somewhere remote, with no connection to SQLServer except when syncronised.
Cheers S
Lol, true, I used it from Omnis 5 on an Apple 3?, through Quartz on Windows 3.11? until 2007 on Windows XP or 7 I can’t remember. Xojo was the ‘spiritual successor’ of Omnis to me, but when I go back to it now … it’s horrendous, it was great in its day though