[Solved] Filter contents of listbox in LibreOffice base

[john_erlandsson]

Hi!

I have a form with two listboxes. The form is based on a table called work_order and contains the fields: id, machine_id and customer_id. the first listbox is for the field customer_id and has the following SQL query as list contents: SELECT "name", "id" FROM "customer".

The second listbox is for the field machine_id. machine_id references a table called machines, which contains the fields: id, name, customer_id.
Now I want my machine listbox to display all the name fields from the machines table where the customer_id field matches the one selected in the first listbox.

Here is an example of what I want my list content sql to look like: SELECT "name", "id" FROM machines WHERE "customer_id" = The value of customer listbox

Is this possible?

//John

[Villeroy]

http://user.services.openoffice.org/en/ ... 13&t=34002

[john_erlandsson]

Hi!

Thanks for your response. I am having a little trouble with this.
Base throws an error while opening the form.

The main form is the filter table as shown in the example. The subform inherits the filter variable as "g" and a listbox in the subform should show the filtered data with this query: SELECT "MACHINE_NAME", "CUSTOMER_ID", "ID" FROM "MACHINE" WHERE ( "CUSTOMER_ID" = :g OR :g IS NULL )

Here is the document:
http://dl.dropbox.com/u/12537421/test4.odb

This is the error:

SQL Status: S1000
Error code: -38

Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
at org.hsqldb.Trace.getStackTrace(Unknown Source)
at org.hsqldb.Trace.doAssert(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
in statement [SELECT "MACHINE_NAME", "CUSTOMER_ID", "ID" FROM "MACHINE" WHERE ( "CUSTOMER_ID" = :g OR :g IS NULL )]

Does anyone have an idea whats wrong?

I did not see the point in making the forms based on queries. As I understood it, the magic happened using the variables...

Any help is appreciated

//John

[Villeroy]

I can not see the problem with your parameter query. SInce it is a list box and you can not edit the entries of a list box you don't need any named parameters. It works with a more simple query filling the box with a read-only set from 2 tables:

Code: Select all

SELECT "MACHINE"."MACHINE_NAME", "MACHINE"."ID" 
FROM "MACHINE", "FILTER" 
WHERE "MACHINE"."CUSTOMER_ID" = "FILTER"."CUSTOMER_ID" AND "FILTER"."ID" = 0

Suggestion for the parent form (Filter):
Source: SELECT * FROM FILTER WHERE ID=0 [select only the distinct row to be used for this task]
Disable all other data options except "Analyse SQL"=Yes and "Modification"=Yes. The user must not insert nor delete and we don't want a navigation bar for the single row. "Analyse"=No means "direct SQL" and would give us a read-ony row set for the form.

[john_erlandsson]

Thanks! That did it.

It sort of works the way i want now. Except for the macro that should refresh the list... If I bind this macro:

Code: Select all

Sub Macro_Refresh (oEv as object)
     oEv.source.model.Refresh
End sub

To the mouse released event, it works. But then it refreshes the list when i make the selection.

So I tried this:

Code: Select all

Sub Macro_Refresh_List (oEv as object)
	oEv.Source.model.parent.getByName("lstMachine").Refresh()
End sub

Bound to the changed event in my filter list. But it throws the error:

Basic runtime error.
An exception occured.
Type: com.sun.star.container.NoSuchElementException
Message:.

My listbox is in fact called lstMachine, so it has to be something else... I have very little experience in basic and java, but i will probably look in to the Bean shell in the future. As for now, i am just trying to learn the database stuff. And i just want a macro that works.

Any help is appreciated.

//John

[Villeroy]

You bind the macro to what? The list box? So box.model.parent refers may refer to the form or it may refer to a table control if the list box is a column of a table control.
If it is a form, .getbyname("blah") refers to control "blah" on the same form. Use the form navigator to make sure that they are on the same form.
If it is a table control I don't know. Simply use any of the usual object inspectors.

[john_erlandsson]

They are in fact not on the same form. The filter listbox is int the main form, and the listbox to bee updated is in a subform. What would bee the recommended method of refreshing an object in a subform?

Edit: It is a form containing list boxes, not table views

[Villeroy]

Please, use an object inspector! It is required if you want to program anything by your own.

oCaller=oEv.Source.getModel()
oForm = oCaller.getParent()
oSubForm = oForm.getByName("SubFormName")
oBox = oSubForm.getByName("lstMachine")
oBox.Refresh()

[john_erlandsson]

Thank you.
This is working... Trying to make sense of the macro documentation now...

[Villeroy]

Make sense of the form navigator which reflects the hierarchy of forms, subforms and controls. Install the MRI extension. Configure it to use http://api.openoffice.org/ in your preferred web browser. Bind your form events to Basic macro MRILib.Module1.mri