Hello.
I’m trying to populate the Details fields of a Master-Details display. The Master is a ListBox that shows parts of the records, and the Details area contains the rest of the fields of the selected row in the ListBox.
I’m trying to implement the logic as shown in the pseudo-code below.
Questions:
Is it necessary to declare a new instance of the database.
How to pass the record ID of the selected row in the listbox to the current handler.
I’m getting the following errors:
Type “DesktopListBox” has no member named “ListIndex”
Type “DesktopListBox: has no member named “Cell”
A listbox double press will trigger the population of the details fields. Thus:
[Event Handler: On listbox.DoublePressed]
db = New SqLiteDatabase
var f as FolderItem = SpecialFolder.Resource("bisoogo.sqlite")
if f <> nil and f.Exists then
try
db.DatabaseFile = f
if db.Connect then
var details_rs as RowSet = db.SelectSQL("select * from words where ID = 1")
if details_rs <> Nil and not details_rs.AfterLastRow then
txtWord.Text = Me.Cell(Me.ListIndex,0)
txtPrefix.Text = Me.Cell(Me.ListIndex,1)
txtPrefixRedup.Text = Me.Cell(Me.ListIndex,2)
txtInfix.Text = Me.Cell(Me.ListIndex,3)
txtRootedup.Text = Me.Cell(Me.ListIndex,4)
txtRoot.Text = Me.Cell(Me.ListIndex,5
txtSuffix.Text = Me.Cell(Me.ListIndex,6)
end if
end if
Catch e as DatabaseException
System.DebugLog e.Message
end try
end if
{return focus on the selected record in the listbox}
No, you can use the same instance, checking the instance is still valid before pr4oceeding.
Instead of the RecordID, use the value of the PrimaryKey. You will get the value of the PrimaryKey in your first query to fill the Listbox by adding the name of the field in the query. Store the value of this key in the Listbox rowTag. When the user select a row in the LIstbox, read the rowTag and when you query the Database, add a WHERE clause to get that specific record. The query would look like
SELECT Field1, Field2, ..., FieldN FROM TableName WHERE keyFieldName = <the value of the rowTag>
Is the “Details” Database the same DB you are using to fetch the “Master” data from? If so, I would declare db as a property of the Window to avoid having to execute the checking for the file and connecting to the database each time a new Row is selected in the Master list. Of course you would have to Connect to the Database on Window Open or when first populating the Master table.
Just a thought.
Yes, it’s the same db table. The details are part of the rows; only the identifying field of the rows are displayed in the listbox. The rest of the fields I’d like to display in a ‘form’ format to the right of the listbox. My trouble is synchronising the display of the “details” with the selected listbox row. How indeed does one refer to those cells?
It’s a familiar and useful interface. It’s a simple problem, and I’m sure that Xojo has a way of addressing this master-detail synching. I just don’t know how, yet. There should be no need for opening another instance of the database, of another window, another listbox, and another record fetch, since the info is already in memory.
And thanks for alerting me that the db can be declared as a property of a window (didn’t know that! Which means it can be made a property of the app as well. Such flexibility!)
Nope, there is not. Xojo is not FileMaker, the later doing a lot of things for the developer … which is fine if it’s implemented the way you are looking for. FileMaker provides you with some functionalities that are baked in, but in no way you implement it your way.
Xojo is much more flexible: you need a Listbox with a single column but data displayed on more than one line, you can do it. I don’t say it’s simple for a beginner, but that can be done.
To compare again: FileMaker includes many functionalities baked in, but not much flexibility. Xojo does not provide loads of high level gadgets, but let you create them.
Yes, I looked at FileMaker (and Livecode, ReTool), and opted for Xojo precisely for the power it provides to the developer (and the absence of worrisome dependencies common in tools like Django, Rails, Flask, etc.) Just suffering from the proverbial birth pangs here. I do have clear ideas of the logic of my application–actually there’s nothing to it, just master and a chain of details – and the steps for implementation. It’s expressing these ideas and steps in Xojo language that I’m wrestling with now. And I’m making progress, thanks to all those who take the time to help out. Any collection of “How do I …” ?
Onm top of that @Dana_Brown has created videos about learning Xojo, she adds new one from time to time.
If you go with a standard Listbox, look at the Xojo Documentation (on-line) you should be fine with that. Trying and searching in the documentation and looking on the forum will help you a lot. That’S the way I learned Xojo a few years ago !
EDIT
The XDev magazine is published 4 times a year. It has great articles, the more complicated subjects are published in more than one publication.
Instead of the RecordID, use the value of the PrimaryKey. You will get the value of the PrimaryKey in your first query to fill the Listbox by adding the name of the field in the query. Store the value of this key in the Listbox rowTag. When the user select a row in the LIstbox, read the rowTag and when you query the Database, add a WHERE clause to get that specific record. The query would look like
SELECT Field1, Field2, ..., FieldN FROM TableName WHERE keyFieldName = <the value of the rowTag>
Here’s my code, but I don’t know what variable I have to put in the “select …” statement. Thanks for th help.
var rs as RowSet = db.SelectSQL("select * from words")
if rs <> Nil and not rs.AfterLastRow then
For each row as DatabaseRow in rs
lstWords.AddRow row.Column("word").StringValue, _
row.Column("root").StringValue
lstWords.RowTagAt(lstWords.LastAddedRowIndex) = row.Column("id").IntegerValue
next
end if
var ds as RowSet = db.SelectSQL("select * from words where id = <what do I put in here?>")
The idea is when you execute your first query, the one that fills the list box, on top of specifying the columns (fields) you wish to display you also specify the column that hold the primary key. As far as I understand, the field name is id.
When you add a row to the Listbox, you display all the fields from your query BUT id. You set the Tag for the added row to the value in id.
When the user selects a row in the Listbox, you will get the row id he clicked on. Then get the Tag set for that Listbox row and save it in a variable, say PrimKey. Then write your query to get the fields for the detail view this way:
db.SelectSQL(“select * from words where id = ?”, PrimKey)
<< But if you’ve already filled the ListBox colums with data from the database, why not get the data directly from the appropriate cell? >>
Yes, the record set already has the field values I want to display in an area outside of the listbox but still within the current window, with the fields of the selected row arranged in forms format. It seems a waste to do another fetch for just that record that is currently in focus. Basically, how do I fill those fields on the right with the values of the selected row in the list box. Thanks for the help.
Yes, it does make sense. And thank you for the explanation. Here’s my code to fetch a record and display the fields. This code is triggered by a DoublePressed event on a row in the listbox. The pk value is a valid ID in the corresponding table. Thanks for the help.
Sub DoublePressed()
Var pk As Integer = lstWords.RowTagAt(lstWords.SelectedRowIndex)
Var sql As String = "select from words where id = ?"
Var ds as RowSet
try
ds = db.SelectSQL(sql, pk). << - CRASHES HERE
Catch err as DatabaseException
MessageDialog.Show("DB Error: " + err.Message)
Return
End Try
txtWord.Text = ds.Column("word").StringValue
txtPrefix.Text = ds.Column("prefix").StringValue
txtPrefixRedup.Text = ds.Column("prefix_redup").StringValue
txtInfix.Text = ds.Column("infix").StringValue
txtRootRedup.Text = ds.Column("root_redup").StringValue
txtRoot.Text = ds.Column("root").StringValue
txtSuffix.Text = ds.Column("suffix").StringValue
End Sub
AlbertoD
But of course those lines are clickable! I didn’t know
‘Database Exception’ says:
Message: Operation cannot be completed because the database is closed.
Reason: Operation cannot be completed because the database is closed.
The listbox where the DoublePressed happens is within the window where the database is identified and instantiated, as shown below, and nowhere is there an instruction to close the database.
db = New SqLiteDatabase
var f as FolderItem = SpecialFolder.Resource(“bisoogo.sqlite”)
//Connect to a SQLite database
Var db as new SQLiteDatabase
db.DatabaseFile = f
