You’re welcome and keep the DBKit feedback coming. I definitely want to continue improving it.
Hi Geoff
Could you explain the related tables concept in a little more detail or link to an example project for DBKit Web 1.06b?
Say you have a Single Page concept but with 2 list boxes for Customers and Invoices, and 2 search fields to filter those results and a set of Customer textfields, all on one page. You recommended 2 DBKit connectors CustomersConnector and InvoicesConnector. The CustomerConnector uses Me.BindControls(Self) to display all the controls on the screen. Do we need to bind the related results to the Invoices listbox using BindListBoxControl or leave it bound to the wrong Connector? Is there an easier way to bind the controls on the page to two different Connectors?
How do we use the LoadingRow event in the CustomersConnector, I assume using the Event handler and Event Definition? Doesn’t seem to work for me. But SelectionChanged seems to work to fetch the related Invoices via the id at me.RowTagAt(me.SelectedRowIndex). Perhaps this not the best way to do it because there are some error messages editing Customers.
Sorry, I’m not quite connecting the dots. Forgive my ignorance.
Yes, you would use two Connectors, one for each table. The idea is that in the LoadingRow event of the CustomersConnector, you would tell the InvoicesConnector to do a query on the Customer ID to load the related invoices. To use the LoadingRow event, just add it to the connector once it’s on the layout. LoadingRow is better because it only fires when a row is being loaded whereas SelectionChanged fires more often.
Does this help?
I need to update the example project to demonstrate loading rows from a related table.
Yes, I do understand that much, but perhaps I am missing something else using the LoadingRow event. Do I need to add an Event Definition for it? Could you show some code or screen shot of the LoadingRow event and I will use that in lieu of an example project?
Correct. Just add the LoadingRow event handler to the Connector on the layout.
Got it thanks! In the LoadingRow event I fetch the related records and tell the Invoice listbox to add those rows. I needed to rebind the Invoice listbox to the InvoiceConnector. I return true at the end of the event. This works.
But I have messed up the loading of the Customer columns into the TextFields when the row is selected. Could you give me an idea of what I should look at to fix that - it was working before.
I would need to see it. Is there an example project you can send me? You can send it privately instead of on the public forum.
Thanks Geoff, but I switched to separate search and edit windows, and this seemed to help. My UI was too crowded anyway!
I did find a problem where I wasn’t selecting the id from the database in the SQL select, so I fixed that.
With these together all is working well now. Thanks!
Excellent. And having it all in one window can get crowded if you have a lot of fields so it sounds like you made a good decision.
Does anyone have an example of what should go into the LoadingRow Event? Does the connector2 need to be passed or used as a ‘variable’ to send data into?
There is no example in the DBKit example file that I could find.
I am using Postgresql in web app. Where the two Dates are stored in a different table.
Thanks
Loading row is called when the connector is well, loading a row. So for example, if you needed a second connector to load a list of related rows from another table, you could trigger that in the LoadingRow event of the first connector. I need to add another table to the example project to show this off.
An example in the project would be great. As I still am unsure how to setup the second connector.
Add a second connector for another table then when the LoadingRow event fires on the first connector, you can tell the second connector to loaded related rows for its table.
Yes, please. I am still really confused after many hours. I have tables, primary keys, foreign keys, etc but I don’t have a way to, for example, click on a name in the customers (Customers table) from EddiesElectronics and have it show me some notes (Notes table, which I added) in another list box.
Basically, how can I display, enter, and alter related data. It is not obvious to me.
My relational database experience has been with Filemaker (may years ago), if that helps.
Sorry if I am being dense.
Regards,
Tom
Further confusion.
First, this is all based on the tutorial for dbKit. I am trying to go beyond the tutorial to a relational db model. So I created a “Notes” table in the EddiesElec… database using SQLite Studio.
CREATE TABLE Notes (
ID INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,
CustID INTEGER REFERENCES Customers (ID),
Note TEXT
);
Using SQLiteStudio, I then added a single entry:
I then added a TableConnector2 to the project, with the table property set to Notes.
I want the note associated with Customer ID10001 to display, one note per line, in the DesktopListBox
So I understand that the TableConnector1.LoadingRows event handler is important. However, If I add just a bit of code to the TableConnector1.LoadingRows event handler*, like this:
The data from Eddiesdb no longer loads:
If I delete the code in Loadingrows, the tutorial works as expected.
Why?
And how can I add Xojo code and sql code to that I get the expected result?
I am new at all of this database stuff. Any help is greatly appreciated.
Regards,
Tom
*Database relations for EddiesElec…
Table: Customers
Primary Key: ID
Table: Notes
Foreign Key: CustID
TableConnector Properties
TableConnector1
Table: Customers
TableConnector2
Table: Notes
I’ve never used DBKit and don’t plan to, but I note that LoadingRow must return a boolean. Perhaps to indicate whether or not you’re handling the event. Try returning true or false. One of the two should have an effect
In the LoadingRow event you need to tell the TableConnector that is responsible for the Notes table to do a query to find the related rows.
FWIW, in the major update to DBKit that I’m working on now (which will come out when 2025r1 is released) this is all automatic. I give you a way to define relationships between tables and then related rows are loaded automatically.