libreoffice base创建一个由另一个列表的值过滤的列表 [英] libreoffice base create a list filtered by another list's value

问题描述

我有一张省份的表格和一张ProvienceID的城市表格。在一个表单中，我想创建一个按挑选值列表筛选的城市列表。
我该怎么做？

我可以创建两个列表，但城市列表显示所有省份的所有城市，但我只想显示该省的城市我已经选择省份名单。

我有另一个表格用户，带有CityID和ProvinceID，我的表单编辑它，我需要保存选定的值省和城市列表中，不仅显示它的形式。

解决方案

创建两个名为Provinces和城市。

  ProvinceID名称
 ~~~~~~~~~~ ~~~~ 
 0南
 1北
 2大中西部
 3东南
 4西
 
 CityID名称ProvinceID 
 ~~~~~~〜 ~~~ ~~~~~~~~~~ 
 0大城市2 
 1大城市2 
 2乡村1 
 3山高地0 
 4沿海平原4 
 5 Metr opolis 2 
  

创建一个名为ProvinceNames的查询：

  SELECTNameAS省
 FROM省份
 ORDER BY省ASC 
  
 
 
 
创建一个名为Province of City的查询： 
 
 
  SELECTProvinces。NameASProvince，Cities。NameASCity
 FROMCities，ProvincesWHERECities。ProvinceID=Provinces 。ProvinceID
 ORDER BY省ASC，CityASC 
  
表单，根据查询ProvinceNames创建一个表格控件。
 
 
使用表单导航器（或表单向导）创建一个查询子表单City of City 。
 
 
 
  
 
 
 
右键单击子表单并选择属性的。在数据标签下：
 
 
 
 
 
链接从属字段省
 
 
 
 
 
也为子表单创建一个表格控件。现在，子窗体控件中显示的城市依赖于在主窗体控件中选择的省份。 
 
 
  
 
 
 
 编辑： 
 
 
以下是一个使用过滤表来存储列表框当前值的示例。创建两个名为Users和FilterCriteria的表。
  UserID Name ProvinceID CityID 
 ~~~~~ 〜~~~~~~~ ~~~~~~~~~~ ~~~~~~ 
 0 Person1 1 2 
 1 Person2 2 0 
 
 RecordID ProvinceID CityID 
 ~~~~~~~~ ~~~~~~~~~~ ~~~~~~ 
只有0 0 
  
我们还需要两个可存储在文档或我的宏中的基本宏。转到工具 - >宏 - >组织宏 - > 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 
如果IsEmpty（selectedItemID）Then 
 selectedItemID = 0 
 End If 
 conn = mainForm.ActiveConnection 
 stmt = conn.createStatement（）
 strSQL =UPDATEFilterCriteriaSETProvinceID =& selectedItemID& _ 
WHERERecordID='唯一''
 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 
如果IsEmpty（selectedItemID）则
 selectedItemID = 0 
 End If 
 mainForm.updateInt（lCityCol，selectedItemID）
 End Sub 
  
现在我们需要设置像这样的形式。在这个例子中，我使用了两个顶层窗体而不是子窗体。 ProvinceID和CityID文本框不是必需的，但可能会有所帮助，以防出现问题。
 
 
 
  
 
 
 
要开始创建此表单，请使用表单向导创建一个新表单并添加Users表中的所有字段。
 
 
 
现在，在表单导航器中创建一个名为CityForm的表单。内容类型是SQL命令，内容是：
  SELECTRecordID，ProvinceID，CityIDFROMFilterCriteria 
 WHERERecordID='唯一'
  
接下来，创建listboxProvince列表在MainForm下的框。数据字段为ProvinceID，列表内容为以下Sql。 
 
 
  SELECTName，ProvinceIDFROMProvinces ORDER BYNameASC 
  
最后，在CityForm下创建listboxCity列表框。数据字段为CityID，列表内容为以下Sql。
 选择名称，城市IDFROM城市WHEREProvinceID=（
 SELECTProvinceIDFROMFilterCriteria
 WHERERecordID='唯一'）
  
宏在每个控件的Events选项卡下链接。 
 
 
 
 
分配记录更改后将MainForm改为ReadProvince（）。 
 
 
将listboxProvince的Changed分配给ReadProvince（）。
 
将listboxCity控件的Changed分配给CityChanged（）。 b $ b 
 
 
 
结果允许我们选择省来过滤城市列表。所选省份和城市保存在用户表中。
 
 
 
  
 
 
还有另一种方法可能会更好，我没有时间去探索。而不是FilterCriteria表，将筛选器应用于城市列表。 ReadProvince（）中的相关代码看起来像这样。
  cityForm.Filter =ProvinceID =& selectedItemID 
 cityForm.ApplyFilter = True 
 cityForm.reload（）
 cityForm.absolute（0）
  
无论采用何种方法，完整的解决方案都需要复杂的宏编程。为了使它更容易，你可以决定使用一个不那么强大的简单解决方案。欲了解更多信息，请访问 https：//forum.openoffice .org / en / forum / viewtopic.php？t = 46470 。 
 
 
 编辑2  
 
 
 
需要更少查询的解决方案位于 https://ask.libreoffice.org/zh/question/143186/how-to-use - 用户选择的值从 -  combobox1功能于combobox2-select语句/？答案= 143231＃后-ID-143231 。第二个列表框基于值列表而不是SQL查询。
 
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.
解决方案
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.

                        
这篇关于libreoffice base创建一个由另一个列表的值过滤的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！