If I'm not mistaken, when doing a form-based filter in a form containing a subform, if the user enters some criteria in one of the fields of the main form and another criteria in one of the fields of the subform, the current behaviour of Base is to first filter the items (table/query) in the first form and then, when visualizing each item using the navigation bar, filter the linked records of the subform by the specified criteria. For example, suppose we have a customers table including a zip code column/field, and a 1:N relationship with a year_sales, in which, for each customer/year (compound key) we have the sum of the total sales for that customer. Assume we also have a form for editing customers details, including a subform for editing the data for year-total sales using a table control or any other similar mechanism. That is: a single form allow us to add/edit all the data for that customer. With the current behaviour/implementation, however, the only way to filter all customers with a combination of zip codes and total sales wound be to (a) define a query to obtain them (not using form-based filter) or (b) use some kind of single-record-search-table in a dedicated form. It would be great to use *the same* add/edit form for the customer/sales tables in order to perform this filter. In this case, the behaviour should change and, instead of filtering first by zip code and, after that, when visualizing each found customer, filtering its sales data by amount, the desired behaviour should be to filter *simultaneously* by all the conditions, that is, obtaining the list of customers that accomplish with any of the specified (combined) filters. Of course, this should not override the original, working-as-spected, current implementation of form-based filtering. Perhaps the easiest way to allow this would be to allow the user to specify that a criteria entered for a subform field/column should work as a primary filter by using right-click and some specific preference, and that should be either marked with some visual hint in the form-based tree or be in a separated subtree in that tree, that hosts all subform criteria that apply in first level.
Wow, sounds like a pretty complex change (in terms of code changes that would have to occur) you're requesting there. I have no idea whether this is something that could be considered for a RFE. I will put a dev on CC, perhaps they can add their own thoughts on the matter.
If I understand correctly, you want the resulting filter on the mainTable (the table that the main form sources from) to be something like: SELECT * FROM mainTable WHERE EXISTS (SELECT * FROM subTable WHERE mainTable.masterField = subTable.slaveField AND condition_on_subTable) This makes extra much sense when the relationship is 1:1, and thus logically the "subForm" has no row navigation, and is thus "invisible" to the form user (not the form designer). In my experience, in this particular subcase, that's how users expect it to work, actually. Also the search.
Yes, that's a pretty good explanation. I don't know whether a "WHERE EXISTS" or some kind of JOIN must be used, I guess that depends on performance, but that's exactly the idea: the results on the main form must be filtered ALSO by conditions defined in the subforms. The case 1:1 is a case where this makes a lot of sense, but it also makes sense in 1:N or M:N cases. In 1:N cases, for example: assume we have "Companies" and "Branches", each company has 1..N branches, and each branch belongs to 1 company. Branches include a postal address, and thus, a city. It seems reasonable to be able to filter the main form (Companies) trying to select those that have branches in some specific cities, regions, etc. And that data lives in the subform (branches table). The same is true if we add a third subform corresponding to a "year_sales" table related to "branches". If a user wants to select companies with a branch with year_sales higher than a given figure and with branches in some specific cities, this proposed enhancement would allow them to do it from the main form/form-based-filter. This particular example, also, exposes some undefined behaviour that should be clarified. For the company -> branch -> year_sales case, defining a "City = Barcelona" AND "year_sales > X" filter could result in two possibilities (additional to the current behaviour): a) Select companies that have a branch located in Barcelona and have some branch with year_sales > X or b) Salect companies that have a branch located in Barcelona with that specific brach having year_sales > X I guess that the proposed GUI change should allow to specify to which parent-form the subform condition should apply (when we want it to apply to parent forms) For the M:N case, just think about a item <-> tags relationship. Being able to select some items by some attributes AND having some tags assigned also looks reasonable. As I stated in my original report, AFAIK, the only way to perform those filters right now is to define an ad-hoc/synthetic query and an associated form to see the data upside-down (duplicating efforts/work) or to use some kind of single-row search-table that also uses synthetic data (the filter configuration) to filter the orginal form. The problems with those appropaches are that (a) they have to be rethought/redone to add/remove search criteria and (b) are not business-user friendly.