I have a question about multiple filters in Base, and before going any further, I thought I should ask the experts.
.
Here are the tables involved in the view assigned to a subform :
JT_TitlePerson
JOIN PT_Person
JOIN RT_FunctionType
LEFT JOIN JT_PersonProfession
LEFT JOIN RT_Profession
.
Is it possible to filter this view by both Profession and Function, or separately?
No problem. A view appears for Base GUI like a table, which couldn’t be edited. So the filter will work for every field in the GUI.
So, from a dropdown list, let’s say “FilterProfession”, I can first apply a filter based on a given selection (Director). And then, from another dropdown list, “FilterGender”, I can apply a second filter.
.
Do the dropdown lists need to be located within the subform, or do they require a separate subform?
When reading “dropdown”: Which filter do you want to use:
A dropdown list belongs to a form. There are no dropdown lists in tables nor queries because tables and queries show raw data only because, unlike MS Access, Base has no lookup fields.
http://access.mvps.org/access/lookupfields.htm
In Base, a lookup list is a field on a form represented by a list box. List boxes do not filter. They write some value into a field that has been selected by some string. Select string, write ID value to foreign key.
First solution:
There is a button labeled “Form based filter” on toolbar “Form Navigation”. It switches the current form into a mode where you can enter filter criteria. Now your listbox takes a filter criterion. In this mode there is another toolbar with buttons to apply the filter, cancel the filter and showing a little window of filter criteria.
There are more solutions to this problem.
I think I found a way to do it. So I created a view which is the data source for a subform. The drop-down lists each have SQL query for the data. They are placed in the subform and call a macro via the ‘Modified’ event.
.
The macro manages according to the selected list. This works well, except for record changes where the filters only work if reapplied again.
.
However, I don’t really know if there is a simpler way to perform filtering that involves more than a table, other than going through the view…???
Views can not be filtered by parent forms. Use a query instead. The query needs to be in “parsed mode” in order to be filtered. Views belong to the database realm. They are always interpreted by the database engine (“direct mode”).
Base-macros are a no-go.
You are right an SQL Query or SQL Command works with filtering. No need for Vue, but when changing records, filtering is not reactivated, so the table control is empty.
The query needs to be in “parsed mode”
What do you mean by “parsed mode”?
Parsed mode = the “normal” Base GUI’s mode. No “Execute SQL directly” button pressed.
To pass parameters via GUI this mode is necessary, like
[…] WHERE “TheField” = :parameter
Some things like UNION SELECTS or User Defined Functions are not allowed this way.
As a work-around, you can create a UNION query (or view) and a parsed one SELECT * FROM "union_query"
https://ask.libreoffice.org/uploads/short-url/i12usZeALIOOQuEeW0YnlBsEwry.odb
Main form is linked to a distinct filter record (FID=1) storing filter criteria.
Subform is linked to a parameter query on one table, so the returned record set is editable.
Another subform is linked to an aggregation (sum, count etc).
Villeroy,
If, in your “Persons with subform” form, based on a person X, let’s say Zorro, I wanted to apply several filters to the “SubForm” using dropdown lists that allow me to select a specific item.
.
For example, “listbox-FoodType” and another “listbox-FilterMonth”.
The “listbox-FilterMonth” list would display the months regardless of the year.
.
Would this be possible?
Do you want the 2 ListBoxes to be independent, or LB2 (“Function”) be filtered by selection on LB1 (“Profession”)?
They should be independent in the sense that I can filter with one or the other or both.
Using form “Filter Data” as is, you can enter a pair of dates (2005-7-1 and 2005-7-31) with a product.
Using a year, a month and a product, the parameter query would look like:
SELECT * FROM "Data"
WHERE YEAR("D") = :paramYear OR :paramYear=NULL
AND YEAR("D") = :paramMonth OR :paramMonth=NULL
AND "CID" = :paramCategory OR :paramCategory=NULL
The filter criteria would be stored in INT1, INT2 and INT3. INT3 needs to be added to the filter table.
“January” and “Banana” with year missing shows the results for Banana in all Januaries.
“2005” and “Banana” with month missing shows the results for Banana in 2005.
“January” and “2005” with product missing shows the results for January 2005.
All criteria missing shows all records.