[Solved] Filter contents of listbox in LibreOffice base

Discuss the database features

[Solved] Filter contents of listbox in LibreOffice base

Postby john_erlandsson » Fri Jun 17, 2011 9:14 pm

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
Last edited by john_erlandsson on Wed Jun 22, 2011 12:09 am, edited 1 time in total.
LibreOffice 3.3.2.2-9.fc15
john_erlandsson
 
Posts: 5
Joined: Fri Jun 17, 2011 8:53 pm

Re: Filter contents of listbox in LibreOffice base

Postby Villeroy » Fri Jun 17, 2011 9:18 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26981
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter contents of listbox in LibreOffice base

Postby john_erlandsson » Sat Jun 18, 2011 1:13 am

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
LibreOffice 3.3.2.2-9.fc15
john_erlandsson
 
Posts: 5
Joined: Fri Jun 17, 2011 8:53 pm

Re: Filter contents of listbox in LibreOffice base

Postby Villeroy » Sat Jun 18, 2011 7:59 am

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26981
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter contents of listbox in LibreOffice base

Postby john_erlandsson » Sun Jun 19, 2011 7:40 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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
LibreOffice 3.3.2.2-9.fc15
john_erlandsson
 
Posts: 5
Joined: Fri Jun 17, 2011 8:53 pm

Re: Filter contents of listbox in LibreOffice base

Postby Villeroy » Sun Jun 19, 2011 7:58 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26981
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter contents of listbox in LibreOffice base

Postby john_erlandsson » Sun Jun 19, 2011 8:04 pm

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
LibreOffice 3.3.2.2-9.fc15
john_erlandsson
 
Posts: 5
Joined: Fri Jun 17, 2011 8:53 pm

Re: Filter contents of listbox in LibreOffice base

Postby Villeroy » Sun Jun 19, 2011 8:42 pm

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()
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26981
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter contents of listbox in LibreOffice base

Postby john_erlandsson » Wed Jun 22, 2011 12:08 am

Thank you.
This is working... Trying to make sense of the macro documentation now...
LibreOffice 3.3.2.2-9.fc15
john_erlandsson
 
Posts: 5
Joined: Fri Jun 17, 2011 8:53 pm

Re: [SOLVED]Filter contents of listbox in LibreOffice base

Postby Villeroy » Wed Jun 22, 2011 12:31 am

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26981
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests