Boundfield property

[marus_b]

Hello everyone,

Within the same form, I created a form with two table controls, each of them had one text box column within. One of them was bound to a table in a database, the other was left unconfigured.

I tried to programmatically configure the uninitialized column but it looks like the boundfield property is what I’m missing. The code, invoked by a push button in my case, is as follows:

dim oForm as object: oForm = oEvt.Source.Model.Parent
dim oTextBox as object: oTextBox = oForm.getByName(“Table Control 1”).getByName(“Text Box 1”)

oForm.commandType = com.sun.star.sdb.CommandType.COMMAND
oForm.command = “select * from database_table_name”
oTextBox.datafield = “database_column_name”
oForm.reload()

I’m reading in the documentation about the forms in Libreoffice developer’s guide, specifically about the Data Awareness aspect of it. But I’m not a professional programmer, and some of these conceptual aspects are still beyond my comprehension level.

As a matter of fact, an easy and pragmatic solution is to simply establish the initial configuration as the form is being built. At which point, the code above is able to provide the necessary service by switching in between table as needed. I just thought I’m going to ask to gain a bit more understanding of the UNO API.

[CRDF]

I guess this should be along this line.

oForm.command = "select * from ""database_table_name"""

Dim oTheColumn As Object
Dim aux As [data type of the record]
[…]
oTheColumn = oForm.Columns.getByIndex([index ]) ’ 0 based index
or getByName(“
These are the columns returned by the SELECT.
The Form’s recordsource.
 
aux = oTheColumn.getString() ’ or adequate data type
oTextBox.Text = aux
 
NOT TESTED

[cpb]

@marus_b,
this is something that most of us will never have previously done, a sample db would have been helpful.
I have uploaded a working example.
this is the sub used:

Sub Initialise(oEv as object)
	'set form properties: commandtype(table), command(table name), order(sort order)
	'set datafield(bound field) for text box
	dim oButton as object, oForm as object, oTextBox as object
	oButton = oEv.Source.Model
	oForm = oButton.parent
	oForm.commandtype = 0
	oForm.command = "tFruits"
	oForm.order = "Name asc"
	oTextBox = oForm.getByName("Table Control  1").getByName("Text Box 1")
	oTextBox.datafield = "Name"
	oForm.load
'	oForm.reload 'enable this line to remove edit icon
End Sub

MissingFormValues.odb (13.4 KB)

[Villeroy]

@cpb

this is something that most of us will never have previously done,

One reason why someone wants to do that could be a many-to-many relation implemented by means of a non-normalized table.

ID    Name    Prop1    Prop2    Prop3    ...

The whole approach is plain wrong.

Many tables for equal structures is another wrong approach.

[CRDF]

@cpb

a working example.

I was trying to solve it the way below, following proposed SELECT Command, and at first just 1 TextBox in the Form.
Key was your .load!
So, no need to setup an explicit connection?
 

[marus_b]

Thank you @cpb, this worked out great. I should have used the “load” command instead of “reload”.

@Villeroy without deviating too much from the original subject of this post… I have several (5 in this case) supporting tables which describe several different aspects of a particular item. In my case it is a type of material that we are working with, and the criteria are (color, format, finish… etc). Each of these tables are just a primary key and a value. I do need to add additional values to each of those 5 tables from time to time. In order to do that I wanted to utilize one form and just switch between these tables programmatically as I needed. From what I have understood in your comment, this is not a correct approach. Would creating a one single table with a third column, which would allow me to filter the data I need for a particular criteria, be a more commonly practiced approach?

[Villeroy]

One foreign key for each property:
ask128542_1.odb (22.0 KB)

[CRDF]

@Villeroy

One foreign key for each property:

Or perhaps consider adding a 7th table with date column – analogous to the “joins” of many-to-manys – if convenient to keep record of changes in the set of properties of each material along the time (?)
Of course, if each time some “material” changes any property it receives a new ID, no sense for this. But anyway, a way to keep records of changes in the set of properties and IDs could be convenient

[Villeroy]

@CRDF

Of course, if each time some “material” changes any property it receives a new ID, no sense for this. But anyway, a way to keep records of changes in the set of properties and IDs could be convenient

Like changing prices in some invoicing application where you need comparative statistics based on prices in the past.
Invoices_Articles_Prices.odb (164.9 KB)

[marus_b]

This makes so much more sense! It’s funny how a person can get stuck with this notion of trying to resolve something in a particular manner, where there is a much easier and more practical solution instead. Thank you all once again for all this.