libreoffice base create a list filtered by another list's value

I have a table of provinces and a table of cities with ProvienceID. In a form, I want to create a list of cities filtered by selected value of provience list. How can I do that?

I can create both lists but Cities list shows all cities from all provinces but i want to show only cities from the province that I have selected in Provinces list.

I have another table "Users" with "CityID" and "ProvinceID" that my form edits it and I need to save selected values of Province and City Lists in it, not only show it in the form.

Solution

Create two example tables named "Provinces" and "Cities".

ProvinceID  Name    
~~~~~~~~~~  ~~~~
0           South   
1           North   
2           Large Midwest   
3           Southeast   
4           West    

CityID  Name              ProvinceID
~~~~~~  ~~~~              ~~~~~~~~~~
0       Big City          2
1       Very Big City     2
2       Rural Village     1
3       Mountain Heights  0
4       Coastal Plains    4
5       Metropolis        2

Create a query called "ProvinceNames":

SELECT "Name" AS "Province"
FROM "Provinces"
ORDER BY "Province" ASC

Create a query called "Province of City":

SELECT "Provinces"."Name" AS "Province", "Cities"."Name" AS "City"
FROM "Cities", "Provinces" WHERE "Cities"."ProvinceID" = "Provinces"."ProvinceID"
ORDER BY "Province" ASC, "City" ASC

In the form, create a table control based on the query "ProvinceNames".

Using the Form Navigator (or the Form Wizard), create a subform for query "Province of City".

Right-click on subform and choose Properties. Under Data tab:

Link master fields "Province"
Link slave fields "Province"

Create a table control for the subform as well. Now, the cities shown in the subform control depend on the province selected in the main form control.

EDIT:

Here is an example using a filter table to store the current value of the list box. Create two more tables named "Users" and "FilterCriteria".

UserID  Name     ProvinceID  CityID
~~~~~~  ~~~~~~~  ~~~~~~~~~~  ~~~~~~
0       Person1  1           2
1       Person2  2           0

RecordID  ProvinceID  CityID
~~~~~~~~  ~~~~~~~~~~  ~~~~~~
the only  0           0

We'll also need two Basic macros which can be stored in the document or in My Macros. Go to Tools -> Macros -> Organize Macros -> LibreOffice Basic.

Sub ReadProvince (oEvent as Object)
    forms = ThisComponent.getDrawPage().getForms()
    mainForm = forms.getByName("MainForm")
    cityForm = forms.getByName("CityForm")
    listboxProvince = mainForm.getByName("listboxProvince")
    listboxCity = cityForm.getByName("listboxCity")
    selectedItemID = listboxProvince.SelectedValue
    If IsEmpty(selectedItemID) Then
        selectedItemID = 0
    End If
    conn = mainForm.ActiveConnection
    stmt = conn.createStatement()
    strSQL = "UPDATE ""FilterCriteria"" SET ""ProvinceID"" = " & selectedItemID & _
             "WHERE ""RecordID"" = 'the only'"
    stmt.executeUpdate(strSQL)
    listboxCity.refresh()
    lCityCol = mainForm.findColumn("CityID")
    currentCityID = mainForm.getInt(lCityCol) 
    cityForm.updateInt(cityForm.findColumn("CityID"), currentCityID)
    listboxCity.refresh()
End Sub

Sub CityChanged (oEvent as Object)
    listboxCity = oEvent.Source.Model
    cityForm = listboxCity.getParent()
    mainForm = cityForm.getParent().getByName("MainForm")
    lCityCol = mainForm.findColumn("CityID")
    selectedItemID = listboxCity.SelectedValue
    If IsEmpty(selectedItemID) Then
        selectedItemID = 0
    End If
    mainForm.updateInt(lCityCol, selectedItemID)
End Sub

Now we need to set up the form like this. In this example, I used two top-level forms instead of a subform. ProvinceID and CityID text boxes are not required but may be helpful in case something goes wrong.

To start creating this form, use the form wizard to create a new form and add all fields from the Users table.

Now, in the Form Navigator, create a form called "CityForm". Content type is SQL command, and Content is:

SELECT "RecordID", "ProvinceID", "CityID" FROM "FilterCriteria"
WHERE "RecordID" = 'the only'

Next, create the "listboxProvince" list box under MainForm. Data Field is "ProvinceID", and List content is the following Sql.

SELECT "Name", "ProvinceID" FROM "Provinces" ORDER BY "Name" ASC

Finally, create the "listboxCity" list box under CityForm. Data Field is "CityID", and List content is the following Sql.

SELECT "Name", "CityID" FROM "Cities" WHERE "ProvinceID" = (
    SELECT "ProvinceID" FROM "FilterCriteria"
    WHERE "RecordID" = 'the only')

Macros are linked under the Events tab of each control.

Assign "After record change" of the MainForm to ReadProvince().
Assign "Changed" of listboxProvince to ReadProvince().
Assign "Changed" of listboxCity control to CityChanged().

The result allows us to select the Province to filter the list of Cities. Provinces and Cities that are selected are saved in the Users table.

There is another approach which may be better that I have not had time to explore. Instead of the "FilterCriteria" table, apply a filter to the Cities list. The relevant code in ReadProvince() would look something like this.

cityForm.Filter = "ProvinceID=" & selectedItemID
cityForm.ApplyFilter = True
cityForm.reload()
cityForm.absolute(0)

Whatever approach is taken, a complete solution requires complex macro programming. To make it easier, you may decide to use a simpler solution that is not as powerful. For more information, there is a tutorial at https://forum.openoffice.org/en/forum/viewtopic.php?t=46470.

EDIT 2

A solution that requires fewer queries is at https://ask.libreoffice.org/en/question/143186/how-to-use-user-selected-value-from-combobox1-in-combobox2-select-statement/?answer=143231#post-id-143231. The second list box is based on a list of values instead of an SQL query.