What I’m doing would be easier to explain with a library analogy.
Let’s say I have a database of books in a table with a category field. And I have a table called checkouts that list the date of a checkout, as well as the id number of a book, and I have a form document with two listboxes at the top, cascading, category and book. The cascading I achieved with a filter table that the form of the first dropdown fills in, and that the sql query for the second listbox references. There’s a table control linked to the checkouts table, and it only shows the checkouts that pertain to the book selected in the second listbox. Every checkout I enter in the table control automatically sets the book field for that record in the checkouts table to the id of the book selected in the second listbox. already set up, and has been working great for years, but now I need to add more fields to the upper part of the form document that fill in fields in the book table. As I go to edit the form document, I discover that I had set up the book listbox to a book filter table, rather than simply setting it up to the book table. I’m struggling to get the fields I’m adding to affect the book table.I can’t figure out why the form of the book listbox had to be tied to a filter table. If I switch that form to linked to my book table, the table control stops working right. It seems the filter table is necessary, but I can’t figure out how I set it up that way and got the table control to work. (Much time has passed, and I’ve had brain surgery and three kids since then.)
I’m uploading a screenshot of my Form Navigator. Just imagine that where it says “MeasurementData” it says “Book” for the library analogy.
I remember understanding filter tables ( and using the navigator) much better after this video. https://youtu.be/Jy9CV1yD7ew
Even if you don’t understand german I guess you can follow the steps…
Hello,
The image presented presents no help in discerning your problem. In your general description, The first list box has a list of all categories. Once a selection is made it is stored in the filter table. Then there must be either a macro or button to refresh the second list box. This second list box is most likely based upon a query which then utilizes the saved information in the filter table to limit the books listed to only that category.
.
This seemingly has nothing to do with you entering/establishing some other information. These are simply list boxes to be able to select records for display elsewhere on that view. Where and what and how needs more information about what you have and what is wanted to be done.
countries_cities.odb (52.9 KB)
The first form has cascading list boxes for continent, country, city. The last listbox pushes a city ID into a new voyage record. The additional list of participants demonstrates a many-to-many relation (other topic).
Form “Voyages_simple” has a listbox in the first column of the table control where you enter a 3 letter country code, a space and the city name. The tall multi-line listbox on the left side does the same.
Third form: Instead of picking continents, countries, cities from a listbox, you can also pick them from table controls.
Thanks, everyone, for pitching in! After a little more digging, I determined that, while the filter table wasn’t being used as a conventional filter table (I had labeled it as a filter table, it was being used to make all the checkouts associate with the selected book. The “QueryForm” under it was set with its contents to a sql command that selected what was selected in the book listbox, and the book ID was used to link to the form containing the checkouts table control. This will prove most useful in adding the controls I want to add.
There is nothing “conventional” with these filter tables. They are a creative solution for a missing feature.
- Form controls write data into a table. The are not designed to filter anything.
- Subforms show filtered records depending on the parent form’s current record which needs to be stored (not in edit mode). A new subform record inherits the same criteria values from its parent form that are used to filter the existing records.
Once you accept these 2 simple rules of the game, you get the idea of a dummy record storing filter criteria which then is able to filter a subform.
Are you saying the “missing feature” is something that should be present, or something that should be neither present nor creatively compensated for? By my counting, they are a creative solution for two missing features:
- Cascading Listboxes
- Subforms that allow for entry and inherit certain fields from their parent form. (As it stands, this requires a SQL Query Form to be inserted between the subform and the parent form, and the SQL Query Form requires a filter table for their query to reference (as in my original post in this thread), as well as a mechanism for refreshing the query form. I would love it if someone would prove this wrong and explain a better way to acheive this.
You may not have the privileges to add a filter table to your company database. A filter form should work without any addition to the database structure. The built-in form based filter is the right approach, haowever barely usable.
There should be some kind of cache for argument values passed to query parameters.
Do you mean having a macro adjust the SQL for the list in the secondary listbox?
No, I mean the form based filter on the form navigation toolbar.
Wow, I guess I wasn’t aware such a thing existed.
Well, the navigation toolbar includes (almost) all the built-in tools availlable to the user of a database form. Navigation, refresh, save, undo, delete, filter, sort and grid view. While in grid view, you have an additional filter button analog to Calc’s standard filter.
In these, such dummy/filter tables are still needed to act as a cache for the selected value, but I just came across a form document I had set up a couple years ago where I did this without the query form in between by simply selecting the necessary fields for the subform under the data tab for “Link master fields” and “Link slave fields”
Good enough for simple A=B and X=Y conditions but not good enough for Date>=Start_Date AND Date<=End_Date AND Lower(“Text”) || ‘*’ LIKE Lower(Starts_With) where you search for a date range and a text starting with a given string case-insensitively.
True enough. The query form is needed for complex relationships to the subforms. Thanks for the input!