Set up a listbox for data entry with value list filtered by another listbox value

English
#1

Hi, I’ve searched a lot of different post and forum entries on the topic. Probably it will end up in a duplication, but probably in that way I will find a similar answer.

I have a postgres database for which I need to create a data entry form with LibreOffice.

I created a form with listbox1, which retrieve values from Table 2 to populate a field of Table 1. All went fine.

Now I need to populate another field of Table 1, retrieving data from Table 3. Table 3 is linked to Table 2 with a foreign key. I need to create a listbox2 that retrieves only the value on Table 3 with a FK equal to those already selected in listbox1.

I need to do that WHITOUT MACROS.

Thanks in advance for the assistance.

#2

Listbox 1 should be chosen and listbox 2 should be changed, if listbox 1 had changed values? This could only work if value from listbox 1 is saved in your database and code for listbox 2 reads this value from your database. Then you have only to enter listbox 2 and press “Refresh Control” on the navigation bar.

Why don’t you want to use macros for this solution?

#3

Thank You for the fast reply.
So I have to enter the value in list box1, save, then enter the value in listbox2 retrieved with a query that use the value stored in the field from listbox1 as a WHERE statement?

As for the macros:
I want to work in a way that I can understand. I can understand sql queries, but at the moment I cannot understand macros, I have no time to study them and I have to distribute the form to other people that have to populate the database. Everything has to be done “for yesterday” and I have no way to get IT assistance.

#4

Problem will be: How to get the saved value of list box 1? But this will be the only way without using macro code.
Might be you are using this only for new entries in a table with an autovalue primarykey. So you could get the right value by looking for max value of primary key.
Other possibility might be to get the value from some log files of PostgreSQL, but I don’t know enough from PostgreSQL to get them.

#5

Probably the only solution, as you have already said, is to use a macro. I have to resign myself and spend time (that I would not have) to learn how to use them.
Thank you

#6

Load up an example Base-file (internal database) with a form, containing 2 list boxes, where the first list box should filter the second. Both list boxes should contain the SQL code for the whole entries they could show. You will need a little bit data inside.

I will try to set the filter the way you won’t need to save any data from the first list box to change what second list box should show.

#7

In the end I’ve managed to understand how macros works and, reading and mixing some other forums entries, I’ve created a macro:

Sub FiltroMaterialeForma()
	Dim oDoc as Object
	Dim oDrawpage as Object
    Dim oForm As Object
    Dim oMateriale As Object
    Dim oForma As Object
    Dim sSelectedValue As String
    Dim sQuery As String
    
    oDoc = thisComponent
    oDrawpage = oDoc.drawpage
    oForm = oDrawpage.forms.getByName("CIRCE_REPERTO")
    oMateriale = oForm.getByName("Materiale")
    oForma = oForm.getByName("Forma")
    sSelectedValue = oMateriale.SelectedValue
    sQuery = "SELECT ""forma"", ""formid"", ""matid"" FROM ""circe"".""forma"" WHERE ""matid"" = " & sSelectedValue & " "
  
    oForma.ListSourceType = 3 
    oForma.ListSource = array(sQuery)
End Sub

The problem is that it works only once: when I use new record button, I select the first listbox (materiale) and the second listbox(forma) is correctly filtered. But if I wanto to insert a second value, the filter remain with the value of the first input.

I’ve tried to put the macro on different events of second listbox and even on events of the main form. Nothing changes.

#8

You have to refresh the content of the list box when the code has been changed.
oForma.refresh (at the end of the code) will help.

#9

It works.
I cannot thank you enough for your assistance and your time. And also for indirectly making me realise that perhaps it was better to strive for a solution beyond my limits.

#10

Without macros: Apache OpenOffice Community Forum - Cascading list boxes with and without macros - (View topic)

#11

Thanks.
Unfortunately the example db in the link does not work properly.
And my db is a little different.
I think the solution with macros proposed above is actually the best one, when amended with RobertG’s suggestions.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.